Loading Data In Logical Units of Work

 

The work done on the Data Integration Utility Web Application is broken up into three parts: Stage tables, DIL tables, and proCube. The table below will describe what tables and cubes are affected for a given Stage table.

 

Note that, based on what Stage fact tables you validate, the Generate Integration Layer step can identify what DIL tables will need to be rebuilt as a result, and the Rebuild proCube step will preselect the cubes to rebuild based on what DIL fact tables that were rebuilt.

 

Files identified as Fact files won't need dimensional data rebuilds when selecting Dimensional Options in Generate DIL. However, if new dimensional files are loaded, dimensional data will need to be rebuilt in the Dimensional Options, and the fact tables that relate to the dimensions that change will need to be re-validated.

 

Upload Data to Stage Table

Type of Data

Generate DIL Fact Tables to Rebuild

Cubes to Rebuild

Stage_Account

Dimension

GL_Journal_Entries_Fact

Xchange GL Data

Stage_Asset_Class

Dimension

Asset_Data_Fact

Xchange Asset Data

Stage_Asset_Data

Fact

Asset_Data_Fact

Xchange Asset Data

Stage_Currency

Dimension

All fact tables

All Xchange cubes

Stage_Employee

Dimension

Employee_Data_Fact, FTE_Fact, Timekeeper_Rates_Fact

Xchange Employee Data, Xchange FTE, Xchange TK Rates

Stage_Employee_Data

Fact

Employee_Data_Fact

Xchange Employee Data

Stage_FTE_Data

Fact

FTE_Fact

Xchange FTE Data

Stage_GL_Class

Dimension

GL_Journal_Entries_Fact

Xchange GL Data

Stage_GL_Data

Fact

GL_Journal_Entries_Fact

Xchange GL Data

Stage_GL_Rollups

Dimension

GL_Journal_Entries_Fact

Xchange GL Data

Stage_GL_Type

Dimension

GL_Journal_Entries_Fact

Xchange GL Data

Stage_Office

Dimension

All fact tables

All Xchange cubes

Stage_PG_Dept

Dimension

FTE_Fact, GL_Journal_Entries_Fact

Xchange FTE, Xchange GL Data

Stage_Salary

Dimension

Employee_Data_Fact, FTE_Fact, Timekeeper_Rates_Fact

Xchange Employee Data, Xchange FTE , Xchange TK Rates

Stage_TK_Rates_Data

Fact

Timekeeper_Rates_Fact

Xchange TK Rates

Stage_Title

Dimension

FTE_Fact

Xchange FTE

Stage_WIP_AR_Data

Fact

WIP_AR_Fact

Xchange WIP AR Data

 

Scenario Example: New Dimensional Data / New Fact and Dimensional Data

A request has been put in to upload a new Stage_Title and Stage_Account file.

  1. Upload the files to their appropriate tables.

  2. In Generate DIL, select "Dimensional Data" for the option in "Please Specify What Data Changes Occurred in Staging"

  3. Select Stage_Account and Stage_Title in the dimensions list box. Then select Stage_FTE_Fact and Stage_GL_Data in the fact tables list box.

  4. Click on Step 1: Validate Staging Data.

  5. (Assuming no validation errors) Select "Build Dimensional Data, Deleting All Existing Members."

  6. Select the FTE_Fact and GL_Journal_Entries_Fact table.

  7. Click on Step 2: Generate Data Integration Layer.

  8. FTE_Fact, GL_Journal_Entries_Fact, their dimensions, and all fact tables that share their dimensions are rebuilt.

  9. Click on proCube Functions.

  10. Log onto the configured proCube server.

  11. Select Xchange GL Data and Xchange FTE, select "Add Metadata and Refresh Fact Data" option.

  12. Click the Rebuild Cubes button.

 

What did we just do? A dimensional data change alone means an impact to potentially multiple fact tables as listed above. The process above would be similar in doing the process for both a fact and dimensional data load. In the case that the fact data being refreshed is separate from the dimensions being refreshed, the user only needs to select a dimension that affects the fact table in order to get it to appear on the fact table list.

 

