Planning modeling concepts in BI planning solutions and scenarios

 

Applies to: SharePoint Server 2010 Enterprise

In this article:

  • Creating the data source view

  • Dimensions and hierarchy considerations

Creating the data source view

Note

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.

Dimensions and hierarchy considerations

Dimension and hierarchy sizing and recommendations

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.

Use of dimension vs. measures

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.

See Also

Concepts

Basic planning scenarios in BI planning solutions and scenarios
Planning the data mart for BI planning solutions and scenarios
Planning modeling concepts in BI planning solutions and scenarios
Cube modeling for Write-back in BI planning solutions and scenarios
Performance considerations and approaches in BI planning solutions and scenarios
Cube modeling with Excel PowerPivot in BI planning solutions and scenarios
Create reports and forms for BI planning solutions and scenarios
Submit plan data for BI planning solutions and scenarios
Workflow actions, workflow diagram, and SharePoint workflow setup for BI planning solutions and scenarios
Audit tracking for BI planning solutions and scenarios
Administration for BI planning solutions and scenarios
Calculations for BI planning solutions and scenarios
Additional planning functions for BI planning solutions and scenarios
Migration for BI planning solutions and scenarios
Maintenance for BI planning solutions and scenarios
Corporate to subsidiary management for BI planning solutions and scenarios
Planning modeling and reporting guide for BI planning solutions and scenarios
Building planning functionalities guide for BI planning solutions and scenarios
Planning and budgeting calculation examples for BI planning solutions and scenarios