1

I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables.

I am considering putting an "id" in a fact table, in order to track the source of the data that the fact was inserted from. Users may upload data in a variety of ways, I am required to support a use case where a user has made a mistake in their data upload and wishes to redact it. I am considering therefore putting job and row ids on all uploaded data and keeping the raw data uploaded (prior to ETL from a staging area). This way the users will be able to see a log of uploads, and can redact them and replace with correct data if need be.

Is it really bad to put these ids in the fact table? An alternative would be to put them in a special tracking dimension. However, this dimension will have one row per fact table row, so does it not make sense to put them in the fact table?

  • I could be approaching this entirely wrong. Another way might be that in the rare case of data needing to be redacted, it is identified by date of the data, since I have a date dimension? – user2800708 Oct 20 '15 at 14:48
1

I will typically have my business key in the fact table so I can easily track back to the source system for any questions. I usually will put a unique constraint on it to ensure the granularity of the fact table is the same as the business key.

  • Good to hear someone else is doing that. I'm trying to think what the drawbacks of doing this might be? Can't think of many, but I've come up with a different solution, which I'll put in an answer. – user2800708 Oct 22 '15 at 8:43
0

The solution I have come up with is this:

To overwrite existing data in the fact table, simply identify which data to overwrite by its date. In this particular data model, each date is allowed to have at most one fact entry.

I think this will work better than putting the business key in the fact table, as the data model evolves I will end up supporting more and more sources of data, so would potentially need business keys for all of them.

I am also going to add an 'audit' dimension as per Kimball's book.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.