Archive

Archive for November, 2014

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 1 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
                        meta
                        [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.
    ID
    =============
    1
    2
    3


    10000
  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}], 
    

    to

    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:

Embedded SQL Reporting Services Reports into Tableau Dashboard

November 13, 2014 Leave a comment

I blogged about Add Microsoft SQL Server Reporting Services charting capability to Tableau a few days ago. And I also blogged how to use SSRS data driven subscription to mass generate hundreds of images to be used as Tableau custom shape. However, Tableau does not support high resolution images in custom shape. Hence, I’ll show you another approach and probably an extremely powerful way: Directly Calling and Displaying a SQL Server Reporting Service Reports inside Tableau Dashboard

Look at the below two screenshot:

1 – Embeded SSRS Reports in Tableau Dashboard – with command toolbarEmbeded SSRS Reports in Tableau Dashboard - with command toolbar

2 – Embeded SSRS Reports in Tableau Dashboard – no command toolbarEmbeded SSRS Reports in Tableau Dashboard - no command toolbar

For experienced SSRS developer, I’m sure you already guess the trick: use SSRS URL access inside a Tableau Web Page control. Basically, in the first one, the URL is:

http://<SSRS Server>/ReportServer?%2fGaugeSamples%2fThermometerChart&rs:Command=Render&Value=102

and the second one is:

http://<SSRS Server>/ReportServer?%2fGaugeSamples%2fThermometerChart&rs:Command=None&Value=102

The first parameter (rs:Command) tells SSRS server whether to render the command toolbar and the other parameter (Value) tells what the Thermometer Indicator’s value should be.

Personally, I’m very excited about this integration. It opens a door of endless possibility. For example, you can:

  • Leverage existing Microsoft BI assess and integrate into Tableau dashboard
  • SSRS is able to handle pixel perfect chart rendering and can be used as a dynamic charting library inside Tableau dashboard. As you can see in the first screenshot, there is a property called “Size of the bulb at the bottom of the thermometer”. You can expose lots of properties through parameters and let Tableau to invoke them on the fly. Imagine you have drop downs in the Tableau dashboard to passing parameters to SSRS reports to precisely control the chart rendering. This is probably what Tableau is lacking of at the moment.
  • Powerful operational reports. SSRS far better superior operational reports capability than Tableau. Now you can use Tableau to give the user a high level glance and dive into super detailed details.
  • SSRS has auto refresh feature that you can let the chart to refresh every X seconds. Great for operational BI. (I have tested whether the auto refresh is working inside Tableau web page control myself. I’ll test it out later.)
  • SSRS has actions (Similar to Tableau dashboard action) that can chain various SSRS reports together to provide “guided navigation” as well as “execute some operational tasks” together with each SSRS report execution. Imagine the scenario that you deep dive into the details and then click the report action inside the SSRS report which invoke a SQL Server Integration Services Package (SSIS, Microsoft’s ETL platform) on the fly and execute a Python script to generate another Tableau Data Extract. I don’t want to dive into details. But when you bring the entire Microsoft BI stack together, you are talking about something beyond the imagination.

The Power View should work as well using this URL integration approach but at this stage, Tableau offers much better interactivity. As the Power BI platform gets more mature, maybe it is another great story. Stay tuned…

Categories: BI, Power BI, SSRS, Tableau Tags: , , ,

Use Power Query to Generate Date Table with Australian Financial Year and Holiday Flag

November 13, 2014 Leave a comment

This is not a new technic as Chris Webb and Matt Mason have both blogged about formulas for generating  a date table using Power Query. Darren Gosbell has another blog for ISO weeks and 4-4-5 period. They are all excellent articles that you can reference as use straight away.

In my recent project, I need to filter out the data with Australian Financial Year (FY) and holidays. So I wrote my own version of the Power Query to get it done. I’ll share the Excel 2013 workbook below for you to download.


I’ll briefly talk about how I do it. You can view the actual Power Query code in my shared Excel 2013 workbook.

Step 1 – Take Matt Masson’s Power Query as the start point and add FY calculation.

Step 2 – Find Australian Holiday information. I could find any useful one by just use Power Query online search. Jump to Google search and find http://australia.gov.au/topics/australian-facts-and-figures/public-holidays. I then use the Power Query to load it and perform all sorts of data manipulation to make it ready for merging with my base query. Having said that, for demo purpose, it is good. In reality, I would prefer to load it once and save it as a static table somewhere for reference. As you can imagine, the Power Query will break if the web page changes and for such small volume, automation does not add much value to manual maintenance.

Step 3 – Merging the Power Query produced from Step 1 and 2 and do the final cleansing.

