Export Power BI Desktop data to SQL Server

Did you ever faced a scenario were you needed to load a collection of CSV/Text files into SQL Server tables?

What solution did you choose?

  • TSQL BULK INSERT?
  • SSIS Package (generated from SSMS Tasks->Import Data or manual)
  • PowerShell “Import-CSV”

And what if the SQL Server destination tables must be typed (numeric, date, text columns,…) and the CSV file has formatting issues (ex: text columns without quotes, datetimes not in ISO format) and you need to transform the columns into the desired types?

A much quicker solution to transform CSV files into the desired shape is using a PowerBI Desktop query (or PowerQuery), for example in seconds I can:

  • Load the CSV
  • Replace a value from all the columns (in this case “NULL” from a real null)
  • Auto detect the datatypes

PBIDesktopQuery

Now to load these queries into a SQL Server database, it’s very easy thanks to DevScope powershell module “PowerBIETL” (also available at PowerShellGallery):


Install-Module PowerBIETL
Import-Module PowerBIETL

Export-PBIDesktopToSQL -pbiDesktopWindowName "*sample*" -sqlConnStr "Data Source=.\SQL2014; Initial Catalog=DestinationDB; Integrated Security=SSPI" -sqlSchema "stg" -verbose

The cmdlet “Export-PBIDesktopToSQL” will take care of:

  1. Connects to the PBI Desktop and read the tables
  2. Automatically create the tables on the SQL Database (if they do not exist)
    • Thanks to DevScope “SQLHelper” powershell module and “Invoke-SQLBulkCopy” cmdlet
  3. Bulk copy the data from PBI Desktop into the SQL Table

The cmdlet has 4 parameters:

  • -PBIDesktopWindowName (mandatory)
    • A wildcard to find the PowerBI Desktop window
  • -Tables (optional, defaults to all the tables)
    • Array of tables to import
  • -SQLConnStr (mandatory)
    • Connection to a SQL Server database
  • -SQLSchema (optional, defaults to “dbo”)
    • The schema under the tables will be created

As a result all the tables from the PBI Desktop file will get copied into the SQL Server database:

image

Off course this will only work to those “one-time-only” or manual scenarios, but I assure you that is much quicker than using a SQL Integration Services package Winking smile

Advertisements

18 thoughts on “Export Power BI Desktop data to SQL Server

  1. This is fantastic, thank you so much !!!
    Do you have a similar thing for Power Query in Excel as well? Advantage there is that we could use user-defined parameters that are entered into the Excel-sheet as dynamic filters (as in PBI you would need to hardcode or create a new table everytime – which would again mean editing the code..)
    Actually being able to export from Excel to csv instead of SQL-server would also be supercool. (thinking lightweight export of tables > 1,1 Mio rows)

    • Me too, until then we need to resort to “hacks” like these to use the amazing M language for data prep/transform… It’s sooo much better than SSIS DataFlow (at least for 80% of the cases :))

  2. Hi Rui,

    I was following you for a long time for tips and tricks esp. This post was exactly what I needed but I am getting below error message. Considering that I am not very techical guy at shell and module things, I am writing the path of pbix file into “”*sample*” part and I am getting that err message. My complete string is ;

    Export-PBIDesktopToSQL -pbiDesktopWindowName “C:\PbiSamples\Sample.pbix” -sqlConnStr “Data Source=.\SQL2014; Initial Catalog=DestinationDB; Integrated Security=SSPI” -sqlSchema “stg” -verbose

    “No PBIDesktop windows opened
    At C:\Program Files\WindowsPowerShell\Modules\PowerBIETL\1.2.0.7\PowerBIETL.psm1:105 char:3
    + throw “No PBIDesktop windows opened”

    What am I doing wrong? Could u pls guide me?

    • Hi,

      You need to open PBIDesktop before running this script.

      Also make sure the parameter -pbiDesktopWindowName matches the window name of tbe file you want to extract data from.

      thanks

    • Hello Halil,
      did you succeeded? I am getting same error. I do not know what is wrong with the file and path:

      No PBIDesktop window that match ‘*d:\PBI\PowerBIETL\MHO.pbix*’
      At C:\Program Files\WindowsPowerShell\Modules\PowerBIETL\1.2.0.8\PowerBIETL.psm1:112 char:3
      + throw “No PBIDesktop window that match ‘*$pbiDesktopWindowNam …

      Kind regards, Marek
      marek.hanys@gmail.com

  3. No sadly that’s not possible, there’s no API to refresh the PBI Desktop… So yes you have to do it manually, that’s why I said it’s for “one-time-only” scenarios like POC’s…

    • i was trying to refresh the embedded SSAS using $database.process(“processfull”), but i get an error, exception calling “process” with 1 …. strageengineused set to tabularmetada. for database in this mode your must use tabular APIs to administer the database.

      do you know what i am doing wrong ?

      regards
      Mim

  4. Did you ever face a timeout-problem using this approach like this: “The XML for Analysis request timed out before it was completed. Timeout value: 30 sec.”?
    Do you have any idea how to solve this? (How) is it possible to adjust the settings of the SSAS-instance that PBI creates in the background?

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