Cube modeling with Excel PowerPivot in BI planning solutions and scenarios


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2011-01-24

In this article:

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.

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.

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

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.

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.