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.
If you are a SQL guy I bet you’ve used “IN” operator zillions of times. You might also looked for the same functionality in DAX and I’m sure you’ve found fantastic blog posts showing you how to mimic the same functionality in DAX. The October release of Power BI Desktop is full of new analytics features such as Grouping, Binning and TOPN filtering. On top of that, one new awesome feature that is not documented at time of writing this article, or at least I haven’t find anything over the internet, is “IN” operator in DAX. In this post I show you how to use it in your DAX expressions.
The latest version of Power BI Desktop (Current version is: 2.40.4554.463 64-bit (October 2016))
Note 1:You need to install SSMS2016 to be able to write DAX queries provided in this article. Alternatively, you can use DAX Studio . If for any reasons you cannot use SSMS 2016 or DAX Studio and you only have Power BI Desktop, don’t worry, I’ll provide some examples in Power BI Desktop as well.
Note 2: If you run previous versions of SQL Server it’s absolutely alright. There is nothing special in AdventureWorksDW2016CTP3 for this article that you don’t get in older versions of the sample database. But, keep in mind that SQL Server 2016 Developer Edition is now free and you can download it very easily. Check this out if you’re interested to see how.
After downloading the latest version of Power BI Desktop run it then
“Get Data” from SQL Server
From AdventureWorksDW2016CTP3 load “FactResellerSales”, “DimProduct”, “DimProductCategory”, “DimProductSubCategory” and “DimDate” to Power BI Desktop model
Find the local port of Power BI Desktop by opening “msmdsrv.port.txt” file from the following path:
“%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaceXXXXXXXX\Data”
Note:The “XXXXXXXX” postfix is a random number.
Open SSMS 2016 and connect to Power BI Desktop model as an Analysis Services local server. Do you want to learn more about how to connect your Power BI Desktop model from different software? Then check this out.
Now we want to filter “CalendarYear” so that the query shows sales values for 2011 and 2012 only. One common scenario we had to do in prior versions of Power BI Desktop, Power Pivot or SSAS Tabular model was to use a logical OR operator “||” like below: