[arcondo]

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,

[r937]

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

[arcondo]

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,

[r937]

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?

[arcondo]

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 ?

[blindman]

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