MS BICOE All about data​: dig, distill, visualize, prognosticate & prescribe

Comparing to past 2 years, the latest published Gartner Magic Quadrant for Business Intelligence and Analytics Platforms has some very interesting changes.

There are only 3 big players stay in the first group: Tableau, Qlik and Microsoft. Given that the MS did a fantastic job on the new Power BI platform, I'm not surprising MS is waken up and rising in this space. I was surprised that Microstrategy, IBM, SAP and Information Builders are fallen out the first group. Particularly, Oracle is dropped off the radar.

This is getting more and more interesting and look forward to how all these players doing this year.

For last few of year, the overseas purchasing agents procurement service business, so-called 'Dai Gou' in Chinese,  has largely impacted the over OTC consumer market in Australia, particularly for those categories like infant formula, multivitamin and supplements and fertility related etc.

This second-hand exporting business has a tremendous impact on Pharma company's business and stocking plan, territory sales targeting and incentive distribution etc.

This is an example how we can utilize the Power BI visualization to perform the exporting analysis on at the outlet and product level and therefore find a solution to calculate the exporting portion of the business.

 Disclaimer: the data used for this example is randomly generated based on seeds and some rules and the pharmacy outlets or banner names are public available information. The purpose of the post is to show how we can utilize MS Power BI to perform analysis, therefore some data is deliberately weighted or distorted to show the trend and exporting proportion. There is no association of data displayed here with to those names in real world.

Slidify is an R package helps to create and publish beautiful HTML5 presentations from RMarkdown. Slidify package is fantastic for creating reproducible presentations.

slidifyLibraries contains external resources used by Slidify.

rCharts is an R package to create, customize and publish interactive javascript visualizations from R using a familiar lattice style plotting interface.

All of these three packages are created and maintained by Ramnath Vaidyanathan of McGill University.  Since all these three packages are not on CRAN and needs to be installed from github using the Hadley's devtools package.

library(devtools)

install_github('ramnathv/slidify')

install_github('ramnathv/slidifyLibraries')

install_github('ramnathv/rCharts')

library(devtools)

build("C:/packages/slidify/", binary=FALSE) # Change C:/packages/ to your folder name

build("C:/packages/slidifyLibraries/", binary=FALSE) # Change C:/packages/ to your folder name

build("C:/packages/rCharts/", binary=FALSE) # Change C:/packages/ to your folder name

# Change C:/packages/ to your folder name

install.packages("C:/packages/slidify_0.5.tar.gz", repos = NULL, type="source")

# Change C:/packages/ to your folder name

install.packages("C:/packages/slidifyLibraries_0.3.1.tar.gz", repos = NULL, type="source")

# Change C:/packages/ to your folder name

install.packages("C:/packages/rCharts_0.4.5.tar.gz", repos = NULL, type="source")

In business analysis, it is very common to break sales into different quantile groups and then it can be used for segmentation purpose. The most commonly used quantiles are:

• Quintiles which creates 5 equal size groups
• Deciles which creates 10 equal size groups

Occasionally, Ventiles (20 groups) and Percentiles (100 groups) will used.

Quantiling process is very powerful when 2 dimensional of sales data is available, like product sales and market sales. In business it is very common to use quantile matrix to perform the account segmentation. An example of segmentation definition can be based on a 2 X 2 deciling matrix like below:

Even this is a very common process, it is indeed not a straightforward simple calculation to get quantile values in SQL server. There are many ways we can do to calculate such, but I found that use window function in conjunction with CEILING function is the easiest way to calculate quantile value. Let’s try. I will use a deciling process to start with.

The first step to calculate decile value for a specific object (Account, Product etc) is to calculate cumulative values and then calculate the cumulative percentage.

This will generate the cumulative product sales value and its cumulative percentage. the top 10 records as below:

Now the question is how can we assign a decile value based on the cumulative percentage value? there are many ways to do it. But as I found that there is a very simple way to do it by using SQL CEILING function:

You can randomly check the results:

Based on this deciling calculation, we can generalize the quantiling calculation on any quantile number between 1 and 100.

I hope this tip is useful and please see the attached scripts if you want to have a try.

Thanks.

Power View Report embedded view is indeed a very nice feature and opens up a big opportunities for ISVs and companies and individuals to share data and visualizations. Please check this blog, Embed Power View Interactive Reports in Your Blogs and Websites, for more details about how to embed a power view report.

While this is great news, I also found that there are some frustrations. The embedded view is rendered nicely in IE 9 but there are issues with IE 11 and other main browsers. 

I have put together a few mains issues I found. If you have same issue and different issue please share your experience with me. Thanks.

In order to make SQL Server 2012 MDS functioning properly under Windows Server 2008 R2, the minimum windows features must be met.

1.       1. Add Application Server role

The following role servers for Application Role need to be included:

·         .NET Framework 3.5.1

·         Web Server (IIS) support

·         HTTP Activation

As illustrated in the diagram below (ticked services):

1.       2. Add Web Server (IIS) role

Make sure that the yellow highlighted services are ticked.

1.     3.  Check additional windows features

Make sure the Features listed below (yellow highlighted) are installed: