We have an older cube that I am trying to replicate and I don't know
how the former developer pulled off a certain trick.

He has a non-shared dimension that has a top level of TEAM (dim_team)
and a second level of AGENT (dim_agent). These roll up using a key in
the agent table that ties back to dim_team. Cool so far.

When he joins the dimension to the fact table these two dimension
source tables do not join with each other - but each joins
independently to the fact table. So dim_team joins the fact table
through team_id and dim_agent joins the fact table through agent_id.

When I try to replicate this the system won't let me. How did he create
this weird set up?

Hi,

In AS2K I saw this using alias. I.e., the same table/view (for 2 different
dimensions) is joined twice. However, I do not like this solution, since I
like to have shared dimensions which allows the Optimize Schema.

"kristl" wrote:

Hi Tiago -
it's actually two different tables, same dimension. The reason we need
it is that agents change teams over time so for a given date the agent
rolls up differently under different teams and this is only evident
once the date is chosen (and fact table rows are chosen).
Kristl


Tiago Rente wrote: