Uploaded image for project: 'Pentaho Metadata'
  1. Pentaho Metadata
  2. PMD-794

Serious flaw in choosing correct relationships from metadata layer both in BI Server and Metadata Designer and also Report Designer

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Fixed
    • Affects Version/s: 4.0.0 GA (4.0.0 GA Suite Release)
    • Component/s: None
    • 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.
    • Browser:
      Mozilla Firefox 5.0.x
    • Operating System/s:
      Mac OSX

      Description

      We have a quite elaborate semantic layer that holds about 50 tables and views. The design is a star-schema form with shared dimensions (like customers (contact in dutch), organisation (organisatie) and (products (artikel)). The facts are all connected to these shared dimensions.

      Somewhere between BI portal version 3.8 , 3.9 and 4.0, the Metadata Editor, Report Designer and the BI Server starts building up query paths the wrong way! In version 3.8, using the metadata layer, it always took the shortest path from dimension to fact tables. We exported the working metadata layer from the ME V3.8.0 to ME V3.8.1 and suddenly it began building query paths through other facts that had nothing to do with the tables that it should have been using. This gives totally wrong results!

      A query like (in Metadata Editor V3.8.0)

      SELECT
      Organisatie.FirmaNaam AS COL0
      ,Contacten.Nummer AS COL1
      ,Aankoop_Facturen_Algemeen.Nummer AS COL2
      ,Aankoop_Factuur_Datum.KalenderJaar AS COL3
      ,SUM(Aankoop_Factuur_Meetwaarden.Gewicht) AS COL4
      FROM
      v_d_aankoop_factuur Aankoop_Facturen_Algemeen
      ,d_tijd Aankoop_Factuur_Datum
      ,v_f_aankoop_factuur Aankoop_Factuur_Meetwaarden
      ,d_contact Contacten
      ,d_vestiging Organisatie
      WHERE
      ( Aankoop_Facturen_Algemeen.TK_Aankoop = Aankoop_Factuur_Meetwaarden.FK_Aankoop )
      AND ( Aankoop_Factuur_Datum.TK_Tijd = Aankoop_Factuur_Meetwaarden.FK_Datum_Document )
      AND ( Contacten.TK_Contact = Aankoop_Factuur_Meetwaarden.FK_Contact_Leverancier )
      AND ( Organisatie.TK_Vestiging = Aankoop_Factuur_Meetwaarden.FK_Vestiging )
      GROUP BY
      Organisatie.FirmaNaam
      ,Contacten.Nummer
      ,Aankoop_Facturen_Algemeen.Nummer
      ,Aankoop_Factuur_Datum.KalenderJaar

      becomes in V3.8.1:

      SELECT
      Organisatie.FirmaNaam AS COL0
      ,Contacten.Nummer AS COL1
      ,Aankoop_Leveringen_Algemeen.Nummer AS COL2
      ,Aankoop_Levering_Datum.KalenderMaandNaam AS COL3
      ,SUM(Aankoop_Levering_Meetwaarden.Gewicht) AS COL4
      FROM
      v_f_aankoop_factuur Aankoop_Factuur_Meetwaarden
      ,d_tijd Aankoop_Levering_Datum
      ,v_f_aankoop_levering Aankoop_Levering_Meetwaarden
      ,v_d_aankoop_levering Aankoop_Leveringen_Algemeen
      ,d_contact Contacten
      ,d_vestiging Organisatie
      WHERE
      ( Aankoop_Leveringen_Algemeen.TK_Aankoop = Aankoop_Levering_Meetwaarden.FK_Aankoop )
      AND ( Aankoop_Levering_Datum.TK_Tijd = Aankoop_Levering_Meetwaarden.FK_Datum_Document )
      AND ( Organisatie.TK_Vestiging = Aankoop_Levering_Meetwaarden.FK_Vestiging )
      AND ( Organisatie.TK_Vestiging = Aankoop_Factuur_Meetwaarden.FK_Vestiging )
      AND ( Contacten.TK_Contact = Aankoop_Factuur_Meetwaarden.FK_Contact_Leverancier )
      GROUP BY
      Organisatie.FirmaNaam
      ,Contacten.Nummer
      ,Aankoop_Leveringen_Algemeen.Nummer
      ,Aankoop_Levering_Datum.KalenderMaandNaam

      NOTE THAT IN THIS SQL, IT STARTS USING THE "v_f_aankoop_factuur" (=purchase invoices), WHEREAS THE QUERY SHOULD ONLY BE USING FACT TABLE "v_f_aankoop_levering" (=purchase deliveries).

      Meaning, it starts using other fact tables to get to the shared dimensions, even though the appropriate facts tables have direct links to these shared dimensions!

      In attachment the metadata layer

        Attachments

        1. CannotDuplicate4_1_CLASSIC_Issue.pdf
          47 kB
        2. CannotDuplicateWithPath_Error_xmi.pdf
          58 kB
        3. dupeit.zip
          11 kB
        4. ME_rel_problem.xmi
          1.28 MB
        5. metadata_19092011.xmi
          3.27 MB
        6. Metadata_Path_Error_20092011.docx
          495 kB
        7. Metadata_Problem.png
          Metadata_Problem.png
          427 kB
        8. path_error.xmi
          2.32 MB
        9. simplified_example.xmi
          2.38 MB

          Issue Links

            Activity

              People

              Assignee:
              gdavid Golda Thomas
              Reporter:
              stefan.leunis@bluebay.be Stefan Leunis
              Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: