A loop and several outer joins. Aliases, contexts?

[SergeS]

Hi there,

Due to the fact that I have to set up my universes directly upon an OLTP-system I don't have the opportunity to set up something like a DWH adjusted to the reporting needs. So, I am encountering several problems of which the one below is an example.

Imagine there are three tables: Department, Costcenter and Project. In the source database the relationships between these table are:

Department (1:1)-(0:N) Costcenter
Department (0:1)-(0:N) FTE
Costcenter (0:1)-(0:N) FTE

To illustrate how the above should be interpreted:
A Department has 0 or more FTE's and a FTE is administrated at 0 or 1 Department.

So, we have a loop here. Some FTE are directly administrated at a Departments. If not, the FTE is administrated at a Costcenters which belongs to a Department, so the FTE is indirectly administrated at a Department.

Imagine I want to generate an overview off all FTE's for a certain Department. How should I realise this? Currently I am think of aliases or contexts, however the FTE table doesn't fullfill different roles in this example.

Can someone help me out?

Kind regards,

Serge

 

[blom0344]

Hi Serge,

I am not sure which solution will be the best here, but since I always go for a context rather than an alias I would come with something like this:

First context ("Indirect&quot:

Department - Costcenter - FTE (context consist of 2 joins)

Second context ("Direct&quot:

Department - FTE (only one join)


At report level first create query that runs through Indirect context. This is a one - to - many - to many situation , which BO docs call fan trap, but at report level only distinct combinations are shown , so i hope this gives you the desired result.
Second query should run along context "Direct" and give straightforward list of FTE's.
Link the dimension "Department" at report level and set this as a section. Show FTE fields within the section.

My experience is that if you do it this way BO will ask you to select a context each time you want to create a query and you can choose the proper one. However the same result I think could be met when you insert an alias for FTE and dump the join between Costcenter and the original FTE table. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com