Power BI Ecosystem Report Authoring Tools Demystified

Power BI Reporting Tools Confusion

There are a lot of discussions these days around Power BI tools to create reports and for sure many of you may have already downloaded and worked with some of them if not all of them. You may also already thought that some of the tools’ names are confusingly similar. I recently had an interesting conversation with fellow who has a lot of SSRS report writing background. I was talking about Paginated reports and said, I downloaded the latest version of Power BI Report Builder… that he immidiately said, wait a second…

  • John: Power BI Report Builder? Oh I see, that’s the one that you can create paginated reports with then you can deploy those reports into an SSRS instance.
  • me: NOPE! That’s not the case I’m afraid.
  • John: Oh I know, I meant Power BI Report Server, you can deploy the reports to an instance of Power BI Report Server. I knew it!
  • me: NO! That’s not what I’m talking about…
  • John: What the…?

I bet some of you had similar conversation with a friend or a customer. OK, in this post I explain a little bit about report authoring tools available to you and your organisation to get the most out of your Power BI ecosystem.

Here is a list of all reporting tools currently available to you:

  • Power BI Service: It is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users in an organisation, based on their access rights, may be able to create and publish data, reports, dashboards in Power BI Service. The users can also schedule data refreshes on the published data as well as securely sharing and distributing the contents. 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 you make in a report may be 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. Use Power BI Service here.
  • Power BI Desktop: It is a desktop report authoring tool that can be used to connect to, or loading data from, varies types of data sources, preparing, transforming and cleansing that data and at last visualising the data. Power BI Desktop is the predominant report authoring tool with a lot more functionalities and flexibility than Power BI Service. For instance, setting up Role Level Security (RLS) is NOT available in Power BI Service. The format of the report file is PBIX. Download Power BI Desktop from here.
  • Power BI Report Builder (Paginated): Paginated reports aka “pixel perfect reports”, as the name resembles, are formatted in a way to fit perfectly on a page. That report page might later be printed. You have exact control over the page formatting to display your data in tables or charts. The reports are not as interactive as Power BI Desktop reports are. Paginated reports are based on RDL technology which is standard report format in SQL Server Reporting Services. The tool for developing paginated report in Power BI ecosystem is Power BI Report Builder. The reports file type is RDL. You can currently publish Paginated reports only to a Workspace that is backed with a premium capacity. Download Power BI Report Builder from here.
Continue reading “Power BI Ecosystem Report Authoring Tools Demystified”

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: How to Enable Dataflows In Power BI Service

Dataflows in Power BI Service

Dataflows (Preview) in Power BI Service has been landed yesterday (6th November 2018). I had a little bit of difficulties to enable this cool new feature so I thought it is good to write a Quick tip about it. While Dataflows is under preveiw at the time of writing this quick tip, the situation may be totally different in the future.

Straight away, fully featured Dataflows is available in a Power BI Premium capacity or in a Power BI Embedded Capacity, but, while this is still in preview, you can take advantage of limited features available in your Power BI Pro license. Features like “Linked entities from other dataflows” or “Computed Entities”, like merging tables to a new table, are not available in a Power BI Pro license.

Dataflows Computed Entities

Enabling Dataflows

  • After sign in to Power BI Service click “Settings”
  • Click “Admin Portal”

Power BI Service Admin Portal

  • Select Capacity type you are in, either Premium or Embedded
  • Click on a desired capacity that you’d like to enable Dataflows

Managing a Premium Capacity in Power BI Admin Portal

  • Scroll down to find and click “Workloads” under “More Options”
  • Enable “Dataflows (Preview)”
  • If you stick to the default “Max Memory (%)” value that is set to 20 you’ll get an error message saying “There was an issue updating your workload setting. Try again in a little while”. The error message is not helpful at all. The reason you get the error message is that the “Max Memory (%)” value must be a number between 27 to 100 while the default is 20.

Enabling Dataflows in Power BI Service Continue reading “Quick Tips: How to Enable Dataflows In Power BI Service”

Export Power BI Service Data to SQL Server

image

Update 2019 April: If you’re interested in exporting the data model from Power BI Service to 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.

A while ago I wrote a blog post explaining how to Export Power BI Data to SQL Server with R. In that post I explained how to get the job done in Power BI Desktop using R scripts. In this post I explain how to export Power BI Service data to SQL server. YES! You can export data from Power BI service to a SQL Server database sitting in your on-premises environment. Keep reading to see how.

How It Works?

This is going to be a short post as I already covered the first part of the process in my other post on Export Power BI Data to SQL Server with R. So in this post I show you how to use the Power BI Desktop file you already created using the method explained in that blog post to export your Power BI Service data to an on-premises instance of SQL Server. All you need to do is to

  • Publish the existing Power BI Desktop solution to Power BI Service
  • Install On-premises Data Gateway in PERSONAL MODE

Note: R is NOT supported by the current version (Version Number: 14.16.6614.5) of the On-premises Data Gateway in Enterprise Mode.

After you successfully published the model to Power BI Service you’ll notice that you cannot refresh the model if you don’t install the On-premises Data Gateway in Personal Mode.

Continue reading “Export Power BI Service Data to SQL Server”

Download Power BI Reports from Power BI Service

Download report from Power BI Service

A new cool feature added to Power BI Service is the ability to download Power BI reports from Power BI Service. This feature is highly demanded and it’s available from November 2016. I was really excited when I noticed that and I had to try it straight away. I was in a bus back to home on Friday, but, I couldn’t wait until I get home and test this cool feature. So I created a personal hotspot and started testing it in the bus. To make the level of my excitement clearer, I should reveal a secret. I get motion sick in the bus very quickly. It gets worth when I read something, even reading a text on my mobile. Man, it’s really horrible feeling. Knowing that I’ll potentially get sick, I turned on my tablet (a Windows 10 tablet of course) to test this new cool feature. So I logged into my Power BI Service account, I opened a report, clicked File menu and this is what I got

Inactive Download report from Power BI Service

But, why?

Two possibilities jumped into my head immediately:

  • The dataset of this particular report is not supported at the moment
  • The “Download report” feature is NOT supported in my area

So I opened Power BI Desktop and created a report on top of an Excel file very quickly, then I published it to the service and voila! It worked. So it is also available in my area.

 

Download report from Power BI Service

But, what was wrong with the previous report though? The dataset?

I checked the report’s dataset, it was on-premises SQL Server. Could it be a problem?

I created another Power BI report in Power BI Desktop on top of adventure works on SQL Server 2016. I published the model and interestingly the download report feature was still active. So how on earth I shouldn’t be able to download that report?

Well, I was in the bus, wobble about and I was feeling that the motion sickness symptom is coming for me and there were a bunch of “whys” in my head.

So I had to experiment some other datasets as well. I tested the following datasets:

  1. CSV files
  2. Folder
  3. SQL Server Direct Query
  4. SQL Server Analysis Services (SSAS) Multidimensional (Connect Live)
  5. SQL Server Analysis Services Tabular
  6. From Web
  7. Azure SQL Database
  8. Azure SQL Data Warehouse

Continue reading “Download Power BI Reports from Power BI Service”

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)