Bi Temporal - As At reporting,

Does anyone know of any references within Kimball or elsewhere related to “As at” reporting. The requirement is to provide both Fact and Dimension historical reporting as the data looked in the warehouse at any point in time.

Note, this is in addition to the Type 2 requirements. This is more like an audit requirement to provide stability in reports ie. A customer report run today for last FY will be the same as it ran 3 months ago irrespective of any late arriving Fact or Dimension data.

As the requirement is to report “As at” any point in time a snapshot of the data will not suffice.

Why won't a snapshot fact work?

Thanks for the reply. A snapshot fact implies a static view of the data at a point in time which would work however, from a single BI package the requirement is to report "As at" reporting at any point in time including time lines at a timestamp grain. This would result in an unmanageable number of snapshot facts.

Got it. That leaves a transaction fact with Type 2 dimensions. That will provide complete history.

At any point in time is tough. I know how to do "As it occurred" and "As it looks now" in the same design, but how last year looked last month is a tough one.

Wouldn't it require a sort of type 3 SCD or maybe a bridge table with an effective and end date and the query would set the AS OF Date Between the effective and end date?

The original post mentioned late arriving facts and late arriving dimensions. Both these activities can cause challenges when looking to provide "At a point in time" reporting.

Late arriving facts : in order to allow for point in time reporting with late arriving facts, you need to add the concept of an "effective date" to your fact records. For example to report data "As of Dec 15,2010", you would constrain your query to all fact records with an effective date <= "Dec 15,2010".

Late arriving dimensions : As BoxesAndLines indicated, standard type-2 processing does most of the work. Your dimensions should have effective start and effective end dates, allowing the BI layer to use "between" syntax on each dimension. The BI layer can get complex, as you are effectively going to snowflake each dimension to itself (based on the business key), with an "as of date" filter on each dimension.

As long as you capture when a transaction occurred in the business as well as when you inserted the row, you should have all the details required to replicate how the business actually was on that day and how the business wants it to be on that day.

Thanks for all the input, just keen to know if others have been involved in delivering against this type of requirement or if there are any known Kimball or other references for schema design.

The standard Type 2 design only delivers part of the requirement (ie standard Track Change) as any late arriving dimension data has to appear as missing when reporting "As at" during the period when the Unknown Dim reference was present and as such we need to preserve this detail.

From a business perspective it does offer a great deal of reporting flexibility and stability however does create greater level complexity in the back end.

I believe if you do a search on SCD type 6 or SCD 6 you will find what you are looking for. I had the same audit/compliance requirement on data that was sensitive to change.

I just read a blog with a "bitemporal manifesto" post

There seems to be an interesting interplay between bitemporal requirements and dimensional modeling.

not sure if it will give you the answers you are looking for but it seems to make some interesting comparisons.

I read the bitemporal blog and, other than the author pointing out common errors people make in their models to record events, I don't see what the big deal is. It most certainly does not deserve to be considered another data modeling methodology, different and distinct from dimension or ER modeling.

It basically boils down to maintaining multiple timestamps on rows. One to record when something occured in the business sense, another to record when it was known in the operational system, and I would also suggest when it was known in the DW.

As far as dimensional joins go, there are already techniques to retrieve current data from a type 2, those same techniques can be used to obtain dimensional state at any point in time and relate it to a fact.

Oh wait... I just invented TRI-Temporal modeling... gee, maybe I should get a .org site!

I am interested in what you mean when you say "techniques to retrieve current data from a type 2" can be used to "obtain dimensional state at any point in time and relate it to a fact".

By this do you mean that "as at" reporting is supported by dimensional modeling ? I am also not sure what it means to "obtain dimensional state" does it involve medidation? (just kidding)

I've had this situation occur while working on a Teradata implementation. It ended up being too hard because of too many variables.

Essentially we came back with the answer - "Keep all of your daily Cognos reports".

i think what LAndrews and ngalemmo are referring to is the fact that if you store multiple images of information about an object (states of an object in a dimension) and you store effective begin and effective end dates for each of these images you can determine what you knew about that object for a specific business time at any system time or "as-at" reporting.

The way to do this for a type 2 dimension which has effective begin and effective end dates on it is to first constrain on the dimension to find the rows for an object which contain information about the business time desired (between the effective begin and end dates). We then need to determine which of these rows was the most recent information at the specified "as-at" time. In order to do this we need to find the dimension row for this object with the max insert date which is still less than or equal to the "as-at" time. This will give us the dimension information we are interested in.

Now to join to the fact table we need to obtain all dimension rows for the object and this is where the self join comes in since we now join the "as-at" dimension row we have selected to all dimension rows for the same object. This is the list of dimension rows we want to join to the fact table.

A similar self join technique can allow type 2 dimensions to be used to produce a type 1 type of results but it is not as expensive (does not require max).

A similar scenaro can be used for fact tables.

However the issue is that this approach may be practical for individual objects but it is very expensive to do for large sets of objects (all customers in texas) and even more so if doing this to multiple tables in a query. (the combination of max and less than hurts use of indexes)

Other bitemporal approaches pay more of a price on insert to make that data more efficient to read (write once read many times philosophy). This is the type of approach which is being supported by temporal features that have been approved for the next version of SQL by ansi/iso, although they have not yet been published. (i saw another post on this, so i will respond there as well) The combination of a penalty on insert philosophy and dbms engine support should go a long way toward addressing bitemporal query performance.

However the issue is that this approach may be practical for individual objects but it is very expensive to do for large sets of objects (all customers in texas) and even more so if doing this to multiple tables in a query. (the combination of max and less than hurts use of indexes)

You described the basic process correctly, but your comment about performance tends to exaggerate the impact. It's not that bad. Also, a self-join can be avoided by maintaining two foreign keys on the fact to the dimension. One being the standard type 2 key (the dimension's PK) and the other a 'type 1' key whose value remains constant for all versions of the natural key's rows. Using this technique, there is no self-join, reducing the query to a standard star join with little performance impact. (This helps make type 2 more usable, as much as we like to keep history, most of the time, users want reports using current informaiton). Also, if you are using b-tree indexes in the dimension, defining an index on the type 1 key and the end timestamp in descending sequence goes a long way to improving performance of a query BETWEEN a start and end date. Although, most of the time, if a large number of rows are being selected, it is usually quicker just to scan the table.

And, of course, all of this is for naught if your source system doesn't maintain timestamps.