Planning modeling concepts in BI planning solutions and scenarios


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2011-01-28

In this article:

For an example of all five data source views used in the sample application, and for more information about the planning data warehouse, see Planning modeling and reporting guide for BI planning solutions and scenarios.

The size of a dimension depends on the number of members and its properties. When creating a dimension, we recommend that you limit the number of attribute hierarchies as this improves the processing performance of SSAS. Also, by keeping the members of the dimension to the minimally required for planning, which results in small cube spaces, better query performance will be achieved overall.

Sometimes it helps to combine two separate dimensions into one for both navigational and performance improvements. The combined dimension would then act as a valid combination of the two dimensions and a navigational hierarchy can be created to aid in filter logic. For example, take the two dimensions ‘Geography’ and ‘Department’. The combined dimension can be called ‘Geography Department’ and with a unique label code generated based on a combination of geography and department codes. The combined dimension can result in both an improvement in the navigational usage and in the dimensionality of the cube space.

When using parent-child hierarchies, we recommend that you limit the number of levels in the hierarchy. The more levels there are in a parent-child hierarchy, the more work the SSAS server must do when answering queries. This is because queries that require data from intermediate levels on the hierarchy must be calculated dynamically. The more levels there are on the parent-child hierarchy, the more calculations have to be computed.

Parent-child hierarchies are very useful for planning scenarios. Each member can be easily configured to show the full details of member properties across all the members of the hierarchy.

For planning needs, a single measure on a measure group is sufficient. Additional measures can be defined as dimension members that then act as unique slices for storing additional data in a cube. What are the benefits of a single fact measure?

  • Avoid the need to change the fact table when you are adding additional measures.

  • Cleaner fact table design, one row of fact equals one fact record.