Power BI Designer – working with data

This is the third post on my series about Power BI dashboards.

Previous post :

Getting your own data in Power BI Dashboards and compromising your existing work !!

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.

SNAGHTML5b82aa41

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 :

image

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 :

  • Product
  • Internet Sales
  • Geography
  • Customer
  • 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 !

image

After 10 minutes still no data in Power Query. So it has nothing to do with Power BI Designer Sad smile

Query folding

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 :

image

From Power BI Designer the result is as expected the same.

Conclusion

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
Advertisements

2 comments

  1. Hi Regis! – Isn’t the reason it is taking so long to pull out data from ssas because you are getting a Cartesian product of dimensions? If you include a measure I expect it will reduce the dimension set to those with values.

    1. Hi Rory, Yes, I wondered about that also. But I have included a measure. The “Internet Sales”. And even if only try to only import to “tables” : customer dimension and internet sales facts it is hopelessly slow. And there is a direct relation between customer and internet sales (Customer key).

      On the good news side, once you have successfully build your model with Power BI dashboard you are not limited to the 250 MB (roughly 2 GB with compression) like in Power BI for Office 365…

      Cheers
      Régis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: