Setting up an Account or Measures dimension with a parent-child reference

In this recipe, we will set up a relational table in a parent-child reference format. We will also review the type of properties that can go in each column and their definitions. The Account or Measure dimension is normally the most dynamic dimension in a financial database and it is recommended that you use the parent-child structure to build the dimension in a relational environment. The parent-child reference also allows ragged hierarchies without having to add columns to your tables when an additional level or generation is needed. We will also review an alternative method, which requires us to use the measures field in our fact table to build our Measure dimension.

Getting ready

To get started, open your SQL Server Management Studio, and add a database called TBC. For this recipe, we are using T-SQL, but the PL\SQL equivalent will be provided where applicable. You should add a SCHEMA called TBC using tools such as TOAD, SQL Developer, or Golden, if you are using Oracle.

How to do it...

1. Run the following scripts to create the Measures table. We can change the script below to PL/SQL by replacing int with INTEGER and varchar() with VARCHAR2(). A screenshot of the table follows the script:

    --This is the syntax in T-SQL  
   create table MEASURES
   (
       SORTKEY            int                   not null,
       MEASURESID         int                   not null,
       PARENT             varchar(85)           null    ,
       CHILD              varchar(85)           not null,
       MEASURES_ALIAS     varchar(85)           null    ,
       CONSOLIDATION      varchar(85)           null    ,
       TWOPASSCALC        varchar(85)           null    ,
       STORAGE            varchar(85)           null    ,
       VARIANCEREPORTING  varchar(85)           null    ,
       TIMEBALANCE        varchar(85)           null    ,
       SKIP               varchar(85)           null    ,
       UDA                varchar(85)           null    ,
       FORMULA            varchar(255)          null    ,
       COMMENT_ESSBASE    varchar(85)           null    ,
       constraint PK_MEASURES primary key (MEASURESID)
   )
   Go

   

   Tip

   Downloading the example code

   You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

2. Execute the following scripts to add the data to your table:

   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
     TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE)
     VALUES (100,1,'Measures','Profit','','+','','X','','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
     TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (200,2,'Profit','Margin','','+','','X','','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (300,3,'Margin','Sales','','+','',
     '','','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES (400,4,'Margin','COGS','Cost of Goods Sold','-','','','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (500,5,'Profit','Total Expenses','','-','','X','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (600,6,'Total Expenses','Marketing','','+',
     '','','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (700,7,'Total Expenses','Payroll','','+','','','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
     TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (800,8,'Total Expenses','Misc','Miscellaneous','+',
     '','','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (900,9,'Measures','Inventory','','~','','O','','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
     TIMEBALANCE,SKIP,UDA,FORMULA,COMMENT_ESSBASE) VALUES
     (1000,10,'Inventory','Opening Inventory','','+','','','E','F','','',
     'IF(NOT @ISMBR(Jan)) "Opening Inventory"=@PRIOR("Ending Inventory");ENDIF;"Ending Inventory"="Opening Inventory"+Additions-Sales;','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,
   COMMENT_ESSBASE) VALUES (1100,11,'Inventory','Additions','','~','','','E','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,
     TIMEBALANCE,SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
     (1200,12,'Inventory','Ending Inventory','','~','','','E','L','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,SKIP,UDA,FORMULA,
   COMMENT_ESSBASE) VALUES (1300,13,'Measures','Ratios','','~','','O','','','','','','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
     (1400,14,'Ratios','Margin %','','+','T','X','','','','','Margin % Sales;','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
     (1500,15,'Ratios','Profit %','','~','T','X','','','','','Profit % Sales;','');
   
   INSERT INTO MEASURES (SORTKEY,MEASURESID,PARENT,CHILD,MEASURES_ALIAS,
     CONSOLIDATION,TWOPASSCALC,STORAGE,VARIANCEREPORTING,TIMEBALANCE,
     SKIP,UDA,FORMULA, COMMENT_ESSBASE) VALUES
     (1600,16,'Ratios','Profit per Ounce','','~','T','X','','','','',
       'Profit/@ATTRIBUTEVAL(Ounces);',''); 
   

How it works...

The MEASURES table has the following columns:

COLUMN	DESCRIPTION
SORTKEY	This column is the integer that helps you sort the MEASURES in the order that you want them to appear in the hierarchy
MEASURESID	This ID is used as the PRIMARY KEY in the MEASURES table and as a FOREIGN KEY in the fact table
PARENT	This column is the Parent in the hierarchy
CHILD	This column is the Child of the Parent column
MEASURES_ALIAS	This is a more intuitive description of Measures normally defined by the business
CONSOLIDATION	This field has the aggregation type for the Child column
TWOPASSCALC	This field has the value "T" if the aggregation requires a second pass through the outline for the results to be right
STORAGE	Storage can have many values and will determine how or if the data in the outline is stored or dynamically calculated
VARIANCEREPORTING	The Variance Reporting column is used to mark Expense accounts for reporting variances
TIMEBALANCE	The Time Balance column is used with your time dimension to determine whether to use LIFO, FIFO, or the Average method for a specific measure
SKIP	The Skip column works with Time Balance to determine how to treat #MISSING or Zero values
UDA	The User Defined Attribute is useful for many purposes including outline formulas, calculation formulas, and the retrieval of data by the criteria defined by the business
FORMULA	These are the outline formulas used in the BSO model
COMMENT_ESSBASE	These are simply comments on the meta-data stored in this table

In step 2, we load the data. The following are descriptions of what goes into some of these columns as per Oracle's documentation.

These are the valid Consolidations values:

TYPE	TYPE DESCRIPTION	TYPE LONG DESCRIPTION
%	Percent	Expresses as a percentage of the current total in a consolidation
*	Multiplication	Multiplies by the current total in a consolidation
+	Addition	Adds to the current total in a consolidation
-	Subtraction	Subtracts from the current total in a consolidation
/	Division	Divides by the current total in a consolidation
^	Never	Excludes from all consolidations in all dimensions
~	Ignore	Excludes from the consolidation

This is the valid Two Pass value:

TYPE	TYPE DESC	TYPE LONG DESCRIPTION
T	Two Pass Calculation	Requires a two-pass calculation (applies to accounts dimensions only)

These are the valid Storage values:

TYPE	TYPE DESC	TYPE LONG DESCRIPTION
N	Never Share	Never allows data sharing
O	Label Only	Tags as label only (store no data)
S	Store Data	Sets member as stored member (non-Dynamic Calc and not label only)
V	Dynamic Calc and Store	Creates as Dynamic Calc and Store
X	Dynamic Calc	Creates as Dynamic Calc

This is the valid Variance Reporting value:

TYPE	TYPE DESC	TYPE LONG DESCRIPTION
E	Expense	Treats as an expense item (applies to accounts dimensions only)

These are the valid Time Balance values:

TYPE	TYPE DESC	TYPE LONG DESCRIPTION
A	Average	Treats as an average time balance item (applies to accounts dimensions only)
F	First	Treats as a first time balance item (applies to accounts dimensions only)
L	Last	Treats as a last time balance item (applies to accounts dimensions only)

These are the valid Skip options per Oracle's Documentation:

TYPE	TYPE DESC	TYPE LONG DESCRIPTION
B	Missing and Zeros	Skips #MISSING data and data that equals zero when calculating the parent value
M	Missing	Skips #MISSING data when calculating the parent value
Z	Zeros	Skips data that equals zero when calculating the parent value

There's more...

Using the parent-child reference table structure will depend on whether we know that our Measures and Accounts are going to change often. The structure of your fact table will have to change if you decide to use Measure tables. A fact table that has the Measures going down a table vertically, as rows, will allow us to use the Measures column in the fact table to join to the MEASURES table. The following screenshot illustrates how this design will look:

We can easily add accounts or change parent-child associations using this format without having to modify the fact table. On the other hand, if our fact table has Measures horizontally, in columns, then the Measures dimension will have to be built in Essbase Studio or Essbase Integration Services instead. The following screenshot is an example of what a fact table, with Measures as columns, would look like:

The Beverage Company (TBC) sample database's SALES and SALESFACT tables are examples of the two different formats.

See also

You can find an example of the MEASURES dimension being built in the recipe Creating hierarchies using a Parent-child reference table in Chapter 3. For an example on how to build the MEASURES dimension using Essbase Studio from the fact table, refer to the recipe Building a Measures dimension from the fact table in Chapter 4.