PIT Point In Time

PIT Point In Time

Introduction

We often have descriptive data in our Power BI models. Data may vary over time and have validity dates. It can become a real headache when creating a data model.

What kind of data?

  • A car could change his production plant through time.
  • A car can have different engines over time
  • A customer can have through time:
    • One or more loyalty cards
    • One or more addresses

What data types?

  • Multiple data
  • With a notion of time. (The information is valid from one date to another and potentially followed or overlapped by another)

PIT – Point In Time – Datavault

Point In Time is a concept widely used when creating a data warehouse in a Datavault flavor. Performing a snapshot and historizing the data makes restitution much easier.

(I will not discuss the pros or cons of Datavault, but I have my preferences ^^)

If you want to know more, I invite you to read this article. (Written by Dan Linstedt)

Value of PIT and Bridge Tables in Data Vault 2.0

Demo

What do we expect?

It is exciting for our users to navigate through the data by applying a time filter (CFR Slicer Date)

How: By creating a DAX measure. (#PIT Shoes)

The example above shows dynamically Pauline’s shoes.

DataModel

Our data model

The following example is quite simple (The Power BI file is available for download at the end of this article)

Many of you know it, I am passionate about Power BI, running, but many ignore my “passion” with running shoes. (Nobody is perfect !)

The example refers to a list of runners who have one or more pairs of shoes over time.

The Runners table:

The Shoes table

Model specificities

  • RunnerId (The Runner Table Key) is available for all shoes.
  • For performance reasons, the date stamps are duplicated and converted into numeric.
    • ValidFrom – ValidFromInt
    • ValidTo – ValidToInt

More information

This is denormalization of a star schema generally composed of a Bridge and a dimension table.

In a Datawarehouse (Kimball), we should have a Bridge: BridgeRunnerShoes and a dimension: DimShoes

Why?

It helps our users to have better comprehension. (Simplicity)
The Vertipaq engine will compress the redundant information
To avoids an extra join / relationship.

You want to know more about Bridges:

 

Multivalued Dimensions and Bridge Tables

A more complete model could look like this one

TimeLine

Graphically analyze the different interactions.

  1. Our user pick a date via the PIT Date slicer
  2. The shoe table changes dynamically and display the one owned on that date.

NB The blue bar drawn on the timeline has been added manually in MS Paint. (If you know a visual component that can display it, do not hesitate to contact me)

Filter: 01/02/2019 | Pauline

Filter: 01/03/2019 | Pauline

Filter: 01/05/2019 | Pauline

Filter: 10/10/2019 | Pauline

Filter: 01/12/2019 | Pauline

Implementation

Create a Calendar table

We have to create a Date table, which will not be associated with our model. (Called here PIT Calendar)

To quickly create a Calendar table, I suggest you write an M Query in the Query Builder. The following articles will give you the approach.

Generating A Date Dimension Table In Power Query

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Create a DAX Measure

This measure Filter the Shoes table by the selected date.

Understand this DAX expression

or

A variant of the previous expression can test if our user specified a PIT Calendar date and apply the current date if not.

Why?

  • The user wants to have the situation of the day (without having to modify the PIT slicer at each opening of the report)
  • The user forgot to create his report with the PIT slicer. Rather than propose the situation in the future, we apply the current one.

The variable contains the date selected by the user and is dynamically applied to our dataset. (Shoes)

Conclusion

Now, you know how to make your data alive.

Feel free to comment if you have more ideas, improvements, tips, or comments.

Download the Power BI example file here:

https://github.com/arnaudgastelblum/LazySnail/raw/master/LazySnail_PointInTime.pbix

 

Pie Chart is bad!

Pie Chart is bad!

      During my presentation at Power Saturday in Paris, I wanted to demonstrate to an already very optimistic public that Power BI is a great tool and that modeling is a must-have for their reports. (Data Modelling is not the subject here)
Power BI a super tool? Yes, and to complete this idea, I added some slides to highlight each piece of the tool.

One of the slides showed the ease and elegance of the visuals.
After a quick search on Google images, I copied a screenshot created by a user in Power BI and added it to my presentation.

In the audience, two friends looked at me with a horrible face. The damage was done, I had slipped the image of a Pie Chart!

Nothing crazy, isn’t it? But when you know a bit of the history of the Pie Chart, we realize that this visual component is not recommended in the world of Data Viz. This blog post also follows some more or less tense discussions between colleagues.

I wrote this article for many reasons:

  • This point is already super documented on internet, but some people never read them before. (I provide links at the end)
  • I wanted to convinced myself first, and created a Power BI file to confirm this point. (CFR the following screenshot)

I was a Pie Chart lover

I know that its use is not recommended and I try not to use it.
But yes, I liked it!
• It’s beautiful!
• It’s colorful!
It gives a professional look to our reports!
• It shows information!
It gives me some pride to not use tables or matrix. (Yes, I like numbers, not you?)

I think we are used to seeing this kind of visualization since our earliest childhood in commercial brochures, professional documents.

The Pie Chart is visual, and help us to return informations quickly. But unfortunately, this information is not always the right one.

Why?

To compare groups, we evaluate the difference of the amplitude of the angles while a histogram will request us to compare lengths between them.

A long description is not needed, I would prefer to show you 2 set of data, both represented in a Pie Chart and histogram.

Your first look will give you some information, but not as much precise compared to the two histograms below. It’s possible to have quick information, but very hard to have a clear interpretation of them.

If I didn’t convince you, try to sort each portion from the smallest to the biggest. Try to do the same with the histogram and compare the time elapsed for both.

PowerBI – Bookmarks

PowerBI – Bookmarks

Bookmarks are not perfect, I can easily confirm you. But with them, you can build beautiful and rich reports.

In this example, I will show you how your users can switch from one visual to another one.

Update Power BI Desktop : More information with less visuals ? Let’s use the Tooltip !

Update Power BI Desktop : More information with less visuals ? Let’s use the Tooltip !

*This post is an update to the video about the Tooltip*

It can be sometimes very difficult to put all the information we need in only one report page. Maybe I could remove this visual to add this one instead? But then, I won’t have the figures for this year. Maybe this one then? Impossible, otherwise I’ll miss the ranking of my product. It can easily become a real headache.

What if there was a wonderful feature inside Power BI Desktop allowing us to add more visuals to our report within the same space… Stop dreaming, I have a good news for you, it exist! Well, kind of. The tooltip will give you the opportunity to add a visual when you hover a cell of another visual. Still a bit confused? Let’s see how to do it and the final result.

As always, let’s first take the LazyDAX file (such a masterpiece I know). We can create a new page and add a new visual in it. Here is our “test” page with a simple Cost by Customer and by Year table.

Nothing complicated until there. By default, when you hover a cell of this report, you will only have a small definition of the cell, or maybe a quick resume of the whole line. Ok, why not but it’s not that interesting.

What we would like to have instead is something more relevant, more useful to the business and the end user, something more visual. Perfect, the tooltip is made for this. Let’s create a new blank page. It will be our tooltip page.

Now we can go on the format pane of the page. Under the “Page Information” tab, we will set “Tooltip” on “On”. That way, the page will be considered by Power BI as a tooltip

Now you can also go on the “Page Size” tab and choose the “Tooltip” type. You don’t have to do it but it’s better to keep something visually harmonious.

Now you can create the report that will be displayed as a tooltip to your report.  Let’s take something like the Cost by Product.

Here is the report with the tooltip format. Ok it gives more information but it’s not really readable. Don’t worry, just wait and see how the magic happens.

But first we have to go back to our report page and select the visual that will have the Cost by Product report as tooltip. Select the visual.

Go on the “Format” tab and enable “Tooltip”. Once it’s done, under “Type” you select “Report page” (the way you will see the tooltip) and then select “Tooltip Page” (the source page with your tooltip).

Now we can see the magic. When I hover one cell of my table, I will have the report of the “Tooltip Page” displayed and sorted by the current line.

This is a really simple way to add more information without overloading your page.

Once again, it was not something very difficult, you just had to be aware of it.

Pin It on Pinterest