Category Archives: Business Intelligence – BI

Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services

image

A while ago I wrote a blog post on how to import you currently existing Power BI Desktop models to SSAS Tabular 2016. However, the method I explained is NOT supported by official Microsoft BI tools like SSDT, so you may consider it as a WORKAROUND only until Microsoft supports imploring Power BI models in SSDT. In this post, I show you how to import Power BI Desktop Model to SSAS Tabular 2017 using Azure Analysis Services. It is easy and hassle free.

Requirements

Notes:

  • In this post I do NOT explain how to install Azure Analysis Services
  • This method works only for SQL Server Analysis Services 2017 Tabular

How it works

As mentioned earlier it is really easy in compare with other methods I explained in my previous post. Azure Analysis Services is capable of importing Power BI Desktop files creating a Tabular model version of your Power BI model in the cloud. Then you can simply download Visual Studio project file and redeploy it in your on-premises instance of SSAS Tabular 2017. Let’s go through the steps…

  • Open Power BI Desktop
  • Import data from WorldWideImportersDW from any desired combination of fact tables and dimensions. I imported
  • Create some simple Measures like:

Total Sales = SUMX(‘Fact Sale’, ‘Fact Sale'[Unit Price] * ‘Fact Sale'[Quantity])

  • Save your Power BI Model and close the file
  • Login to your Azure PortalBrowse to your instance of Azure Analysis Services
  • Click on “Open” under “Web designer—preview”

image

  • This opens a new page for fabulous “Azure Analysis Services Web Designer”. You’re right, a web designer for tabular models. How cool is that? Smile
  • Click “Add” button under “Models” section
  • Yes, you got it, enter a name for your model and click “Power BI Desktop” button
  • Click “Browse” and select the Power BI file you saved earlier then click “Import”

image

Continue reading Import Power BI Desktop Model to SSAS Tabular 2017 Using Azure Analysis Services

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

Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

Dynamic_Measures_in_Card_Visual_-_Power_BI_Desktop

A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext. But, until then a workaround would be entering data to a CSV file then load it to the model. Another way is to implement custom tables in DAX in SQL Server 2016 Tabular models using Table and Row Constructors. In this post I show you a way of creating custom table in SSAS Tabular using table constructors in DAX. You can do the same in Power BI as the same principle applies. Therefore, in case you’d prefer not to use “Enter Data” feature which effectively uses Power Query to create a new table in Power BI Desktop, then you can use DAX to do the same.

Requirements

If don’t already have SQL Server 2016 it’s probably time to download and install it. I use AdventureWorksDW as sample database in this article.

Scenario

You are involved with an SSAS Tabular project and the customer asked for a report in Power BI with dynamic Card so that the values shown in the Card visual should dynamically change based on selected measure from a slicer. You have several different measures in the model and the customer wants to show some of them dynamically in only one Card visual. Consider you have the following measures to be shown in the Card:

  • Total Internet Sales
  • Internet Sales in 2014
  • Total Number of Internet Sales Transactions

You have to create a logic so that the users can selected any of the above measures to show in a single Card visual.

How it works

After you meet the requirements, you’re good to start implementing the above scenario in SQL Server Data Tool (SSDT). Creating a calculated table in SSAS Tabular 2016 is fairly easy. All we need to do is to create a custom table with two columns. One column stores friendly names for measures and the other one holds DAX expressions for the measures. As you might have noticed, I’m talking about creating a custom table in DAX and populating it with values. Continue reading to see how. What we are going to do is to create a calculated table using table constructors in DAX. Table and Row Constructors weren’t available in previous versions of DAX in SSAS Tabular. They are very similar to Lists or a list of Tuples just like what we have in MDX.

I’ll explain this later when we created our sample model in SSDT. Continue reading Creating Custom Table in SSAS Tabular using Table and Row Constructors in DAX

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

Import Power BI Desktop Model to SSAS Tabular 2016

Import Power BI Model to SSAS Tabular

Note: This article has been updated on June 2017 to support latest versions of Power BI Desktop and SSAS Tabular 2017.

Have you created a robust model in Power BI Desktop and you are looking for a way to import it to an instance of SQL Server Analysis Services Tabular? Hmm, it would be highly beneficial if you could import Power BI model to SSAS Tabular and it potentially saves lots of development time and costs. The good news is that with SQL Server 2016 and SQL Server Data Tools for Visual Studio 2015 it is possible. In this post I show you how to import Power BI Desktop model to SSAS Tabular 2016. Unfortunately, you cannot do the job in any prior versions of SQL Server, SQL Server Management Studio or SSDT.

Requirements

  • SQL Server 2016 Tabular: You can download SQL Server 2016 Developer Edition for free. Check this out for more information
  • SQL Server Management Studio (SSMS) 2016: Down SSMS 2016 from here
  • SQL Server Data Tools for Visual Studio 2015 (SSDT 2015): You can download it here
  • Power BI Desktop: Download Power BI Desktop from here

How it works?

The idea is to

  1. Connect to Power BI Desktop model from SSMS 2016
  2. Script the model
  3. Modify the script
  4. Execute the scripts on your on-premises instance of SSAS Tabular 2016
  5. Open the new SSAS Tabular database in SSDT 2016
  6. Modify the model
  7. Redeploy and process the model

Note: Do not close Power BI Desktop until we completely import the model to SSAS Tabular.

Assumptions

I assume

  • You’re familiar with all required tools listed above
  • You’re familiar with SQL Server Analysis Services Tabular models and any corresponding concepts, security settings and so forth
  • You’re familiar with DAX and Power Query

Continue reading Import Power BI Desktop Model to SSAS Tabular 2016

Connect to Power BI Desktop Model from Excel and SSMS

Power BI Desktop Excel SSMSPower BI Desktop is a fantastic report authoring tool. I have lots of experience working with Tableau as well and I can say, man, Power BI is growing very quickly. Lots of awesome ideas have been added to Power BI and a lot more is coming. But, It might be a question for some of you that is that possible to connect to a Power BI Desktop model from Excel, SQL Server Management Studio (SSMS) or SQL Server Profiler? The answer is yes, you can. But, how on earth someone should connect to a Power BI Desktop model from Excel, SSMS or SQL Server Profiler? Well, it could be useful for the following scenarios:

  • Connecting to the model using SQL Server Profiler for performance tuning, monitoring and so forth
  • Again, if you have some performance issues you might need to connect to the model from SSMS
  • You have a complex model and it’s hard for you understand it, but, you are a great Excel developer, so you can connect to Power BI Desktop model from Excel so you can use reach features available in Excel like named sets
  • Just for curiosity! You are curious about writing MDX codes over an existing model, you want to see how your model look like in Excel and so forth

In this article I show you how to connect to Power BI Desktop model regardless of any use case scenarios. So for whatever reason you’d like to connect to a Power BI Desktop model this post will help you achieve your goal.

How it works

Power BI Desktop uses xVelocity technology and loads data into memory. It uses a local instance of SQL Server Analysis Services (SSAS). It does the job by running msmdsrv.exe file which can be found in “bin” folder under your Power BI Desktop installation folder which is normally under you Program Files. The msmdsrv.exe is indeed the SSAS service file. So even if you haven’t installed SSAS on your machine Power BI Desktop runs msmdsrv.exe. When Power BI Desktop runs msmdsrv.exe it creates a local instance of SSAS. This local SSAS instance uses a random port number so it would be valid until Power BI Desktop is not closed or the msmdsrv.exe is not killed from Task Manager.

Find msmdsrv.exe in Power BI Desktop Folder

So, we have a local instance of SSAS using a random port number. Therefore, we should be able to connect to the instance from Excel, SSMS or SQL Server Profiler only if we know the port number.

Note: If you have installed an instance of SSAS on your machine you can find msmdsrv.exe under “\OLAP\bin” folder from SQL Server installation path:

%ProgramFiles%\Microsoft SQL Server\msasXX.INSTANCE_NAME\OLAP\bin

which XX is your version of SQL Server. So XX would be 10, for SQL Server 2008R2, could be 11 for SQL Server 2012 and so on. The difference between the local msmdsrv.exe file located in your Power BI Desktop\bin folder with the other one you can find under your SQL Server installation folder is that the one which Power BI Desktop runs is a console programme while the other one is a Windows service programme.

How to find Power BI Desktop local port?

There are various methods you can obtain the port number. In this post I explain three of them.

  • Finding Power BI Desktop local port using Windows Command Prompt (CMD)
  • Using DAX Studio
  • Finding local port number from Power BI Desktop temp directory

Continue reading Connect to Power BI Desktop Model from Excel and SSMS

SSRS 2016 and Power BI

SSRS 2016 and Power BI

Without a doubt SQL Server Reporting Services (SSRS) is one of the most powerful reporting tools for several years. There are tons of features that you can use to make a report that suits your customers’ needs. Despite programmability and extensibility are key strengths of Reporting Services platform when it comes to creating dashboards, SSRS has absolutely nothing to offer as SSRS is a report authoring tool. So it never supposed to offer dashboards. In old days we could create web parts in SharePoint or we could install Performance Point and include SSRS reports in Performance Point dashboards. But, setting up and implementing dashboards in SharePoint/Performance Point was always a painful job. Happily with the new version of SQL Server 2016 we are able to pin visuals from existing on-prem SSRS reports to a Power BI dashboard. In this article I explain how SSRS 2016 and Power BI integration works.

Requirements

When you meet the above requirements you can pin visuals from existing SSRS reports to Power BI or you can create brand new reports and pin the visuals to Power BI.

Note: You can only pin report visuals to Power BI that means you won’t be able to pin tables and matrix to Power BI.

Note: If you don’t want to install the developer edition of SQL Server 2016 OR for any reason you cannot use the developer edition, don’t worry, the functionality I’m going to explain is available in other editions of SQL Server 2016. Indeed, the only editions that doesn’t support SSRS integration with Power BI are “Express Edition” and ” Express with Tools” editions. Check this out for more information.

Register SSRS with Power BI

After installing SQL Server you need to configure Reporting Services. As configuring Reporting Services is out of scope I leave it to you.

Note: At the time of writing this article I was using SQL Server 2016 CTP3. The same principles apply to SQL Server 2016.

  • Open “Reporting Services Configuration Manager”
  • Enter the “Server Name” and “Reporting Services Instance” then click “Connect”

SSRS 2016 Continue reading SSRS 2016 and Power BI