This is the third post on my series about Power BI dashboards.
Previous post :
All posts in the series.
While I am on my way from Copenhagen to Roma I might as well spend the flight time in a productive way and try the Power BI Companion tool, which is also known as the Power BI Designer.
The version I have is : 2.18.3881.203 which I downloaded on December 22nd. I expect the product to be updated with new versions quite often.
the Power BI dashboard themselves – since it is web-based – will also be upgraded with short cycles, at least until it hits the GA status.
The Power BI Designer
First of all it is downloadable from there :
or from the Power BI dashboard.
This tool is the client application of predilection to build Power BI content.
At first sight it looks a lot like Power Query. Same data sources, same “M” interface, same ribbons for editing queries. But are we really getting the same functionality under the hood ?
Getting the data
The designer can get its data from a lot of different data sources. Much like Power Query – which by the way has its UI designed by probably the same person.
Actually Power BI Designer and Power Query can connect to exactly the same data sources. It still cannot connect to Microsoft Parallel Data Warehouse !
From a UI and functionality point of view it looks a lot like Power Query. Below are a few non-scientific test to confirm if it is the case or not
Loading data from Tabular model
While I’m writing I am trying to load data from a tabular model of Adventure works 2014. Product, Internet Sales, Geography, Customer, Product Category and Product Subcategory. It has now been “loading” for more than 10 minutes and nothing has happened yet…I am not convinced that something will happen at all. But I go and enjoy my inflight coffee while I let Power BI Designer crunching the almost 80.000 rows of tabular data…after approx. 7 minutes it is done and I get this message :
After a couple of tries both with the power plugged into my laptop and running on the battery I had to give up. The query for those 6 tables :
- Internet Sales
- Product Category
- Product Subcategory
Never returned any results. It either timed out or invited me to use a 64 bits version of the product because of my lack of RAM (16GB on my laptop).
Let’s try getting those dimensions and this fact table in Power Query and from the same laptop !
After 10 minutes still no data in Power Query. So it has nothing to do with Power BI Designer
One of the great features of Power Query when working with SQL server is the ability to do query folding. Meaning that filtering and sorting will be pushed to the data source instead of being executed in the Power Query. This can be tested with the Profiler.
Loading DimDate table from Adventure works in Power Query and filtering only mondays will produce this query folding result in the Profiler :
From Power BI Designer the result is as expected the same.
In this post I have established (in a non-scientific way) that Power BI Designer is using the same “engine” to as Power Query for Excel.
This is a good thing since it will help interoperability and open the door for other scenarios:
- Power BI data refresh with SSIS
- Compatibility between models built in Power Query and uploaded to Power BI
- Only one engine and one language to learn and master