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?”

Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models

Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models
Photo by Markus Spiske

In an article I posted a while back I showed different methods of creating Time dimension in Power BI and Tabular models. The Time dimension I explained was in Minutes. In this post I show you simple way to create Time dimension supporting Seconds. As this is a quick tip, I only show you how to get the Time and ID columns in the Time dimension. If you need to add time bands (time buckets) check this out for more details.

Time Dimension in Seconds Grain with Power Query (M):

Copy/paste the code below in Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[ID]))),
#"Changed Type" = Table.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Type}, {"Time", type time}})
in
#"Changed Type" 
Time Dimension in Power Query

Time Dimension in Seconds Grain with DAX:

Run the DAX expression below in a new calculated Table in Power BI or SSAS Tabular model:

Time in DAX = ADDCOLUMNS(
                       GENERATESERIES(1, 86400, 1)
                        , "Time", TIME(0, 0, 0) + [Value]/86400
                        )
Time Dimension in Seconds Level with DAX
Continue reading “Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models”

Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data Directly to Excel

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

Update 2019 April: If you’re interested in exporting the data model from Power BI Desktop or Power BI Service to CSV or SQL Server check this out.

Note: The method explained in the above post involves Power BI Premium or Embedded capacities with XMLA endpoints connectivity at the time of writing.

In my previous post I explained how to copy and paste data from Power BI Desktop into Excel or CSV. I also explained how easy you can export Power BI Desktop data to CSV using DAX Studio. As I promised, in this post I show you how to import Power BI Desktop data to Excel directly. In this method you don’t need to use any third-party software and the performance is much better than the previous methods.

Note: The method I explain in this post is tested in Excel 2016 only. But, it should work for Excel 2013.

Importing Power BI Desktop Directly to Excel

In one of my previous posts I explained how to connect to a Power BI Desktop from Excel. To import Power BI Desktop data to Excel we have to do the same thing. I explain the way to connect to a Power BI Desktop model directly from Excel, then I show you how to use this method to import Power BI Desktop data.

Finding Power BI Desktop local port number from Power BI Desktop temp directory

We can find Power BI Desktop local port number in number of ways explained here. So in this post I don’t go through all methods.

Whenever we run Power BI Desktop, it opens a random port number. The port number is independent of the model so it doesn’t really matter if  we haven’t connected to any data sources or even if we haven’t open any saved Power BI Desktop (*.PBIX) files. That port number is stored in a text file named “msmdsrv.port.txt”. So the only thing we need is to do is to browse the temp directory of Power BI Desktop and open the “msmdsrv.port.txt” text file. You can find Power BI Desktop temp folder here:

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

There should be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random number. Open that folder then open “Data” and Find “msmdsrv.port.txt”. Open the file to see Power BI Desktop local port number.

image

Continue reading “Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data Directly to Excel”

Webinar Materials: Power BI Under the Hood

Pass DW BI VC Power BI Under the Hood with Soheil BakhshiI’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:

If you’ve missed the webinar you can watch it online here:

Download the Power Point presentation file here.:

Download (PPTX, 1.91MB)

Here is the PDF version of presentation:

Download (PDF, 534KB)

Analyse Power BI Data in Excel

A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.

Requirements

  • Desktop versions of Microsoft Excel 2007 and later
  • Download and install Power BI Publisher for Excel
  • Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.

Enable Power BI Publisher for Excel

Connect to and Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)

Previously we could analyse Power BI data in Excel directly from Power BI service by:

  • Log in to Power BI Service
  • Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”

Analyse Power BI Data in Excel from Power BI Service

  • Clicking ellipsis button of a desired report and clicking “Analyse in Excel”

Analyse Power BI Reports in Excel from Power BI Service

  • Doing either way, it downloads an “odc” file that could be opened in Excel.

Analyse Power BI Data in Excel from Power BI Service Enable Data Connection

  • Now you can analyse the data in Excel using pivot tables and pivot charts.

Analyse Power BI Data in Excel

Continue reading “Analyse Power BI Data in Excel”

How to Overcome Map Related Issues in Power BI, Power View and Power Map

One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.

Requirements

To experiment everything I explain in this post you need to have:

  • The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
  • The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
  • Excel 2016 or Excel 2013

If you use Excel 2016, then you need to turn on Power View on.

Check this out if you want to learn more about BI features in Excel 2016.

Get Data in Power BI

  • Open Power BI Desktop
  • Get Data from SQL Server Database
  • Select Fact.Sales and Dimension.City then load data

Power BI Desktop

Map Issues In Power BI

Wrong Cities in Power BI

  • Expand the “Dimension City” table
  • Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)

Power BI Desktop Data Category

  • Put a Map visual into the page
  • Put “City” on Location
  • Put “Total Excluding Tax” on Size

Power BI Desktop Map

As you see sales distributed across different countries, but, this is not quiet right.

  • Put a slicer on the page then put “Country” on the slicer
  • Click “United States” to filter the Map

Power BI Desktop Slicer

Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.

Continue reading “How to Overcome Map Related Issues in Power BI, Power View and Power Map”