Possible Dimension Snowflaking with Multiple Companies in a DW.

View previous topic View next topic Go down

Possible Dimension Snowflaking with Multiple Companies in a DW.

Post  DavidStein on Thu Aug 26, 2010 4:32 pm

Users of this OLTP system sometimes end up with multiple subsidiaries to track, each with a different database. Sometimes they share some of the same vendors, customers, etc.

However, the data is not well managed so aggregations including more than one company would be difficult because they may use different product classes, customer numbers (for the same customer), etc.

Also, most often they will not have SQL Server Enterprise so they cannot benefit from fuzzy logic.

If you will not get a consensus between the subsidiaries to conform their information - Customer Numbers, Vendor Numbers, Product Classes, Product Id's, etc. is there any value to combining their dimensions and snowflaking a company designation into each of them?

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Possible Dimension Snowflaking with Multiple Companies in a DW.

Post  ngalemmo on Fri Aug 27, 2010 5:51 pm

Yes there is value, because you would be ready when they finally decide to do it.

You don't need to snowflake, however. You just need a natural key that allows for different identification systems. I usualy define an abstract natrual key that is a string containing a concatenation of a code identifying the source system or coding method and the key used in the source system. This allows storing products, customers or whatever from different sources without fear of conflicting natural keys.

Later, when they do decide to integrate, you introduce a new natural key column that contains the common identifier (non unique in the dimension). You use this key to handle dimensional attribute updates moving forward so that dimension rows that have been identified as the same entity will be updated with the same attribute values. You can then integrate them through those values.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Possible Dimension Snowflaking with Multiple Companies in a DW.

Post  DavidStein on Sat Aug 28, 2010 12:57 pm

Thank you, I'll do that. That was something I never thought of.

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Possible Dimension Snowflaking with Multiple Companies in a DW.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum