Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Understanding Materialized Views as aggregate tables

2 posters

Go down

Understanding Materialized Views as aggregate tables Empty Understanding Materialized Views as aggregate tables

Post  kclark 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

Back to top Go down

Understanding Materialized Views as aggregate tables Empty Re: Understanding Materialized Views as aggregate tables

Post  hang 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

Back to top Go down

Understanding Materialized Views as aggregate tables Empty Re: Understanding Materialized Views as aggregate tables

Post  kclark 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

Back to top Go down

Understanding Materialized Views as aggregate tables Empty Re: Understanding Materialized Views as aggregate tables

Post  hang 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

Back to top Go down

Understanding Materialized Views as aggregate tables Empty Re: Understanding Materialized Views as aggregate tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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