Tag: Power BI

Dynamic time zone conversion using Power BI

Dynamic time zone conversion using Power BI

Published October 26, 2020

Have you ever wanted to show your time data in different time zones simultaneously? Or allows users of the same report to display time values in their own time zone? This article outlines one approach for doing so.

If you’ve spent much time building reports for users in more than one time zone, you’ve likely come across a few of the idiosyncrasies of Power BI and date/time values. In fact, if you’ve worked with time zone values in Power Query and you don’t happen to live in the UK) , you’ve likely noticed that your reports show different time based values when they get published to the service. This is because the Power BI service operates in the UTC time zone, and evaluates all locale based time functions in that time zone. Power BI Desktop evaluates them according to the locale of the user.

For that reason, UTC date/time values are paramount. Luckily, most source data is available in UTC format, and it’s up to report designers to convert it as necessary. However, what happens when a single report is meant to serve users in different time zones? Alternatively, what if you want to use a single data model to serve reports in different time zones?

Time calculations can be performed both in Power Query, and in DAX. However, if we want our users to be able to to select their time zone from filters or slicers, we’re going to be restricted to using DAX. We’re also going to need a good source of time zone data. In the end, we need the time offset from UTC so that our time calculations can adjust time accordingly.

One good source of time zone offset is the Time Zone Database. You can register for an API key (its free), and call it directly using Power BI’s web connector. This means that when we refresh our data, we will get up to date offset data when daylight saving time changes, or there are local changes to the time zone rules.

To retrieve the time zone data, connect to it using Get Data in Power BI Desktop, then select the Web connector. If prompted, choose “Anonymous” as the authentication type, and enter the following for the url:

http://api.timezonedb.com/v2.1/list-time-zone?key=XXXXXXXX&format=json

Where key is the API key that you received when registering at the Time Zone Database.

As of October 2020, Power Query will then convert the resultant JSON data into a simple table. Some of the columns are unnecessary, and we can safely remove status, message, and timestamp. I like to rename the columns into something a little more user friendly. The offset value returned in in seconds. DAX does its date calculations in days, so I create another column with the same value converted to days (the listed value divided by 86,400). It’s also a good idea to rename the query. When complete, your table should look something like below.

At this point, we can select Close and Apply to load the data into the model.

Our report will show the current time for any selected time zone. We therefore need to know which time zone is selected. We will assume that a filter or slicer, or a row filter has been applied, and there is only one currently selected value. We need to use an aggregate function in order to return the offset value, so in this case, we will MAX. We can therefore create a calculated measure to hold the selected offset value:

Current Offset = MAX('Time Zones'[Offset (days)])

Next, we need the current time. DAX has a Now() function that will return this value, but it will be returned in the locale of the user. When it runs on the service, it will return UTC time. We want this to work properly everywhere, so instead of Now() we will use UTCNow() which always returns the current time in UTC. We will next create two calculated measures – Current time (UTC) and Current time (Local).

Current Time (UTC) = UTCNOW()
Current Time (Local) = UTCNOW() + [Current Offset]

Now we can add a slicer to our report page, and use the “Zone ” dimension. Next, we add two card slicers, one displaying the current time in the UTC time zone, and the other will display the current time in the zone selected in the slicer. It’s a good idea to use the slicer’s selection control to “Single select” to prevent multiple zones from being selected. Every slicer selection will update the two “clocks” and the local time should reflect the currently selected time zone.

To see row filters in action, simply open a new page, and add a table that displays the Zone name (and any other relevant dimensions) along with the Current time (Local).

Given that the fact that slicer selections and filter values can be selected by users and persisted, this allows a single report to be used my multiple users in different time zones, but these users can see the data in their own local time zone right in the Power BI service.

4 Comments

Introducing the new Power BI Premium “Gen-2” Architecture

Introducing the new Power BI Premium “Gen-2” Architecture

Published September 23, 2020

At Microsoft Ignite this week, the Power BI team unveiled the next generation of the architecture for their “dedicated capacity” customers. This architecture promises to resolve many of the issues experienced with the first generation of Premium, and opens up several possibilities moving forward.

Gen-1

The Power BI dedicated capacity SKUs (which include Premium) were introduced 3 years ago in order to provide a scalable pricing model, and to provide predictable performance. Unlike the Pro SKU, which is licensed per user, these SKUs represent specific sets of resources running in Azure. There are currently 3 SKUs in this category, the A SKU (purchased hourly from Azure, the EM SKU (for embedding), and the well know P or Premium SKU.

When an organization purchases one of these SKUs, they are essentially purchasing memory, CPU cores and storage. The isolation allows for predictability, but it also means that when the resources become over allocated, catastrophic errors can occur. Refreshing a data set can also be particularly memory intensive, and the memory usage during a refresh could increase by more than double what might be normally required. This has an impact on normal operations during refresh, and means that capacity need to be over-sized to accommodate refresh in some cases.

Once acquired, Gen-1 capacities need to be configured, and decisions made as to what services to allow, and how many resources to allocate to them. It’s not always obvious as to what those settings should be. I’ve also seen situations where an overzealous administrator gets so excited about the new Premium capacity that they assign hundreds of workspaces to it, and bring reports to their knees.

Gen-2 – A Different Approach

The new “Gen-2” architecture aims to deliver the isolation and predictability that dedicated capacity brings, without the drawbacks. Gen-2 is in fact, not dedicated, as resources are drawn from a massive pool as needed. However, the performance level is guaranteed, and is focused on CPU cycles. In fact, memory is not even a consideration, apart from per-dataset overall size limits.

Memory will be allocated as needed to refreshes, ending the need to worry about refreshes failing, or impacting end user experiences. The CPU charge for refreshes will be allocated immediately, but the usage allocation will be spread out throughout the day. This provides consistent fast performance without requiring resource overallocation to handle peaks due to resourcing. Memory is no longer a factor whatsoever for refresh.

This architecture also allows for automatic scale up/down for intensive or unpredictable workloads. Administrators will no longer need to make so many decisions up front, or react to changes as frequently. If autoscale is not enabled, queries can potentially be slowed down, but a refresh kicking off can no longer make reports unavailable. The new architecture is moving back to a SaaS model, after being rather close to IaaS with Gen 1

In the past, services that required full isolation like Paginated reports were not available on some of the lower end A and EM SKUs. With this new architecture, they will be available on all of them. In fact, with the newly announced Premium per user SKU, they will even be available on a per user basis.

This new architecture will be available to all of the “dedicated” SKUs, A, EM and P. The preview of the new P SKU will begin later in 2020. As an ISV, I am particularly interested in this new architecture for the A SKUs. The memory spikes caused by large refreshes have been particularly problematic for us in the past. Gen-2 architecture appears to be just what the doctor ordered.

I have often referred to this group of SKUs as the dedicated capacity SKUs in the past, but with this change, that term is no longer accurate. With the term Premium being so pervasive, I think we’ll just have to call them all Premium SKUs, whether they are P or otherwise.

Power BI Announces Premium-per-User licensing

Published September 22, 2020

The license for the rest of us

Today at Microsoft Ignite, Microsoft announced the upcoming availability of a new licensing model for Premium features in Power BI entitled “Premium per User”. With this model, individual users, or subsets of users can subscribe to most of the capabilities of Power BI Premium for an additional monthly fee.

For a preliminary FAQ about this new license, read this official blog post from the Power BI team.

Three years ago, Microsoft introduced the capacity based Premium license. Prior to this, the only license available for Power BI was the user based Pro license, which listed at $10 per user per month. The problem with this model was that large organizations found it to be prohibitively expensive, especially for casual user. The capacity based SKUs (Premium) had the effect of solving that problem. An organization could purchase their own dedicated resources and then allocate them in any way they saw fit. Report consumers do not need a Pro license with this model.

While Premium went a long way to solve the cost problem with large organizations, it introduced a significant new issue with smaller to mid sized organizations. The problem is the price tag. The entry level Premium SKU (P1) carries a list price of $5,000 US per month. This means that an organization needs to have more that 500 regular Power BI users before the cost of Premium starts to make sense from a sharing only perspective.

Compounding the price tag issue, since the release of Premium, more and more features have been released that require it to function. Features like Paginated reports, AI capabilities, deployment pipelines, and the XMLA endpoint all require Premium. A small organization may have the need for this type of feature, but cannot justify the spend of a Premium license.

The new Premium per user (PPU) license promises to solve this problem. Premium per user will be a new license that will include all of the capabilities of the Pro license, but will also include almost all of the features available in Premium. Details about which features are available can be found here. It will NOT include unlimited sharing. Users with this license will be able to publish content to a PPU workspace, and that content can be consumed by other users that have a PPU license.

The next question is of course going to be “great, so how much is it?”. Therein lies the rub. Microsoft is not saying, at least not at this point. From the official blog post announcing the PPU license, Microsoft says:

“Stay tuned for the official pricing announcement as we get closer to the GA timeframe. I guarantee you won’t want to miss it“
Arun Ulag, Corporate Vice President, Power BI

It does seem awfully odd to announce a new license without stating the price, but that’s the situation that stands today. However, given that the goal of this SKU is clearly to make Premium features more accessible across the board, I fully expect it to be quite reasonable.

If, as I expect, the price is reasonable, the PPU license will unlock a lot of doors, making Premium far more widely available. In fact, I expect that PPU will become the go-to license generally. Now, we simply have to wait for the price, before we get too excited.

Connect to Application Insights and Log Analytics with Direct Query in Power BI

Published June 24, 2020

Application Insights (AI) and Log Analytics (LA) from Microsoft Azure provide easy and inexpensive ways to instrument applications. Using just an instrumentation key, any application can send operational data to AI which can then provide a rich array of tools to monitor the operation of the application. In fact, the blog that you are reading uses an Application Insights plugin for WordPress that registers each view of a page into an instance of AI in my Azure tenant.

Application Insights data can be queried directly in the Azure portal to provide rich insights. In addition, the data can be exported to Excel for further analysis, or, it can be queried using Power Query in either Excel or Power BI. The procedure for using Power Query can be found in this article. The approach for doing so, uses the Web connector in Power Query, which can be automatically refreshed on a regular basis. The Web connector does not however support Direct Query, so the latency of the data in this scenario will be limited by the refresh schedule configured in Power BI. Any features that depend on Direct Query (Aggregations, Automatic Page Refresh) will also not work.

If you’ve worked with AI or LA, and dropped down to the Query editor, you’ve been exposed to KQL – The Kusto Query Language. This is the language that is used by Azure Data Explorer (ADX), or as its code name, “Kusto”. This is of course not a coincidence, as the Kusto engine powers both AI and LA.

Power BI contains a native connector for ADX, and if you can configure an ADX cluster for yourself, populate it, and work with it in Power BI for both imported and Direct Query datasets. Given that ADX is what powers AI and LA, it should be possible to use this connector to query the data for AI and LA. It turns out that the introduction of a new feature known as the ADX proxy will allow us to do just that.

The ADX proxy is designed to allow the ADX user interface to connect to instances of AI and LA and run queries from the same screens as native ADX clusters. The entire process is described in the document Query data in Azure Monitor using Azure Data Explorer. The document explains the process, but what we are particularly interested in is the syntax used to express an AI or LA instance as an ADX cluster. Multiple variations are described in the document, but the ones that we are most interested in are here:

For LA: https://ade.loganalytics.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.operationalinsights/workspaces/<workspace-name>

For AI: https://ade.applicationinsights.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.insights/components/<ai-app-name>

By substituting in your subscription ID, resource group name, and resource name, you can treat these resources as if they were ADX clusters, and query them in Power BI using Direct Query. As an example, a simple query on this blog can be formed using the ADX connector:

And the result will appear as:

The precise query is provided in the query section of the connector above.

Once the report is built, it can be deployed to the PowerBI service, and refreshed using AAD credentials.

It is important to note that this method does NOT require you to configure an ADX cluster of your own. We are simply utilizing the cluster provided to all instances of AI and LA. We therefore do not have any control over performance levels, as we would have in a full ADX cluster. However, if the performance is adequate,(and the queries designed appropriately), this can be a good approach to work with AI and LA data that has low latency (near real time) requirements.

31 Comments

Formatting the X Axis in Power BI Charts for Date and Time

Formatting the X Axis in Power BI Charts for Date and Time

Published May 6, 2020

Dates and times are probably the most commonly used dimensions in Power BI charts, or any charts for that matter. Power BI contains a number of features that help to display data with respect to time. Features like the automated date hierarchy reduce the need for users to construct or connect to a date dimension table (even though they likely should), which helps casual users get to solution more quickly. This is particularly true of using date/time on the axis of a chart. There are a lot of options for displaying this data, and they may not all be that well understood. This article will attempt to explain a number of them.

The scenario

We will be working with thermostat data taken in 5 minute increments over several months. The shape of the data is relatively simple. There are measures for outdoor temperature and heating/cooling system run times in seconds, as well as a date/time dimensions names DateAndTime. An example can be seen below

We want to plot these runtimes over time, and we will be working with a “Line and clustered column chart” to do this. The 4 different heating/cooling runtimes are used for the column values, the Outdoor temperature is used for the line values (with average being the default aggregation behaviour). This gets us to our point – what is the best way to display time on the X axis?

Plotting with DateTime

When the DateAndTime column is added to the X axis, by default it is converted to a date hierarchy. This behaviour is on by default but can be turned off (and in many cases, should be). We initially want to work with the raw datetime value, so we can control that by setting the dropdown option in the shared axis section of the chart and selecting the name of the dimension instead of “Date Hierarchy”.

Doing this with our data results in a rather messy looking chart.

The data here is far too granular to display all of it across all of the available times. By default, using a date or datetime dimension on an X axis will display continuously like this. However, we can control this behaviour through an X axis property on the chart itself.

Opening up the chart display properties, and then opening the X axis section reveals that “Continuous” is selected for the Type property. This is the display mode that will scale the axis to include all available date/time values. The other option is “Categorical”. The Categorical option displays each date/time value as a discrete data element. Changing the axis type property to continuous results in the chart appearing as follows.

The continuous and categorical options are only available for date and date/time dimensions. If the dimension in the X axis is not one of these types, the “Type” option will not appear.

Using Continuous, each and every date and time value is displayed on the X axis, and the data values are clearly resolved. However, in our case, there are far too many values to make this useful. Finding what we’re after would take a lot of scrolling. It’s best in this case (and in most cases) to view the data in aggregate, which is to say totals and averages across different time periods, years, months, days etc. This is where the Date Hierarchy shows value.

Formatting with Date Hierarchy

Selecting our “DateAndTime” dimension back to “Date Hierarchy” immediately changes the chart to show all of the data aggregated by Year. It is also possible to see the detail of the hierarchy in the Shared axis property for the chart.

The top level of the hierarchy is shown, which is all of the data aggregated to the Year level.

I rarely use the “Quarter” level of the hierarchy, so I simply remove it, and have done so for the remainder of the operations. It can be removed simply by selecting the x beside it in the Shared axis property box.

If we want to see our data in a more granular fashion, we have three options – Drill down, Go down one level, and Expand all down one level, which are the icons listed left to right in the highlighter section in the image above. Drilling down is meant to be interactive. With Drill down selected, clicking on the data point in the chart will go down to the next level in the chart for that data point. It replaces the standard cross filtering or cross highlighting that would normally happen when selecting a data point. For example, with drilldown turned on, clicking on any column for 2019 results in the chart below.

Notice that the X axis now shows month names instead of the year. This cart is showing our measures by month now, but only for the year 2019. The up arrow in the upper left arrow can be selected at any time to go back up to year, or selecting one of the months will drill down further to show the values for all of the days in the selected month.

The second option, Go down one level behaves in a similar fashion, but it does not filter to the year, it simply takes the chart down one level in the hierarchy without first filtering by year. This could be useful when comparing months to each other in aggregate. The X axis changes in the same way as drill down, showing the values for that level of the hierarchy.

If we want to show the data more granularly than the year level, but we don’t want to aggregate all of the same month names together, we can use the third option – Expand all down one level, or as I like to call it, “drill down and out”. Selecting this option results in the chart below.

We can see the data broken out by both year and month in order. This is a much richer view and still understandable. For example, you can see that 2018 was generally warmer than 2019 due to the amount of cooling necessary at a glance. The title is automatically changed (if it wasn’t set manually) to reflect this configuration, and the X axis also shows both year and month.

In this particular example the X axis is still readable, but drilling down and out more than one level can be cumbersome, and very wordy. At the same time, you do need to know which year, month, and day a particular data point pertains to. The Z axis formatting pane has some further options that help with this. By default, all of the hierarchy levels are concatenated together when a hierarchy is expanded in this way. Going into the chart format tab, and selecting the X axis, we can see an option for this – “Concatenate Labels”. Turning this off presents each level categorically on different lines. This to my mind is much easier to read and is the configuration that I use.