This article focuses on the third step of data integration: updating Facts/Models. We will be updating the Fact staging (Staging_F) and target (F_) tables.
- The Staging_F table serves as an intermediate workspace used for data processing before loading your data to the F_ table.
- The F_ table stores all of a Model's fact records.
Tip: Please refer to our Data Integration Guide before attempting the process below. Click here for the first data integration step and here for the second.
Process
Note: If your staging or target tables do not look right, reference our Model and Dimension Overviews while reviewing your Application in the Modeler.
1. Truncate the Staging_F table.
2. Extract data from the source and populate the Staging_F table.
3. Verify all MemberLabel values in Staging_F have an associated MemberId in the H_ table.
Note: Each Model Dimension will have an associated Member List (H_).
4. Delete records from the F_ table for the target scope.
5. Load data from Staging_F to the F_ table.
Note: You will need to join the Staging_F table with the D_ tables to get the MemberIds for the F_ table.
6. Repeat steps 1-5 for other F_ tables, as needed.
Columns
The following columns comprise the F_ table:
| Column | Function |
|---|---|
| RowId | Auto-populates when new rows are entered into the table |
| Hierarchy MemberIds | One or more fields that map to Members from the H_ table as defined by its Model Dimensions |
| Value | Stores the fact records/data |
| Comment | Stores annotations |
| Status (Writeback only) | When loading data, this field can be set to 0 or any numerical value ≥ 5. Numbers < 5 are reserved for system values, and numbers > 5 can be used for user-defined values. |
| Activity (Writeback only) |
Tracks changes to the fact records. For data loading purposes, you can set the field to NULL. Non-empty values should correspond to records in the Activities table. |
Example
The images below show a typical Model structure and the records it can contain:
F_ table
Validation
When a Model is updated, verify that all MemberIds in the F_ table are found in the H_ table.
USE [Integrated Financial Planning]
GO
SELECT *
FROM [dbo].[F_Financial Statement_CoreMG_Actual]
WHERE [AccountID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Account_Account])
OR [TimeID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Time_Months])
OR [EntityID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Entity_Entity])
OR [ScenarioID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Scenario_Scenario])
OR [Currency TypeID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Currency Type_Currency Type])
OR [Time TypeID] NOT IN (SELECT [MemberId] FROM [dbo].[H_Time Type_Time Type])
Comments
0 comments
Please sign in to leave a comment.