Tag Archives: SQL Server Data Tools

Exporting Power BI Data to SQL Server

 

Exporting Power BI Data to SQL Server

In the previous blog posts I explained how to export Power BI data to Excel and CSV here and here. As promised in this post I explain how to export data from Power BI Desktop to SQL Server.

Hans Peter Pfister has already explained how to export data from Power BI Desktop to SQL Server using R scripts. Although Hans has done a brilliant job, it might be a bit hard to make it work if you don’t have any R experience and you don’t even know how to install and call R libraries. That’s so true about myself, I’m NOT an R guy, but, who knows, maybe I will be. Smile 

But, what if you don’t want to go with R? If you are more involved with BI than analytics, then using R might not really be your cup of tea. Luckily, there is another way to export your Power BI data to SQL Server which is more BI friendly. You can export Power BI data to SQL Server using SSIS (SQL Server Integration Services). So if you are familiar with SSIS, then it might be your your preferred choice.

With respect to Hans, in this post, I explain his method of exporting data from Power BI Desktop to SQL Server more in details so that anyone who is not that familiar with R can make it work. I also explain how to export data from Power BI Desktop to SQL Server using SSIS. If there is any other methods you’re aware of please let me know in the comment section below.

Exporting Data from Power BI Desktop to SQL Server with R

As stated before, Hans has already explained this method here. So I don’t explain exactly what he did, but, I use his method to export data from existing Power BI Desktop model to SQL Server and I explain it step-by-step.

Requirements

To make this method work you need to:

  • Latest version of Power BI Desktop, you can download it from here
  • Have access to an instance of SQL Server, either on your own machine or on a server in your local network to export the data to
  • Either install R for Windows, you can download it from here OR using an existing R-Server OR install SQL Server 2016 R Services
  • Install RODBC library for R, you can download the library from here

Note: I haven’t installed R Studio and nothing went wrong.

Installing RODBC Library for R and SQL Server R Services

As mentioned earlier, you can install R OR SQL Server R Services OR R-Server, but, as I haven’t tried R-Server myself I just explain how to install RODBC in R and SQL Server R Services.

You have to download the library from the link provided above, then extract the contents of the zip file which contains a “RODBC” folder. Then all you really need to do is to copy the “RODBC” to the “library” folder exists in either R or SQL Server 2016 folders in your “Program Files” folder.

Library folder in R

Library folder in SQL Server 2016

How Does It Work?

Open an existing Power BI Desktop model that you’re willing to export its data to a SQL Server table and follow the steps below: (I use “Internet Sales” model created on top of AdventureWorksDW2016CTP3. You can download my Power BI Desktop model at the end of this post.)

Continue reading Exporting Power BI Data to SQL Server

Query Azure SQL Data Warehouse in SSMS and SSDT

Azure SQL Data Warehouse in SSMS and SSTD

A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.

Requirements

Querying Azure SQL Data Warehouse from Visual Studio

Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:

  • Open SQL Server Data Tools 2015
  • Click “SQL Object Explorer” from View menu

SQL Server Object Explorer Visual Studio

  • Click “Add SQL Server”

Add Server to SQL Server Object Explorer Visual Studio

Connect to Azure SQL Data Warehouse in SQL Server Object Explorer Visual Studio

  • Enter “Server Name”
  • If you don’t recall server name then open a web browser and log into Azure portal
  • Click “SQL databases”
  • Click any desired Azure SQL Data Warehouse you created before. Make sure the database is “Online”

Azure SQL Data Warehouse in Azure Portal

Continue reading Query Azure SQL Data Warehouse in SSMS and SSDT

Installing SQL Server 2014 CTP1

  • After downloading the SQL Server 2014, mount the media file downloaded and run “setup.exe”
  • In SQL Server Installation Centre click on “Installation”

image

  • Click on “New SQL Server stand-alone installation or add features to an existing installation”

image

Note that if you have any of previous versions of SQL Server installed on your machine the setup process will fail because this release of SQL Server cannot be installed in until the existing instances of SQL Servers are uninstalled.

image

If you click on “Failed” you can read the complete error message that is “Previous SQL product installation failed. A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.”

image

Continue reading Installing SQL Server 2014 CTP1