Upgrade PowerPivot Data Model to SSAS Tabular

In the previous articles, I discussed the steps for building PowerPivot data models for Dynamics GP Sales and Financial (GL) data using PowerPivot for Excel. This is a great way for power users to build powerful business intelligence solutions without expensive hardware and difficult to use development tools. It is also a great way for professional BI developers to prototype a complete solution quickly.

However, there will be situations where PowerPivot based solutions may not meet your business needs. In this case, there are two upgrade paths available to migrate your PowerPivot data model. These are PowerPivot for SharePoint and SSAS Tabular. In this article, I will discuss SSAS Tabular.

Here are some of the reasons why you may want to upgrade from PowerPivot to SSAS Tabular.

Security

Tabular model databases can use row-level security, using role-based permissions in Analysis Services. For example, you can configure security to enable Sales Manager to view all sales data while Salesperson to view only his or her sales data.

Power Pivot workbooks are secured at the file level. A user who has access to the file can view all the information contained within the workbook.

Scalability

PowerPivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in Power Pivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a Power Pivot workbook to a tabular solution on a standalone Analysis Services instance is to get around the file size limitation.

Partitioning

Partitions, in tabular models, divide a table into logical partition objects. Each partition can then be processed independent of other partitions. For example, a table may include certain row sets that contain data that rarely changes (historical sales data, for example), but other row sets have data that changes often (current year’s sales data).

Query and Reporting

Excel and PowerView are your only options if you use PowerPivot. With SSAS Tabular, you can use many other reporting tools including SQL Server Reporting Services (SSRS).

You will find detailed comparison of SSAS Tabular and PowerPivot here.

Before you upgrade your PowerPivot data model to SSAS Tabular, you will need the following:

1. SQL Server Analysis Services instance running in Tabular mode (I used SQL Server 2014)
2. SQL Server Data Tools (SSDT) (This is required if you use Visual Studio to import your data model to SSAS Tabular. Please see below for details)

There are two ways you can import your PowerPivot data model to SSAS Tabular.

Option 1:

SQL Server Management Studio option: Import directly to SQL Server using Restore from PowerPivot option as shown in the following screenshot

Option 2:

Visual Studio (SSDT) option: Import using Visual Studio (SSDT) as shown in the following screenshot

Once completed, you will be able to connect to the SSAS Tabular data model on the server and create a report in Excel and other reporting tools such as SSRS.

Here’s the PivotTable created using Excel. You will notice that the presentation of PivotTable fields is slightly different from what you see when you create a PivotTable from PowerPivot data model. For example, KPIs have their own folder. However, the end result is the same.

Because the data is no longer stored within the Workbook but is stored on the server, the file size is very small as shown in the following screenshot.

After you have migrated the PowerPivot data model to SSAS Tabular, you do not need the original Excel workbook any more. You will need to use Visual Studio to make changes to the Tabular data model and implement new features such as security and partitioning.

If you would like to learn more about SSAS Tabular, you will find step by step tutorials here.

Build PowerPivot Data Model for Dynamics GP Sales Analysis

Power Pivot is an Excel 2013 add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. (See a related article Upgrade PowerPivot Data Model to SSAS Tabular).

In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.

The basic difference between Power Pivot and Excel is that you can create a much more sophisticated data model by working on it in the Power Pivot window.

Using PowerPivot and Excel, you can:

• Build an analytical database inside Excel by importing data from one or more data sources including SQL Server, Access, Excel and OData
• Build relationships among the data from multiple sources to analyze it as if it all originated from a single source
• Write advanced formulas with the Data Analysis Expressions (DAX) expression language to create calculated columns and fields
• Define Hierarchies to use everywhere in a workbook, including Power View
• Create KPIs to use in PivotTables and Power View reports
• Create Perspectives to limit the number of columns and tables your workbook consumers see
• Use various public and private data sources for analysis

In this article, I will describe the steps I used to build a data model to analyze Microsoft Dynamics GP Sales data.

The following screenshot shows the final PowerPivot data model in the diagram view.

The PowerPivot data model includes the following tables.

Table	Source	Additional information
SalesFact	SalesLineItems view	Filtered to retrieve only Invoice and Return line items. Signs reversed for Quantity, Cost Amount and Sales Amount for Return line items.
Customer	Customers view
Item	Items view
Site	IV40700 table
Salesperson	RM00301 table
Territory	RM00303 table
Calendar	Excel sheet containing dates and other related information (linked table)	Mark table as Date table for time based calculations Sort Month Name by Month (number) for correct sorting Hierarchy for Year, Quarter, Month and Day
Period	Pasted from Excel table	Used for time based calculation in DAX formula for: Current MTD QTD YTD PrevYear PrevYearMTD PrevYearQTD PrevYearYTD

Calculated Column and Fields

Calculated Column/Field	Formula	Additional information
Gross Margin	SalesFact[Sales Amount]-SalesFact[Cost Amount]	Calculated column. The rest are calculated fields.
Sales	SUM([Sales Amount])	Total sales based on calendar dates selection
SalesMTD	[Sales](DATESMTD(Calendar[Date]))	Total MTD sales based on the calendar dates selection
SalesQTD	[Sales](DATESQTD(Calendar[Date]))	Total QTD sales based on the calendar dates selection
SalesYTD	[Sales](DATESYTD(Calendar[Date]))	Total YTD sales based on the calendar dates selection
SalesPrevYearMTD	[Sales](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR))	Total MTD sales last year based on the calendar dates selection
SalesPrevYearQTD	[Sales](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR))	Total QTD sales last year based on the calendar dates selection
SalesPrevYearYTD	[Sales](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR))	Total YTD sales last year based on the calendar dates selection
SalesPrevYear	[Sales](DATEADD(Calendar[Date],-1,YEAR))	Total sales last year based on calendar dates selection
PeriodSales	IF(HASONEVALUE(Period[Period]),  SWITCH(VALUES(Period[Period]),  "Current", [Sales],  "MTD", [Sales](DATESMTD(Calendar[Date])),  "QTD", [Sales](DATESQTD(Calendar[Date])),  "YTD", [Sales](DATESYTD(Calendar[Date])),  "PrevYear", [Sales](DATEADD(Calendar[Date],-1,YEAR)),  "PrevYearMTD", [Sales](DATEADD(DATESMTD(Calendar[Date]),-1,YEAR)),  "PrevYearQTD", [Sales](DATEADD(DATESQTD(Calendar[Date]),-1,YEAR)),  "PrevYearYTD", [Sales](DATEADD(DATESYTD(Calendar[Date]),-1,YEAR)),  BLANK()))	Total Sales for the calendar dates selection and period (Current, MTD, QTD, etc.). This field can be dragged to the Pivot Table or report along with dates and period to dynamically calculate the sales amount.
YOYGrowth	IF([SalesPrevYear], ([Sales] - [SalesPrevYear]) / [SalesPrevYear], BLANK())	Year over year growth This field has an associated KPI (Less than -10% as red, -10% to +10% as yellow and greater than +10% as green)
GrossMargin	SUM([Gross Margin Amount])	Total Gross Margin based on calendar dates selection
GrossMarginPct	IF([Sales], [GrossMargin]/[Sales], BLANK())	Gross Margin Percentage
Cost	SUM([Cost Amount])	Total Cost based on calendar dates selection

The following screenshot shows the partial contents of the Calendar table. This table is marked as Date table.

The following screenshot shows the contents of the Period table which is used in the DAX formula for PeriodSales (see above).

The purpose of this table can be illustrated with the following two Pivot Tables which are essentially the same. However, the second Pivot Table was created by simply dragging the Period field to the columns area and the PeriodSales calculated field in the values area. The first Pivot Table requires adding each field to the values area i.e. Sales, SalesPrevYear, SalesMTD, etc.

Here are a few sample reports created from the data model.

Top 10 Customers - Sales for current year, previous year and YOY growth

Top 10 Items - Sales for current year, previous year and YOY growth

Top 10 Items - Sales, Cost, Gross Margin and Gross Margin Percent

Sales for current year and previous year and YOY growth by Location

Sales for current year and previous year and YOY growth by Salesperson

Sales for current year and previous year and YOY growth by Territory

PowerView report – Sales by State

Here are a few other reports you can build using this model:

• Fastest/slowest growing products
• New customer additions this year/quarter, etc.

In addition to the above sample reports, there are many more possibilities for enhancing the data model for creating more insightful reports depending on the available data and your business needs. For example, if you are a retailer and maintain square footage for each location in the Location table, you can create a calculated field for Sales per Square Foot. Similarly, if you maintain the employee count for each location, you can create a calculated field and KPI for Sales per Employee or Gross Margin per Employee.
You may also combine data from other sources like CRM to create meaningful reports.