Power BI and Leveraging the Cloud (Microsoft Azure)

Whilst there is a lot of information about Power BI and Azure I thought it would be good to see how Power BI leverages the cloud namely Azure.

Personally I have read up and am well aware of what Azure has to offer.

I am sure that I am not alone, in that often I think is Azure really as fast as my own servers, in which I know what the actual hardware is, as well as having personally installed and configured the software. So what I plan to do here is to touch on each of the area’s that Power BI leverages in Azure and gain a better understanding of how it works. I will also give an explanation of each component and what it has to offer relating it to the Power BI Service. And I have no doubt that each component could be used individually.

I am sure we can all agree that once you start to use the Power BI service, it is really quick and responsive. Which in turn means that the Azure offerings or infrastructure is well configured and installed to both scale and perform for the required workloads.

In this blog post below I am primarily going to focus on the Back End.

Below is an image of the components that I am going to be talking about.

Web Front End (WFE)

The Web Front End (WFE) is responsible for the initial connection and authentication. Once the connection and authentication has been established everything is then managed by the Back End, which is where the majority of the Azure services are utilized.

It does use multiple components to complete the initial connection and authentication as briefly explained below.

ASP.NET

This is the web page that you come to when either looking to log into the Power BI service or interact with Power BI via the web browser or mobile application.

This website is part of Azure and once again it shows to me that Azure is designed to perform and scale really well. I have always had really quick interactions with Power BI, which shows that if you had to build your website and it became the new Facebook, Twitter or Uber it would be able to handle the required load.

If you want to read more about what you can do with the ASP.NET you can read the information here: ASP.NET

Azure Active Directory (AAD)

The AAD is where your user account gets authenticated and management of your AAD account. I would go as far as to say that this is very similar to your traditional Active Directory, but that it is all hosted and managed by Microsoft.

You can find out more details about AAD here: Azure Active Directory

Azure Traffic Manager (ATM)

The ATM is where it uses your IP address to direct you to the closest data center that hosts all the Power BI static content. What this means is that after you have been authenticated it will then point you to the closed data center to provide you the static content as quickly as possible.

You can find out more about how ATM works here: Traffic Manager

Azure Content Delivery Network (CDN)

What then happens in the background is that the CDN is responsible to ensure that all the static content is replicated to all the Azure data centers. This ensures that the static information can be located and served from data center that was advised from the ATM. This ensures that you get the required data from the closest location as quickly as possible.

You can read more about the CDN here: Content Delivery Network

Now that all the authentication and connection has been established we can now look into the Back End.

Gateway Role / Azure API Management

This is the first layer in the back end. The Gateway Role (or eventually the Azure API Management) is the interface between the public or internet and the back end Azure services. Users and people from the internet will never directly access any of the services directly.

As you can see from the image below, the Azure API Management is where you can create your own APIs, leveraging the Azure platform to ensure that your API is always available, secure and can scale when needed.

At the last time I can remember Power BI had released some numbers it was over 500 000 unique users from 45 000 companies. And that was at July 2015. I have no doubt, that the numbers have grown significantly since then. Related Article (Over 500,000 unique users from 45,000 companies across 185 countries helped shape the new Power BI)

I know that when I log into Power BI and it is using the Gateway Role / Azure API Management that it is really quick, which shows how well the Azure API Management works.

If you would like to find out more information about the Azure API Management and what it can potentially do for your business, please follow this link: Azure API Management

Data Storage Architecture

When it comes to how the data is stored, moved or accessed for Power BI, this is where we can reference the above image which shows the two Azure services that are used, namely Azure SQL Database and Azure Blob storage.

Azure SQL Database

Once again as we can see from the above image is that there are quite a few components of the Power BI service that utilize Azure SQL Database.

It is truly amazing that when you log into the Power BI service and start to use it, that all this information is stored, retrieved and access so quickly from the Azure SQL Database. Which as I have said so before, shows that using the Azure cloud services is really fast, scalable and optimized to do what it does best and to do it quickly.

