December 13, 2010 at 1:46 pm
being new to DW I believe I am suppose to to create duplicate Dim table....and then each one belong to its own Fact table, this is for browsing?
so this would be a total of 4 tables? (of course I am going to have many more Dim tables)
example:
Schema(POLICY)
Fact ----POLICY.policy snapshot.(attrb1,attrib2,ect)
Dem -- POLICY.Risk(policy_key,risk_grade)
example:
Schema(CLAIMS)
Fact ---CLAIMS.policy snapshot.(attrb1,attrib2,ect)
Dem -- CLAIMS.Risk(policy_key,risk_grade)
THANKS....I am Kimballs sec book now...so I am still geting up to speed
December 13, 2010 at 3:43 pm
You can use the same dimension for multiple fact tables. These are referred to as conformed dimensions. In fact, this method is preferred versus recreating the same dimension for each fact table. This is a huge advantage for developers and business alike. Once you have your standard set of dimensions, it is much easier to roll out new data marts as needed by business.
I have one dimension at a client right now that is reused in over 20 different fact tables. I'm sure there are some companies with even more.
December 13, 2010 at 3:53 pm
its seems like the conformed dimensions. is much simpler on the developer, but no much on the end user? I am thinking more on the lines of accounting or sales mang. seems like the have an easier time if I broke it up into there own schema......although lots more work for me....
Thanks again
December 14, 2010 at 5:49 am
456789psw (12/13/2010)
its seems like the conformed dimensions. is much simpler on the developer, but no much on the end user? I am thinking more on the lines of accounting or sales mang. seems like the have an easier time if I broke it up into there own schema......although lots more work for me...
Not sure what's the conundrum here.
"Own schema?" are you creating each datamart in a different schema?
If this is the case just add a CONFORMED_DIM or SHARED_DIM schema and everybody will be happy - duplicating a table rarely is a good idea.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 14, 2010 at 7:01 am
Thanks again....like that idea
December 14, 2010 at 7:14 am
I don't see how creating a dimension for each separate schema would be more difficult on the developer. In fact, if designed and developed correctly, it is something that you should only have to commit time to once. If anything, it should be easier.
Plus, if you decide you want additional data marts with additinal fact tables later on down the roadmap, then you already have your historical information captured if it is a type two dimension and will make your historical loads much easier since you won't have to rerun that dimension again...which could take time.
Not sure how it would be more difficult for the end user, but if you are worried about that you could always setup security for different roles to only show them the fields they need or want to see.
December 15, 2010 at 1:43 am
Yes, sure you can reuse your many times for several facts.
Examples :
- Date dimension for FactInvoices (invoice date, ship date), FactSubscriptions (subscription date)
- Product dimension for FactSales (sold product), FactPurchases (purchased product)
- Employee dimension for FactSales (sales person), FactProductionOrders (approval person)
- .... etcetera ...
Many more examples are thinkable.
Advantages of this approach :
- 1 table designed in the relational database : one time development effort, only disk space needed for 1 table
- indexes only on 1 table : less disk space needed
- one time development effort for setting up the properties for your dimension attributes (key, name, order by, attributehierarchy visible, ... )
- one time development effort for defining attribute relationships
- one time development effort for defining drill down user hierarchies
- 1 dimension to be processed in the SSAS solution
How to :
- For each time you want to reuse your dimension, you define a relationship in the data source view from your fact table to your dimension.
- In the cube designer add your cube dimension and give it an appropriate name.
Best regards,
Franky Leeuwerck
Franky L.
December 16, 2010 at 4:22 am
its seems like the conformed dimensions. is much simpler on the developer, but no much on the end user? I am thinking more on the lines of accounting or sales mang. seems like the have an easier time if I broke it up into there own schema......although lots more work for me....
I have found the opposite to be the case with things that aren't obviously conformed, such as dates. Conformed dimensions are much harder on me because I have to take discrepencies in the use of the same or similar data in different departments and solve them (hence making them conformed). If I had simply but the information in different tables, that would have been easy.
There should normally be some business input into the conforming process.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply