April 1, 2014 at 2:22 pm
Hello,
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.
Thank you in advance.
m
April 1, 2014 at 2:36 pm
How many measures do you have?
Do they all measure the same business process and do they all have the same grain?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 1, 2014 at 3:06 pm
Hello,
It will be 50 to start, but will grow. They are all a part of the same process and grain.
thank you,
m
April 1, 2014 at 3:23 pm
Are they all basic measures, or are some measures derived from other measures?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 10:18 am
Hello,
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.
thank you in advance.
April 4, 2014 at 12:18 am
If you have too many measures, you can exclude the derived measures unless they are really complex to calculate.
If they are easy to calculate, the front-end can do it.
Not sure what you mean by buckets though.
It also seems strange that you have measures based on dimensions. Can you give an example? I would think those should belong in a seperate fact table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 4, 2014 at 10:16 am
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.
April 4, 2014 at 2:30 pm
Hello,
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.
Thank you
April 5, 2014 at 3:09 am
sqlvogel (4/4/2014)
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply