In some of my old posts, which are most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.
Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.
After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.
XMLA endpoint connectivity for public preview has been announced late March 2019. As at today, it is only available for Power BI Premium capacity users. This sounds like a massive restriction to a lot of people who don’t have a Premium capacity, but they’d love to see how it works. In this article I show you an easy way to get your hands to Power BI XMLA endpoint as quick as possible. Before I start, I’d like to simply explain what XMLA endpoint is and what it really means for Power BI users.
Power BI is Like Onion! It has layers!
Generally speaking, Power BI has two different layers, presentation layer and data model layer. Presentation layer is the visual layer, the one you make all those compelling reports and visualisations. The data model as the name resembles, is the layer that you make your data model in. This layer is the one you can access it via XMLA connectivity.
In a Power BI Desktop file, you can see both layers:
Seriously? Yes, seriously. It is not new. It’s been around
for many years and perhaps you’ve already used it zillions of times. Whenever
you’re connecting to an instance of SQL Server Analysis Services, either
Multidimensional or Tabular from any tools like SQL Server Management Studio
(SSMS), Power BI Report Builder, Excel, Tableau, etc…, you’re using XMLA
Power BI is an Instance of SSAS Tabular
It is true. Power BI runs a local instance of SSAS Tabular
model. So, whenever you open a Power BI Desktop file (PBIX), Power BI creates a
local instance of SSAS Tabular model with a random local port number that can
be accessed on your local machine only. When you close the file, the local
instance of SSAS Tabular is shut down and its port number is released.
you are a Business Intelligence consultant working in Power Platform, Azure
Logic Apps and Azure Analysis Services landscape, you probably felt that
On-premises Data Gateway is one of the essential parts of your engagements with
the your customers. Installing On-premises Data Gateway can go smoothly if you
already have a well thought implementation plan otherwise, it can quickly turn
to a beast if you don’t have one. In this post I do my best to provide you some
guidelines that can help you with your On-premises Data Gateway implementation
planning. Consider the following points before, during and after the
Culture of Engagement
Environments (with all peopleinvolved)
Identity Access Management
Installation, Configuration and Testing
Here is a diagram of important point that you should consider:
need to understand the use of On-premises Data Gateway for your customer. If
they need the gateway for their Power Platform, Azure Logic Apps, Azure
Analysis Services or all of them. This is important as you either need to have
access to your customer’s Power BI Service or Azure Portal or both, or you need
to assist your customer to configure On-premises Data Gateway in Azure or in
Power BI Service. The next points are:
Accessing customer’s Azure Portal and/or Power BI Service: The customer to decide whether to create a new account with sufficient rights for you or give you the credentials of an existing account. It is important to make sure you can access all environments and you have necessary rights to install/configure the gateway
You assist/consult a person at customer side with the implementation: you need to make sure you communicate with that person and see if he/she understands the requirements before the implementation date. Send them a calendar invitation beforehand to make sure he/she is present at that date. Always ask for a backup person just in case of an emergency happening to the primary person.
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.
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)
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:
Select “Analysis Services” as “Server Type”
Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”
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.
Technology is growing fast and we are enjoying it. We reshape our daily created data in a form that satisfies our needs. One of the technologies which is used more commonly these days is SQL Server Tabular Models, SSAS Tabular in short. Lots of industries decide to go with SSAS Tabular in their new projects and some defined new projects to slowly switch their existing SSAS Multidimensional to SSAS Tabular. I know, there is a big debate around SSAS Multidimensional vs. SSAS Tabular. But my aim is to prevent going through that sort of discussion. As the title implies, this post is about documentation that I believe is one of the most important parts of every project which is also sacrificed the most. In this post I explain how to document your SSAS Tabular model in Power BI Desktop and Excel. I know, there are some products you can find in the internet that can generate documentation in various formats like Word, PDF, HTML and so on. But, If you’re looking for a free and somehow more intuitive way of documenting your SSAS Tabular Models with Power BI then this article is for you. Through this article, we create a documentation tool with Power BI. I call it SSAS Tabular Model Documenter. In this method we don’t use DMVs at all. For those who are not familiar with DMVs I shortly explain what DMVs are, if you’re already familiar with DMVs you can jump this section.
You can download a copy of SSAS Tabular Model Documenter in Power BI template format (pbit) at the end of this post. It is a Christmas present for you.
What are DMVs?
Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:
SELECT * FROM $System.<schemaRowset>
So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:
Note:This method only works with SSAS Tabular 2016 and above.
Document SSAS Tabular without DMVs
In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.
NOTE: This method is no longer available (from 1st March 2019) as Microsoft discontinued supporting Web Designer in Azure portal. Microsoft is working on an alternative solution, but there is no timeframes or details to announce yet. Read more here. In the meantime, you still can import your Power BI Desktop Models to SSAS Tabular following the method described here.
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.
Power BI Desktop: Make sure you have the latest version (Current Version: 2.52.4921.581 64-bit (November 2017))
Azure Subscription: You need to install an instance of Azure Analysis Services. Don’t worry about the costs, you can create an Azure free account with $200 credit for 30 days. Learn more here.
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