September 13, 2011 at 4:05 am
Hello,
I need to create a status dimension. In the source system each job has a status which is a text column selected from a drop down on the front end. As there is no status table in source my dimension will be a distinct of the status column from the job table.
The tradeoff is I do not have a business key to work with - sure I can derive one but when it comes to the surrogate key look up for the fact I have to match on the business key. I don't really want to be matching on text fields! The only other option is use the same derive rules in a view which provides the fact data to the ETL and match on this.
What is the best way to handle this? Is there a tried and tested approach anyone uses?
September 13, 2011 at 10:41 am
aaa-322853 (9/13/2011)
Hello,I need to create a status dimension. In the source system each job has a status which is a text column selected from a drop down on the front end. As there is no status table in source my dimension will be a distinct of the status column from the job table.
The tradeoff is I do not have a business key to work with - sure I can derive one but when it comes to the surrogate key look up for the fact I have to match on the business key. I don't really want to be matching on text fields! The only other option is use the same derive rules in a view which provides the fact data to the ETL and match on this.
What is the best way to handle this? Is there a tried and tested approach anyone uses?
Hi,
It is not uncommon to have a scenario like this. I agree with your approach do have a Status dimension where the only attribute (Status Name) is the "business key", simply because there is no other business key available.
Some people might want to treat the status as a degenerate dimension, and therefore include the status in the fact table itself (http://en.wikipedia.org/wiki/Degenerate_dimension).
In the case of a status though, I would also prefer to have a dimension because you are likely to have only a few distinct values...unless the status is a user-defined attribute but it does't look like that from your description.
Hope this helps.
Martin.
September 20, 2011 at 8:08 am
If there is a 'drop down on the front end' where is the data for that coming from? why not use the same source, you'll save on maintenance and build time. Hang on, dont tell me, the drop down box options are hard coded on the front end?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply