4 Replies Latest reply on Apr 27, 2018 4:41 PM by Thomas Dodds

    Getting Repetitive data in OBI

    3521884

      Hello Team,

       

       

      I am facing a strange issue regarding a report.

      I have Dimension as Location, Time and a fact table F_S_SUMMARY.

      I have taken 3 dimension column, as Year_ID, COUNTRY_NAME and COMPANY_CODE and a fact column REMAINING_QUANTITY.

      Hirearchy is like Country_Name > Company_code > Plant_code > location code.

       

       

      When I creating a report with below column

      COMPANY_CODE,COUNTRY_NAME, YEAR_ID, REMAINING_QUANTITY

      Result is like below

       

      Capture-1.JPG

       

      I am getting repetitive data for company code highlighted above.

      The query is getting fired on database is

       

      WITH

      SAWITH0 AS (select T129802.COMPANY_CODE as c2,

           T129802.COUNTRY_NAME as c3,

           T129381.YEAR_ID as c4,

           sum(T210124.REMAINING_QUANTITY/1000) as c5,

           T129381.DAY_ID as c6

      from

           DIM_LOCATION T129802,

           DWD_TIME T129381 /* DWD_TIME_SNAPSHOT_DATE */ ,

           F_S_SUMMARY T210124

      where  ( T129381.DAY_ID = T210124.SNAPSHOT_DATE and T129381.YEAR_ID = '2018' and T129802.LOCATION_SID = T210124.STORAGE_LOCATION_SID )

      group by T129381.DAY_ID, T129381.YEAR_ID, T129802.COUNTRY_NAME, T129802.COMPANY_CODE),

      SAWITH1 AS (select LAST_VALUE(D1.c5 IGNORE NULLS) OVER (PARTITION BY D1.c2, D1.c4 ORDER BY D1.c2 NULLS FIRST, D1.c4 NULLS FIRST, D1.c6 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c1,

           D1.c2 as c2,

           D1.c3 as c3,

           D1.c4 as c4

      from

           SAWITH0 D1)

      select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select D1.c1 as c1,

           D1.c2 as c2,

           D1.c3 as c3,

           D1.c4 as c4,

           D1.c5 as c5

      from

           (select 0 as c1,

                     D1.c2 as c2,

                     D1.c3 as c3,

                     D1.c4 as c4,

                     D1.c1 as c5,

                     ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c4 ORDER BY D1.c2 ASC, D1.c3 ASC, D1.c4 ASC) as c6

                from

                     SAWITH1 D1

           ) D1

      where  ( D1.c6 = 1 )

      order by c4, c3, c2 ) D1 where rownum <= 5000001

       

      Can any one give me any pointer where its getting wrong.

      I have checked everything but have no clue.

       

      Thanks for your great help.

       

      Regards,

      Abhi

        • 1. Re: Getting Repetitive data in OBI
          Gianni Ceresa

          The issue is your model (and data) ...

          You have a LAST aggregation which is executed on the company code and year. But you have the same company code for various countries, which explain why the same values for a given company are used in every single country.

          I would say you have issues in your logical keys.

          1 person found this helpful
          • 2. Re: Getting Repetitive data in OBI
            3521884

            Hello Ceresa,

             

            Thank you for response, below is the attached dimension Hierarchy for dimension which is in place right now.

             

             

            so anything need to be changed here ?

             

            Thank you very much for your help.

            Much Appreciated.

            • 3. Re: Getting Repetitive data in OBI
              Gianni Ceresa

              Who knows ...

              I said it looks like your logical keys aren't correct. But you are the one knowing how they should be instead as it's your data and your model.

               

              But just from the picture I believe you have an issue as if "Company code" is the one of the first post it's clearly not unique for every country.

              • 4. Re: Getting Repetitive data in OBI
                Thomas Dodds

                A single company can be in multiple countries ... you need a distinct key for company-country ... the hierarchy keys have to be related ... currently you have no relationship (logically) set for country and company -- data just repeats at the summary level where a 'match' exists.

                 

                Use of information drives physical/logical data model design.  Knowing that companies are in multiple countries should have driven the design of not only the physical data but also the logical relationships.