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

Using “IN” Operator in DAX

IN operator in DAX

If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.

Requirements

Note 1: You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.

Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.

Getting Started

After downloading the latest version of Power BI Desktop run it then

  • “Get Data” from SQL Server
  • From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
  • Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:

“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”

Note: The “XXXXXXXX” postfix is a random number. 

  • Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.

SSMS Connect to Power BI Desktop Model

  • Open an MDX new query
  • Run the following DAX query

Here is the results:

Writing DAX in SSMS

Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below:

From now on we can write the above query using “IN” operator in DAX like below:

Here is the results:

IN operator in DAX

Continue reading Using “IN” Operator in DAX

Use KPI in Table, Matrix and Card Visualisations in Power BI

Use KPI in Table, Matrix and Card Visualisations in Power BI

One of the coolest features in Power Pivot is the ability to define KPIs based on calculated measures. You can create KPIs in SSAS Tabular as well. Unfortunately, this feature is missing from Power BI. In this post I show you a very simple way to import KPIs and use them in Table, Matrix, Multi-row card and Card visualisations in Power BI.

I use the word “IMPORT” as this feature is NOT available in Power BI Desktop yet so we CANNOT create KPIs directly in Power BI Desktop, but, there is work around for it that I explain it in this post.

Requirements

  • Latest version of Power BI Desktop
  • Microsoft Excel (2007 or later)
  • Power Pivot add-on if using Excel 2007 to 2013 (Power Pivot is already available in Excel 2016)
  • Power Query add-on if you need to transform your data (Power Query is available only in Excel 2010 Professional Plus and Excel 2013. It’s added to Excel 2016 as a built-in feature. Check this out to find out more about BI features in Excel 2016.). In this post I’m not loading data using Power Query, so you can ignore Power Query if you want to follow this article to make your first sample KPI work.

How It Works

The work around is really easy. You only need to

  • open Excel
  • load data into Power Pivot model from your source
  • create desired calculated measures in Power Pivot
  • create desired KPIs on top of your calculated measure(s)
  • save the Model (Excel file)
  • import the Model to Power BI Desktop

Let’s go through the whole process step-by-step to see how it works on real world.

Note: I use Excel 2016 and Adventure Works DW SQL Server sample database. If you’re using prior versions of Excel, you have to download and install Power Pivot for Excel. All steps below are pretty much the same.

  • Open Excel 2016
  • From Data tab click “Manage Data Model”

Excel 2016 Manage Data Model

Note: In case you’re using prior versions of Excel you need to click “Manage” from Power Pivot tab. All other steps would be the same.

  • Get external data from SQL ServerPower Pivot Get External Data From
  • Enter server name and database name then click Next

Power Pivot Get Data From SQL Server

  • Select “FactResellerSales”, “DimProduct”, “DimProductCategory”  and “DimProductSubCategory” then click Finish

Power Pivot Get Data From SQL Server

  • After the data successfully imported click Close

Power Pivot Get Data From SQL Server

Continue reading Use KPI in Table, Matrix and Card Visualisations in Power BI

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

Download (PPTX, 1.91MB)

Here is the PDF version of presentation:

Download (PDF, 534KB)

Data Classification in Power BI

Power BI Data ClassificationIn many corporations depending on the type of data is being used there could be different types of the sensitivities that should be applied to that data. Data Classification fulfills in Power BI Service this matter very easily. In today’s post you’ll learn how to setup Data Classification in Power BI Service.

First of all I want to inform you that Data Classification is NOT a sort of security or privacy setting. It is only a TAG which is all about informing Power BI users across a corporation to take extra care when they want to share data with other people inside or outside of that corporation. For instance some data might be OK to be shared externally outside the company, but, the other data might not be shared with groups of people even within that corporation.

Depending on your corporation you might have different levels of sensitivity like

  • High Sensitive Data
  • Medium Sensitive Data
  • Low Sensitive Data

So depending on what level of sensitivity, for instance for High Sensitive Data, the Power BI users should be really careful of who they share Power BI Dashboards and data with. In Power BI Service we can easily setup data classification on our dashboards so anyone who is looking at that dashboard is able to understand how sensitive that dashboard is and who they can share it with.

Requirements

To be able to setup Data Classification in Power BI Service you have to:

In case that you want to add another admin user,and if you already integrated your on-premises Active Directory with Azure Active Directory (AD) then you can either grant necessary admin rights to that user from your Azure portal in Azure AD or directly from Office 365 Admin Centre.

The user needs to be an Office 365 “Global Administrator” to be able to setup data classification in Power BI Service. A global administrator will have access to “Admin Portal” panel within Power BI Service which includes data classification and many more other important settings.

Make a User Global Administrator in Office 365

After you signed into your Power BI Service account,

  • Click “Admin” tile from the app launcher

Office 365 Admin Centre

Continue reading Data Classification in Power BI

Power BI Desktop Query Parameters, Part 3, List Output

List Output in Power BI Query Parameters

In the previous posts, here and here, I explained how you can use Power BI Desktop Query Parameters for many different use cases. Power BI development team added another cool feature to Power BI Desktop on July 2016 which is the ability to add a List Query output to a query parameter as it’s “Suggested Values” (formerly “Allowed Values”). This feature is very useful and from now on we are not restricted to proviode a static list of values in “Manage Parameters”. In this post I show you how to use a list output in query parameters.

Note: This feature is NOT available in DirectQuery mode at the time of writing this post.

Requirements

In this post as usual I’ll connect to a SQL Server database as a sample. To be able to follow this post you have to have:

  1. The latest version of Power BI Desktop (current version is 2.38.4491.282 64-bit (August 2016))
  2. Adventure Works 2016 CTP3

Scenario

In the first post of these series I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. But, what if we want to filter query results based on the values of a column from a particular table? Previously we couldn’t answer these sort of questions if we want to filter FactInternetSales based on a selected values of EnglishProductName column from DimProductCategories using Query Parameters. But, now we can easily implement those sort of scenarios.

Let’s implement this scenario.

Loading Data into the Model:

  • Open Power BI Desktop
  • Get data from SQL Server and connect to Adventure Works DW 2016 CTP3
  • Select “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click “Load”

Power BI Desktop Loading Data into the Model

  • Switch to “Relationships” view to make sure the relationships detected correctly then click “Edit Queries” from the ribbon

Power BI Desktop Edit Queries

Continue reading Power BI Desktop Query Parameters, Part 3, List Output

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

Business Intelligence and Data Visualisation Articles