|
Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
|
|
|
Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.
|
- Advertisement - |
|||||||||||||||||||||||||||||||||||||||||||||
rollup and cube
Posted - 2007-12-04 : 05:50:55
)one of my deliverables is an aggregated view for the underlying data (for report writers), unfortunately the aggregates are basically count distincts. in 2005 you cannot use count distinct with rollup / cube. so what i ended up playing with is a view made up of unions, doing count distincts at different levels in the hierarchy with a column to indicate at what level it is aggregated to. the added bonus of this is that if you select from the view at only only 1 level (i.e. where aggregate_column = 1 etc.) the optimiser only actually executes the select that it needs (as in only one of the selects that were union'ed)in 2008 i find that the rollup and cube stuff is completely different, much more reminiscent of Oracle actually. now i can use count distinct in the rollup, i can mix rollup and cube specifying which columns to rollup and which to cube, and others which are just a simple group by, and i can use the grouping_id to give me the equivalent of the 'aggregation level' i was deriving in the union'ed selects in the 2005 view. so.... the sql is nice and slick, just a few lines of code instead of loads of unions. put it into a view so i can start selecting from it and... OMG! the execution plan is the scariest thing i've ever seen!am i on my own here or is anyone else actually using this? any thoughts?... anyone? 
Em