Author: Soheil Bakhshi

BISM Tabular, Power BI, Power BI Desktop, SQL Server Analysis Services - SSAS

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.

Update July 2020: While this method still works with Power BI Desktop June 2017 release and SSAS 2017, it potentially doesn’t work with the later versions of Power BI Desktop (Jul 2020) and SSAS 2019 as there are new DAX functions that are available in the Power BI Desktop but not in SSAS 2019. So use this method as an experimental method AT YOUR OWN RISK!

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

BISM Tabular, Business Intelligence - BI, DAX, Power BI, Power BI Desktop

Connect to Power BI Desktop Model via its Local Port 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 via its Local Port from Excel and SSMS” →

SQL Server 2016

How to Download SQL Server 2016 Developer Edition for Free

Update1: As per April 2018, the process has slightly changes since I wrote this blog post. Now you can find it under “Downloads” tab, under “Servers” section. (Thanks to “David Shannon” for pointing this out.)

Update2: Click here to utilise the SQL Server installer. By clicking the link you’ll download an executable file “SQLServer2016-SSEI-Dev.EXE” produced by Microsoft that gives you three options:

  • “Basic”: To quickly install basic features like SQL Server Database Engine
  • “Custom”: You go through SQL Server installation wizard to choose what you want to install
  • “Download Media”: To download SQL Server setup files and install them later

SQL Server Developer Edition with SP2

A while ago Microsoft decided to provide Developer edition of SQL Server 2014 and 2016 for free. So we should be able to download SQL Server 2016 Developer Edition for free legitimately. If you search for it on the Internet you’ll quickly find out that it is NOT that straightforward. In this post you see how to download SQL Server 2016 Developer Edition for free legally.

But, what is the difference between SQL Server Developer Edition and any other editions? Well, generally speaking, the Developer edition has all features of an Enterprise edition, but, it’s not for commercial use. To learn more about different editions of SQL Server 2016 have a look at here. I encourage you to see this datasheet as well.

To be able to download SQL Server 2016 Developer Edition for free you need to have MSDN Subscription or you can join Visual Studio Dev Essentials.

Download SQL Server 2016 Developer Edition using MSDN Subscription:

  • If you do have an MSDN subscription you can easily download SQL Server 2016 Developer Edition for free. Just click here and download.

SQL Server 2016 Developer Edition from MSDN

Join Visual Studio Dev Essentials and Download SQL Server 2016 Developer Edition:

  • Joining Visual Studio Dev Essentials is free and simple. While you join Visual Studio Dev Essentials lots of benefits will get unlocked including a free SQL Server Developer Edition.
  • Click here to join Visual Studio Dev Essentials
  • Click “Join Now”

Joining Visual Studio Dev Essentials

Continue reading “How to Download SQL Server 2016 Developer Edition for Free” →

Power BI, Power BI Desktop, Power BI Service

Power BI Synonyms, Take Q&A Experience to the Next Level

In April 2016 a bunch of fantastic features were added to Power BI Desktop. Some of these features, like Query Parameters, Power BI Templates and new drill action to see records, quickly grasped my attention. I wrote about Query Parameters before. You can learn how to use Query Parameters in Power BI Desktop here or more complicated use cases like Query Parameters and SQL Server 2016 Dynamic Data Masking (DDM) here.

Another cool feature is adding Synonyms to the model. Power BI Synonyms can significantly improve the Q&A and query experience. With synonyms, we can now add descriptions to the data model objects such as tables, columns and measures in the Power BI Desktop. The descriptive information could include names that the end-users may possibly use to refer to an object or abbreviations used across the business. Addin these descriptions or, as the name suggests, synonyms makes using Q&A even easier for our customers to find what they are looking for. The customers don’t know all table,  column or measure names. Defining a standard list of names for tables, columns, or measures makes Q&A much more helpful.

For instance, we can add the following synonyms:

Note: The following tables and columns are from AdventureWorksDW.

Original Name Object Type Synonym
FactInternetSales Table Internet Sales, InternetSales
OrderQuantity Column Order Quantity, Order Qty, ord qty
SalesAmount Column Sales Amount, Sales Amt, Internet Sales Amount, Internet Sales Amt
TaxAmt Column Tax Amount, Tax Amt
Freight Column freight
OrderDate Column order date

How it works

It’s easy to set up synonyms in Power BI Desktop. Switch to Model view, then click “Synonyms” from the “Modeling” tab from the ribbon. Then, simply enter the synonyms.

Power BI Desktop Synonyms

After we publish a Power BI Desktop model to Power BI Service, the synonyms will play a great role in Q&A so that when the customer types “ord qty” the Q&A engine will recognise it as “OrderQuantity” and display the results. It’s really cool, isn’t it?

But let’s think a little bit out of the box. What if we add some translations as synonyms? Hmm. I think it would be really great if a Spanish customer could type Spanish column names in Q&A rather than English. I added some translations to FactInternetSales columns and DimDate columns.

Power BI Desktop Synonyms

Thanks to Google Translate for French and Spanish translations. Sorry French and Spanish guys, if the translation looks funny. Smile

Now, I publish the model to Power BI Service. To do so, just click on “Publish” from the “Home” tab from the ribbon.

Publish Power BI Desktop Model

Continue reading “Power BI Synonyms, Take Q&A Experience to the Next Level” →