Search code examples
obiee

OBIEE Join Results from 2 Analysis

I have read as much documentation as possible but still am unsure if joins between 2 separate analysis in the OBI presentation layer are possible.

The data is structured as panel data captured on the first of every month. There is a date field giving the date the data was captured, an ID field to identify records across time, and an attribute which can change (I will call it color) similar to the below example:

Date .,. ID .,. Color

1/1/2017,1,Blue

1/1/2017,2,Green

1/1/2017,3,Red

...

7/1/2017, 1, Blue

7/1/2017, 3, Orange

.

Currently I have 2 queries in OBIEE both of which subset on the date.

The first query retrieves the data as of the first of the year:

Select ID,Color as Old_Color ... where Date = 1/1/2017

The second query retrieves the data as of any other month:

Select ID,Color as New_Color ... where Date = 7/1/2017

In SQL I would use temp tables to join the queries by ID which would identify that ID 1 started the year as blue and is still blue in July. ID 2 started as green but is no longer present in July. Finally ID 3 started the year as Red but it is orange as of July.

Can similar results be achieved in the presentation layer of OBIEE?

I saw a similar question and answer to use union queries but I am looking for the data to be joined on ID opposed to appended to eachother.

Thanks in advance for your help and sorry I could not figure out how to format a nice looking table in markdown.

Solution

  • OBI doesn't work with SQL queries but with models.

    The question is: What do you actually want to do with this?