Saturday, November 22, 2014

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.

2 comments:

Anonymous said...

Where can I get the data for populating Calendar table? Thanks.

Abi Shende said...

You can download the Calendar data from the following link: https://onedrive.live.com/redir?resid=9678c45f4a823615%218611