Enjoy!

Categories: BI, Excel, Power Query Tags: , ,

Displaying Top N, Bottom N, and “All Others” – Dynamically

November 12, 2014 12 comments

I just read Rob Collie’s post Displaying Top N, Bottom N, and “All Others”. Its a great post with lots of details.

I’ve come up with another approach that use the Set to achieve the similar outcome but dynamically. The final solution looks like the below

Dynamic TopN and AllOthers

The base data looks like the below:

base data

Both table as added to Model as the linked table.

I created the following calculated measure in the Power Pivot Model:

SelectedN:=MAX([N])

Total Sales:=SUM(Sales[Sales])

Rank:=RANKX(All(Sales),[Total Sales])

Top N Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]<=[SelectedN]))

All Other Total Sales:=CALCULATE([Total Sales],FILTER(Sales,[Rank]>[SelectedN]))

In addition to the DAX fomula, I created two sets using MDX

Excel OLAP Set

Set Name: TopN

Set Definition: TOPCOUNT([Sales].[CustomerID].Children, [Measures].[SelectedN], [Measures].[Sum of Sales])

Set Name: AllOthers

Set Definition: [Sales].[CustomerID].Children – [TopN]

* Both of the above two sets has the checkbox “Recalculate set with every update” checked.

You can download the workbook (Excel 2013 version) at: https://onedrive.live.com/?cid=8BA997FE18F7FDAE&id=8BA997FE18F7FDAE%212373

Categories: BI, BISM Tabular, DAX, Excel, PowerPivot Tags: ,

Another one, Thermometer Chart in Tableau

November 11, 2014 Leave a comment

Following up Gauge Chart in Tableau and Add Microsoft SQL Server Reporting Services charting capability to Tableau, here is another one: Thermometer Chart (value from -22 to 104(F) )

10028

The Tableau workbook can be found at: https://public.tableausoftware.com/views/TableauThermometerChart/Thermometer

The image I generated can be downloaded :

Categories: BI, SSRS, Tableau Tags: , ,

Gauge Chart in Tableau

November 10, 2014 3 comments

Continue my previous post Add Microsoft SQL Server Reporting Services charting capability to Tableau, I’ll walk through how do I add gauge charts to Tableau.

1. go to: https://msftrsprodsamples.codeplex.com/wikipage?title=SS2008!AdventureWorksOffline%20Report%20Samples to download the sample reports to get a demo ready Gauge chart in SSRS. It use XML data source so that you can even run the report offline without connect to any data source.

2. change the gauge chart RDL to create a parameter and set the gauge indicator value to  derive from this parameter

3. change the chart size and report page size. In my test, I set all of them to 5cm. I also set margin to 0cm so that there is no gap around the gauge.

4. deploy to the SSRS report server.

5. navigate to the report management page and create a data driven subscription. Basically, the data driven subscription query returns a table with 100 rows to get value from 1 to 100. The data driven subscription loop through this query and generate the gauge chart one by one and push to a shared folder location.

6. copy the generated 100 TIFF images to Tableau’s custom shape folder C:\Users\George.Qiao\Documents\My Tableau Repository\Shapes\MyGaugeChart\

7. Create Tableau worksheet and enjoy! Below is the one I created:

Gauge Chart in Tableau

To play with it, you can go to https://public.tableausoftware.com/views/TableauGaugeChart/Dashboard-Gauge

For those one who want the gauge chart images, here is the link:

Categories: BI, SSRS, Tableau Tags: , ,

Add Microsoft SQL Server Reporting Services charting capability to Tableau

November 10, 2014 3 comments

Tableau is great for its interactivity in dashboard while Microsoft SQL Server Reporting Services (SSRS) is great for its pixel perfect reporting. When we add them together, they are truly awesome!

SSRS has a rich out of box charting library and a few 3rd party add-ons, e.g. Nevron. You can also use .NET drawing library to perform custom drawing in SSRS (Ref: http://blog.oraylis.de/2012/04/ssrs-custom-drawing-code). In addition, SSRS is able to use Google Chart API to render chart, such as Venn diagram etc.

ssrs chart types

 

SSRS has powerful expression language support and tons of properties to precisely control the chart rendering and be able to export to various formats. TIFF, one of the output format, gives us the ability to prepare custom images, which can be used within Tableau as custom shapes. SSRS has another great feature, mighty “Data Driven Subscription” (be aware, SQL Server Enterprise Edition only!). Combining the image format export and data driven subscription, we can automate the Tableau custom shape image generation.

In next blog post, I’ll show you how to add gauge charts into Tableau.

Categories: BI, SSRS, Tableau Tags: , ,