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

 

Learn DAX from examples: Lazy DAX

Learn DAX from examples: Lazy DAX

Concrete cases in DAX

Working with Power BI (or SSAS Tabular) does not require much effort. It’s quite intuitive and easy to set up.
However, to fully enjoy it, it is recommended to take the time to learn DAX.

Create a measure, in the formula bar, is done through an expression written in this language.
Very powerful, the DAX contains some functions that will give all the interest to your beautiful data model.

It would be a shame to see him as a cherry on the cake! It would be rather a cherry cake over the cake!

Simple syntax, very powerful function in a few lines, but it can be complicated.

You will find here support with some concrete cases, from the simplest to the most complicated.

 

  • A Power BI Desktop file
  • A PowerPoint file with multiple explanations. You can use it to provide training or initiation to the DAX.

If you have new exciting measures, do not hesitate to send them (via LinkedIn), I will be happy to add them in the Power BI file.

 

My colleague Mathieu Ricour and myself are happy to share you another way to learn DAX.

In Lazy DAX, you will found:

A Power BI Desktop file

  • It contains multiple examples based on a simple and understandable set of data
  • With multiple clear examples, you can change the expressions / scripts and directly see the result. (It will help you to understand and develop in DAX)
  • For now it covers:
    • The basics functions (COUNT, DISTINCTCOUNT, SUM, …)
    • CALCULATE function
    • Time Intelligence
    • Semi Additive measures
    • Calculated Columns
    • Rank
    • EARLIER
    • Variables
    • A concreate example: Churn Rate

A PowerPoint file

  • In this power point we provide you some explanations to learn step by step.
  • You can use it to understand the basics (maybe give you a first view of what you can do with DAX and Power BI)
  • If you have to prepare your own DAX training, it could be a starter.

 

Our files are available for download on GitHub

https://github.com/arnaudgastelblum/LazySnail

Pin It on Pinterest