Archive for November, 2014

News On SSAS Data Source Support In Power BI

November 7, 2014 Leave a comment

Chris Webb's BI Blog

Yesterday we heard (again) that SSAS will be supported as a data source for cloud-based reports in Power BI. Today, in a session, two new important details on this emerged:

  • It will work with both Tabular and Multidimensional
  • It will connect as the user running the query, so SSAS security (eg dimension security) will work just the same as it does on-premises. No special setup will be needed; there were no details apart from the fact it will work using the EffectiveUserName connection string property.

I’m sure a lot of people will be interested to hear this…

View original post

Floor Map Visualisation

November 6, 2014 3 comments

In a recent POC project, I have to produce the data visualization over custom images, namely, floor maps. I’ll briefly talk about what I did for Excel 2013 and Tableau 8.2.4
Excel 2013

Excel 2013 floor map visulisation

The only option is to use VBA Macro. I know, I know, VBA should be the past and we should avoid using it. However, with Excel 2013, it is the only option. Basically, I insert a image (floor map, PNG format preferred). “Format” ribbon –> “Color” –> “Set to Transparent Color”. Once you got the floor map displayed, “Insert” ribbon –> insert a shape to overlay the shape on top of the floor map. You can also set the shape to be transparent, as shown below. Of course, in the final solution, the transparent level should be controlled via the VBA code.

Excel Format Shape Pane

Excel 2013 allow you to assign Macro to all your shapes and custom images and text box. This is the entry point you can do some very powerful interactive features to your workbook.



Tableau 8.2

Tableau is no doubt the best commercial data visualization software and the overall dashboard design experience is awesome.

Tableau 8.2 floor map visulisation

Tableau 8.2 floor map visulisation

Categories: BI, Excel, Power BI, Tableau Tags: , ,

Power Query with SSIS OData Feed Publishing Components

November 6, 2014 Leave a comment

Power Query won’t be able to connect to Oracle / IBM DB2 database unless the driver is installed locally. In many cases, we cannot freely install the driver due to enterprise security policy and lots of other reasons. And to mass distribute the diver to client PCs is another big issue. SSIS Data Feed component removes this gap via exposing complex ETL as a single SQL Server database view which can be queried by Power Query directly. With SSISDB introduced in SSIS 2012, powerful auditing and performance logging can be enabled with the help of out of box reporting features as well as lots of nice CodePlex community projects.

In a recent project, I use this technic to enable a scenario that an end user is able to query a SQL Server database view which actually invoke an ETL job in real time and streaming data back to Excel. If you do have the Power BI subscription, you can even share the Power Query through Power Query Data Catalog and become searchable by colleagues. To take further, I build an ASP.NET as a query register and capture some metadata of the query. Then I use Varigence Mist (BIML IDE, a super powerful tool!) to dynamically generate the SSIS packages on the fly and deploy to server via Power Shell and create a view on top of it. It is truly awesome and I’m proud of what I achieved.

The only drawback is that the Project Deployment does not support incremental deployment at the moment. I wish in next SSIS release, the incremental deployment can be implemented so that we can add additional SSIS Data Feeds without redeploy the entire project.