Mixed levels of granularity in a fact...how to model?

[RudiHendrix]

In the current DWH I get a new requirement.

There is a fact table that holds hourly data.

There is an hourly offtake that needs to be displayed per hour.
Next to that there is a yearly contracted capacity that is one value for the whole year. Not a problem: just make sure every fact record contains the right year value in that field and make sure aggregation is set to "unsupported".

But the problem comes with another field of the fact table. It is the price. It is being calculated by dividing the Billed Amount by the Billed Volume. These values are monthly values. Now the user has two requirements:
-If hour or day is selected the monthly value should be displayed
-If yearly level is selected the monthly values should be aggregated as a volume weighted average and displayed.

Now the first requirement can be solved by just using the same method as for the yearly values. But that would make the second requirement impossible.
The second requirement can be achieved by breaking down the monthly values into hourly values and use the technique from this topic to calculate the volume weighted average.

But both requirements on the same time seems impossible!

Now I discussed this with one of hour ETL specialists and he has thought up a solution that might just work. At least: for the ETL part!

This would then be the time dimension:


And this would be the fact table:


My apologies for the large pictures, but "mijnalbum"  didn't upload if I didn't make them at least 400x400 pixels :(

In this example the rows 1 and 2 display hourly data. As a price the month price can be inserted.
The row with fact ID 123 displays monthly data and is thus connected to a time dimension SK that goes down to only month level. The hourly offtake is aggregated to month level in ETL and the price is still the monthly value.
The row with fact ID 7812 displays yearly data and is thus connected to a time dimension SK that goes down to only year level. The hourly offtake is aggregated to year level in ETL. The price is aggregated by calculating a volume weighted average of the monthly prices.

The red column in the time dimension is needed for the ETL. In the example of the Time Dim below you need a column where you hold the lowest value of your time hierarchy.
So for determining the SK, you need to link on this field. If then your hourly records come in, you will find the correct SK values when linking on this field, when your monthly data comes in, you will also find the correct SK and for year the same.

This looks like the best solution. All values will be calculated in the ETL part and I only need to pick up the correct values. But the question now is: how can I model my framework to have it handle these tables? How can I make sure that if somebody selects only 2010 from the time dimension they will only get the values from fact ID 7812? How can I be sure that it will not take all values from the fact table that are linked to a time dimension record that has 2010 in the year field as well?

[RudiHendrix]

Nothing? :(

[blom0344]

I read your story twice, but even with more than a few years of both ETL and reporting experience I failed to get the picture. I suppose SK is the surrogate key. Why not add the restriction that lowest level = year in case you want only fact with ID 7812 based on year?

[RudiHendrix]

Thanks for at least doing the effort of reading this.

SK is indeed the surrogate key.

If I understand you correctly what you proposing is exactly what I am looking for! But the question is: how can I add that restriction in Cognos FM?

So, if I import this time dimension table in FM, how can I tell that if the user selects the month April from 2010 it only should pick up those records where the LOWEST_LEVEL field is 201004?

Anyway...even if that is possible aggregating against another dimension would not be done volume weighted. (Since in this example aggregation on the measure level is set to "unsupported")

I think with the two requirements:
-If hour or day is selected the monthly value should be displayed
-If yearly level is selected the monthly values should be aggregated as a volume weighted average and displayed.

the designer made it impossible to build a model that fulfills both needs.

What I am going to do instead is just create one measure in the fact table that will always display the monthly figure; no matter what dimension or level is selected.
Next to that I will create a measure that uses the monthly amount and the monthly volume to calculate the monthly price. By setting that up as a volume weighted average one will be able to aggregate it using no matter what dimension. For example: in query studio they take Contract, Year and Price. Then they will get a volume weighted average price per Contract per Year. If they remove Contract from the selection the Price will automatically be Volume weighted over all Contracts and displayed per year.

But I still feel this is a design-issue. The downside is that both designers are on vacation :(