skip to Main Content

Usability

Can your data model (Power Pivot or Power BI) easily be used by 100s of users? Is your model fairly intuitive to understand for the end-user even with little or no training?

Usability may be the key to achieving the Self-Service BI nirvana.

The very first Power Pivot model that I had designed…

Number of users I expected to use my first Power Pivot Model    1 (myself)
Number of users that actually ended up using the model    600+

I learnt my lesson the hard way. The one-audience data model was filled with oddities that was not always easy for end users to understand. I ended up doing a lot of video tutorials (now you know how I got good at video Winking smile , check out our YouTube channel).

Now when I design a model for any client, I design with usability in mind. Such that perhaps not right away, but at some point 100s of users can use the BI Model for Self-Service BI. They can easily navigate the model to create their own Reports and Dashboards (in Excel or Power BI).

I use a few techniques when designing for usability; good Data Modeling (star schema), naming conventions, use of Perspectives, automated Data Dictionary (documentation) are some that I lean on.

In this blog post, I would illustrate one scenario where, some would argue I go to great lengths to make the model more usable. As you will realize, it is well worth it.
GOAL: Out goal is to combine two data tables – Sales and Budget – which are at varying granularity, but without the use of a an extra lookup table.

image
GOAL: Combine Sales and Budget without creating a “Subcategory” Lookup table

Jump to section:
Setup and the Classic Solution
Usability Issues with the Classic Solution
Elegant Solution
Results
Epilogue: Single Data Table with Differing Granularity

Need help designing your BI Model? Check out our Training and Consulting Services

Setup and the Classic Solution

