Cube modeling with Excel PowerPivot in BI planning solutions and scenarios

 

Applies to: SharePoint Server 2010 Enterprise

In this article:

  • Modeling with PowerPivot

  • Connect to a relational database

  • Select dimensions and facts

  • Manage table relationships

  • Publish to SharePoint

Modeling with PowerPivot

An alternative to data modeling in Microsoft SQL Server 2008 Analysis Services (SSAS) is to build the multidimensional model directly in Microsoft Excel 2010 by using PowerPivot. PowerPivot provides the ability for users to build sophisticated data models from the familiar Excel environment that most IWs are comfortable with. Some considerations to note when you are building PowerPivot models including the following:

  • PivotTables do not support write-back to PowerPivot models.

  • Security is defined over the workbook and not on particular slices of PowerPivot data.

  • No support for parent child hierarchies.

PowerPivot is designed for the group of users that want to work with data directly. Assuming that IWs have access to pre-scrubbed data, PowerPivot provides the mechanism for self-modeling or self-service BI.

We build the Forecast cube with PowerPivot with the same dimensionality as the one we have for the SQL Server 2008 Analysis Services cube. To do this, we will take advantage of several of the tables that we have previously defined in our relational data model. These include the dimension and fact tables.

Note that we can basically borrow the same see comment-dimensional design from our SQL Server 2008 Analysis Services-based cube for use in our PowerPivot model, with the main exception being there is no native support for parent child hierarchies. When we have to show a Chart of Accounts, we can use Data Analysis Expression (DAX) to emulate the necessary account aggregations for use in the PowerPivot model.

The overview of the process to build a PowerPivot model from the relational tables we have includes the following:

  • Connect to relational database.

  • Select the appropriate dimension and fact tables.

  • Design table relationships.

  • Publish to Microsoft SharePoint Server 2010.

Connect to a relational database

With PowerPivot client Add-in installed within Excel 2010:

  1. Start the PowerPivot window under the PowerPivot tab.

  2. From the Home tab, click From Database, and then click From SQL Server.

Select dimensions and facts

For the Forecast model, we will bring in the following tables to our PowerPivot model:

  • D_Account

  • D_Geography

  • D_Product

  • D_Scenario

  • D_Time

  • F_Forecast_CoreMG_Writeback

Manage table relationships

Next, we will define the relationships between our fact tables with all the related dimension tables. The MemberId columns are the primary keys on the dimension tables, whereas the fact table contains 5 foreign keys relating back to the dimension tables.

When the data model in PowerPivot is complete, you can immediately start to explore by using PivotTables. Here you can select what you want to see on rows, columns, slicers and data. A very nice feature in PowerPivot PivotTable is the slicers that let you quickly filter down the data that is relevant to you.

Publish to SharePoint

Using SharePoint Server 2010 PowerPivot Gallery, IWs can store data models created by PowerPivot. By using PowerPivot models, IWs can build dashboards, reports and scorecards. The same goes for building forms to support functions of planning, budgeting and forecasting. As called out previously, PowerPivot does not natively support many planning based scenarios such as PivotTable writeback to the underlying data model.

Once the model is complete and ready to be published on SharePoint, the model can be saved directly to the URL of the PowerPivot Gallery location. IWs can consume the shared data model either by creating a workbook off the shared data model or directly updating the PowerPivot workbook.

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