Category Archives: Power BI Web App

Download Power BI Reports from Power BI Service

Download report from Power BI Service

A new cool feature added to Power BI Service is the ability to download Power BI reports from Power BI Service. This feature is highly demanded and it’s available from November 2016. I was really excited when I noticed that and I had to try it straight away. I was in a bus back to home on Friday, but, I couldn’t wait until I get home and test this cool feature. So I created a personal hotspot and started testing it in the bus. To make the level of my excitement clearer, I should reveal a secret. I get motion sick in the bus very quickly. It gets worth when I read something, even reading a text on my mobile. Man, it’s really horrible feeling. Knowing that I’ll potentially get sick, I turned on my tablet (a Windows 10 tablet of course) to test this new cool feature. So I logged into my Power BI Service account, I opened a report, clicked File menu and this is what I got

Inactive Download report from Power BI Service

But, why?

Two possibilities jumped into my head immediately:

  • The dataset of this particular report is not supported at the moment
  • The “Download report” feature is NOT supported in my area

So I opened Power BI Desktop and created a report on top of an Excel file very quickly, then I published it to the service and voila! It worked. So it is also available in my area.

 

Download report from Power BI Service

But, what was wrong with the previous report though? The dataset?

I checked the report’s dataset, it was on-premises SQL Server. Could it be a problem?

I created another Power BI report in Power BI Desktop on top of adventure works on SQL Server 2016. I published the model and interestingly the download report feature was still active. So how on earth I shouldn’t be able to download that report?

Well, I was in the bus, wobble about and I was feeling that the motion sickness symptom is coming for me and there were a bunch of “whys” in my head.

So I had to experiment some other datasets as well. I tested the following datasets:

  1. CSV files
  2. Folder
  3. SQL Server Direct Query
  4. SQL Server Analysis Services (SSAS) Multidimensional (Connect Live)
  5. SQL Server Analysis Services Tabular
  6. From Web
  7. Azure SQL Database
  8. Azure SQL Data Warehouse

Continue reading Download Power BI Reports from Power BI Service

Azure SQL Data Warehouse and Power BI

Azure SQL Data Warehouse and Power BI

Without a doubt cloud computing is going to change the future of data analytics and data visualisation very significantly. Microsoft Azure SQL Data Warehouse recently released for public preview. Combining Power BI as a powerful data visualisation tool with Azure SQL Data Warehouse will give the users the ability to see data insights of their data stored in Azure Data Warehouse very easily. In this post I explain how to install Azure SQL Data Warehouse and the the way it works with Power BI. Before going any further I’d like to have a look at the Azure SQL Data Warehouse very briefly.

What Is Azure SQL Data Warehouse?

Based on Microsoft documentation a SQL Data Warehouse is

Azure SQL Data Warehouse is an enterprise-class distributed database capable of processing petabyte volumes of relational and non-relational data.

Azure SQL Data Warehouse supports stored procedures, user-defined functions, indexes and collations. It uses columnstore index technology which significantly improves query performance as well as getting you up to 5 times compression in compare with traditional row based indexing.

I leave it to you learn more about Azure SQL Data Warehouse. But, it is important to keep in mind that there are some features like primary keys and foreign keys that are NOT supported in Azure SQL Data Warehouse which affect the way we use Power BI as a data visualisation tool over Azure SQL Data Warehouse. Without primary keys and foreign keys there is no physical relationships between the tables so Power BI service cannot detect any relationships by itself. There is a workaround for this that we can create some SQL views in Azure side to make it work. This can be an expensive solution. The other way is to load the data warehouse into a Power BI Desktop model which can detect the relationships automatically.

Now you know a bit bout Azure SQL Data Warehouse let’s get back to the subject and talk more about Power BI and Azure SQL Data Warehouse.

First things first. You need to have a Microsoft Azure subscription. If you don’t already have it you can use it for a one month trial here. You’ll also get $250 credit. But, remember that if you succeed the $250 in less than a month then you’ll need to pay for it if you want to use it longer.

Install Azure SQL Data Warehouse

After you get your Azure subscription, login to your account and you should see a dashboard like this

Install Azure SQL Data Warehouse

I’m not going to explain the above dashboard as it is out of scope of this article.

  • Click New

Install Azure SQL Data Warehouse 01

  • Click “Data + Storage” then click “SQL Data Ware House”

Install Azure SQL Data Warehouse 02

  • Enter a name for your database
  • Select a performance value

Continue reading Azure SQL Data Warehouse and Power BI

Power BI and Dynamics CRM

Dynamics CRM and Power BI

Microsoft is building lots of cloud based technologies these days and Microsoft Dynamics CRM is not an exception. With a powerful data visualisation tool like Power BI Microsoft took a great step to integrate Dynamics CRM and Power BI which means you can easily connect from Power BI website and Power BI Desktop to a cloud based Microsoft Dynamics CRM instance. In this article I express a step-by-step tutorial to be able to connect to connect to Dynamics CRM from Power BI website as well as Power BI Desktop.

Prerequisites:

  • You can only connect to Dynamics CRM Online (Cloud based Dynamics CRM) from both Power BI Desktop and Power BI website. If you have an older version on-premises Dynamics CRM and you’re willing to create visualisations on Power BI then you need to connect to the CRM database on SQL Server instance just like any other SQL Server databases.
  • You need to have a valid OData URL for a Dynamics CRM Online instance and an administrator must enable the OData endpoint in the CRM site settings. To find the OData endpoint address:
    • After browsing your CRM Online in a browser click “Customizations” from “Settings”

Dynamics CRM OData for Power BI

  • Click “Developer Resources”

Dynamics CRM OData for Power BI 2

  • Scroll down and then you can see OData URL under “Organization Data Service”

Dynamics CRM OData for Power BI 3

  • You should connect to Dynamics CRM Online using the same user account as your Power BI website. So if you have a different Power BI account then unfortunately you need to create a new account in Power BI which is identical to your CRM account.
  • Your browser’s popup blocker should be disabled or you should exclude https://login.microsoftonline.com from your popup blocker.
  • As Microsoft Dynamics CRM is integrated with Power BI you need to have Office 365 subscription. If you don’t have Dynamics CRM Online and Office 365 subscription, but, you’re willing to learn how Power BI and Dynamics CRM Online work together you can sign up for a 30-day trial of Microsoft Dynamics CRM here. There is also a trial guide for Microsoft Dynamics CRM Online and Office 365 here.
  • Continue reading Power BI and Dynamics CRM

    Drill Action in Power BI

    Two days ago Microsoft added  some new exiting features to Power BI with the new Power BI Weekly Service Update. The more exiting one is Drill action in Power BI reports. So from now on we’re able to add a number of fields we’d like to have on the reports with a drill path. That means the users can navigate from a level to another. In this short article I show you how to implement a drill action in Power BI. In the “Data Visualisation with Power BI Desktop” I explained how to implement and publish a complete visualisation in Power BI Desktop. In this post I’m going to use the sample we published to the Power BI website as a sample. So if you are not familiar with how to create data visualisations in Power BI Desktop I encourage you to have a look at this.

    Adding Drill Action to Sales by Product Category – Column Chart:

    • Login to your Power BI account
    • Open a report that you want to add a drill action to (as a sample I’m using Adventure Works Reseller Sales which I published previously)
    • Click “Edit Report”Drill Action in Power BI 01
    • Select “Sales by Product Category” chart
    • From “Fields” pane expand “Products”
    • Drag and drop Product to Axis right behind the Product CategoryDrill Action in Power BI 02
    • Now you can see the Drill Down Level icon (image) on the chartDrill Action in Power BI 03

    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

    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

    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?