3

I have a situation here that I have not faced before.

I have multiple instances of the same ERP system, differing by satellite locale. Each locale is assigned their own ID.

Within each satellite location, the DB schema is the same as the others, same tables, same values.

When combining the tables, lets say Parts, from two or more of these locales, their Natural Operational Keys will be the same, but the additional attribute data may differ. And as I need to be able to link to a part, based upon which satellite locale it came from, I am thinking I need a composite key here - Part ID, and Satellite ID.

Now this would be fine for this single dimension, however, this Satellite ID is used in the same manner elsewhere in many other dimensions. It is also the prime slicer for many fact tables.

How should I treat this attribute? Put it in its own dimension, and snowflake? Or push the value into each dimension (duplication), but then have the fact table hold the sole FK to the Satellite Dimension?

Bhavesh Odedra
  • 10,990
  • 12
  • 33
  • 58
Srixon
  • 31
  • 1
  • can you clarify what does the "the additional attribute data may differ" is supposed to mean. And make sure you complement with one example (that captures the essence of the problem and exceptions). – SQL.injection Jan 29 '15 at 15:30
  • The Satellite ID will be different, but the Natural key will be the same. Other attribute data such as description, Unit of Measure, and perhaps Commodity code can be different as each Satellite locale treats the part slightly different then the others. Hope this clarifies. – Srixon Jan 30 '15 at 17:34

1 Answers1

2

In an ideal world the solution would be to replace during the ETL process the Natural Operational Keys with surrogate keys that will be unique for each PartID/SatelliteID (and for every dimension which is in the same situation, for example I do suspect that for the Time dimension you can skip a surrogate key).

Of course this will require to add this surrogate key not just in the dimension tables but also for the fact tables.

The Satellite ID column will also be present as a separate dimension if you need to report by Satellite.

This will be an ideal solution.

A quick and dirty solution could be as you suggested to add an additional column with the Satellite ID beside the natural keys, you need to add this column for each dimension (again except the Time dimension) and fact table. Then you need to include the Satellite ID column every time you have a join.

In this case in your reporting tool you need to include the Satellite ID as part of a composite ID, formed by the Natural Operational Key and the Satellite ID.

Also you can create a specific Satellite Dimension that you can use to select data for a specific Satellite.

mucio
  • 7,014
  • 1
  • 21
  • 33