Business Modeling and Planning with PowerPivot - Part 2

Following on from Part 1 the wide form is usually best where you have:

  • relatively few measures (not a chart of accounts)
  • there are calculations based of off several measures
  • there are no chart-of-accout style hierarchies except very simple ones created using calculated measures

The narrow form is suitable for financial or accounting applications that involve a chart of accounts. A chart of accounts typically includes a hierarchy in which the number of levels above or below a level in can vary. In the extract below on the left from the Microsoft AdventureWorks accounts dimension, Other Assets has no levels below, Property, Plant, Equipment has 1 level below, and Current Assets has up to 2 levels below.

Example of an unbalanced hierarchy

Unbalanced chart of accounts example

Example of a balanced hierarchy

Balanced reporting chart of account hierarchy

In PowerPivot The DAX language has several functions (such as PATH and PATHITEM) that help you navigate such hierarchies. Alternatively you can create your chart of accounts in PowerPivot as a balanced hierarchy (see above, right) and repeat the levels as necessary to fill up levels that don't exist in the unbalanced form. Then the members at the bottom of each branch (Cash, Trade Receivables, Other Receivables etc) are all at the same level.

Microsoft Analysis Services Multidimensional supports Calculated Members in addition to Calculated Measures. These are calculations within a dimension such as these:
[Account].[Gross Profit] = [Account].[Sales] - [Account].[Cost of Goods Sold]
Calculated Members are not supported in PowerPivot nor in MSAS Tabular.You can create a measure called [Gross Profit] using a somewhat similar definition but its placement in a PivotTable is restricted to the Values section

So why the fuss about narrow/wide table formats and balanced/unbalanced hierarchies?

The upshot here is that if you are going to perform some degree of financial planning or modeling in PowerPivot, you should use the wide form of table and use PowerPivot's calculated columns for your modeling calculations and simulations. If you are doing your financial modelling outside PowerPivot and prior to importing the data then the narrow form approach may work better for you. Part 3 explains how you can introduce assumptions into your planning model.

© 2011-17 Excelcraft.com

Home            Terms of Use            Privacy            Cookies            Contact