Steps 1-3 involves loading the data to Stage tables and validating the data in the Stage tables. Steps 4-5 rebuilds the DIL tables from their Stage tables. Finally, steps 6-7 rebuilds the proCube databases from their DIL table sources.

Scenario Example: New Fact Data

A request has been put in to upload a new Stage_TK_Rates_Data file.

  1. Upload the file to its appropriate table.

  2. In Generate DIL, select "Fact Data" for the option in "Please Specify What Data Changes Occurred in Staging."

  3. Select Stage_TK_Rates_Data in the fact tables list box.

  4. Click on Step 1: Validate Staging Data.

  5. (Assuming no validation errors) Select "Do Not Build Dimensional Data, Just Build the Fact Table(s)."

  6. Select Timekeeper_Rates_Fact.

  7. Timekeeper_Rates_Fact, its dimensions, and all the fact tables that share its dimensions are rebuilt.

  8. Click on proCube Functions.

  9. Log onto the configured proCube server.

  10. Select Xchange TK Rates, select "Add Metadata and Refresh Fact Data" option.

  11. Click the Rebuild Cubes button.

 

What did we just do? If only a fact table changes, there is no need to refresh dimensional members in the DIL.

 

Scenario Example: New Hierarchical Data (Stage_GL_Rollups)

This only applies to a new Stage_GL_Rollups file. Stage_GL_Rollups was designed so that hierarchies for GL Accounts would not need to be defined on a row-by-row basis. The Stage_GL_Rollups file defines accounts in ranges, and as a post-load operation, updates the accounts in the given ranges with the properly defined hierarchies. So, intuitively, Stage_GL_Rollups is more a "subset" of Stage_Account

  1. Upload the files to their appropriate tables

  2. In Generate DIL, select "Dimensional Data" for the option in "Please Specify What Data Changes Occurred in Staging."

  3. Select Stage_Account in the dimensions list box.

  4. Select Stage_GL_Data in the fact tables list box.

  5. Click on Step 1: Validate Staging Data.

  6. (Assuming no validation errors) Select "Build Dimensional Data, Deleting All Existing Members."

  7. Select theGL_Journal_Entries_Fact table.

  8. Click on Step 2: Generate Data Integration Layer.

  9. GL_Journal_Entries_Fact, its dimensions, and all fact tables that share its dimensions are rebuilt.

  10. Click on proCube Functions.

  11. Log onto the configured proCube server

  12. Select Xchange GL Data, select "Add Metadata and Refresh Fact Data" option, then check off "Rebuild Hierarchies."

  13. Click the Rebuild Cubes button.

 

What did we just do? Since Stage_GL_Rollups fundamentally changes the hierarchical structure of the Account dimension, in proCube, we must specify a "Rebuild Hierarchies" when rebuilding Xchange GL Data.

 

Data Loading Tips

Consistent Approach

 

The options presented during the Generate DIL step were designed to save some process time. However, in the event that the user wishes to have simply one consistent approach, the user can simply select all dimensions and all tables to validate and load to DIL, and it would essentially result in the same data set for the data that did not change, since data is persisted in the Stage tables if you do not upload any new data(and the DIL tables draw from them). The user would then just need to know what proCube cubes to rebuild.

 

Data Loading Considerations in Production

When the proCube server goes into production, data loads must be planned carefully. Since data loads can potentially change the fundamental data from which a cube extracts (as well as sometimes being timely operations), the best practice in production mode would be to schedule data loads in the hours that users are not actively accessing the proCube from V-i-Performance. Rebuilding while users are actively accessing proCube is not recommended, as it can potentially lead to data corruption issues (due to memory over-usage), as well as present application responsiveness issues for users.

 

Finally, in production mode, since manual hierarchies can be created after data loads, it is recommended that Rebuild Hierarchies in proCube Functions be used only when necessary. Again, from a data load perspective, the only time hierarchical data will be loaded with new data is with a Stage_GL_Rollups file.