tempporal modeling of bridge tables

Hi,

I'm a bit surprised that the ideas of temporal datamodeling for bridge modeling are not widely available. I've looked in the Kimball book and the only thing there is using a validfrom and a validthru field for checking the validity of bridge records. But I would like to read more about this. Does anyone know more info on this subject?

Should you handle the temporal modeling of the bridge table in a report? For instance, suppose a n:m relation between a location and a department (Multivalue dimension, M2M dimension) and you run a report with multiple valid periods (via parameter) of relations between location and departments? How should you handle this? Showing all the relations? And count the records multiple times?

Gr,
Hennie

It does not make sense to use a hierarchy based on a date range, so, the simple answer is, it isn't done.

When applying a hierarchy to a report, it is as of a specific point in time. Date ranges that drive reports refer to the measures, not the hierarchy.

When selecting the appropriate bridge row you have two basic choices:
1. Use a predetermined date (either user specified or some default), which is the most common, or...
2. Use the date of the measure

Hi Ngalemmo,

Let me rephrase your answer. Let's see if i understand this correctly. If i want to show data in a multivalue dimension it's not advisable to build a report with a start and an enddate? Only Point - In -Time report? Most of the reports i'm building are about a period of time. So in this case multiple hierarchies can exist in my multivalue dimension. I think that the situation i've described here is the second option you mentioned. Determining the hierarchy based on the measure?! But then i've different hierarchies?? A measure can belong to different hierarchies in a period of time. So this would mean i've to show the different hierarchies in the report?! grrrrmph....

You first option doesn't seem right to me. You're suggesting a date parameter in the report and let the user determine at which PIT of the hierarchy the user wants to see the data?!

Unless i don't understand you correcting, í'm not very happy with the options. May be it's just the way it is. So there are three options(IMO):

• period report
  
  • show multiple hierarchies in report (which is difficult)
    
  • Use a fixed date (parameter)
  
  
• PIT report

Thnx for your answers.

Hennie


Correct layout bulleted list:
*Period
- Show multiple hierachies
- Use a date parameter
*PIT

If i want to show data in a multivalue dimension it's not advisable to build a report with a start and an enddate?

No, date ranges are very common. But they apply to the measures, not the hierarchy.

What I am saying is a measure has a time associated with it. When using a hierarchy, do you want to apply the measure (i.e. one fact row) to the hierarchy at the point in time of the measure or at some other point in time? It does not make sense to apply the measure to a continuum of choices because the hierarchy has only one state at a particular point in time.

I am assuming your bridge table includes an effective/expiration date on the row to identify when a particular relationship is in effect.

The first option is very common. In accounting it is called a 'pro forma' statement. It is basically reporting historical data under the current organizational structure. For example, two companies merge, it is common to report the historical portion of the new corporation's financials as if both companies had been together all along.

So in this case multiple hierarchies can exist in my multivalue dimension.

What multivalue dimension are you taking about? You only mention a location/department hierarchy. That is not a multivalued dimension.

But then i've different hierarchies?? A measure can belong to different hierarchies in a period of time.

Do you really mean different hierarchies or do you mean different versions of the same hierarchy?

So this would mean i've to show the different hierarchies in the report?!

If we are talking about different time-based versions of the same hierarchy, what choice do you have? Your original post was about how to deal with multiple versions of a hierarchy over a period of time. Well, the answer is: you either pick one point in time and use that version for all measures or you let the measure drive the point in time to select the appropriate hierarchy version.

I don't understand your concern. Let's say we have location A and departments X and Y. In the first half of the month A rolled up to X and in the second half of the month A rolled up to Y. During the first half, A had $100 in sales and in the second half A had $80 in sales.

Under option 1, if you were to report using the hierarchy as of the beginning of the month, X would show all $180 of A's sales. If you were to report using the hierarchy at the end of the month, Y would show all $180 of A's sales.

Under option 2, if you select which version based on the date of the transaction, $100 of A's sales would show under X and $80 of A's sales would show under Y.

There is simply no other way you can report this stuff that makes any sense.

Hi ngalemmo,

Thnx for your answers. I think we're are agreeing about this subject.

My concern was dat if we are reporting over a period of time, we will see something like this in the report:

Department X
* Location A 100

Department Y
* Location A 80

This is a bit confusing if a user doesn't know the details about the thoughts behind modelling this in a M2M dimension.

What is a multivalue dimension in your opinion? We have a n:m relation between location and department. Isn't this a multivalue dimension?

Greetz,
Hennie

This is a bit confusing if a user doesn't know the details about the thoughts behind modelling this in a M2M dimension.

It becomes a training issue. But it is not a modeling issue. It was a change made by the business to its organization.

What is a multivalue dimension in your opinion? We have a n:m relation between location and department. Isn't this a multivalue dimension?

A multivalued dimension is a dimension that naturally may have more than one value relating to a fact. A hierarchy is a structure built upon a dimension. Both use a bridge to implement (because they both result in ,many-to-many relationships), but they are very different things.