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

DAX Measure Dependencies in SSAS Tabular and Power BI

DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.

A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:

In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.

This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.

How It Works

This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

Open SSMS
Select “Analysis Services” as “Server Type”
Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”

Continue reading “DAX Measure Dependencies in SSAS Tabular and Power BI” →

Business Intelligence - BI, On-premises Data Gateway Enterprise Mode, On-premises Data Gateway Personal Mode, Power BI, Power BI Desktop, Power BI Service, SQL Server

Export Power BI Service Data to SQL Server

Update 2021 March:

You can now export the data directly from Power BI Desktop using my tool, Power BI Exporter. Read more here.

Update 2019 April:

If you’re interested in exporting the data model from Power BI Service to SQL Server check this out.

A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

Publish the existing Power BI Desktop solution to Power BI Service
Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

To see the dataset settings:

Continue reading →

Business Intelligence - BI, Power BI, Power BI Desktop

Four Different Ways to Find Your Power BI Desktop Local Port Number

Update December 2023:

From the October 2023 release of Power BI Desktop onwards, there is a 5’th method to find the port number within the Power BI Desktop itself. This method is explained here.

About two years ago I wrote a blog post on how to connect to a Power BI Desktop model from different tools like SSMS, Excel, and SQL Server Profiler. A big chunk of that blog post was about different ways of finding Power BI Desktop local port. Having the port number is crucial if you want to connect to a Power BI Desktop model from any sort of tool for different purposes like monitoring in SQL Server Profiler or analysing the model in Excel. I’ve been asked several times here and there about different reasons people need to find the Power BI Desktop local port number, so I decided to dedicate a post to the Power BI Desktop local port number.

When you run Power BI Desktop, it runs a local instance of SSAS Tabular model in the background and it assigns a random port number to that local instance. Therefore, if you want to connect to the model to analyse it in Excel you need to know that port number. There are several ways to achieve the port number and this post will cover most of them.

As you may know, Power BI Desktop is now available as a Windows App that can be installed from Windows Store. In general, the main difference between the App and the downloaded edition from the Microsoft website is that you will get a notification whenever a newer version is available to download. Then you have to manually download and install the newer version on your machine, but, the App will be updated automatically. However, some other differences are out of the scope of this article. There is just one difference that is relevant to this article which is that Power BI Desktop creates its temporary folders in a different location than the Power BI Desktop App does.

The methods described in this post work for both the downloaded edition and the Store edition.

Different Methods to Find Power BI Desktop Local Port

· Using DAX Studio

· Tabular Editor

· Power BI Desktop Temporary Location

· Command Line (CMD)

The reason that I explain different methods is that not all methods are doable in all environments. You may work at a customer site that doesn’t allow you to install new software like DAX Studio on their machines. But you always have access on Power BI Desktop temporary files.

Continue reading →

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

Demystifying “DirectQuery” and “Connect Live”

The terms “DirectQuery” and “Connect Live” are somehow confusing. I saw lots of people are using both terminologies as alternatives. But, the context of “DirectQuery” and “Connect Live” are very different indeed. Therefore, if use a a terminology when we’re talking about a different context then the whole situation might get quite confusing. in this post I try to explain the differences and make it more clear to prevent using a wrong terminology and make sure everyone is on the same page when we’re referring to “DirectQuery” or “Connect Live”.

When we use the “DirectQuery” terminology we are actually talking about connecting from Power BI Desktop instance to an RDBMS type of data source like SQL Server DB or Oracle DB.

There are two types of data connections when we’re connecting to RDBMS like SQL Server or Oracle DB from Power BI Desktop:

Import Data: which literally loads data into the underlying model to make it available in memory
DirectQuery: which doesn’t load data into model. Instead, it runs multiple concurrent queries on the RDBMS side (data source side) and gets the results. This is good to support real-time data processing.

Note: The same principal applies to SSAS Tabular.

On the other hand, when talk about “Connect Live”, we are referring to the data connection type from a reporting tool like Power BI Desktop OR Excel to an instance of SSAS, either SSAS Multidimensional or SSAS Tabular.

Continue reading “Demystifying “DirectQuery” and “Connect Live”” →