What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?

Test Environment from Power BI XMLA Endpoint

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:

Different layers of Power BI

How XMLA Relates to Different Layers in Power BI?

As you may have already guessed, XMLA is only related to the data model layer and it has nothing to do with the presentation layer. So you may connect to a data model, browse the data model, import data from the model to other platforms like Excel and so forth.

XMLA Is Not New!

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 connectivity indeed.

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.

I first revealed the fact that you can connect to the underlying data model in Power BI Desktop from whole different range of tools like SSMS, SQL Server Profiler, Excel, etc… on Jun 2016. So, we indeed were using XMLA to connect to Power BI data models for a long time. We can even take a step further to import our Power BI data models into an instance of SSAS Tabular. In that sense, we are literally generating XMLA scripts from Power BI to create the same data model in SSAS Tabular. How cool is that?

Sooo… What is new then?

Continue reading “What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?”

Using Unicode Characters in Power BI

Unicode Characters in Power BI

There are several scenarios to use Unicode characters in Power BI including but not limited to:

  • Creating simple KPI columns in Table or Matrix visuals
  • To show the status of a measure more visually like using starts
  • Using Unicode characters as icons in your reports representing the subject

Chris Webb explained some of the above scenarios here.

In this post I explain how you can use Power BI as a tool to generate almost all valid Unicode characters in Power BI. You can download the PBIT at the bottom of this post. Then you can copy the Unicode characters from Power BI and use them in all textual parts of your report like visual titles, text boxes and so on.

The Unicode planes start from 0 to 1,114,111 which is decimal equivalent of 0 to 10FFFF in hexadecimal numeral system. For more information on Unicode planes check this out.

So, a simple way to generate all possible Unicode characters is to generate a list of decimal numbers starting from 0 ending at 1,114,111. This way we generate a series of decimal numbers regardless of the gaps between starting and ending Unicode blocks. Then using UNICHAR() function in DAX to generate corresponding Unicode characters. With the following DAX expression you can easily generate a list and the corresponding Unicode characters: Continue reading “Using Unicode Characters in Power BI”

Quick Tips: Hiding Multiple Columns in Power BI Desktop

From now on I will post some quick tips to help you accelerate your Power BI Desktop development. As the first post of these series, I explain a simple way for hiding multiple columns in Power BI Desktop. To do so:

  • Navigate to Relationships veiew in Power BI Desktop
  • Maximise the table you want to hide some columns in
  • Select the first column
  • Pres Shift and use arrow keys to highlight multiple columns
  • Here is the trick: DO NOT RIGHT CLICK! Instead, press the menu button on your keyboard. If your keyboard doesn’t have contect menu button, don’t worry, you can press Shift + F10 to do the same job

Menu key on PC Keyboard

  • Then click on “Hide in report view”

Enjoy!

Select Multiple Columns in Power BI Desktop

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”