Data Integration for models

Introduction

This article focuses on the third step of data integration: updating Facts/Models. We will update 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 are within each Model Dimension's 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.

7. Select Deploy in the Modeler. 

Columns

The following columns comprise the F_ table:

Table Example

The images below show a typical F_ table structure and the records it can contain:

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])

Example

Below is an example of loading data for the Revenue Planning model, Actual partition.

USE [Integrated Financial Planning]
GO

-- 1. Truncate the Staging_F table.
TRUNCATE TABLE [dbo].[Staging_F_Revenue-Planning_CoreMG_Actual];

-- 2. Extract data from the source and populate the Staging_F table.
-- For dimensions that do not exist in the source, but are used in the Kepion model, e.g. Metric, specify the desired MemberLabel in the INSERT statement.
INSERT INTO [dbo].[Staging_F_Revenue-Planning_CoreMG_Actual]
([AccountLabel]
,[TimeLabel]
,[MetricLabel]
,[ProductLabel]
,[ScenarioLabel]
,[EntityLabel]
,[Sales ChannelLabel]
,[LineLabel]
,[Value]
,[Comment])
SELECT 
[AccountLabel]
,DT.[MemberLabel] [TimeLabel]
,N'Amount' [MetricLabel]
,[ProductLabel]
,N'Actual' [ScenarioLabel]
,[EntityLabel]
,[Sales ChannelLabel]
,N'1' [LineLabel]
,[Value]
,[Comment]
FROM
[SourceDB].[dbo].[Revenue_Actual] S -- Source table
LEFT JOIN [dbo].[D_Time] DT 
ON LEFT(CONVERT(VARCHAR(8),S.[Date],112),6) = LEFT(CONVERT(VARCHAR(8),DT.[Date],112),6)
WHERE 
DT.[Granularity] = N'Month';

-- 3. Verify all MemberLabel values in Staging_F are within each Model Dimension's Member List (H_). 
SELECT 
S.*
FROM 
[dbo].[Staging_F_Revenue-Planning_CoreMG_Actual] S
LEFT JOIN [dbo].[D_Account] DA ON S.[AccountLabel] = DA.[MemberLabel]
LEFT JOIN [dbo].[D_Time] DT ON S.[TimeLabel] = DT.[MemberLabel]
LEFT JOIN [dbo].[D_Metric] DM ON S.[MetricLabel] = DM.[MemberLabel]
LEFT JOIN [dbo].[D_Product] DP ON S.[ProductLabel] = DP.[MemberLabel]
LEFT JOIN [dbo].[D_Scenario] DS ON S.[ScenarioLabel] = DS.[MemberLabel]
LEFT JOIN [dbo].[D_Entity] DE ON S.[EntityLabel] = DE.[MemberLabel]
LEFT JOIN [dbo].[D_Sales Channel] DSC ON S.[Sales ChannelLabel] = DSC.[MemberLabel]
LEFT JOIN [dbo].[D_Line] DL ON S.[LineLabel] = DL.[MemberLabel]
WHERE 
DA.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Account_Account])
AND DM.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Metric_Metric])
AND DP.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Product_Product])
AND DS.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Scenario_Scenario])
AND DE.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Entity_Entity])
AND DSC.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Sales Channel_Sales Channel])
AND DL.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Line_Line])
AND DT.[MemberId] NOT IN (SELECT [MemberId] FROM [H_Time_Months]);

-- 4. Delete records from the F_ table for the target scope.
-- In this example, we only update actuals for the prior month.
DECLARE @CurrentMonthID INT = (
SELECT TOP 1 [VariableValue]
FROM [dbo].[ApplicationVariables]
WHERE [VariableName] = N'Current Month'
);

DECLARE @PriorMonthID INT = 
(
SELECT [MemberId]
FROM D_Time
WHERE [Date] = 
(
SELECT DATEADD(MONTH, -1, [Date])
FROM D_Time
WHERE [MemberId] = @CurrentMonthID
) AND [Granularity] = N'Month'
);

DELETE FROM [dbo].[F_Revenue-Planning_CoreMG_Actual]
WHERE [TimeID] = @PriorMonthID;

-- 5. Load data from Staging_F to the F_ table.
-- Ensure all MemberLabel values loaded to the F_ table are within each Model Dimension's Member List (H_). 
INSERT INTO [dbo].[F_Revenue-Planning_CoreMG_Actual]
([AccountID]
,[TimeID]
,[MetricID]
,[ProductID]
,[ScenarioID]
,[EntityID]
,[Sales ChannelID]
,[LineID]
,[Value]
,[Comment])
SELECT
DA.[MemberId] [AccountID]
,DT.[MemberId] [TimeID]
,DM.[MemberId] [MetricID]
,DP.[MemberId] [ProductID]
,DS.[MemberId] [ScenarioID]
,DE.[MemberId] [EntityID]
,DSC.[MemberId] [Sales ChannelID]
,DL.[MemberId] [LineID]
,S.[Value]
,S.[Comment]
FROM 
[dbo].[Staging_F_Revenue-Planning_CoreMG_Actual] S
INNER JOIN [dbo].[D_Account] DA ON S.[AccountLabel] = DA.[MemberLabel]
INNER JOIN [dbo].[D_Time] DT ON S.[TimeLabel] = DT.[MemberLabel]
INNER JOIN [dbo].[D_Metric] DM ON S.[MetricLabel] = DM.[MemberLabel]
INNER JOIN [dbo].[D_Product] DP ON S.[ProductLabel] = DP.[MemberLabel]
INNER JOIN [dbo].[D_Scenario] DS ON S.[ScenarioLabel] = DS.[MemberLabel]
INNER JOIN [dbo].[D_Entity] DE ON S.[EntityLabel] = DE.[MemberLabel]
INNER JOIN [dbo].[D_Sales Channel] DSC ON S.[Sales ChannelLabel] = DSC.[MemberLabel]
INNER JOIN [dbo].[D_Line] DL ON S.[LineLabel] = DL.[MemberLabel]
WHERE 
DA.[MemberId] IN (SELECT [MemberId] FROM [H_Account_Account])
AND DT.[MemberId] IN (SELECT [MemberId] FROM [H_Time_Months]) AND DT.[MemberId] = @PriorMonthID
AND DM.[MemberId] IN (SELECT [MemberId] FROM [H_Metric_Metric])
AND DP.[MemberId] IN (SELECT [MemberId] FROM [H_Product_Product])
AND DS.[MemberId] IN (SELECT [MemberId] FROM [H_Scenario_Scenario])
AND DE.[MemberId] IN (SELECT [MemberId] FROM [H_Entity_Entity])
AND DSC.[MemberId] IN (SELECT [MemberId] FROM [H_Sales Channel_Sales Channel])
AND DL.[MemberId] IN (SELECT [MemberId] FROM [H_Line_Line]);