Time traveling for Power BI

Spoiler alert: Nerd-article

The SMS-Timing software is more and more used as a single tool to run an entire business or family entertainment center.  Also on the area’s that we haven’t foreseen earlier.  Topics like stock management, staff management, data analytics, everything is being handled and is offered as part of one product, with the same quality and ease of use expected from that product.

Data for statistical analysis is very different from data needed for live operations. In live operations, it is fine to know who “is doing it now”.  When we start processing data of years, we need to know “who did it back then”.

In close cooperation with our Power BI team, we have found a way to record all the data required for processing.  With little to no work, our data-analysis team can inform the framework that “time traveling” data should be recorded on certain data.

The software will understand this requirement and from that version on, each change on data will be processed in a Time-Traveling table and can be used for further analysis by Power BI.

Since this blog is also used by internal developers, I’ll explain quickly how to achieve this.

1. Put the meta-tag <Timetravel=on/> on the table that should record time traveling data.
2. Run our AAA-Tool.  (Special tool that is used to manage the database structure)
   • A partner-table is created, suffixed “_TT”.
     A UTC Timestamp field is added.
     History is disabled on this new table.
     Synchronization is still happening though.
   • This new table fills up automatically by a set of triggers.  All these triggers are created by the framework and should not be maintained.

If you don’t fully understand what is happening here, don’t worry, you are only supposed to understand the end result.  The goal of this article is to announce that we have an easy solution to answer questions like: “Who made that sales on December 26th and to what sales team did he belong back then so we give the right manager his commission!”

Opt-out fields

It is in the latest version also possible to “opt-out” certain fields or domains from being added to the time travel list.  As soon as you mark a table for TimeTraveling, all fields are included, except those that the tag <timetravel=off/> mentioned explicitly inside the description of the field.