Dear all,

I'm looking for advice to make a choice of aggregation on a datamart I've built. is there any one who conducted such work ? any insights ? any web site where I can find litterature on this topic.

Thanks in advance,

what are your aggregation options? what do the datamart tables look like?

you say you need to make a choice, but you don't give us any idea of what those choices are

So my datamart is very simple : It emcompasses one big fact table (3,3 Go; 4 billion rows) - 5 dimensions tables with 2 SCD.

I'm thinking on 2 different strategies to build my fact aggregates.

1 - analyzing the business requirements in terms of reporting
2 - analyzing the parsed SQL submitted by user and then try to locate what could be aggregated.

Indeed, I'm more looking for best practices in terms of aggregation.

Cheers,

excellent analysis

i've done both 1 and 2

1 works with senior management support, otherwise it's a disaster

2 only works if you can get the users to rewrite their old queries to take advantage of the aggregates you've built -- or is this parsing something you plan to do on the fly during query execution?

Thanks for your feedback,

I understand that I'm not lost.

I heard that it is possible to dynamicaly build the aggregate during execution but seems to be weird for me.

any insight on this ?

To find out what users need, I'd rather communicate with them directly than merely watch what SQL statements they submit.