Submit a request

Data integration for dimensions

Avatar
Kepion Support
  • Updated

Introduction

This article focuses on the first step of data integration: updating dimensions. We will update the Dimension staging (DS_ and DSE_) and target (D_ and DO_) tables.

 

  • The DS_ table is an intermediate workspace for data processing before loading your data to the D_ table. 
  • The DSE_ table contains any errors in the DS_ table.
  • The D_ table stores the list of Dimension Members
  • The DO_ table stores the ordering information of the Dimension Members. 

 

Tip: Refer to our Data Integration Guide before attempting the process below. Click here for the second data integration step and here for the third. 

 

 

Process

Follow these steps to perform data integration for a dimension:

 

1.  Truncate DS_ table.

2.  Extract data from the source and populate the DS_ table.

 

Note: If your staging or target tables look wrong, reference our Model and Dimension Overviews while reviewing your Application in the Modeler.

 

3.  Call [dbo].[sp_Load_Dimension_From_Staging] for the dimension:

 

EXEC  [dbo].[sp_Load_Dimension_From_Staging]
@DimensionName = N'<DIMENSION_NAME>',
-- Business key column is MemberLabel (i.e. a unique column)
@BusinessKeyColumns = N'MemberLabel',
@CheckStagingUniqueness = TRUE

 

Note: The SP will automatically manage and maintain MemberIds when merging the DS_ table and the D_table's data.

 

4.  Review the DSE_ table for any errors.

5.  Update DO_ table for ordering.

 

Note: Updating the DO_ table is optional because it can be manually updated through the Modeler. 

 

6.  Repeat steps 1-5 for other Dimensions, as needed.

 

 

Columns

The following columns constitute the DS_ table:

 

Column Function
RowId Auto-populates when new rows are entered into the table.
MemberLabel (a.k.a. Business Key)

The unique label for each Dimension Member within the D_ table. This field takes Unicode character strings of up to 200.
MemberName The user-friendly name for each Dimension Member within the D_ table. This field takes Unicode character strings of up to 200.
Input Indicates if user input is allowed for the Member. A value of 1 indicates this Member allows user input; a value of 0 indicates this Member disallows user input.
Annotate Indicates whether a Form's data cell displays text. Data cells are defined by their Dimension Members. When a Dimension Member has Annotate enabled, the data cell will display text. 1 means annotations are enabled; 0 means unspecified. 
Account Type (only Account Dimension) Indicates the type of general ledger (GL) account for the Member. The values in this column should correspond to those in the Label column of the AccountLogic_AccountType table.
Dimension Attributes Dimension Attributes that users have created in the Modeler will appear as additional columns. 

 

Table example

The images below show a typical dimension structure and the dembers it can contain:

 

D_ table

dtable.png

 

dimensiontablefinal.png

 

DO_ table

domenu.png

 

dotable.png

 

 

Validation

After updating a dimension, check for the following:

 

  • No MemberId value is 0.
  • All MemberLabel values are unique.
  • (Only Account Dimension) All values in the AccountType column map to the values in the Label column of the AccountLogic_AccountType.
  • (Only if using SP) Any errors in the DSE_ table.

 

You can use the following scripts to verify what's listed above: 

 

-- Check for MemberId = 0
SELECT * FROM [dbo].[D_Account]
WHERE [MemberID] = 0;

-- Display non-unique MemberLabels
SELECT [MemberLabel], COUNT([MemberLabel]) [Count]
FROM [dbo].[D_Account]
GROUP BY [MemberLabel]
HAVING COUNT([MemberLabel]) <> 1;

-- Show invalid AccountTypes
SELECT N'`' + DA.[AccountType] + '`'
FROM [dbo].[D_Account] DA
LEFT OUTER JOIN [dbo].[AccountLogic_AccountType] AT
ON DA.[AccountType] = AT.[Label] 
WHERE AT.[Label] IS NULL;

-- Show all DS_ errors
SELECT * FROM [dbo].[DSE_Account];

 

 

Example

Below is an example of loading a Product dimension.

USE [Integrated Financial Planning]
GO

-- 1. Truncate the DS_ table.
TRUNCATE TABLE [dbo].[DS_Product];

-- 2. Extract data from the source and populate the DS_ table.
INSERT INTO [dbo].[DS_Product]
([MemberLabel]
,[MemberName]
,[Input]
,[Annotate]
,[Property]
,[Code]
,[Category]
,[Brand])
SELECT
[Product Code] [MemberLabel] -- Use a column that can uniquely represent a member as the MemberLabel
,[Product Description] [MemberName] -- Use a column that has the friendly display name as the MemberName
,1 [Input]
,0 [Annotate]
,[Granularity] [Property]
,[Product Code] [Code]
,[Category]
,[Brand]
FROM
[SourceDB].[dbo].[Product]; -- Source table

-- 3. Call [dbo].[sp_Load_Dimension_From_Staging] for the Dimension.
EXEC [dbo].[sp_Load_Dimension_From_Staging]
@DimensionName = N'Product',
@BusinessKeyColumns = N'MemberLabel',
@CheckStagingUniqueness = TRUE;

 

 

Next steps

Complete the second step of the data integration process: Data Integration for Hierarchies.

Related articles