Category: Business Intelligence – BI

Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Power BI Documenter, Power Query

What is Power BI Documenter

Power BI Documenter Logo (Small)

On Saturday, 9th June 2018, we announced the existence of Power BI Documenter. As the name resembles, Power BI Documenter is a tool to help individuals and businesses to document their Power BI Desktop models. Everyone who already have several Power BI Desktop reports probably realized that documenting the solutions is not as easy as how creating a report in Power BI Desktop is. The issue is more visible in larger organisations with several Power BI Developers who are busy enough with a big list of tasks that are assigned to them on a day to day basis. Therefore, there is no time left to take care of the documentation. Every IT expert knows how important is to have proper documentation. We at Data Vizioner decided to do something tangible about this issue. So we started the project several months ago with the vision of creating web app to help individuals and businesses to keep their Power BI documentation on track. In this post I’m not going to explain how you can easily start documenting your Power BI Desktop reports using Power BI Documenter. You can learn more about Power BI Documenter and how to use it here. Despite the fact that the current version of Power BI Documenter is the very first version of the app with lots of limitations, it indeed can help users with their Power BI documentation tasks. All you need to do is to export the Power BI Desktop files (PBIX) to Power BI Template format (PBIT) and upload it to Power BI Documenter web app. Continue reading “What is Power BI Documenter” →

Azure, Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Power BI, Power BI Desktop, Power Query, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

Time Dimension in Power BI and SSAS Tabular Model Supporting Minutes Time Bands

2018-05-23 12_58_48-Symbols (Open in Visio).vsdx - Visio Professional

Date dimension has been discussed quite a lot on the Internet and you can find lots of valuable articles around it here and there. But what if you need to analyse your data in time level? A customer has a requirement to analyse their data in Minutes level. This means that the granularity of the fact table would be at minute level. So, if they store the data in their transactional database in seconds level, then we need to aggregate that data to minutes level. I don’t want to go there, just bear in mind that the granularity of your fact table is something that you must think about at the very first steps. In most cases, if not all cases, you’d be better to have a separate Time dimension. Then you need to have a TimeID or Time column in your fact table to be able to create a relationship between the Time dimension and the fact table. In this post I show you two ways to create Time dimension in Power BI:

  • Creating Time dimension with DAX
  • Creating Time dimension with Power Query (M)

Alternatively, you can take care of the Time dimension in the source system like SQL Server. Continue reading and you’ll find a T-SQL codes as complementary.

The techniques that I explain here can be done in SSAS Tabular model and Azure Analysis Services as well.

Requirements:

To follow the steps of building the test model you need to have:

  • Power BI Desktop: Download the latest version from here
  • A sample fact table containing time or datetime. I modified FactInternetSales from AdventureWorksDW and made it available for you to download in Excel format (find the download link at the bottom of the post)

Continue reading “Time Dimension in Power BI and SSAS Tabular Model Supporting Minutes Time Bands” →

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”

Connect to Power BI Desktop Model from SSMS

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

image

 

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 “Export Power BI Service Data to SQL Server” →