I have a fact table holding keys of a field, observation, and date.
The fact grain is one row per field value.
The field dimensions holds information on the field itself (type, system name, label...). Usually the type should be number only as the fact values should be numeric and continuous. Thus, all free-text fields or fields holding specific range of values are neglected and removed from it.
The observation dimension is a combination of fields and dates (imagine it as a row in an excel).
And the date dimension is holding the date of this observation.
Each row coming with data has some textual indicators. Depending on this tip from Kimball group website, the best way to do it is with a Junk dimension. And in this tip from the same site, they are proposing two ways of building this "junk drawer" with either a pre-built, or on the fly.
This is not the problem. What concerning us is that we cannot join this dimension to the fact, and also NOT to the observation dimension, for few of reasons:
- These textual fields are not related to other numeric facts, thus, when adding an FK for it into the fact table, we are imposing a relation between the numeric field and the text field.
- Although connecting the junk dimension into the observation dimensions sounds like a good way to end up all the hassle, we'll be snowflaking the design.
- We cannot treat it as degenerate dimension as this will increase the size of the observation dimension. In other meaning, if there is 3 textual fields in an observation, this will lead to 3 rows in it.
Any idea for a workaround?
UPDATE
The only possible solution is to set a factless
fact table to join the junk dimension too. We would appreciate if there other ideas, we don't mind to update later.