In our book, Chapter 18 Multiple Data Tables – Differing Granularity, we show how you can combine Data Tables of differing granularity.
(You have a copy of our book, right? Hmmm…why would you wait to buy the #1 bestselling Power BI book on the market, Carpe diem my friend!).
Grain or granularity is a fancy word, which simply means what one row of data represents. For example in our data set:-

  • Sales – is at a Daily and Product SKU (Individual Product or Product Key) level granularity
  • Budget – is at a Monthly and Product SubCategory level granularity

image
Sales Table at grain – ProductKey, Date

image
Budget Table at grain – Product SubCategory and Month

You would encounter this pattern frequently when working with real world data sets – Data tables of differing granularity. And the book already shows you how to solve this using a shared Lookup table.

image
Sales and Budget linked by a newly created SubCategory Lookup Table

Creating a shared lookup table, lets you:

  • Slice and dice your Sales and Budget numbers using any of the shared lookup tables (Calendar, SubCategory in this case)
  • Define “Hybrid” measures which do math across the two data tables (e.g. Variance to Budget, or VTB = [Total Sales] – [Total Budget])

image
Slice and dice Sales and Budget using Lookup Tables and define “Hybrid” measures

You would encounter this pattern frequently when working with real world data sets – Data tables of differing granularity.

Usability Issues with the Classic Solution

When I think of Data Modeling, usually I have two criteria in mind – Performance and Usability. Let us consider these for the problem at hand:
(Data modeling best practices are sprinkled throughout our book. I also wrote an article which summarizes the key best practices: Data Modeling for Power Pivot and Power BI).

  • Performance: Having a cascading Lookup table (SubCategory) with just one or two columns is generally inefficient. But performance is not our main criteria here.
  • Usability: With the Classic solution, you end up with the “SubCategory” column in two places – in Products table and SubCategory table. And users are expected to remember to use the right SubCategory column.
    – Use SubCategory[SubCategory] when using measures on [Budget] or hybrid measures
    – Can use Product[SubCategory] when not using [Budget] related measures

 

If you or any user using the model to create Pivots/Reports uses the Category/SubCategory from the “wrong” table, then they can get unexpected results (see image below). You could hide the Category/SubCategory columns from the Product table, but that only feels like a hack.
– Category/SubCategory are Product attributes and logically belong in the Product table
– For users that do not care about [Budget] table and would never use those measures, this feels like an additional burden for them to find the Category/SubCategory in a separate table.

image
You always need to remember to use the Category/SubCategory fields from the “right” table, else you may not get the correct results

Elegant Solution

Here is how we can go about combining the Products and the newly created SubCategory table for a more usable and elegant solution. I have highlighted the key steps, you can examine the solution file to go step by step in the Power BI Query.
Note: Don’t be scared of the M code, 99% of these steps were generated using the Query ribbon – I have included those screenshots as well.

1. Create a SubCategory Lookup Table

Step1_BuildSubCategoryTable = _Product,
 #"Removed Other Columns" = Table.SelectColumns(Step1_BuildSubCategoryTable,{"SubCategory", "Category"}),
 #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"SubCategory"}),
 #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([SubCategory] <> null)),
 SubCategoryTable = #"Filtered Rows",

image
Steps to create a SubCategory Lookup Table (click to enlarge)

This is so far identical to how we built the SubCategory Lookup Table in the classic solution.

2. Append Products and SubCategory Table

Step2_AppendTables = _Product,
#"Appended Query" = Table.Combine({Step2_AppendTables, SubCategoryTable}),

image
Append Queries is available on the Query ribbon interface

In the new appended table, the ProductKey is null for all rows coming from the SubCategory table.

image
ProductKey now has null values for the rows coming from SubCategory Lookup

This won’t do for a Lookup table (key used to create relationship cannot contain null values), thus we create a new key column.

3. Create New Product Key

   #"Added Index" = Table.AddIndexColumn(#"Appended Query", "NewProductKey", 1, 1),

image
Add Index Column is available on the Query ribbon interface

Now we have a unique key for our appended table.

image
Now we have a NewProductKey that we could use

4. Update Sales and Budget Tables with New Product Key

All that’s left is to go back to our Data tables – Sales and Budget, and update those to use the NewProductKey.
The steps below are for updating Sales table; Budget table would be updated on similar lines to add the NewProductKey.

Step4_UpdateProductKey = #"ShiftYear to 2016",
#"Merged Queries" = Table.NestedJoin(#"Step4_UpdateProductKey",{"ProductKey"},Product,{"ProductKey"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"NewProductKey"}, {"NewProductKey"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"ProductKey"}),

image
Steps to pull in the NewProductKey into our Sales table (click to enlarge)

BONUS CHALLENGE: You can try generating NewProductKey in such a way that the Sales table would not need to be updated with a New Product Key. You would only need to worry about the Budget table.
I can think of a few ways to accomplish that. Up for the challenge? Modify our provided file and post a link to your solution file in comments.

Final Step: Link Sales and Budget to new Products Table

Next, you can link both the Sales and Budget data table to our new Products table using the NewProductKey column!

image
Sales and Budget linked by a Product Table, even though Budget is set at SubCategory

Results

We do not need to worry about using the right table for the right occasion (based on the measures being used). Furthermore, the Category, SubCategory fields – which are Product attributes – are found in the Products table, which would make more intuitive sense for end users.

image
Our new model is more user friendly: Product Category/SubCategory can be used to view both Sales & Budget

Epilogue: Single Data Table with Differing Granularity

The differing granularity problem not only occurs when multiple data tables are involved (e.g. Sales and Budget), but can also occur within a single data table. For example, Sales can record transactions at different grain of Geography (Territories table in our model).

  • Some Sales transactions at the lowest granularity (Region in our case)
  • Some Sales transactions at a higher granularity (e.g. Country). This can occur in a scenario where the deal was made, let’s say with the Government of a Country. Then ‘Country’ is the only level of information available.

This can be addressed using a similar approach as outlined above to build a Geography/Territory lookup table at a different grain. If interested in such a problem, leave us a comment and we’ll try to cover this in a follow up blog post.

Download Solution File: Usability_PBIX_Files.zip

Power On!
-Avi Singh

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 9 Comments
  1. Hi Avi,
    this is a very interesting approach. Basically introducing a completely new type of dimension table: Unambiguousness only through the new “technical” key (as we’ve now a couple of null-values in the “native” key of the product table).
    Think I totally love that 🙂

  2. So instead of creating a new key(-column) why not just adding the new keys from the Budget/subcategory into the existing key-column (say with a leading “s” or so to maintain uniqueness). That way you wouldn’t have to change the existing connections from your Sales table but just have to connect your Budget table to the new values.

  3. Why such an elaborate ETL design? Keep the snowflake model and hide the SubCategory table from the user.

    In essense the relation between Product and Budget is a many-to-many with SubCategory being the bridge table.

    Same pattern as this – by Marco Russo:
    https://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

    So all budget related measures just have to include this table in a CALCULATETABLE statement.

    For ease of use you can denormalize the Category column into either Product and/or Budget table using Calulated column with RELATED() statement.

    It will then not matter which on is being used by the user if the Bridge table is in all of the Budget measures (and possibly Sales measures).

  4. Thanks for sharing this Avi! After seeing this at PASS, now it makes a lot of sense! Appreciate all the screenshots and use of the GUI to generate the M code.

  5. Hi Avi!

    How do you manage the data in the budget table to reflect NewProductKey, i.e. how do you assign the values 607, 608, etc. for NewProductKey as a foreign key to all rows in the budget table (that currently only have SubCategory as an attribute)?

    Expanding on ImkeF’s post, why not use the distinct values of SubCategory in the Budget table as the ProductKey in Products (a data type change might be required for that column) – that way no change would be required apart from appending the rows for each SubCategory in Budget to Products and joining the Budget table to Products … ?

    Best,
    Helmut.

  6. I would suggest a solution using a many-to-many design pattern with the SubCategory being filtered “upwards” by the Product table.

    Here is what you do:

    – Hide SubCategory table from report view.
    – Replace columns SubCategory[Category], SubCategory[SubCategory] in the report with Product[Category], Product[SubCategory]
    – Create a new measure (or modify [TotalBudget]) as: TotalBudgetM2M = CALCULATE( SUM(Budget[Budget]), CALCULATETABLE( Subcategory, ‘Product’) )
    – If you created a new measure – then replace all references to [TotalBudget] with [TotalBudgetM2M] – in dependent measures and visualisations

    Done!

    No “hard” ETL design – a simple snowflake schema – but without the clutterer from the SubCategory table.

  7. Hi Avi,

    Budgets are created and exist at the intersection of Department and Account. Could you help illustrate how one might structure the data tables to arrive at similarly elegant solution? The existing example works well when there is only one entity without any departments but in practice I believe it is more common to see budgets created and pushed down to the “department.account” level.

    Thanks,

    Joaquin

Leave a Comment or Question