Re: Are Tableau and Power BI the future of Business Intelligence?

August 20, 2015 Leave a comment

Recently, I come across a LinkedIn post regarding MS Power BI and Tableau: Here

I love both and I posted my comment:

No doubt Tableau offers the best interactive data visualization front end but is weak at data preparation. You always need to use Tableau together with at least one data preparation package such as Alteryx, which is popular in high end Tableau community. With the recent release of Tableau 9.0’s LOD calculation,it is closing the gap with traditional OLAP technology such as SQL Server Analysis Services Multi-dimensional mode. But not there yet, for example, the lacking of SCOPE assignment (cell value overriding). But I do admit, using Tableau is addictive. It is an art, not just a tool.

Power BI is different. Within Power BI stack, Microsoft Power Query has VERY strong self service data preparing (ETL) and sharing (shared query repository, a.k.a Data Catalog) capabilities. Power Pivot, on the other hand, is a very powerful true (multi-tables) data modelling engine. You can build full dimensional modelling in Power Pivot and you can scale up from desktop based Power Pivot to server based SQL Server Analysis Services Tabular cube. Combined with the next generation SQL Server 2016, it is deeply integrated into the whole Microsoft Data Platform not just BI but whole data management landscape.

Categories: BI, Power BI, Power Query, Tableau

Use Power Query to connect to Salesforce custom domain

February 14, 2015 2 comments

Power Query Salesforce connector is great but it does not support Salesforce custom domain at the moment.

Well, that is the limitation of using the PQ UI. You can actually make it work if you use M directly.

With the help of Curt Hagenlocher from Microsoft, I figured out how. What he said:

You should be able to use a custom domain in Power Query itself by manually editing the formula to include the login domain name. That is, instead of
You’d have



Categories: BI, Power Query Tags: ,

A thought-provoking post – The Future of Data Warehousing: 7 Industry Experts Share Their Predictions

January 7, 2015 Leave a comment

The Future of Data Warehousing: 7 Industry Experts Share Their Predictions discusses the modern data warehouse architecture and emerging trends.

Categories: BI Tags:

Follow Up of Chris Webb’s post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” – Using Dynamic Value

November 28, 2014 Leave a comment

Another great blog post of “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query” from Chris Webb.

In his post, he showed a way to use a dropdown instead of a plain text box to prompt the user to select the value for the parameter of a Power Query function.

In his code between line 13 and 17:

MySecondNumberParamType = type number
                        [Documentation.Description = "Please choose a number",
                         Documentation.AllowedValues = {1..5}],    

The allowed value 1,2,3,4,5 is hard-coded. I wonder can I use a Power Query function to dynamically populate the AllowedValues instead? So I did the following test:

  1. Create a single column table in Excel, looks like the blow.

  2. Add this table to Power Query (Power Query ribbon –> From Table) and name this Power Query as “Table_Param”
  3. Change the column type to Text. This is important, see blow:
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", type text}})
  4. Go to Chris’s Power Query “MultipleTwoNumbersV2” and open the advanced editor
  5. Change the code from
    Documentation.AllowedValues = {1..5}], 


    Documentation.AllowedValues = Table.ToList(Table_Param)],
  6. Now try to invoke this Power Query function and you will find the dropdown box having the value return form the function.

This dynamic value approach should enable you to get the data from various sources e.g. database, Excel, web etc

However, there is two limitations:

  • The data type in Table_Param must be Text. And this is highlighted in the above step 3. Therefore, you need to either perform an inline type cast or a change data type step in the Power Query.
  • Although the Table.Tolist() support large volume of data, the number of the allowedvalue populated in the dropdown box will only be first 1000 items. Well, in most cases, that’s more than enough.





Categories: Excel, Power Query Tags: ,

Thoughts after attending the Azure Machine Learning Meetup

November 26, 2014 Leave a comment

This is a long overdue post about the feedback after I attended the Azure ML meetup  hosted by Microsoft and Big Data Analytics user group. Overall, it was a great and informative session. Big thanks to Shashank Pawar and Rami Mukhtar.

The session stated with high level overview about what Azure ML’s capability. A few demos were given to showcase how easily you can leverage the ML to turn the predictive analytics into REST API that can be invoked easily. One of the fascinating demo Shashank showed is that you can call the ML web services on the fly from an Excel formula (CodePlex project – Azure Machine Learning Excel Add-In) while filling a table in Excel. Chris Webb has also blogged about using Power Query to do sentiment analysis for Facebook posts via Lexicon based API from ML.

One of the very interesting thing during the session is that no one, not even single one, asked about what algorithms Microsoft has been added into ML. The questions are pretty much focused on How R is integrated into the ML. People had asked about the performance, parallelism, available R packages, ability to upload custom R packages etc. It looks like most people who are interested in ML are actually considering using ML as the wrapper to operationalize R execution. And I’m not sure whether Microsoft thinks the same way. Will Microsoft treat the R execution as the first class citizen in the ML stack? ML offers a graphical interface to provide basic data exploring such as data profiling etc. I doubt how many people will actually use those features. I would imagine data scientist would just use their own “tool of the choice” and only use ML to operationalize the model. Personally, I would love to see the ML evolves to a predictive analytics automation engine with rich APIs for automation rather than a standalone predictive analysis packages.

A side note, one of the data source supported by ML is Power Query! This is a great idea and I’m really looking forward to seeing more demo of using PQ together with ML.

SSRS and R

November 19, 2014 Leave a comment

Jen Underwood just published her latest blog (R Visualizations in Reporting Services) and it is very exciting topic. This solution looks like a native SSRS solution and I would love to give it a try.

Back to year 2010, I read a article (Analyzing Direct Marketing Data with R) by Liang Wei and Brendan Kitts from Lucid Commerce Inc. discussing how to render a R chart within SSRS. 

Categories: BI, R, SSRS Tags: , ,

FW: The Graphic Continuum

November 18, 2014 Leave a comment
Categories: BI Tags:

Get every new post delivered to your Inbox.

Join 74 other followers