Power BI 101, Report Authoring Tools

This is my last blog post in 2021. I wish you all a wonderful break and a happy new year.

In the first post of my Power BI 101 series, we learnt that Power BI is a data platform containing various tools and services. We also explained the currently available subscriptions within the Power BI platform. The focus of the second post of this series is on what we should learn to achieve our goals more efficiently. In this post, we focus on the reporting tools available to us according to our subscription plan. From this post onwards, we discuss more specific aspects of the Power BI platform.

We learnt so far that Power BI is not just a reporting tool to build sophisticated reports; it is indeed a platform supplying a wide range of features from data preparation, data modelling and data visualization to contribute to an organisation’s data analysis journey in many ways such as sharing datasets, reports, and dashboards. All of these are possible only if we take the correct steps in building our Power BI ecosystem. But, it is very true that Power BI gives us the flexibility to create professional-looking and eye-catching visualisations providing easy to understand insights around a subject. The most renowned tool within Power BI is Power BI Desktop, but it is not the only tool available to us to create reports. Besides, Power BI Desktop reports are not necessarily the best answer to all business requirements. In fact, the business requirements define the Power BI architecture that supports those requirements. Based on the architecture, organisations decide to acquire a certain subscription plan, and based on the subscription plan, we have various reporting tools available to us. At this point, you might ask, “well, what architecture supports my organisation requires?”. Let’s answer that question in a future blog post.

For the purpose of this blog post, it is enough to know what reporting tools are available under which subscription plans. Let’s get started.

Reporting tools available in Power BI

As mentioned earlier, there are various reporting tools available to us. Let’s first see what reporting tools are available to us regardless of the subscription plans. Then we will look at the subscription plans supporting those tools.

Power BI Service

Power BI Service is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users within an organisation, depending on their access rights, may create reports directly in Power BI Service. The users can also securely share and distribute those reports. While creating or editing reports is possible in Power BI Service, it is strongly recommended to avoid this method for several reasons. The most obvious one is that the changes we make to a report may soon get overwritten by someone else that republishes the same report from Power BI Desktop. Check this blog post from SQLChick to see why you should avoid creating or editing reports directly from Power BI Service. The reports are downloadable in PBIX format. 

You can access Power BI Service here.

Power BI Desktop

It is a desktop application built for data preparation, data modelling and data visualisation. If you like to learn about data modelling with Power BI, check out my book here. We can use Power BI Desktop to connect to more than 250 different data sources, preparing, transforming and cleansing that data and at lastly visualising the data. Power BI Desktop is the predominant and most renowned report authoring tool available in the Power BI platform. It has many more functionalities and is more flexible than Power BI Service. For instance, setting up Role Level Security (RLS) is not available in Power BI Service. The file format of the reports created in Power BI Desktop is PBIX. 

Download Power BI Desktop from here.

Power BI Report Builder (Paginated)

Paginated reports aka pixel-perfect reports are formatted in a way to fit perfectly on a page. That report page might later be printed. We have exact control over page formatting to display our data in tables or charts. The reports are not as interactive as the reports created in Power BI Desktop.

Paginated reports are based on RDL technology which is standard report format in SQL Server Reporting Services (SSRS). The tool for developing a paginated report in the Power BI ecosystem is Power BI Report Builder. The reports file type is RDL. We can currently publish Paginated reports only to a Workspace that is backed with either a Premium Per User (PPU) or a Premium Capacity. 

Download Power BI Report Builder from here.

Power BI Desktop Optimised for Report Server (RS)

Power BI Report Server (PBIRS) is an on-premises server capable of rendering Power BI report files (PBIX). If we have a PBIRS up and running within our organisation and we require to publish Power BI reports to PBIRS, then we will need to create our reports in a special edition of Power BI Desktop which is optimised for PBIRS. This edition is different from Power BI Desktop, which we usually use to create and publish our reports to Power BI Service. For instance, Power BI Desktop RS does not include the preview features we used to see in Power BI Desktop until those features are released. Building reports in Power BI Desktop RS guarantees that the reports are fully functional after being deployed to our PBIRS. We can install Power BI Desktop and Power BI Desktop RS side-by-side on the same machine. 

Download Power BI Desktop RS from here.

Continue reading “Power BI 101, Report Authoring Tools”

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