Another thing that I would like to highlight with regards to the Azure SQL Database is that often they release new updates and new functionality into Azure SQL Database, which will take some time to filter down to the On-Premise versions. As for example they now offer In-Memory tables, as well as Row-Level Security, Dynamic Data Masking etc., which you can use as soon as it has been rolled out. Which I think is a great way to always ensure that you are on the bleeding edge.

I have no doubt that there is a significant amount of data stored in the Azure SQL Databases.

Below is a brief explanation of each of the area’s in the Azure SQL Database

• Power BI Service
  • This is where all the Power BI Service related data elements are stored.
  • As we can see this contains all overall information with regards to each tenant.
• Dataset
  • This relates to all the dataset credentials information in the specific areas where of the Power BI Service.
• Reports
  • This stores the new style of reports called Minerva.
  • As well as it stores the caches for the reports on the last save operation.

You can find out more information with regards to Azure SQL Database here: Azure SQL Database

Azure Blob

To be totally honest I have not had a lot of experience with Azure Blob and blob storage, but as I understand it, blob storage is a mechanism to store any time of information as a blob. After which you can then access it in a variety of methods.

When we relate this to the image above, we can see that all data that is stored at rest is stored in Azure Blob storage.

And based on the initial numbers that every user who uses the free version gets 1Gb of data, that is roughly 500 000 GB of Azure Blob Storage. And that does not factor in all the new users, as well as the Pro Version users who will get 10 Gb each. Whilst I know that these numbers are not significant, what I want to point out is that they are potentially storing a lot of data as Azure Blob Storage.

Which goes to show that it is stored and used in a very efficient manner, because when you are using the Power BI service, and it has to take your data from rest and then put it into the SSAS Tabular database, and then show that to you in the browser it all only takes a few seconds. Which is once again a great engineering effort and result from the guys at Microsoft.

Below is a brief explanation of each of the area’s in the Azure Blob Storage

• Dataset
  • This is where it stores all the Power BI data that has been uploaded and is at rest (Rest meaning that is not currently being used)
  • It also stores all the metadata and credentials related information.
• Reports
  • Here it stores the older style VRM reports. Which I have no doubt they moved that to the Azure SQL Database to get some performance gains.

You can find out more information about Azure Blob Storage here: Blob Storage

In-Memory Analysis Services Database

Let me start by pointing out that currently you cannot get an In-Memory Analysis Services database in Azure. And by that I mean currently you can use Azure SQL Database as a Software as a Service (SaaS), but there is no option for In-Memory Analysis Services database.

I have no doubt that this is something that must be on the Azure roadmap somewhere. In my mind this would be the next great thing to be released, which would complement not only Azure but also the Business Intelligence offering in the cloud.

Yes, they are already doing this with Power BI, but I know that I would definitely be using it if it was available for me to utilize in quite a few scenarios’.

So I am hoping that sooner than later there will be some details released or some information on how this can be used as a SaaS.

Dashboard Tiles Refreshed

Whilst once again there has been no direct mention of how they achieve this in the Power BI service, it would be interesting to know how they achieve this, when moving data via one of the Power BI Gateways, and storing it on the Azure SQL Database, which stores the dashboard related metadata, but then also has to move this data to the Azure Blob Storage for the actual reporting requirements.

However, they are doing it, they are doing it once again at scale and at the end looks like a very efficient and simple process. Which means to me that they have done their homework and made something that could be quite complex (and I am sure it is rather complex) and made a simple solution.

I would wonder if they are using Azure Data Factory??

It is interesting to note how they go about refreshing the Dashboard Tiles as shown below:

So in conclusion the Power BI Service is utilizing quite a few Azure services, and when I look at the entire Azure offering I have no doubt that in the near future more of the Azure Services will be incorporated into the Power BI offering.

We can already see this with the Cortana Analytics Suite, which is leveraging even more of the Azure services in such a way that it is seamless for the people using Cortana Analytics Suite.

And it does really impress me with what Microsoft have done in such a short amount of time, and it excites me to be working in this space, because it can only get better as time goes on.