Designing universe with aggregate tables.

[Former Member]

Hi Experts,

I have a question on aggregate tables.

If we have two aggregate tables (monthly and yearly)  for a daily fact table. Do we require to join these aggregate tables with dimensions or leave it standalone ?

I saw in best practices that we need to join the aggregate table as well?  If we join the dim tables, do we require to create context for each aggregation to avoid loops?

Please suggest..

In efashion sample, they were not joined..

Thanks

Pavani.

[MariannevL]

Hi Pavani,

/* sorry about that, my keyboard got stuck, I try again */

It is not necessary to have the dimension tables, but it helps,

it makes creating aggregate aware objects a lot simpler if you only have to do the measures

and don't have to worry about the dimension objects.

You need to have them available at the right level however.

That is why they are not linked in the eFashion example.

There is no 'year' dimension table and joining it to the year in a date calendar table or in a month table would be incorrect.

Only if you have a real 'snowflake' schema you can use the same table/column for a particular dimension,

Daily facts would go to calendar, then to month, then to year.

Monthly aggr to month then to year

and year aggr to year.

The year dimension would come from the same table/column always.

There is however a trade-off in performance. It is faster to have only star-joins.

Then the dimension would be aggregate aware too, coming from calendar with daily facts,

from month with monthly aggr and year with year aggr.

In this case if you need nothing else from the dimension tabel on that level,

it is just as easy to pick the column from the aggregate and do not join.

On the context question, as soon as you have joins to the aggregate tables,

you need them to be in separate contexts (chasm trap).

This can be detected automatically.

Hope this helps,

Marianne

[former_member182521]

It is not necessary to join the aggregate tables with the fact table unless otherwise you have an business context to join them. I.e. requirement like report with the particular day (from daily fact table) and the monthly sales(from monthly aggregate table). If not we can leave them as standalone tables itself.