Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DW Data Model - Financial services


DW Data Model - Financial services

Author
Message
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
How many measures do you have?
Do they all measure the same business process and do they all have the same grain?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
Hello,

It will be 50 to start, but will grow. They are all a part of the same process and grain.

thank you,
m
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
Are they all basic measures, or are some measures derived from other measures?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlvogel
sqlvogel
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 3706
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.
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search