0 Replies Latest reply: Jan 31, 2013 4:24 AM by 988297 RSS

    Query on OBIEE 11g Lookup feature

    988297
      Hi All,

      I am trying to create a dense lookup.

      Fact table has data at 4 levels. [Grain]

      Plant,OU,BU,BS

      Plant will have data for ou, bu and bs
      OU will have data for BU and BS and like wise for BU and BS. All higher level hierarchies are populated.

      All these are id fields.

      I want to populate description for these from org master.

      I would not be able to join org master as plant id is the lowest level there and that is the only way I could join. Else, I would have to create 4 dimensions, if that is an option. Instead I thought of going for lookup feature in 11g. Sparse as I wanted to populate a base value as 'desc not found' in case desc unavailable.

      In answers, I created prompt for these 4 hierarchies and added these descriptions to the report. When I filter it with valid values it works. When I choose, desc not found it brings me all the valid records with 'desc not found'.

      For example, for customer 123, there are 2 entries in fact.

      Fact:
      Customer plant ou bu bs grain
      ------------------------------------------------------------------------------------
      123 x y z a plant
      ------------------------------------------------------------------------------------
      1234 x1 y1 z1 b plant
      ------------------------------------------------------------------------------------

      Dim:

      Plant_id plant_desc ou_id ou_desc bu_id bu_desc bs_id bs_desc
      ------------------------------------------------------------------------------------------------------------------------
      x alpha y beta z gamma a theeta
      ------------------------------------------------------------------------------------------------------------------------
      x1 alpha1 y1 null z1 gamma1 a1 theeta1
      ------------------------------------------------------------------------------------------------------------------------

      When I run the report, I expect to get 'ou desc not found' for x1 level for ou desc and it works as expected. It returns all valid records along with 'ou desc not found'.

      But, as I am using this lookup fields in prompts too, it gets populated with 'ou desc not found' for OU prompt, 'plant desc not found' for plant prompt and so on.

      When I choose this, i expect to get only the record for customer 1234 which points to OU y1. But it populates the report like this,

      With no prompts:

      Customer ou_desc
      ----------------------------
      123 beta
      ----------------------------
      1234 ou desc not found
      -----------------------------

      With prompt on 123 as beta:

      Customer ou_desc
      ----------------------------
      123 beta
      -------------------------------

      With prompt as 'ou desc not found'

      Customer ou_desc
      -------------------------------------------------
      123 ou desc not found
      -------------------------------------------------
      1234 ou desc not found
      --------------------------------------------------

      Could you please help me understand this behaviour.

      Also, why is 'OU Desc not found' getting populated in prompt value when there the dimension has all descriptions for all ids.

      Plant_id plant_desc ou_id ou_desc bu_id bu_desc bs_id bs_desc
      ------------------------------------------------------------------------------------------------------------------------
      x alpha y beta z gamma a theeta
      ------------------------------------------------------------------------------------------------------------------------
      x1 alpha1 y1 beta1l z1 gamma1 a1 theeta1
      ------------------------------------------------------------------------------------------------------------------------

      Edited by: user9128220 on Jan 31, 2013 2:23 AM