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.

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: