Category: SSIS/SSAS/SSRS/MDX

MDX #4 – Sales Ratio aganist Parent Levels

Business Question 1: How much sales percentage does each product sub-category take aganist the total sales of ‘Components’ cateogry in 2007

In the example above, the .parent is used to return the current product’s parent category. Alternatively, ANCESTOR function can also be used which could specify the level of the category (e.g, we hope to get the percentage of current product aganist top-level product category).


WITH
MEMBER [Measures].[Product Sale] AS
SUM(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sale in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sale in Category]=0,
NULL,
[Measures].[Product Sale]
/[Measures].[Total Sale in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sale],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
ORDER
([Product].[Product Categories].[SubCategory].members
,[Measures].[Sales % of Parent Category], BDESC) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)

Where [Date].[Calendar].[Calendar Year].&[2007]

Business Question 2: List product sub-categories which make up 80% of total sales in 2007

We could acheive this using TOPPERCENT function.

WITH
MEMBER [Measures].[Product Sales] AS
AGGREGATE(
[Product].[Product Categories].currentmember,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Total Sales in Category] AS
AGGREGATE(
[Product].[Product Categories].currentmember.parent,
[Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales % of Parent Category] AS
IIF ([Measures].[Total Sales in Category]=0,
NULL,
[Measures].[Product Sales]
/[Measures].[Total Sales in Category]
)
, FORMAT_STRING="##.#%"

SELECT
{
[Measures].[Product Sales],
[Measures].[Sales % of Parent Category]}  ON COLUMNS,
TOPPercent(
[Product].[Product Categories].[SubCategory].members
,80
,[Measures].[Sales % of Parent Category]) ON ROWS
FROM
(SELECT [Product].[Product Categories].[Category].&[2] ON COLUMNS
FROM [Adventure Works]
)
Where [Date].[Calendar].[Calendar Year].&[2007]

Business Question 3: ABC analysis on the product Sales in UK in 2007

We want to classfy the products based on their sales amount in UK in 2007 into three categories: ‘A’ (make up the top 5% of total sales), ‘B’ (make up the top %5-20%), ‘C’ (the others). Firstly, we need rank the products based on their sales amount in UK in 2007 and calculate the cumulative sales amount by the rank. Then, we need calculate the percentage of the cumulative sales amount for each product and check which category is fallen into.

WITH
SET [Products on Sales] AS
ORDER
(
EXISTING NONEMPTY ([Product].[Product Categories].[Product].MEMBERS,
[Measures].[Sales Amount])
, [Measures].[Sales Amount], BDESC
)

MEMBER [Measures].[Cumulative Sales Amount by Rank] AS
SUM (
HEAD ([Products on Sales]
, RANK ([Product].[Product Categories].CURRENTMEMBER,
[Products on Sales])
)
, [Measures].[Sales Amount]
)

MEMBER [Measures].[Cumulative Sales % of parent category] AS
[Measures].[Cumulative Sales Amount by Rank]
/SUM ([Products on Sales],[Measures].[Sales Amount])
, FORMAT_STRING = "percent"

Member test as
[Products on Sales].CURRENTMEMBER.LAG(1).name

MEMBER [Measures].[Classfication Category] AS
CASE
WHEN [Measures].[Cumulative Sales % of parent category]<=.05 THEN 'A'
WHEN [Measures].[Cumulative Sales % of parent category]<=.2 THEN 'B'
ELSE 'C'
END

SELECT {[Measures].[Sales Amount],[Measures].[Classfication Category]} ON COLUMNS,
[Products on Sales] ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Calendar Year].&[2007]
,[Sales Territory].[Sales Territory].[Country].&[United Kingdom])

MDX #3 – Year-Over-Year Comparison within Given Period

Requirement: Users specify a date range by giving a Start Date and a End Date, and expect to make Year-Over-Year comparison of a measure (e.g. Sales Amount) in the given period. For example, a user input start date as “04/07/2007” and end date as “04/11/2007”, and want to compare the sales between the period “04/07/2007 – 04/11/2007” and the same period in last year “04/07/2006 – 04/11/2006”.

First, we need get the data range using the range operator ‘:’ on the given start date and end date, and aggregate the sales occurred within that period.

MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

Then we could use PARALLELPERIOD to get the same period in last year. Please note that we could use range operator within a PARALLELPERIOD function, so we have to separately PARALLELPERIOD the start date and the end date, and use range operator afterwards.

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060211]")
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER("[Date].[Calendar].[Date].&[20060911]")
)
}
, [Measures].[Reseller Sales Amount]
)

Below is the complete script:

WITH
MEMBER [Measures].[Sales in Given Period] AS
AGGREGATE (
{STRTOMEMBER(@StartDate, CONSTRAINED) : STRTOMEMBER(@EndDate, CONSTRAINED) }
, [Measures].[Reseller Sales Amount]
)

MEMBER [Measures].[Sales in Given Period Last Year] AS
AGGREGATE (
{
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@StartDate, CONSTRAINED)
):
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year]
,1
,STRTOMEMBER(@EndDate,  CONSTRAINED)
)
}
, [Measures].[Reseller Sales Amount]
)

SELECT {
[Measures].[Sales in Given Period],
[Measures].[Sales in Given Period Last Year]
} ON COLUMNS,
{
[Product].[Product Categories].[Category]
} ON ROWS
FROM [Adventure Works]

Tip: when using STRTOMEMBER function to convert the data string into Member value, the data string has to be in correct format. We could directly pass data parameter into MDX query and build the data string to the correct format in the MDX script. However, a better way is to build the data string at SSRS report level (using expression in the parameter of dataset) as the snapshot shown below:

MDX #2 – Top (n) Products Sales in each Sales Territory

To get top (n) products with highest sales in every sales territory, the GENERATE function is exactly what we need which applies a set to each member of another set.

SELECT
	{[Measures].[Reseller Sales Amount]} ON Columns,
	GENERATE(
		[Sales Territory].[Sales Territory].[Country].MEMBERS
		,TOPCOUNT([Sales Territory].[Sales Territory].CURRENTMEMBER
			*[Product].[Product].[Product].MEMBERS
			,5
			,[Measures].[Reseller Sales Amount]
		)
	) ON ROWS
FROM [Adventure Works]

MDX #1 – Year-Over-Year Comparison

1. Monthly or Quarterly Year-Over-Year Comparision

PARALLELPERIOD is the function to get the same period in previous year.

WITH
	MEMBER [Measures].[SaleInCurrentYear] AS
		AGGREGATE(
			[Date].[Calendar].currentmember,
			[Measures].[Reseller Sales Amount]
		)

	MEMBER [Measures].[SaleInPreviousYear] AS
		AGGREGATE(
			PARALLELPERIOD(
				[Date].[Calendar].[Calendar Year],
				1,
				[Date].[Calendar].currentmember
			)
			, [Measures].[Reseller Sales Amount]
		)

	MEMBER [Measures].[Sales Growth] AS
		IIF ([SaleInPreviousYear]=0,
				NULL,
				([Measures].[SaleInCurrentYear] - [SaleInPreviousYear])
				/[SaleInPreviousYear]
			)
		, FORMAT_STRING="##%" 

SELECT
	{
		[Measures].[SaleInCurrentYear],
		[Measures].[SaleInPreviousYear],
		[Measures].[Sales Growth]
	 } ON COLUMNS,
	 {
		[Date].[Calendar].[Month].MEMBERS
	 } ON ROWS
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].&[2007]}

2. YTD Year-Over-Year Comparison

YTD function can be used to get the period from start of this year to current date. To get the same YTD period in last year, we could combine the YTD and PARALLELPERIOD used above.

With
	MEMBER [Measures].[YTD Sales] AS
		AGGREGATE (
            YTD([Date].[Calendar].CurrentMember),
            [Measures].[Sales Amount]
        )

	MEMBER [Measures].[Last YTD Sales] AS
		AGGREGATE(
            YTD(
                ParallelPeriod(
                    [Date].[Calendar].[Calendar Year],
                    1,
                    [Date].[Calendar].CurrentMember
                )
            ),
            [Measures].[Sales Amount]
        )

     MEMBER [Measures].[YTD Sales Growth] AS
		[Measures].[YTD Sales] - [Measures].[Last YTD Sales]

SELECT
	{[Measures].[Last YTD Sales],
     [Measures].[YTD Sales],
     [Measures].[YTD Sales Growth]} ON COLUMNS,
    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2007], 2 ) ON ROWS
FROM [Adventure Works]

3. Fiscal YTD Year-Over-Year Comparison

Fiscal year is often not aligned with calendar year, so we could not use YTD but instead using PERIORDTODATE function.

