If you need a convincing reason to use Edge Dev, user profiles might be it. I need access to various Power BI tenants that I need to log in. Or, your organization might have multiple Power BI tenants, such as a byproduct of acquisitions. Previously, I had to either use multiple browsers, open an incognito session (the caveat is that you can’t have two incognito sessions with different credentials), or install browser extensions to support simultaneous open sessions to Power BI. Now, all I have to do is to create a profile for each client. To do so:
Open Edge Dev.
Click the Profile icon in the top right and then click Add Profile.
Follow the steps to log in using the needed credentials.
Once the profile is created, click the profile icon and then click the desired profile. This will open a new normal (not incognito) session side by side with the other Edge DEV window. Now you have two session connected to two different Power BI tenants!
Power BI has made tremendous strides in features solidifying its position as a BI leader and increasing the feature distance over the competition (see latest Gartner report here). And rightfully so, considering that it’s much more than a visualization tool. However, you might find its advanced presentation capabilities still lagging. During a current BI assessment for a large mortgage company, the executive sponsor who have used before Tableau and Qlik told me that “some features that could be done in Qlik or Tableau in 10 minutes could take days with Power BI”. So much about “five seconds to sign up, five minutes to wow!” It’s hard to vow an audience that has seen better …
Here are the top 5 Power BI UX gaps to watch for especially if you’re migrating to Power BI from these two tools:
No dynamic binding – A long time ago, Microsoft promised that most of the Power BI properties would be expression-driven. Only title captions and conditional formatting currently support expressions. However, it’s not uncommon for dashboards to let the user specify what dimension and measures that want to see in a visual. Dynamics measures are not so difficult to implement with calculation groups (require Tabular Editor as today Power BI Desktop doesn’t have UI for calculation groups). Dynamic dimensions are much more difficult to implement. This gap could be solved elegantly if one day Power BI decides to support expressions for fields used in a visual.
No visual container support – It’s also not uncommon to organize visuals in a tabbed interface to save space. The current kludge is to use bookmarks to show or hide UI elements leading to such as a mess that no one can figure out and that should make Microsoft ashamed. So, a container interface to implement a visual that can host other visuals would allow the community to come up with creative gadgets that should make this easier.
No repeater visual – Want to embed a graph or sparkline that’s repeated for each row in a table? Can’t do today unless you use DAX measure that render HTML or SVG (both approaches require advanced DAX or UI skills). Microsoft should extend the Table and Matrix visuals (BTW, why do we have two visuals?) to allow nesting and repeating other visuals, like SSRS Tablix.
No asymmetric crosstab layouts – Currently, Matrix supports only symmetric layouts where the measure is repeated for each column forcing developers to use black belt techniques, such as the one I describe in my “Implementing Asymmetric Reports in Power BI“. Microsoft should enhance Matrix to support flexible layouts, like the SSRS Tablix control.
No Default members – Almost every dashboard requires defaulting the time period to a current period and automatically preselecting it when the period changes, such as when a new month starts. And of course, the user should be able to switch easily to a past period. A long-term Tabular limitation is that it doesn’t support default members. This limitation and the lack of dynamic binding forces developers to come up with workarounds, the most common being replacing the caption of the current period, e.g. “Current Month”, with the caveat that the user can’t see what the current period is. Tabular default members or expression-based slicer and filter default could help.
As you’ve seen, the prevailing theme of this rant is that I’d like Power BI to add more SSRS-like features, so we don’t look for the exit sign when management asks for more advanced and visually appealing reports.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2021-04-11 19:07:472021-04-11 19:10:15Top 5 Power BI UX Gaps
Microsoft awarded me FastTrack Recognized Solution Architect – Power BI! This prestigious recognition is conferred by the Power Platform product engineering team for consistently exhibiting deep architecture expertise and creating high quality solutions for customers during project engagements. I’m one of the 33 individuals worldwide who must meet the following criteria:
Must have a minimum of 2 years of experience with Power BI and a minimum of 5 years of experience with Enterprise BI solutions
Must have a minimum of 2 years of experience as an Enterprise BI architect
Must be working for a partner with Gold certification in Data Analytics MPN competency
Must have been lead architect for at least 2 Power BI in-production implementations with at least 200 active users (Preferably for CAT managed customers)
Scenario: You deploy a model to a Power BI workspace. You assign users to Members and Viewers roles. Everyone is happy. You later added a row-level security role and republish the model. Admins, Contributors and Members continue to view reports connected to the dataset as usual. However, Viewers report an error like the one shown below (didn’t Microsoft do an outstanding job explaining what went wrong with all of these guids?):
Analysis: Users with Administrator, Member, and Contributor permissions bypass any row-level security policies even if they assigned as role members. However, viewers are refused access unless they are added to a role that grants them the appropriate permissions. So, the likely culprit here is that there are some viewers that are not assigned to a role.
If viewers should have unrestricted access to an RLS-enabled dataset, create an Open Access role and add them to the role. As a best practice, you should create a security group and grant the group membership to the workspace and RLS.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2021-03-18 21:01:042021-05-20 17:16:46When Something Goes Wrong (Unable to Load the Model)
Scenario: You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.
Analysis: There is a long-standing Tabular limitation that prevents USERELATIONSHIP for an added level of security which may be triggered even if USERELATIONSHIP doesn’t enable a relationship on the security propagation path. This is done to prevent information disclosure in case there is some other active relationship (since UseRelationship would disable security propagation across the other relationship). Unfortunately, the current design is “no inactive relationship, no problem”. A better option would have been to introduce a metadata table-based (or relationship-based) attribute to remove this rule.
Workaround: Since currently there is no magic switch you need to find a workaround depending on your specific case. For example, in one case where only external users were affected, I added a new set of measures. I didn’t change the original measures for two reasons: a) avoid re-testing the entire model and b) dynamic relationship always underperform materialized relationships. The new set could use INTERSECT (or TREATAS if you on SQL Server 2016+) to replace USERELATIONSHIP. For example, instead of:
Note that you might not get exactly the same behavior because materialized and dynamic relationship differ in how the missing members are handled (see my blog “Propagating DAX BLANK() Over Relationships” to understand this better).
Amidst the pandemic, the Houston Health Department (HHD) had another predicament to tackle. With lab results accumulating rapidly at one million cases per month, the vendor system they used for capturing and analyzing COVID data couldn’t keep up. The SQL Server database had large tables with normalized name-value pairs for each question and answer received from the patient, and for each investigation result. Read our case study to learn how Prologika implemented a BI solution powered by SQL Server and Power BI to help HHD gain reliable and timely insights from COVID lab results.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2021-02-26 14:19:222021-02-26 14:26:19State Health Department Gains Reliable and Rapid COVID Insights
Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, March 1st, at 6:30 PM. Your humble correspondent will discuss the business value of semantic models and implementation options for self-service BI and organizational BI. For more details, visit our group page.
A semantic model is a layer between the data source and end user. Data analysts create self-service semantic models with Power BI Desktop or Excel. BI developers implement organizational semantic models with SSDT, Tabular Editor, and PBI Desktop. Join this session to:
· Learn what is a semantic model and how to choose between the self-service and organizational paths.
· Understand the implementation options for self-service BI models and best modeling practices.
· Find how organizational semantic models can help you achieve the “Discipline at the core, Flexibility at the Edge” tenant
· Learn how to choose a hosting platform and tool for implementing organizational semantic models.
· Learn how data analysts can extend organizational semantic models.
Speaker:
Teo Lachev is a consultant, author, and mentor, with a focus on Microsoft BI. Through his Atlanta-based company Prologika (a Microsoft Gold Partner in Data Analytics and Data Platform) he designs and implements innovative solutions that bring tremendous value to his clients. Teo has authored and co-authored several books, and he has been leading the Atlanta Microsoft Business Intelligence group since he founded it in 2010. Teo is one of the few FastTrack Recognized Solution Architects by Microsoft for Power BI in the world. Microsoft has also acknowledged Teo’s expertise and contributions to the technical community by awarding him the Microsoft Most Valuable Professional (MVP) Data Platform status for 15 years.
Prototypes without pizza:
Power BI Latest
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2021-02-21 19:49:192021-02-27 13:58:50Atlanta MS BI and Power BI Group Meeting on March 1st
Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, February 1st, at 6:30 PM. Paul Turley (MVP) will show you how to use Power Query to shape and transform data. For more details, visit our group page.
Presentation:
Preparing, shaping & transforming Power BI source data
In a business intelligence solution, data must be shaped and transformed. Your source data is rarely, if ever, going to be in the right format for analytic reporting. It may need to be consolidated into related fact and dimension tables, summarized, grouped or just cleaned-up before tables can be imported into a data model for reporting.
· Where should I shape and transform data… At the source? In Power Query, or In the BI data model?
· Where and what is Power Query? Understand how to get the most from this amazing tool and how to use it most efficiently in your environment.
· Understand Query Folding and how this affects the way you prepare, connect and interact with your data sources – whether using files, unstructured storage, native SQL, views or stored procedures.
· Learn to use parameters to manage connections and make your solution portable. Tune and organize queries for efficiency and to make them maintainable.
Speaker:
Paul (Blog | LinkedIn | Twitter) is a Principal Consultant for 3Cloud Solutions (formerly Pragmatic Works), a Mentor and Microsoft Data Platform MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to model data, visualize and deliver critical information to make informed business decisions; using the Microsoft data platform and business analytics tools. He is a Director of the Oregon Data Community PASS chapter & user group, the author and lead author of Professional SQL Server 2016 Reporting Services and 14 other titles from Wrox & Microsoft Press. He holds several certifications including MCSE for the Data Platform and BI.
Prototypes without pizza:
Power BI Latest
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2021-01-31 09:20:382021-01-31 09:34:36Atlanta MS BI and Power BI Group Meeting on February 1st
The moment you add a calculation group to your model, Power BI sets DiscourageImplicitMeasures = True on the model. Although this property can trick you to be believe that they are still supported, you can’t create implicit measures, such as by dragging a numeric field on the report to summarize that field. That’s because implicit measures are created as inline calculations which calculation groups don’t support.
Also, there is a current issue where when you add a column from a calculation group to a filter, “Require single selection” is set to on and it can’t be changed. Therefore, you won’t be able to filter multiple calculation items, such as to present t only MTD, QTD, and YTD from a list of many items in your calculation group. As a workaround, you add a calculated column that flags the desired values and filter on it. You can vote to expedite the fix here.
I’ve noticed severe performance degradation after refreshing a Power BI Desktop model with some five million rows. The Power BI Desktop process showed a sustained 50-60 % utilization for minutes in the Windows Task Manager. I did a profiler trace and I saw expensive DAX queries like these:
As it turned out, Power BI Desktop autogenerates these queries when building a Q&A index. The 100-size limit is because Power BI wants to keep the index small. In addition, values that are longer than 100 characters are unlikely to be asked by the user. Why not check thd the maximum column value and skip the column? Power BI wants to skip instances that are too long but still index the remaining instances of the column.
To avoid this performance degradation when modeling on the desktop you could disable the Q&A feature. This will also disable smart narratives because they depend on Q&A.
To do this, go to the File, Options and Settings, Options, and turn off the Q&A option.
If Power BI Desktop is connected to a remote model, such as a published Power BI dataset, you’ll see also an option to create a local index. This option was added because Power BI needs to ask user permission to query data from remote sources, build the data index, and store it on user’s machine. By default, it’s disabled until the user explicitly turns on Q&A. For import models, as the data is already on user’s machine, Power BI doesn’t need to ask the permission to query data anymore. That’s why the option to build a local index is not applicable to models with imported data.
Disabling the Q&A in Power BI Desktop affects the local file only. When you publish the model, you reenable Q&A from the data settings if you want end users to use Q&A features. For remote models, if you leave the first option, “Turn on Q&A to ask …”, on, but disable the second option, “Create a local index….”, and publish the model to the service, then Q&A will be enabled in the service by default. That is, you don’t have to go to dataset settings to enable Q&A for that model. For import models, you have to disable the first option, and then after publishing the model to the service, you have to go to dataset settings to enable Q&A there.
