Master Reference Tables

Availability: \\SSIS\IMPORT_EXPORT\Master_Ref_Tables.xlsx

The master EIQ2 import excel file, encompasses the underlying management of implementing master reference data tables and provides a simplified interface to change previously defined attributes. Master reference data is generally composed of static identifiers such as the names of countries, gender types etc. Such data is reasonably constant but can change.

Master reference data is frequently considered as a subset of master data. Having sourced core data to populate a master reference data set, i.e.: “Country Names” and “Country Codes”, it may well be, where related values do not exist at source, that additional or alternate information is needed, i.e.: “Regional Area” or a different spelling (“UK” to “United Kingdom”). EIQ2 manages the Master Reference Data via a circular ETL process.

Initially, EIQ2 manages the population of the landing pad staging area through ETL, by pulling new or changed “Core” records from source. In parallel, it maps to the same area of the landing pad staging data, a file system excel file. The excel file has additional columns, where alternate information can be added. Having made changes to the excel file, a secondary ETL process picks up the excel changes and applies these to the landing pad staging area, for upstream visibility.

Edit the excel file: “\\SSIS\IMPORT_EXPORT\Master_Ref_Tables.xlsx” and following “Data > Refresh”, add the following entries:

• Category – Groups the newly generated Master Reference Data into sub categories
• Master Ref Table Name – Name for the newly generated Master Reference Data
• Master Ref Query – SQL “SELECT” query, to populate the Master Reference Data. Note: “Lookup_ID, Lookup_Code, Lookup_Name and Lookup_Category” output fields are compulsory

Review your changes and save the excel file, and then run the SQL agent scheduler “ETL Sourced Import Export xlsx Data to Landing Pad”.

The SQL agent job processes the underlying “\\SSIS\IMPORT_EXPORT\Master_Ref_Tables.xlsx” excel file, then based on the “Master Ref Query” field, generates (where applicable), related file system and database related objects:

• File System Repositories (\SSIS\DataSource_Name\)
• SQL Databases (Delta / Full)
• Database Schemas (Concatenation of “Data Source + ‘_’ + Schema”)
• Database Tables (Full / Delta)
• Database Table Indexes (Used to improve access time against the table)
• Database Views (Full / Cleansing / Duplicated)
• Database Stored Procedures
• File System “Deploy” Table Deployment Scripts (\SSIS\Deployment\)
• File System Versioning Scripts (\SSIS\Database_Versioning\)
• File System Failed Processes (\SSIS\Failed\)
• File System “Imported / Archived” Flat File Repositories (\SSIS\Data_Files_To\)
• File System Master SSIS Packages (\SSIS\Master\)
• File System SSIS Packages (Full / Delta) (\SSIS\Datasource_Name\)
• File System Excel File (\SSIS\Master_Data\xxxx\xxxx.xlsx) Note: Path and file name dependent on “Category” and “Master Ref Table Name” from originating “\\SSIS\IMPORT_EXPORT\Master_Ref_Tables.xlsx” excel file
• File System Excel Configuration File (\SSIS\Master_Data\Connectivity\xxxx.odc) Note: File name dependent on “Master Ref Table Name” from originating “\\SSIS\IMPORT_EXPORT\Master_Ref_Tables.xlsx” excel file

Once previous step has completed, run the newly generated SQL agent scheduler “ETL Sourced Master Data xlsx Data to Landing Pad”.

Finally and once the SQL agent scheduler: “ETL Sourced Master Data xlsx Data to Landing Pad”. Has completed, open the recently created “SSIS\Master_Data\xxxx\xxxx.xlsx” excel file and prior to refreshing and editing the data, repoint the excel file to its corresponding configuration file: (\SSIS\Master_Data\Connectivity\xxxx.odc).

Data values edited against the alternate columns in the excel file, will be reflected against the staging landing pad database, when ETL for the “Master Data” process is next run.

Column Metadata