I was helping and answering some questions on the Power BI community forums and I came across a question where someone was looking to query data from Power BI Premium using SSIS (SQL Server Integration Services).

This is definitely possible and I will show you below how I can use SSIS, to query data from Power BI Premium and store it in a local CSV

Pre-requisites

In order to get this completed I had to have either SQL Server 2017 or SQL Server 2019 with SSIS installed. (If this is not installed, the Power Query Source will not install)

I went to my Power BI Premium App Workspace and went into the setting for the App and made a note of the Workspace Connection. My example below it is: powerbi://api.powerbi.com/v1.0/myorg/Hours Worked

Example

In my working example I am going to connect to my Power BI Premium dataset.

I will then run a simple DAX query which will return the Sales by Date.

Finally, I will then extract this data into a CSV File.

Installation and configuration of the Power Query Source

On the server where I have got SQL Server (2017,2019) installed is where I will download and install the Power Query Source.

  • Here is the location where I downloaded the Microsoft SQL Server Integration Services Connector for Power Query (Preview)
  • NOTE: When using SSIS and SQL Server SSIS I had to download and install both the x86 and x64 versions
  • As with my example I selected the 2019 version
  • Once downloaded I then installed the Power Query Source for both x86 and x64
  • Once completed it said it was successfully installed

Creating the Power Query M Code to be used in SSIS

The Power Query Source in SSIS only uses the M Code, which is found in Power Query Editor.

As with most things there is often an easier way to do this. In my working example I do this by using Power BI Desktop with the Power Query Editor to generate the M code.

  • I opened Power BI Desktop and went into Power Query
  • Next, I clicked on Get Data and selected SQL Server Analysis Services database
    • Please note that this will work for Power BI Premium too!
  • I then put in the Workspace Connection and the dataset name (Which is known asa the Database)
  • I then changed the data to be import.
  • And then I put in the following DAX to extract the sales by date
  • The next step was for me to authenticate and depending I do not have MFA enabled on this account, so I put in my details under Basic settings
  • I then loaded the data and changed the data types.
  • I could then see the following in my Power Query Editor
  • I then went into the Advanced Editor and copied all the M Code into Notepad
  • CODE

    let

    Source = AnalysisServices.Database(“powerbi://api.powerbi.com/v1.0/myorg/Hours Worked”, “WWI Sales – Incremental Refresh”, [Implementation=”2.0″, Query=”EVALUATE#(lf)#(lf)SUMMARIZECOLUMNS(#(lf)#(tab)’Date'[Date],#(lf)#(tab)””Total Sales””,[Sales]#(lf)#(tab))”]),

    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Date[Date]”, type date}, {“[Total Sales]”, Currency.Type}})

    in

    #”Changed Type”

  • I now have my Power Query M code, with the DAX query ready for SSIS

Creating the SSIS Dataflow task to connect to and extract data from Power BI Premium

In the steps below I then create the SSIS dataflow task, query the data from the Power Query Source and then extract the data into a CSV

  • I created a new SSIS project, and went into my Package.dtsx
  • Next, I added a dataflow task
  • Once in the dataflow task I then dragged in Power Query Source from the Common section
  • I then opened the Power Query Source and pasted in my M Code from the earlier steps
  • I then click on Connection Managers
    • Then I clicked on Detect Data Source
  • It takes a few seconds and I then got a confirmation that it detected a data source
    • I could then see the Name and Description in the Power Query Connection Manager.
  • As shown above the Connection Manager is blank.
    • I then clicked on the Connection Manager and clicked on New Connection
    • This already completes the Data Source Kind, Data Source Path, I now need to confuigure how I am going to connect and I change it from Windows Authentication to Username Password.
    • I then put in my username and password
    • I then clicked on Test Connection to make sure it works. It does take a few seconds to complete.
    • I then clicked Ok twice to get back to the Power Query Source Editor
  • My final step was to click on Columns to view the columns that are returned (Once again this will take a few seconds to complete)
  • I then clicked Ok
  • My next step was to add in my Flat File Destination and configure it
    • Once configured I can then see my columns mapped through
    • I then click on the Mappings and map the columns
    • I then clicked Ok (Once again this will take a few seconds to complete)
  • Now, I tested that this would run successfully in SSIS by running the dataflow task
  • And to make sure that the data has been extract as expected here is what the CSV looks like when opened
  • I have successfully got the data extracted.

Summary

As I have shown in this blog post I have demonstrated how to connect to Power BI Premium and extract data using the Power Query Source and SSIS.

This will allow me to extract the data and insert it into almost any other data source where the data can then be hosted.

Any questions or comments are most welcome. Thanks for reading!!