data not flowing in cube for child locations. RRS feed

  • Question

  • Hi friends,

    I have a cube "MAX_Prod", that cube has a 49 tables. now i am bothered about 1 table in it. Table is "dim_Loc". It has many partitions(t01,t02..t03,YTM,MTD,historical), and partitions uses a VIEW query as-  select * from dim_loc query with where condition on "changedate".

    T01(12 AM -1 AM)- It fetches data from 12 AM to 1 AM. It is processed at 1:50 AM. 

    ex for partition query - select * from dim_loc
    Where CHANGEDATE between trunc(SYSDATE) +00/24 and (trunc(SYSDATE) +01/24)- INTERVAL '1' SECOND 
    T02(1 AM -2 AM)- It fetches data from 1 AM to 2 AM. ....so on till T23 partitions.

    Now the problem is - I have a MAX application , where the data(tech column) is there.From  this application data flows into our DB view- dim_loc query.

    now our dim_loc table, we have a master location ABC1-0001,in max application, it has many child(ABC1-0001-ab) , grandchildren(ABC1-0001-ab-00), great grant children(ABC1-0001-ab-00-1q) to it .

    we update data (tech)only for parent location, in max application,then child tech also gets updated in our dim_loc qury in oracle DB. but the "changedate" is updated only for parent location and NOT for child location. 

    example- suppose i had ABC1-0001 tech as "B" and now i chnged it to "C" in max application, then the 'tech' value changes for  both parent, child as "c" and changedate becomes 10-may-2018 for parent(ABC1-0001) but for child locations we have old date only in changedate column.   In oracle view, i get data as below after changing/updating parent location.

    location                    tech      changedate

    ABC1-0001                 c         10-may-2018 05:15:26

    ABC1-0001-ab             c         30-apr-2017  12:55:56

    ABC1-0001-ab-00       c         30-apr-2017  12:55:56

    ABC1-0001-ab-00-1q  c        28-apr-2017  12:55:56....

    now all this parent location,child locations , its updated values should flow into my cube. But in my partitions, because of the where condition(Where CHANGEDATE between trunc(SYSDATE) +05/24 and (trunc(SYSDATE) +06/24)- INTERVAL '1' SECOND), i get only parent location in my cube as it has new changedate.

    But I want my child locations also to come in cube, without changing/updating the changedate for child locations and without changing partitions query because it is business requirement. I need help to do this. note [may or may not be helpful]- well I have a table 'ANCESTOR', which ha columns 'ancestor','location'--if i write a query as select * from ancestor where ancestor='ABC1-000' it gets all children ,grand children etc.    


    Thursday, May 10, 2018 2:29 PM