2 Replies Latest reply: Mar 1, 2013 5:27 PM by VidyaS RSS

    Circular Joins in OBIA

    VidyaS
      Hello Gurus,

      I have created a logical model where I am using conformed dimensions for reporting on multiple facts withing that Business Model.

      So lets say I have two dimensions Dim - Work Order and Dim - Company and I have two facts Fact - GL and Fact - SLA

      Fact GL is joined to Dim - Work Order and Dim - Company also does the Fact SLA in both physical and logical.

      I have created two Presentation subject areas and called it GL Analytics and SLA Analytics and put my respective GL and SLA facts and thier dims.

      When I try to pull the columns only from by dimensions Work Order Number from Dim- Work Order and Company Name from Dim - Comapany, even though these are joined to Fact -SLA table OBIEE is issuing qeury to Fact - GL instead. So I can sense a circular join problem here. I can see only results from one Company Name but not Work order number in the analysis.

      However in the BI Apps OOTB data model. I consider AP - Posted Transactions , AR - Posted Transactions with Dim - GL Account(Segment 1) and Dim - GL Company ( semgment 2) they to have a similar join. However when I query only dims Account number and Company name I get results for both and the query send joins to correct table W_AP_XACT_F and joins these dim tables.

      What am I doing wrong here.

      could someone please help me out.

      Thanks in advance.

      VidyaS
        • 1. Re: Circular Joins in OBIA
          Benoît
          Hi VidyaS,

          In the properties of your presentation subject area, you could specify an implicit fact column:

          For each subject area in the Presentation layer, you can set an implicit fact column. The implicit fact column is added to a query when it contains columns from two or more dimension tables and no measures. The column is not visible in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives or contexts.

          Hope it helps,

          Benoit
          • 2. Re: Circular Joins in OBIA
            VidyaS
            Perfect.! Thanks Benoît I can now tell BI Server to which fact to pick from.

            Appreciate your help greatly.

            Regards,
            VidyaS