Export Power BI Service Data to SQL Server

image

 

Update 2021 March:

You can now export the data directly from Power BI Desktop using my tool, Power BI Exporter. Read more here.

Update 2019 April:

If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.

A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

  • Publish the existing Power BI Desktop solution to Power BI Service
  • Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

To see the dataset settings:

Continue reading “Export Power BI Service Data to SQL Server”

Webinar Materials: Power BI Under the Hood

Pass DW BI VC Power BI Under the Hood with Soheil BakhshiI’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:

If you’ve missed the webinar you can watch it online here:

Download the Power Point presentation file here.:

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation-2.pptx”]

Here is the PDF version of presentation:

[gview file=”https://www.biinsight.com/wp-content/uploads/2016/10/Pass-DW-BI^LJ-Power-BI-Under-The-Hood-with-Soheil-Bakhshi-Presentation.pdf”]

Power BI and Active Directory for System Administrators

Active Directory and Power BI

One of the most interesting things about Power BI is that it covers a wide range of areas. Therefore, it can help a wide range of different users to analyse and understand their businesses easily. For instance system administrators can use Power BI to analyse  their Microsoft Windows Active Directory. As a matter of fact, Power BI and Active Directory can work together very nicely so that a system administrator can create high level reports and dashboards.

In this , we’ll create a report of the following charts:

  • Total number of computers by Operating System/Service Pack
  • Total number of  computers by year and Operating System
  • Total number of computers
  • Print pages per minute by printer
  • Total number of printers by year and driver name

As a system administrator you can create heaps of other useful reports.

Get Data

  • On Power BI Desktop click “Get Data” then click “More”

Power BI and Active Directory 01

  • Click “Other”, click “Active Directory” then click “Connect”

Power BI and Active Directory 02

  • Enter a Domain name then click OK

Power BI and Active Directory 03

  • As you can see there are 374 tables you can select to create heaps of reports. In this post I use “Computer” and “PrintQueue”

Power BI and Active Directory 04

Continue reading “Power BI and Active Directory for System Administrators”

MySQL and Power BI, How Does It Work?

MySQL and Power BI

Update 1: At the time of writing this blog post (Aug 2015) Power BI Service called Power BI Web. I hope it doesn’t make any confusions.

Update 2: MySQL data source is available in “On-premises Data Gateway – Enterprise Mode” as well. So if you are setting this up for an organisation, then “Personal Mode” (AKA Power BI Personal Gateway) would not be suitable. When I wrote this blog post only “Power BI Personal Gateway” was available.

In this post I explain how to use MySQL and Power BI. This post covers the following areas:

  • Get data from MySQL
  • Schedule refresh on-premises MySQL from power BI web app

First of all I’d like to mention that in this post I use AdventureWorksDW which is imported into MySQL. If you want to do so you can use “Migration Wizard” from “Database” menu on MySQL Workbench.

MySQL and Power BI

I’m not going to explain the migration process as it’s out of scope.

How MySQL and Power BI work together

MySQL is one of the world’s most popular relational database management systems (RDBMS) widely used by the industry. It’s open source, works with many different system platforms including Microsoft Windows and Linux. So it is worth to have a look at it and see how it works with Power BI.

Luckily Microsoft provided the built-in connector in Power BI Desktop. This is how it works all together:

MySQL and Power BI

I’d like to say that it’s not necessary to create reports in Power BI Desktop. You can get data from a MySQL database then publish it to the Power BI cloud then setup a schedule data refresh in the Power BI web app. Then you can create your reports and dashboards on the cloud and share them with your colleagues very easily.

Continue reading “MySQL and Power BI, How Does It Work?”