Possible Dimension Snowflaking with Multiple Companies in a DW.

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?

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.

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