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.

Tuesday, December 24, 2013

Create Calendar style report with Gauge Indicator using SQL Server Reporting Services

In the previous post, I described the steps to create a calendar view report using a matrix type data region. This works well for many operational scenarios described in the article. The report provides detailed numerical information such as billable hours, number of phone calls made, and Internet sales on the e-Commerce web site that can be useful for department level and mid-level managers and supervisors.


However, this report may include too much detailed information for higher level managers and executives. They may want to see the exception based report that highlights the numbers that do not meet a certain target or standard.
Once you have created a matrix report described in the previous post, it is quite easy to convert it to a graphical report using the gauge component available in SSRS.


Simply drag the Gauge control on the top of the field in the matrix that you want to report on.



If you right click on the control, click Indicator Properties and select the Value and States tab, you will see the following window.



Let us review the properties in this window.


Value: This defaults to the underlying field used to evaluate the indicator.


States Measurement Unit: This can be a percentage or numeric i.e. the absolute value of the field above. You may also use a function to calculate this value. It defaults to Percentage. I will explain how the Percentage is calculated below.


Synchronization scope: In this case, it defaults to the entire matrix of which the cell is part of.


Minimum and Maximum: Both of them default to “Auto”. In other words, it uses the minimum and maximum values from the entire matrix for the purpose of calculation.


Indicator states: By default, you get 3 states representing 3 different color icons. You can click “Add” to add more states or “Delete” to delete a state.


Now, let us look at how the indicator is evaluated. SSRS first finds the minimum and maximum values in the matrix. It then divides the range i.e. the difference between the minimum and maximum, using the Start and End values in the Indicator states table above. So, using the default numbers in the table above, it divides the range into bottom 33%, 33% to 66% and 66% to 100% and then calculates the target sales numbers to be used for evaluating the indicators. In the example for June 2007 below, the minimum number for the month is $9,130 and maximum number is $31,241. The range is $22,111 i.e. the difference between the minimum and maximum. Based on this range, the states are assigned as follows:


$9,130 to $16,426.63: Red


$16,426.64 to $23,723.26: Yellow


$23,723.27 to $31,241: Green






I used the default states and start and end values. However, you can change the values based on your business needs. For example, you may want to include bottom 20% of the values in the “red” state and top 20% of the values in the “green” state.


If you have the budgets or sales quota stored in the database, you can use this data for states and start and end values. You will need to use the expression to populate these values.


You may create a drill-down report if the manager is interested in viewing underlying data that makes up this indicator. You can configure this on the Action tab of the Indicator Properties window.


The report definition (RDL) file for this report is available here.

Thursday, July 18, 2013

Create Calendar style reports in SQL Reporting Services

Sometimes it is easier to visualize the activity related information in a calendar view. Here are a few scenarios where Calendar style report provides a better view of information than a standard table or list.


- number of outbound telephone calls by salesperson or department in a telemarketing company
- billable and non-billable hours by consultant/department in a consulting company
- Internet sales on the e-Commerce web site


You can create a calendar style report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.

In this article, I will explain the steps required to create such a report from AdventureWorksDW database (available for download on Codeplex). I have also provided a link to download the report definition (RDL) file below.


Here are the key points to note for creating a calendar style report using SSRS.


1. Dates or Calendar table: We will use this table to include date, week number and day of the week information in the data set. This table contains all dates in each calendar year you will be reporting for. The AdventureWorksDW has the DimDate table that contains this information. If you do not have such as table in your database, you can create a temp table and use it in the query.


2. Outer Join: We will need to use the Outer Join between the dates table and the fact table so that all dates are included in the data set whether there is data or activity for that date or not.


3. Week Number: We will need this field to generate the matrix. This field has erroneous data in the AdventureWorksDW database. Therefore, I had to create a calculated field to calculate this value. Please see below for details.


4. Use Matrix to create the Calendar view. The Matrix has the WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. These fields are used only for grouping and should be hidden in the final report design. The WeekNumber can be any number from 1 through 53. The DayNumberOfWeek can be any number from 1 through 7 – 1 being Sunday and 7 Saturday.


5. The matrix can have from 4 to 6 rows depending on the number of weeks in the month and the day on which the month starts. It always has 7 columns – one for each day of the week.


The screen shot below shows the query used for the data set used in the matrix. Note the LEFT OUTER JOIN between DimDate and FactInternetSales tables.




As mentioned earlier, the week number field in the DimDate table in AdventureWorksDW has erroneous data. Therefore, I created a calculated field WeekNumber as follows:


=DatePart(DateInterval.WeekOfYear, CDate(Fields!FullDateAlternateKey.Value))


The following screen shot shows the report in the design mode. As you will notice, the matrix uses WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. EnglishDayNameOfWeek is used to display the days – Sunday through Saturday.




The following screen shot shows the sample output for the month of June 2007. This month has 5 rows representing 5 weeks and 7 columns representing Sunday through Saturday.




The following screen shot shows the sample output for the month of September 2007. Note that this month has 6 rows representing 6 weeks.




The report definition (RDL) file is available for download here.


Please also see the related post to display gauge indicator on the calendar style report.

Sunday, March 14, 2010

Scribe Insight 7.0 supports multiple targets

Scribe Software recently announced the availability of Scribe Insight 7.0. One of the most important features of this release is the ability to connect to more than one target in the same DTS.

The blog post on Scribe's web site describes various scenarios where this feature can be valuable. These include:
  1. If the source record was triggered via a publisher, then the update source feature is not available since the source for that DTS is a message queue and not the original application which triggered the integration.
  2. The source system uses an adapter which does not support the update source feature (for example, Scribe adapter for Dynamics CRM).
  3. The source query is a complex join which update source cannot support.
  4. You need to be able to update multiple fields on the source record.
The scenarios 1 and 2 above are applicable in most closed-loop integrations between Dynamics CRM and another ERP system.

For example, because the previous release of Scribe Insight did not support multiple target connections in the same DTS, Dynamics GP to Dynamics CRM template has to use two separate DTSs for CRM to GP Sales Order integration as follows:
  1. OrderToERP.dts: Sends submitted orders from Dynamics CRM to Dynamics GP
  2. OrderToERP_Result.dts: Updates the Sales Order in Dynamics CRM based on the result of the OrderToERP.dts
Now, with Scribe Insight 7.0, you can combine both of the above steps in one DTS using both Dynamics GP and Dynamics CRM as targets.

This new feature will be very valuable for addressing many Dynamics CRM integration needs including the one above.