How to use Power BI as data source in SSIS

Power BI is a fascinating tool. Built for self service BI in the last year it has grown to a full BI and analytical platform. The huge wave of new features and updates every month is amazing. I use Power BI every day and each time I find a new trick for loading, analyzing or visualising data. In the last weeks I have spent a lot of time in workshops and proof of conecpts to help our customers to use and integrate Power BI into their BI infrastructure. During one of these workshops I found an easy way to use Power BI Desktop as a data source for SSIS.

During these workshops I often have to grab data from different sources (often .csv or other flat files) and clean it. Normally, I try to do all my work in Power BI but sometimes I need a stronger backend like the SQL Server to host the data. The problem is that using the SQL Server data import wizzard and the SSIS toolset are a real pain compared to using Power BI query editor. So I thought about a way to bring data from Power BI back into a database. I wrote an article about a similar problem in Feb. 2016 . However, this time I will  not use Excel Pivot, but integration services.

Parsing Json files with Power BI

I started with a very ugly json file from my home town cologne in Germany. Every 10 minutes the city administration publishes the current fill status of all cologne car parks (Link) . With Power BI it was very easy to open the JSON file, extract the needed fill state and build a simple Power BI data model for analyzing the utilization of the car park. I know it is possible to use SQL Server 2016 to parse JSON files to a flat table, but in this case we had no SQL Server 2016 and by the way, in Power BI it is much more easier :-).

Connecting with SSIS to Power BI

Now that the data is inside of Power BI Desktop, how to get it into SSIS? This is the tricky part. First, we have to get the port which Power BI Desktop uses to communicate, there are several ways. I described two ways in my previous blog  post. The easiest way is to open DAX Studio and connect to your local instance of Power BI Desktop, down in the footer of the application you can see the port.

Now you can use this server address (in my case „localhost:58507“) in SSIS to connect to the model. For that:

1. Create a new SSIS project
2. Add a Data Flow Task
3. Add a OLE DB Data Source

Now you have to configure the connection. Double Click the OLE DB Datasource and click on „New“

After that click again on new (2). A new window will open, change the provider to „Microsoft OLE DB Provider for Analysis Services 13.0“.  

After that you can type in your server address, localhost:58507 in my case.

Now you have set up the connection and SSIS can load data from you Power BI data model. You can load a full table (see screenshot)

Or you can write your own DAX-Query like I did in this example.

You can now do more data transformation with SSIS or write your data to a SQL Server like I did in my example. SSIS will recognize the data type from the model so there is no problem with converting etc.

Conclusion

Power BI is a very powerful tool for converting and shaping data. By combining it with SSIS, like I did in this example, it is possible to import unstrucutred or not well defined data very fast without using the SSIS data import wizzard. I’m very sure I will use this method more often in the future as for me it is more comfortable and faster! There are some limitations to that approach.

1. Power BI has to import the data into the internal data model, if your data set is too big to import you cannot use this method.
2. It is very hard to use this approach in a productive scenario because you can’t trigger a data refresh automatically in Power BI and every time you restart Power BI, it will get a new port. In this case you have to change the entered port in your SSIS Package, that is no very convenient.

Thanks for reading. I hope you find my solution useful.