Fact Table with different aggregation level

[philelmousse]

Hello,

I have a fact table with different aggregation level(year and month). I would like to know the best way to modeling that in framework manager to meet the requirement of my reporting. My report will have column value based on the actual month and other columns based on the year or ytd.
For now I only have created the dimension and measures but I have issue in my report to have the good values.

Thanks for helping

[jive]

Hi Phile,

I think this is a right place to used determinant in your fact table to get the right aggregation level needed. I have in my current project a fact table with 3 level of aggregation, mostly same of yours,ytd, quarterly and month. In the fact table i set 3 determinants one for each level and I used the same argument in the dimension. for each level the determinant are

ytd = year
quarterly=year+quarter number
month = year+ month number

In the dimension I used the same information and everything work find.

I hope this help

Regards Jacques

[philelmousse]

Thanks for the answer. its mendatory to have the determinants in the fact table ? Because I only have the key of the time dimension. And on the report studio side how do you get the yearly aggregation for one column and the monthly for the other ?

[blom0344]

We had some elaborate discussions some time ago on this topic. A fact normally consists of a set of FK's and measures. If 2 facts of different granularity converge on 1  dimension then determinants are needed to resolve the multi-fact / multi grain scenario. (By adding them to the dimension object !!)

The major discussion can be found here:

http://www.cognoise.com/community/index.php/topic,11557.0.html