Understanding Materialized Views as aggregate tables

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

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.

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!

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.