I’ve seen various reports designed to track COVID-19. I personally like the Microsoft Bing tracker (https://www.bing.com/covid). Not only does the report track the current counts, but it also shows a trend over time. It even goes down to a county level (the trend is not available at that level)! And it’s very fast. As good as it is, this is one report I hope I don’t have to use for long… Stay healthy!
Here is another more advanced dashboard that a data geek will appreciate.
As it stands, Power Apps doesn’t offer an easy way to get to the app source code. Yet, there are scenarios where this could be useful, including:
Here is the fastest way to get to the app source code:
In my case, the exported app has three *.json files in the Controls folder. The interesting code is in the largest json file: 3.json.
Open this file in your favorite JSON editor and this is how we get to the app source code.
Power BI Service (powerbi.com) packs a graphical lineage view with the caveat that it only works within a workspace. As a Power BI admin, you may need a utility to inventory all Power BI artifacts published to all workspaces (including My Workspaces) in your Power BI tenant. Fortunately, the Admin – Groups GetGroupsAsAdmin can do the job in one call without any coding! Don’t be misled by “groups” in the API name as groups are equivalent to workspaces (the original V1 workspaces were joined by the hip with O365 groups so Microsoft got carried away here, which I’m sure they regret by now given than V2 workspaces decoupled from groups :-).
Next run the API and get the results as JSON. You can use one of the online JSON viewers, such as the Code Beautify JSON Viewer, to get a user-friendly view of the data. The Tree Viewer is particularly useful to drill down a workspace to items.
In my previous post “Power BI Embedded, Service Principals, and SSAS“, I discussed how you can integrate Power BI Embedded (App Owns Data) configured for service principal authentication with SSAS to pass the effective user identity. One important observation is that you can use this approach with both internal and external users. For internal users, the Power BI gateway (running under an account that has admin rights to the SSAS instance) passes the effective user identity under the EffectiveUserName connection string setting. For internal users, the effective user identity maps to the user UPN, such as john.doe@prologika.com, so that AAS can map it to the corresponding AAS account. For external users, you can configure the gateway for CustomData, and pass whatever you want as an effective user identity.
Suppose that one day you migrate your code to Azure Analysis Services (AAS)? AAS. Will it work? Unfortunately, not. Since there is no gateway between Power BI and AAS, there isn’t a layer to authenticate using a trusted account. So, the Power BI team has decided to go only with CustomData instead and Power BI Embedded supports a special parameter which only works for AAS . Although the documentation doesn’t emphasize this difference, it has an important paragraph “The only way to have dynamic RLS (which uses dynamic values for filter evaluation) in Azure Analysis Services, is using the CUSTOMDATA() function”. Let’s break this down.
Your Power BI Embedded App Owns Data implementation will need different code for SSAS and AAS. The AAS version relies on CUSTOMDATA for handling row-level security.
Although this implementation path is fundamentally different from SSAS, it will work with external users that are not part of your Azure AD. But users registered in Azure AD cannot be just added to AAS roles. This will be pointless because you won’t be able to pass their identity under EffectiveUserName and AAS won’t be able to evaluate them as AAD users. So, both internal and external users must go somehow through CUSTOMDATA.
Null and zero typically have different semantics, where null indicates an unknown or missing value while zero is an explicit value. Sometimes, however, you want to show nulls as zeros. For example, an insurance company might want to show 0 claims instead of a blank value. By default, when a DAX filter doesn’t find any rows, the formula returns null (BLANK in DAX). For example, this Claims Count measure will return null when no claims match the report filters.
Claims Count = DISTINCTCOUNT(FactClaims[UniqueClaimNumber])
How do we show nulls as zeros?
The easiest and fastest way is to simply append zero.
Claims Count = DISTINCTCOUNT(FactClaims[UniqueClaimNumber]) + 0
Note that the storage engine is optimized to eliminate empty spaces so converting measures to zero can impact performance negatively. In addition, by default, reports remove dimension members with empty measures. This won’t happen if measures return zero.
Scenario: Management has requested an easy way to view a subset of strategic reports located in different Power BI workspaces. You can ask the users to mark reports and dashboards as favorites so they can access pertinent content in the Favorites menu, but you’re looking for an easier configuration, such as to create a book of reports with a built-in navigation that organizes reports in groups (like a table of contents), such as the screenshot below demonstrates.
Workaround: Creating a Power BI app might be your best option. However, a long-standing limitation of apps is that there is 1:1 relationship between an app and a workspace. Therefore, by default all included content must come from the same workspace and you can’t create multiple apps in a workspace, such as to distribute content to different audiences.
But thanks to the upgraded navigation experience, an app can include links to reports in other workspaces if consumers have at least Viewer permission to these workspaces.
Here is a configuration for a report residing in a different workspace. Notice that to preserve the navigation experience, the report is configured to open in the page content.
There are two main caveats of this approach:
Scenario: You plan to display a Power BI report on a monitor. You want the report to automatically cycle through report pages, showing each after a configurable time delay, like a photo slide show.
Solution: There are at least two solutions to accomplish this:
Besides the built-in cross-filtering and cross-highlighting among visuals, Power BI supports two explicit filtering options: slicers and filters. Which one to use? Traditionally, you would use a slicer when you want the user to easily see what’s filtered on the report page. But with the introduction of the new filter pane and slicer enhancements, the choice becomes more difficult. Let’s compare the two options:
| Criteria | Slicer | Filter |
| Placement | Report page (requires space on the page as other visuals) | Report pane |
| Filter target | Visual, page, report | Visual, page, report |
| Configuration | Drop-down, list, slider (numbers and dates), “buttons” | Basic and advanced |
| TopN/BottomN filtering | No | Yes |
| Can be hidden | Yes | Yes |
| Can be visible but read-only | No | Yes |
| Searching | Yes (must be enabled) | Yes (automatically enabled for larger lists) |
| Relative dates | Yes | Yes |
| Can be filtered by measure | Yes | No |
| Geo location filtering | No | Yes |
| Cross-filtering fields in same table | Automatically cross-filters other slicers with fields from the same table | Automatically cross-filters other slicers with fields from the same table |
| Cross-filtering fields in other tables | Can be accomplished with a filter on a measure | No |
| Support hierarchies | Yes | No |
| Selecting default value programmatically | No | Only with Power BI Embedded JavaScript APIs |
| Persistent filters | Yes | Yes |
| Power BI Mobile considerations | Users find it difficult to select values in lists | Better user experience |
| Power BI Embedded considerations | Passing a filter via JavaScript APIs to set default values cross-filters the slicer but doesn’t pre-select the slicer default value | Sets the filter as expected |
A glaring gap for both filters and slicers is that you can’t currently set the default value programmatically, such as to default a date filter to the last date with data. As a workaround, you can add a field to the Date table, such as IsToday, that is set to Yes for the last date and prefilter on this field, but users must be educated to know how to remove the filter if they want to select another date. This is especially cumbersome with slicers, which don’t even support a single date selection, unless configured as a drop-down or Before/After.
Based on experience, people tend to rely mostly on slicers. But because it’s not uncommon to create reports that must be visually appealing on desktops and mobile devices, here are some recommendations to accommodate both large and small displays.
Power BI Embedded supports two ways that your custom app can authenticate to Power BI using a trusted account: master account (the original option) and more recently service principle. Service principal authentication is preferred because it doesn’t require storing and using credentials of a Power BI Pro account. Configuring the service principal and embedding reports with imported data is easy. Not so much with embedding reports connected to on-prem Analysis Services models, mainly because of documentation gaps. Here are some notes you might find useful that I harvested from a recent engagement.
TIP: Don’t write code but use the documentation page to call the API (isn’t this nice!). Construct the body to look like this:
{
“identifier”: “3d9b93c6-7b6d-4801-a491-1738910904fd“,
“datasourceAccessRight”: “ReadOverrideEffectiveIdentity”,
“principalType”: “App”
}
What the API page doesn’t tell you is what you need to use for the identifier in the request body. Your first attempt might be to use ApplicationID, but you’ll get greeted with error ” DMTS_PrincipalsAreInvalidError”. Instead, you must use the object id (in Azure portal, go to the app registration, and then click the app name in the “Managed Application in local directory” property).
Then, on the next page, copy Object ID.
Notice that in line 16, your app must pass a valid Windows login to Analysis Services because behind the scenes the gateway will be append EffectiveUserName to the connection string.
MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on February 3rd, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Shabnan Watson will discuss how to apply aggregations to Power BI DirectQuery datasets to improve report performance. Melissa will sponsor the meeting. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).
| Presentation: | Aggregations in Power BI |
| Date: | February 3rd, 2020 |
| Time | 6:30 – 8:30 PM ET |
| Place: | Microsoft Office (Alpharetta) 8000 Avalon Boulevard Suite 900 Alpharetta, GA 30009 |
| Overview: | Aggregations are one of the most important optimization methods for managing big datasets in Power BI. Combined with Direct Query storage mode, they allow big datasets to be analyzed efficiently by answering high level analytical queries quickly from memory while sending more detailed queries back to the source database. In this session, you will learn about the concept of aggregations, different table storage modes in Power BI, different kinds of aggregation tables, how to configure aggregation tables so that they can answer high level user queries, and finally how to use tools such as DAX Studio or Extended Events to determine if the aggregations are actually being used. |
| Speaker: | Shabnam Watson is a Business Intelligence consultant with 18 years of experience developing data warehouse and BI solutions. Her work focus within the Microsoft BI Stack has been on Analysis Services and Power BI. She is an active member of PASS community and has spoken at PASS Summit, PASS SQL Saturdays, PASS Women In Technology Virtual Chapter, and other Local user groups. She is one of the organizers of SQL Saturday Atlanta and SQL Saturday Atlanta BI Edition. She holds a master’s degree in computer science, a bachelor’s degree in Computer Engineering, and a Certified Business Intelligence Professional (CBIP) certification by The Data Warehouse Institute (TDWI). |
| Sponsor: | Bad data is bad business. Melissa helps organizations profile, cleanse and verify, dedupe and enrich all their people data (name, address, email and phone number) and more. With clean, accurate and up-to-date customer information, organizations can monetize Big Data, improve sales and marketing, reduce costs and drive business insight. https://www.melissa.com/ |
| Prototypes with Pizza | TBD |
