Export Power BI Desktop data to SQL Server

Posted in Power BI, PowerQuery, PowerShell, SQL ServerTagged , , ,

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

30 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)

    Reply

    1. 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 :))

      Reply

  3. 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?

    Reply

    1. 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

      Reply

    2. 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

      Reply

  5. Interesting approach (looking at the powershell source), but I don’t see that this actually forces the PowerBI model to actually refresh? Does this have to be done manually?

    Reply

  6. 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…

    Reply

    1. 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

      Reply

  9. 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?

    Reply
  11. Pingback: Export Power BI Desktop data to CSV Files – Rui Romano Blog
  12. Pingback: PBI Takım Çantası – SwissKnife – PowerBI İstanbul

  14. is there a way to pass in authentication other than windows based within this?

    what if i had an azure database that has server: rabbits.database.windows.net
    DB: DatabaseName
    schema: DBO

    username: thisuser
    password: thispassword

    what would teh script look like?

    Reply

  15. Hi Rui,
    I’ve been using this incredible “hack” for a long time. Is there any way to increase number of rows that can be imported?

    I know this is not intended to import everything in any size . And I am able to import tables with 25 million rows, what can be done for bigger tables?

    SSIS and R-scripts methods are not valid for me, I had many difficulties in running them.

    Reply

    1. Hi Halil, thanks! You are limited by the memory on the laptop… This script uses the in memory database in PBIDesktop. But beware that this is for one time only scenarios because you cannot automate this on a server.

      Regards

      Reply

      1. Hi Rui,
        I was checking your PowerBIPS.Tools module, I tried to convert PBIX file into SSAS but couldnt achieved it. I wrote

        Convert-PowerBIDesktopToASTabular -pbiDesktopWindowName “*MyPBIFileName*” -outputPath “$currentPath\XPS”.

        It runs and doesnt give any error message but does not create AS file in my server. What to write in output path? My server name is XPS. Tried several syntax without success. If it does work, I am sure it is my fault for now, it’s hell of a script!

      2. Hi Halil, thanks for your feedback. Actually the script dont deploy to SSAS. Only creates the Visual Studio project, but to deploy you should use Visual Studio. Thanks

  16. That’s wonderful Rui, realy realy wonderful! Hell of a script. One thing I noticed whether I am not sure it’s my mistake, when I open project I saw all the expressions, tables measures etc without any problem. However I didnt see the data sources and I had to add them manually. Fyi.

    This will be my second favorite script, first one is copying pbi data to sql file.
    Thanks a lot.

    Reply

Leave a comment