Getting Repetitive data in OBI

User_B7ZTH

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

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

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.

User_B7ZTH

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.

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.

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.