we are creating a new DW at our firm. I have several questions. The first one is In regards to fact tables. I am starting to build this table out and noticing that the number of measure will be extremly high. I was wondering if anyone has any ideas/recommendation on the subject.

We are interested in using a BI tool in the future. Most likely Analysis Services.

sorry I thought I responded to your question, but i just noticed i did not.

there are a total of about 60 measures. Most measures are basic. There are some that are derived from other measures and also depend on dim attribute types. There are also 10 measure attributes that are more like buckets.

mishka-723908 (4/3/2014)there are a total of about 60 measures. Most measures are basic. There are some that are derived from other measures and also depend on dim attribute types. There are also 10 measure attributes that are more like buckets.

In financial services it's not unusual to have lots of measures. I would have expected buckets almost by definition to belong in a bucketing dimension (or dimensions) rather than a measure.

When you say some measures are derived from dimensions, that sounds very like you don't have a normalized data warehouse behind this. If you had a sufficiently normalized data warehouse (or even just an ODS) you could populate your derived measures from it without having to derive them from dimension data. For any complex data warehouse solution, don't try to populate a star schema direct from sources via ETL/staging. Keeping the storage (Data Warehouse) and presentation (Data Mart) layers separate will save you and your users lots of effort and frustration in the long run.

Thank you all for the replies. Yes the current DataWarehosue is not properly normalized. We are currently working on the new design. We also did think about creating a bucket dimension, I just wanted to make sure that is the best way to do it. I appreciate your help. Any other ideas are appreciated.

mishka-723908 (4/3/2014)there are a total of about 60 measures. Most measures are basic. There are some that are derived from other measures and also depend on dim attribute types. There are also 10 measure attributes that are more like buckets.

When you say some measures are derived from dimensions, that sounds very like you don't have a normalized data warehouse behind this. If you had a sufficiently normalized data warehouse (or even just an ODS) you could populate your derived measures from it without having to derive them from dimension data. For any complex data warehouse solution, don't try to populate a star schema direct from sources via ETL/staging. Keeping the storage (Data Warehouse) and presentation (Data Mart) layers separate will save you and your users lots of effort and frustration in the long run.

Depends what the measures are. For example, # of customers is a measure derived from the dimension. It doesn't belong in the transaction fact table though, it belongs in a seperate fact table. The measure # of active customers is a derived measure from the transaction fact table (count distinct on SK_Customer). This shouldn't be stored in the fact table, but calculated in the cube or front-end application.