Account Dimension

Details and interaction with Power BI

The account dimension consists of three tables in the Finance Mart. One for the actual dimension, DimAccount, and two for the account arrangements in the Data Perspective. This unorthodox structure is due to limitations in Power BI and its matrix visual.

DimAccount(dAccount)

Regular dimension with the following columns:

  • le_id - Long - Foreign key to DimLegalEntity 
  • account_id - Long - Primary key for this dimension
  • account_number - String - The account number
  • account_name - String - The account name
  • account_label - String - Concatenated value of account_number and account_name

DimAccountHierarchy(dAccountHierarchy)

Note that DimAccountHierarchy's groups can be the same as the account groups found in DimAccountMisc

Hierarchy dimension that contains the following columns:
  • le_id - Long - Foreign key to DimLegalEntity 
  • account_hierarchy_id - Long - Primary key for this dimension
  • account__id - Long - Primary key for this dimension
  • mode - String - "Le" or "Cg" depending on whether the hierarchy belongs to the individual legal entity or to the corporate group
  • group_n_code - String - The hierarchy group's identifier
  • group_n_name - String - The hierarchy group's name
  • group_n_sort_order - Integer - The hierarchy group's sort order

DimAccountMisc(dAccountMisc)

This is used together with DimAccount, DimAccountHierarchy and the [Actuals] measure to create the Income Statement and a couple of KPIs in Power BI.

Unique support table that contains the following columns:
  • le_id - Long - Foreign key to DimLegalEntity 
  • account_misc_id- Long - Primary key for this dimension
  • account__id - Long - Primary key for this dimension
  • mode - String - "Le" or "Cg" depending on whether the summarization row/account group belongs to the individual legal entity or to the corporate group
  • group_code - String - the visualization code of the account arrangement group
  • group_desc - String - DEPRECATED
  • group_type - String - the type of the group. "RS" for income statement and "BR" for balance sheet. "RS-KPI" and "BR-KPI" are used to denote if visualization codes have been added to none-root hierarchy groups.
  • group_display_name - String - the name of the hierarchy group or summarization row
  • group_display_hide_details - String - "1" for summarization row, "0" for account group
  • sort_code - the sort order of the summarization row/account group