Storing financial data

After you create the lookup tables and relationship table to create the hierarchical structure of your financial data, you must store and categorize the financial data itself. This data includes all of the various financial accounts that you plan to include in your financial report. For example, recall the following example financial report:

Each separate line item on the report is an account, and this data needs to be stored so that it can be retrieved by MicroStrategy for financial reporting.

There are various ways in which you can store data in a database. To facilitate financial reporting in MicroStrategy, it is recommended to store all of the data for all financial accounts in a single column, at the lowest logical level. This results in a table with multiple columns that will be modeled as attributes in MicroStrategy, and a single column that stores all financial data. For example, the table would have a structure similar to the following:

Day

Other Attributes

Financial Account

Data

1/1/2012

Account, Vendor, Customer, etc.

-14 (Revenue)

300

1/1/2012

Account, Vendor, Customer, etc.

-13 (Cost of Revenue)

500

1/1/2012

Account, Vendor, Customer, etc.

-12 (Gross Profit)

125

...

...

...

...

1/2/2012

Account, Vendor, Customer, etc.

-14 (Revenue)

480

1/2/2012

Account, Vendor, Customer, etc.

-13 (Cost of Revenue)

320

1/2/2012

Account, Vendor, Customer, etc.

-12 (Gross Profit)

95

...

...

...

...

When storing your data in a table with this structure:

Include a column for each category that gives context to the financial data. For example, you might need to know the account, vendor, or customer for a given piece of financial data, as well as the day when the transaction occurred. This should include all aspects of the financial data at the lowest logical level of data. This concept is discussed more in detail in Fact tables: Fact data and levels of aggregation.

The concepts related to fully categorizing your data are discussed in The Logical Data Model. Be aware that each category required needs to be fully defined in your database. This means you must create lookup tables to provide the necessary, descriptive information to describe these categories. For additional details on lookup table design, see Lookup tables: Attribute storage.

Include a column that identifies which financial line item the data is associated with. These identification numbers must match the ID column of the lowest level lookup table you created to define the hierarchical organization of the final financial report (see Creating tables to provide hierarchical organization). In the example scenario, the Revenue line item is associated with the value -14 in the lowest level lookup table, which is LU_LEVEL3. So any entry of Revenue data must use the value of -14 in this Financial Account column to identify it as Revenue data.
Include a column that stores all of the financial data for each separate financial line item. This data is entered for each line item for every record that is applicable.

Do not include data for any financial line items that are percentages. This includes financial line items such as margins and ratios. For example, data such as Gross Profit Margin should not be included in this table. This data is created later in MicroStrategy with the use of metrics.

The resulting table has more rows than columns. This is because, rather than storing the financial data in their own separate columns, each entry is stored in a single column.