Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-1595

As an ETL developer, I would like a step for maintaining helper tables for multivalued dimensions

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open
    • Severity: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: Step
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      Imagine you have a fact table joined to the helper table. The helper table is joined to the dimension table, but also includes a weighting factor column (such that fact rollups are accurate). This is used to handle many-to-many relationships in the dimension.
      Summarized nicely at: http://www.dbmsmag.com/9808d05.html

      Imagine I have two transactional records:

      1,one,$100
      2,two,$500.

      I also have the following records relating site to market:

      100,1,10
      101,1,20
      102,2,10

      Site 1 is part of market 10 & 20. Site 2 is just part of market 10.
      So you can see there is a many-to-many relationship between site and market.

      Ideally, I need a fact table built that would contain the following:

      fact key, join to helper table key, $50
      fact key, join to helper table key, $50
      fact key, join to helper table key, $500

      This way the sum of the revenue is correct.

      This is where the dimensional helper table comes in. It would look like the following:

      helper key, site id, market id, weighting
      200,1,10,0.5
      201,1,20,0.5
      202,2,10,1.0

      There must be a way to build the helper table dynamically. (i.e. weights are dependent on the number of many to many relationships to market for a given site).

        Attachments

        1. 3698_helperTable.zip
          3 kB
        2. 9808d051.gif
          9808d051.gif
          30 kB
        3. 9808d052.gif
          9808d052.gif
          24 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jbleuel Jens Bleuel
            Votes:
            4 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: