Planning the data mart for BI planning solutions and scenarios

 

Applies to: SharePoint Server 2010 Enterprise

In this article:

  • Schema and layout

  • Models for solution

  • Creating dimension tables

  • Creating hierarchy tables

  • Creating fact tables

Schema and layout

The data mart will be created by using SQL Server 2008 R2 relational server to serve as the single point of record for IWs, with all data coming into the data mart strictly controlled. The data mart will be used by the SSAS server as the central data source for all cubes, dimensions and hierarchies. Three kinds of tables will be needed to support the SSAS data model and include dimension, hierarchy, and fact.

Note

It is possible for a cube to use more than one fact table. This can be achieved through partitioning on the measure group and also by using more than one measure group within the cube.

We will create the following models to support the data models needed for the planning process. These models will dictate the number of tables that we will exist within our data mart.

Models for solution

  • Forecast: This model will be primarily used to capture data entry for forecast s of revenue and operational expense for the forward looking periods.

  • Account: The account dimension will contain the chart of accounts, which shows revenue and expense items for forecasting

  • Scenario: The scenario dimension will partition the data between ‘Actual’ and ‘Forecast’

  • Time: The time dimension will determine the fiscal periods for the forecast.

  • Geography: The geography dimension will be used to control the data entry process by each IW. Individual IWs from each geography will perform data entry in their local currencies.

  • Product: The product dimension is used to represent the full list of active and available products. Revenue will be forecasted by product.

  • HR Budget: This model will be used by line managers to budget expected headcount for the fiscal year. IWs will interact with this model by entering in expected hours worked and pay grade classification for each resource. A what-if analysis will be run to determine how much budget will be required for payroll based on changes in the assumptions.

  • Metric: This dimension will store members such as ‘Hours Worked’, ‘Pay Grade’, ‘Total Compensation’, and so on

  • Geography: The location that will receive the resource.

  • Time: Budget for headcount to be done at the year level.

  • Employee: A list of existing resources and new TBH placeholders.

  • Pay Rates: This model is used to set the base pay rates for the year. The pay rates information will be fed into the HR Budget model as base assumption data for use in calculations.

  • Time: Pay rates are entered at the year level.

  • Pay Grade: A list of pay grades that will determine the base salary.

  • Exchange Rates: The exchange rate model is used to determine the currency conversion rates to use by month when it converts data from the forecast model into the financial consolidation model. Since data is entered into each geography by its local currency, the exchange rate table will be used for currency translation rules and data flow packages.

  • Time: Exchange rates are entered in by month.

  • SourceCurrency: The source currency of a conversion.

  • DestinationCurrency: The destination currency of a conversion.

  • Financial Consolidation: The financial consolidation model is used for financial reporting using a single currency across all geographies.

  • Account: Consolidated chart of accounts.

  • Scenario: Will contain ‘Actual’ and ‘Forecast

  • Time: The lowest level of granularity will be months

  • Geography: All the geographies that have P&L

  • Currency Type: View the data either in reporting currency (EUR) or in the local currency, in which case is determined by the currently selected geography in filter.

With a good understanding of the models that are needed, we can set up the data store. Here we will have five fact tables and the appropriate dimension and hierarchy tables. These tables will be arranged in a star schema with the fact tables sitting at the center and the dimension and hierarchy tables forming the outer points of the “star” schema. By defining relationships via foreign keys between the fact tables, dimensions and hierarchy in the data store, we can quickly generate the data source view in SSAS when it comes time to building out our cubes and dimensions.

Creating dimension tables

Dimensions are the building blocks of any multidimensional database. Grouping a set of dimensions together will form the general basis for a cube. Dimension tables store data of a particular kind together. For example, you have a dimension table to store all the account members together, with each row of the table representing a unique account member of the dimension. Dimension tables can also store any related properties together via table columns. For example, on the Account dimension we have a column called ‘AccountType’ that stores the particular account type for the dimension member.

MemberId MemberLabel MemberName SortOrder AccountType ExpenseType

1

3100

Sales Revenue

100

Income

not applicable 

2

3200

Other Operating Revenue

200

Income

 not applicable

3

8100

Interest Revenues

300

Income

 not applicable

MemberId MemberLabel MemberName SortOrder Input Currency Target Currency

1

SEA

Seattle

100

USD

USD

2

OLY

Olympia

200

USD

USD

3

SPK

Spokane

300

USD

USD

Recommendation

We recommend that the following fields be created for a dimension table:

Id: We recommend that dimension keys be of integer type (TinyInt, SmallInt, Int, BigInt) versus any other type for optimal performance. Please refer to the performance section for additional reading. Also, use the most appropriate data type based on the sizing of the dimension.

Label: Use a unique code for the caption/name display of a dimension member. Keeping this unique will let you author cube based rules using MdxScript that is human readable versus member specification using key value notation, such as ‘&[key]’.

Name: All too often end-users will want to see members of a dimension with a friendly name instead of the label code. For instance, in our solution we have account codes used in label which will makes sense when authoring calculation rules but not when they are displayed in a PivotTable. Creating this property ensures you can easily update display names without affecting any underlying logic to rule definitions.

Order: It is a good idea to have a sort column that can be used by the dimension to determine the ordering of the dimension members.

Dimension tables for planning scenarios typically do not exceed 200,000 members. If you are working with dimensions that have become very large, we recommend that you trim down the dimension. Any data associated to dimension members that are trimmed can be aggregated together and feed to other dimension members. As a rule, the smaller the dimensions, the better the planning cubes will perform overall.

Note

Dimension columns and member properties are closely related.

Creating hierarchy tables

Hierarchy tables are needed when you use the Parent-Child hierarchy feature in SSAS. The parent-child hierarchy should use three columns, key (which represent a member of the hierarchy’s dimension), parent key (another member from the same dimension) and sort column for ordering of the members in a level of the hierarchy. Most parent-child hierarchies are supported by these 3 columns except when it requires custom member aggregation. For example, a chart of accounts will need custom aggregation to be defined. Account aggregations are determined by each account member’s account type and their respective parent account member. To support hierarchies requiring custom aggregation, we must create a fourth column. That column, the Unary Operator column will contain the following values, +, - and ~ with + meaning aggregate to parent, - meaning subtract from parent and ~ meaning ignore aggregation to parent.

Id Parent Id Sort Order Unary Operator Label Name

1

102

100

+

3100

Sales Revenue

2

102

200

+

3200

Other Operating Revenue

3

103

300

+

8100

Interest Revenues

4

103

400

+

9100

Gain on Sale of Assets

Id Parent Id Sort Order Label Name

1

4

500

SEA

Seattle

2

4

700

OLY

Olympia

3

4

600

SPK

Spokane

Level-based hierarchies are created based on the columns defined on a dimension. The columns of a relational table can construct attribute hierarchies in SSAS. Defining relationships between attribute hierarchies will let you create efficient level based hierarchies. For the time being, it will be sufficient to include all the related properties of a dimension as columns on the dimension table when it comes time to level based hierarchies in SSAS.

For more information, see Hierarchies and Levels.

Creating fact tables

Fact tables store all the numeric data for a cube. The number of columns in a fact table will vary depending on how many dimensions are associated to the cube. For instance, the Forecast cube has 7 columns, with 6 columns representing each dimension that relates to the forecast cube and one to store the number value. The column that stores the numeric value is called the ‘measure.’ In our solution, we use only one measure for the fact table.

Each column other than the measure column is related to a dimension via its key. In the following example, we see that the HR Budget model has five dimensions that relate to the fact table, and each row of the fact table represents a single fact record. It is good practice to avoid any duplicate values in the fact records on the dimension keys, for example if we have the same value across all the dimension keys on multiple fact rows. If this is the case, combine the value into a single row.

Rowld Metric ID Geography ID EmployeeID TimeID Value

1

2

15

1010

20100000

2000

2

2

15

1009

20100000

2000

3

2

15

1008

20100000

2000

Rowld Account ID TimeID ScenarioID Geography ID ProductID VersionID Value

1151

1

20100200

1

1

232

1

1391

1153

1

20100400

2

1

232

1

1124

1155

1

20100600

2

1

232

1

1322

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