Understanding Materialized Views as aggregate tables

View previous topic View next topic Go down

Understanding Materialized Views as aggregate tables

Post  kclark on Tue Dec 07, 2010 11:53 am

Hello I have to provide a count and I am using a materialized view in sql 2008 as an aggregate table. My issue is understanding how the count will work. I need a count by 3 different categories (Product, Type, and TypeName). So....should that field be just count(OrderID) and the cube will handle the HOW? Like the count of orders per product, or the count of orders per type, etc.

Thanks for any help

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Understanding Materialized Views as aggregate tables

Post  hang on Thu Dec 30, 2010 6:24 pm

You mean indexed view, that's what Microsoft call materialized view. Personally I don't find the feature as useful as Oracle's materialized view. If you use cube, the cube will do materialization for you anyway.

However, if you do need a base level aggregations for some reason, you can always load the physical aggregate tables in nightly ETL. The only thing you need to remember is that any higher level aggregates are based on SUM not COUNT. So just using cube's default aggregate behaviour (sum) will give you correct counts along any hierarchies configured in your cube.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Understanding Materialized Views as aggregate tables

Post  kclark on Fri Jan 07, 2011 6:26 pm

Thanks! My main issue was the tool I was using to build the cube has a limitation on the count so I needed to have the count first and then use the sum aggregation in the tool. Thanks for the info!

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Understanding Materialized Views as aggregate tables

Post  hang on Fri Jan 07, 2011 9:54 pm

It's not the limitation of the cube, it's the standard behaviour of the count function in ANSI SQL as it is not additive. Kimball suggested to include an additive measure 1 into the base fact table, so that you can consistently use sum across all the hierarchy levels.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Understanding Materialized Views as aggregate tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum