Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DW Data Model - Financial services Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 2:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
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
Post #1557197
Posted Tuesday, April 1, 2014 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557205
Posted Tuesday, April 1, 2014 3:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Hello,

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

thank you,
m


Post #1557223
Posted Tuesday, April 1, 2014 3:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1557236
Posted Thursday, April 3, 2014 10:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
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.
Post #1558086
Posted Friday, April 4, 2014 12:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558375
Posted Friday, April 4, 2014 10:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:41 PM
Points: 448, Visits: 3,338
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.


David
Post #1558587
Posted Friday, April 4, 2014 2:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
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
Post #1558652
Posted Saturday, April 5, 2014 3:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse