Designing a Project for Financial Reporting » Physical warehouse requirements » Creating tables to provide hierarchical organization

Creating tables to provide hierarchical organization

As shown in the example financial report below, the metric data is presented in a hierarchical organization similar to that of attributes in MicroStrategy:

To create this structure, you store each element in lookup tables, and then define the hierarchical relationship between the elements using a relationship table.

Creating the lookup tables

The lookup tables define the hierarchical structure of your financial data for display on financial reports.

To illustrate these techniques, below are examples of the Level 1, Level 2, and Level 3 tables that would need to be created for the example financial report provided in Designing a Project for Financial Reporting.

Level1_ID

Level1_DESC

1

Revenue

2

Gross Profit

3

Operating Income

4

EBITDA

5

Net Income

 

Level2_ID

Level2_DESC

1

Cost of Revenues

2

Gross Profit Margin

3

Operating Expense

4

Deprecation Expense

5

Operating Margin

6

EBIT

7

Interest Income

8

Interest Expense

9

Taxes

-1

null

-2

null

-3

null

-4

null

-5

null

 

Level3_ID

Level3_DESC

1

Other Income

2

Interest Coverage Ratio

-1

null

-2

null

-3

null

-4

null

-5

null

-6

null

-7

null

-8

null

-9

null

-10

null

-11

null

-12

null

-13

null

-14

null

To create the lookup tables

1 Gather the following information:
What is the deepest level of elements for financial reporting? In other words, what is the maximum number of levels that are used to represent any given element in your financial report?

In the example financial report, the elements Other Income and Interest Coverage Ratio are both at the deepest level for the financial report, which uses three levels.

The number of levels needed is the number of lookup tables that need to be created to represent this organization in your physical warehouse.

How many elements are included in each level?

In the example financial report, Level 1 has five items, Level 2 has nine items, and Level 3 has two items.

The number of items in each level is used to determine how many elements are needed in each lookup table.

With this information, you can create a lookup table for each level, starting with Level 1, the highest level.

2 To create the first lookup table, create an ID column to uniquely identify the item, and a description column that provides the name of the element when displayed on financial reports. It is recommended that you use a simple sequential list of positive numbers for the ID values. This lookup table defines the elements for the Level 1 attribute. The table contains all of the elements that are displayed at the highest level. An example of a Level 1 table is provided in Creating the lookup tables.
3 Create a lookup table for each additional level of elements that is required. In addition to sharing all of the requirements for the first lookup table, each successive table must also meet the following requirements:
In addition to storing each element at the associated level, null or dummy elements must also be included. These elements are required to ensure that the hierarchical structure can be supported in MicroStrategy reports. When creating dummy elements, use the following guidelines:
Include the regular elements as the first elements in the table, using the same standard of sequential ID values and description information.
Include dummy rows equal to the total number of elements in the next highest level lookup table, including both regular elements and dummy elements.

For example, if you are creating the Level 2 table, you need to create a number of dummy elements equal to the total number of elements in the Level 1 table. If you are creating the Level 3 table, you need to create a number of dummy elements equal to the total number of elements in the Level 2 table.

For each dummy element, it is recommended that you use sequential, negative values for the ID value. For example, the first dummy element of a table should have -1 as its ID value, the second dummy element should use -2, and so on.
Leave the description column blank for any dummy elements, so that it has a null or empty value.

An example of a Level 2 and Level 3 tables is provided in Creating the lookup tables. In these example tables, notice that the Level 2 table has five dummy elements. This is because the Level 1 table has five total elements. The Level 3 table has 14 dummy elements. This is because the Level 2 table has 14 total elements when counting both the regular and dummy elements. The dummy elements for each table also use sequential, negative values for the ID values of the dummy elements.

Creating a relationship table

Once you have the lookup tables created, you must create a relationship table to define the hierarchical organization of the level attributes.

To create a relationship table

1 To determine how to assign matching elements between the lookup tables, you need to combine all of the regular elements from all tables together, mimicking the hierarchical organization required for the final report. For example, using the three lookup tables described above, the regular elements and their ID values would be combined as follows:

Level1_ID

Level1_DESC

Level2_ID

Level2_DESC

Level3_ID

Level3_DESC

1

Revenue

 

 

 

 

1

1

Cost of Revenues

 

 

2

Gross Profit

 

 

 

 

2

2

Gross Profit Margin

 

 

2

3

Operating Expense

 

 

2

4

Deprecation Expense

 

 

3

Operating Income

 

 

 

 

3

5

Operating Margin

 

 

3

5

1

Other Income

3

6

EBIT

 

 

4

EBITDA

 

 

 

 

4

7

Interest Income

 

 

4

8

Interest Expense

 

 

4

8

2

Interest Coverage Ratio

4

9

Taxes

 

 

5

Net Income

 

 

 

 

This mimics the organization of the final required report, and provides a relationship between all of the regular elements for the three level lookup tables.

2 Assign the dummy elements to fill out the structure. It is recommended to assign these elements in reverse order. For example, the first element, Revenue, is assigned the lowest dummy element value for the Level 2 table. Using this strategy, the table is defined as follows:

Level1_ID

Level1_DESC

Level2_ID

Level2_DESC

Level3_ID

Level3_DESC

1

Revenue

-5

 

-14

 

1

1

Cost of Revenues

-13

 

2

Gross Profit

-4

 

-12

 

2

2

Gross Profit Margin

-11

 

2

3

Operating Expense

-10

 

2

4

Deprecation Expense

-9

 

3

Operating Income

-3

 

-8

 

3

5

Operating Margin

-7

 

3

5

1

Other Income

3

6

EBIT

-6

 

4

EBITDA

-2

 

-5

 

4

7

Interest Income

-4

 

4

8

Interest Expense

-3

 

4

8

2

Interest Coverage Ratio

4

9

Taxes

-2

 

5

Net Income

-1

 

-1

 

3 Since the relationship table is only needed to create a relationship between the level attributes, you can exclude all description information from the table. This leaves you with the following relationship table, which defines the hierarchical organization of the level attributes in the example above:

Level1_ID

Level2_ID

Level3_ID

1

-5

-14

1

1

-13

2

-4

-12

2

2

-11

2

3

-10

2

4

-9

3

-3

-8

3

5

-7

3

5

1

3

6

-6

4

-2

-5

4

7

-4

4

8

-3

4

8

2

4

9

-2

5

-1

-1

Notice the ID column for the lowest level attribute in this table, in the example above, Level3_ID. There is a unique value in this ID column for each entry, which means that this column can be used as the primary key of the table. Additionally, this single ID column can be used to uniquely identify each financial line item. This value is used later to identify each financial line item.

This table data is used to create attributes to represent your financial line items in financial reports. Information on creating these attributes is included in Creating attributes for financial line items.