Search code examples
oracleobiee

All measure columns in analyses suddenly showing null in OBIEE

Very strange thing has happened in our OBIEE. There were no modifications in rpd or in database, but every analyses that contains measure column has null values for that column. In all of them.

Here is one example that has been working fine till now. Criteria: enter image description here

Corresponding Result:

enter image description here

Checked a physical query generated for this simple analyses and it is different:

WITH SAWITH0 AS
 (select distinct T5520.CAL_DAY as c1, T3160.CODE as c2
    from DM_FILIALS_V   T3160 /* D04 Filials */,
         DM_CALENDAR_V  T5520 /* D03 Calendar */,
         DM_FACT_DATA_V T74769 /* F44 Dm Fact Data */
   where (T3160.CODE = T74769.FILIAL_CODE and T5520.CAL_DAY = T74769.PERIOD and
         T5520.CAL_DAY = TO_DATE('2021-06-11', 'YYYY-MM-DD') and
         T74769.PERIOD = TO_DATE('2021-06-11', 'YYYY-MM-DD')))
select D1.c1 as c1,
       D1.c2 as c2,
       D1.c3 as c3,
       D1.c4 as c4,
       D1.c5 as c5,
       D1.c6 as c6
  from (select D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 as c3,
               D1.c4 as c4,
               D1.c5 as c5,
               D1.c6 as c6
          from (select 0 as c1,
                       D1.c1 as c2,
                       D1.c2 as c3,
                       cast(NULL as DOUBLE PRECISION) as c4,
                       cast(NULL as DOUBLE PRECISION) as c5,
                       cast(NULL as DOUBLE PRECISION) as c6,
                       ROW_NUMBER() OVER(PARTITION BY D1.c1, D1.c2 ORDER BY D1.c1 ASC, D1.c2 ASC) as c7
                  from SAWITH0 D1) D1
         where (D1.c7 = 1)
         order by c2, c3) D1
 where rownum <= 10000000

Can anybody tell what is going on here? I tried to restart BI services from EM, but that didn't help.

Solution

  • If the underlying model isn't valid you will always run into issues. It isn't "strange" since basically you modeled something which - to the model - implied that the fact had no valid relationship with the dimension. I.e. that the fact can't be analyzed by that dimension. Think in terms of conformed and non-conformed dimensions. Yours had become a non-conformed dimension.

    Never forget that logically you model "relationships", not technical "joins".