WITH
	MEMBER [Measures].[YTD Sales] AS
		AGGREGATE (
            YTD([Date].[Calendar].CurrentMember),
            [Measures].[Sales Amount]
        )

	MEMBER [Measures].[Last YTD Sales] AS
		AGGREGATE(
            YTD(
                ParallelPeriod(
                    [Date].[Calendar].[Calendar Year],
                    1,
                    [Date].[Calendar].CurrentMember
                )
            ),
            [Measures].[Sales Amount]
        )

     MEMBER [Measures].[YTD Sales Growth] AS
		[Measures].[YTD Sales] - [Measures].[Last YTD Sales]

SELECT
	{[Measures].[Last YTD Sales],
     [Measures].[YTD Sales],
     [Measures].[YTD Sales Growth]} ON COLUMNS,
    DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2007], 2 ) ON ROWS
FROM [Adventure Works]

Tip & Tricks – SSAS Performance Tuning Flow Chart

In the Analysis Services Performance Guide white paper, Microsoft has introduces a very handy flow chat for tuning SSAS performance.

The first and key step of the flow chat is to investigate whether the performance problem is caused by query processor or storage engine. If the problem lies in query processor, SSAS developers need to focus on optimising MDX scripts. If the problem lies in storage engine, SSAS developers need considering to optimise dimension design, design aggregations, or use partitions to improve query performance.

The white paper has introduced a method to determine whether the problem lies in the query processor or storage engine with the help of SQL Server Profiler. Firstly, create a trace, select only query subcube verbose and filter the event on event subclass = 22 (non-cache data). Then check whether the majority of time is spent in the storage engine with long running query subcube events. If the answer is yes, there is a high possibility that the problem lies in storage engine. Otherwise, the problem lies in query processor.

Creating Custom SSRS Spatial Map Reports for Dynamics CRM 2011

A CRM system in an organisation is often the central place to store business data related to customers, sales, and markets, which are highly valuable for any business. The BI feature in Dynamics CRM 2011 is very powerful which allows developers or even end-users to create report tables or charts very easily. However, it is better to visualise some types of business data against a geographical background, e.g, customers geographical distribution or sales geographical distribution.

This blog post goes through the steps to create a spatial map report (customers geographical distribution in UK) using SSRS for Dynamics CRM 2011. Please find the sample report from my Codeplex site.

Create a SSRS project in BIDS, and add a report.
Create a Data Source connecting to the Dynamics CRM organisation database, and create a Data Set to query the customer lists (in this example, I used Account entity) from the CRM database.
Convert UK postcode (stored as customer’s attribute in Account entity) to spatial data format type (latitude and longitude). Although the Account entity has fields to store latitude and longitude values of an address, few of users would actually input latitude/longitude into MSCRM. Instead, most of users prefer to store postcode in MSCRM to record the location of an account. However, the spatial data type supported by SQL Server 2008 R2 is converted from latitude/longitude instead of postcode. Therefore, we need convert postcode (in UK) to latitude/longitude using a UK postcode dataset.Alex Whittles has a blog post which suggests a number of UK postcode datasets, and also introduces how to convert postcode to geography data type and how to use the spatial data in SSRS report.

Below is the complete query for the dataset in this example (based on the query suggested by Alex Whittles).

SELECT p.postcode,
    geography::STPointFromText('POINT(' +
         CAST(MAX(p.longitude) AS VARCHAR(20)) + ' ' +
         CAST(MAX(p.latitude) AS VARCHAR(20)) + ')', 4326).STBuffer(2000)  AS Geog,
       Count(*)as [Count]
FROM [YOURSERVER_MSCRM].[dbo].[FilteredAccount] a
Inner join [UKPostcodeTable] p on  p.postcode =
  CASE WHEN
            CHARINDEX(' ', a.address1_postalcode)=0 THEN a.address1_postalcode
            ELSE LEFT(a.address1_postalcode, CHARINDEX(' ', a.address1_postalcode)-1)
           END
GROUP BY p.postcode

Add a Map to the report which consumes the spatial data from the dataset we just created.

Deploy the report we just created in BIDS on to MSCRM 2011.

Publish the report for external use

Get the URL of the deployed report (though web service of the report server)
Create a IFRAME which links to the report (don’t forget to hide the toolbar of the report viewer by adding ‘rc:Toolbar=false’ to the URL), and add the IFRAME to a dashboard.

And then, we have a spatial map showing on MSCRM 2011.

Business Question 1: How much sales percentage does each product sub-category take aganist the total sales of ‘Components’ cateogry in 2007

Business Question 2: List product sub-categories which make up 80% of total sales in 2007

Business Question 3: ABC analysis on the product Sales in UK in 2007

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: