<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Data Warehousing
»
Strategies and Ideas
»
Does it make sense store measures in a...
11 posts, Page 1 of 2
1
2
»»
Does it make sense store measures in a dimension table?
Rate Topic
Display Mode
Topic Options
Author
Message
Paul Hernández
Paul Hernández
Posted Wednesday, October 31, 2012 4:29 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 17, 2013 7:09 AM
Points: 73,
Visits: 253
Hi everybody:
Right now I´m working in a BI solution and I received a set of tables with a multidimensional structure. I have a Fact table with transaction from customers and several dimensional tables. One of them is the KUNDE or customer table, in which the typical customer information is stored, like customer key, a key to another table (contact details), customer since date, etc. I also found numeric values in this table like "amount of purchases last 12 Month" or "Total shopping days" inside this table. That makes me wonder if it makes sense or not to store this measures inside the table, moreover, I'm building another table to store the customer data as a type II SCD and I´m not able to persuade the analyst to not include measures in this table.
What is your opinion about that?
Any comment would be appreciated.
Kind Regards,
Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1379230
Koen Verbeeck
Koen Verbeeck
Posted Wednesday, October 31, 2012 8:14 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376,
Visits: 6,472
I wouldn't store those in a dimension table. These are measures (albeit calculated), not attributes. These measures are facts that you should be able to calculate from the fact table (at the top of my head, the two examples you gave are very easy to calculate with TSQL).
Furthermore, these measures are very prone to change, so that would mean that you would have to update your customer dimension too often.
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 #1379332
Paul Hernández
Paul Hernández
Posted Wednesday, October 31, 2012 8:41 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 17, 2013 7:09 AM
Points: 73,
Visits: 253
Thanks for your quick answer. That's exactly what I thought. I think aCRM guys are a little bit lazy and don't want to make joins. Anyway, there are a couple of values that we received from an external provider inside the Customer table that I cannot change. What I do is to create new tables and store aggregated measures in a new fact table and create also a new Customer table with the appropriate fields. Now I'll try the SCD Transfomation (SSIS 2008) that lets me treat some fields as type 1 and others as type 2.
Kind Regards,
Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1379360
prakashsa2709
prakashsa2709
Posted Thursday, November 01, 2012 1:23 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 5:29 AM
Points: 1,
Visits: 31
Hi Paul,
I think that storing the value " amount of purchases last 12 Month" in the dimension table is valid.Let me explain one scenario where it would be usefull.Suppose If the user has to answer the business question the top ten customers in terms of purchases for the last twelve months then in such a scenario instead of calculating those figures from the fact it would be easier to query if they are stored in the dimension table.Hope it gives a valid reason for having it in the dim table
Post #1379648
Koen Verbeeck
Koen Verbeeck
Posted Thursday, November 01, 2012 1:51 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376,
Visits: 6,472
prakashsa2709 (11/1/2012)
Hi Paul,
I think that storing the value " amount of purchases last 12 Month" in the dimension table is valid.Let me explain one scenario where it would be usefull.Suppose If the user has to answer the business question the top ten customers in terms of purchases for the last twelve months then in such a scenario instead of calculating those figures from the fact it would be easier to query if they are stored in the dimension table.Hope it gives a valid reason for having it in the dim table
So you would recalculate your entire customer dimension each month?
What if you want history on that? Duplcate your entire customer dimension each month?
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 #1380013
RonKyle
RonKyle
Posted Thursday, November 01, 2012 1:52 PM
Say Hey Kid
Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 671,
Visits: 1,509
I think that storing the value " amount of purchases last 12 Month" in the dimension table is valid.
I agree. If the information is there, it must be valuable to your business users. You should make that information available in the dimension.
Post #1380014
mmartin1
mmartin1
Posted Thursday, November 01, 2012 4:30 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192,
Visits: 640
One reason I think this is not a good practice is that the cardinality can be quite high. This can be valid to do if business regards this as important. A better solution is to build categories into the dimension, So those customers that purchased between 0 and n are type 1, etc...etc...
As long as you dont forsee these categories changing. You can even base an attribute on if a customer is one of your top ten purchasers. I dont see an issue with reprocessing the dimension often as needed.
Post #1380077
PMwar
PMwar
Posted Friday, November 02, 2012 5:53 AM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 2:26 AM
Points: 87,
Visits: 228
Given the use case ("amount of purchases last 12 Month"), I'd consider unlikely a user query like "amount of purchases last 12 Month" = 37, so the suggestion of categories seems adequate. But something like "amount of purchases last 12 Month" < 6 might be useful, and categories might mess this up
But it's always the matter of understanding the need for this information and how it'll be used.
PMWar
Post #1380299
mmartin1
mmartin1
Posted Friday, November 02, 2012 12:08 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:43 PM
Points: 192,
Visits: 640
"But something like "amount of purchases last 12 Month" < 6 might be useful, and categories might mess this up "
A good point but it still all fits. If the amount of people that bought 3,4,5 items is of reporting importance, then call just these categories cat3, cat4, cat5, etc.. As the number of purchases increase you can increase the span of each category in a mathematical fashion.
Post #1380546
Evil Kraig F
Evil Kraig F
Posted Friday, November 02, 2012 4:32 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678,
Visits: 6,126
Paul,
In a case like this I would look towards chaining fact tables. I don't know the proper term, I'm not a warehouse expert, I just build 'em.
What it would look like is something like this:
(Existing Fact Tables) -> CustomerData with only attributes (Dimension) -> FactTable storing the calculated measures for date of measure taken.
You'd have to be careful about including only the most recent fact for each customer when treating it like a continuation of the dimension, but it will both allow you to monitor changes to the measures being delivered as well as separate out the concern of modifying attributes on a dimension when indeed your measures are mobile.
From there, you can have a discussion about 'better ways to achieve the same result' with your developers.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions
|
Forum Netiquette
For index/tuning help, follow these directions.
|
Tally Tables
Twitter: @AnyWayDBA
Post #1380626
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
