0

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:

  1. 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.
  2. 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.
  3. 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.

alim1990
  • 4,656
  • 12
  • 67
  • 130
  • I have no idea what your 3rd paragraph means (the one starting with "The field dimensions holds information") - can you update your question with some sample data that illustrates what you are talking about? If your junk dimension doesn't relate to your fact then why are you creating it in the first place? Are you saying there is a 1:M relationship between your fact/other dimensions and the junk dimension? It would be easier to visualise this if you provided some source data records and how you propose mapping them to the various fact and dimensions you have in your model – NickW Apr 13 '22 at 11:05
  • the junk is more related to a dimension, but can't merge into it as it might produce a huge dimension. that's why we decided to have a factless fact table to connect observation to junk. – alim1990 Apr 14 '22 at 06:09
  • @NickW if there is 3 text fields related to an observation, this wil produce 3 rows to the same observation key, which is not logical. however, the number can go up to 100 text values...that's why in part 3, degenerate dimension is not possible as a solution for us. it would produce a near same fact table size. – alim1990 Apr 14 '22 at 06:11

0 Answers0