Category: Power BI Desktop

On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Web App, Power Query

Power BI and SSAS Multidimensional

Update: If you are here to learn how to browse your SSAS Multidimensional model in Power BI please refer to this article. The current article shows you how to refresh data on Power BI service on top of a SSAS Multidimensional instance based on data import scenario.

It’s been a while that lots of us are waiting for seeing improvements on Power BI and SSAS Multidimensional. The good news is that Microsoft released a new version of Power BI Personal Gateway last week on 3 Sep 2015. One of the new features added to this release is that we can now refresh an on-prem SSAS Multidimensional model (data import scenario) after we published it to Power BI website. But, what data import scenario means? That means we cannot create mashups with data we already have in an existing SSAS Multidimensional database/cube through the SQL Server Analysis Services connector which is available on Power BI website. So we need to connect to a SSAS multidimensional instance through Power BI Desktop and load the cube’s data into the Power BI model. Indeed we will create a relational model on top a multidimensional model from SSAS.

Then we can create reports and publish them to Power BI website and finally we’ll be able to schedule data refresh on the Power BI website.

We can also connect to a SSAS Multidimensional instance through Power Pivot AND/OR Power Query from Excel then load the Excel file into Power BI website.

Note: We can do the same through Power Query, but, we won’t able to setup a data refresh schedule on Power BI website if we didn’t load

It’s just awesome isn’t it?

In this post I show you how to implement all the data import scenarios using Power BI Desktop, Power Pivot and Power Query from Excel.

First of all you need to download the gateway from here. Then you need to uninstall the existing version of Power BI Personal Gateway from your machine and install the new version. The whole gateway installation and process of refreshing an on-prem SSAS database is pretty much the same as what I explained in this post so I leave the installation part to you.  However, I explain the data refresh part again.

SSAS Multidimensional Data Import Scenario Through Power BI Desktop:

Get Data

  • Open Power BI Desktop
  • Click Get Data
  • Select “SQL Server Analysis Services Database” from the list and click “Connect”

Power BI and SSAS Multidimensional 01

  • Enter the SQL Server Analysis Services instance name
  • The database name is optional, but, I put “AdventureWorksDW2012”
  • Click “Select items and get data from Multidimensional or Tabular model”
  • As you can see you can also put your MDX or DAX custom queries, but, we leave it blank in our sample
  • Click OK

Power BI and SSAS Multidimensional 02

Continue reading “Power BI and SSAS Multidimensional” →

Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power Pivot

Side-by-side Role-Playing Dimensions In Power BI

Role-playing dimension is one those concepts that is discussed a lot from time to time. I also posted an article about implementing role-playing dimensions in Tabular models.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
  3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

I this post I explain implementation of the third option above. In this scenario you need to create especial calculated measures based on the roles you have in a fact table. One the most common role-playing dimensions is Date dimension. Consider you have to show Internet Sales Amount by Order Date, Due Date and Ship Date in a single chart in your report. In this case, having 3 different date tables won’t help us to achieve the goal.

New to Power BI? Quickly learn about Data Visualisation in Power BI here.

Defining new Measures in Power BI Desktop

Basically, what I’m going to explain in this post is using inactive relationships between FactInternetSales table and the DimDate dimension by adding a new Calculated measure. In this case, we’ll be able to show Sales Amount by different roles, well, dates in this sample in a single chart.

Continue reading “Side-by-side Role-Playing Dimensions In Power BI” →

Power BI, Power BI Desktop, Power BI Web App, Power Query

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” →

On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Web App

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?” →