"Connecting" two dimensions?

[#1]

Hi!

Consider this datamodel:

The two dimensions Executing and Responsible is the same tabels, but connected to different fact. In a dasboard I use the Responsible Department as a listboxes for the end user, but I also want to use the Executing Dep dimension and connect the selections fra Resp Dep to also select vaules from Exe Dep table.

Can I do this?

Best regards

Trond E

[Gysbert_Wassenaar]

You could try creating a link table that links a department table to the transactions. That part of the datamodel would look something like this:

You will need a %TransactionKey field that uniquely identifies the transactions. All your departments will exist in only the Departments table. You can then drop the department fields from your Transactions table. You can now select a department and get all the transactions where the department is involved in some role. You can use the DepartmentRole field to filter on the role your interested in.

You can create the link table like this:

TransactionDepartmentLink:

load

    %TransactionKey

    ,%ExecutingDepKey as %DepartmentKey

    ,'Executing' as  DepartmentRole

from TransactionTable;

concatenate(TransactionDepartmentLink)

load

    %TransactionKey

    ,%ResponsibleDepKey as %DepartmentKey

    ,'Responsible' as  DepartmentRole

from TransactionTable;   

You might want to store your transactions table into a qvd and use that to load the TransactionDepartmentLink table. That's probably faster than using the original source or a resident load.

The disadvantage of this method is that it can bloat your document size. It will have to create a table with twice the number of records of your transactions table if there are two departments involved in every transaction (or one in two roles).

An alternative is to use your current data model and use field triggers that execute Select in Field actions so that a selection in one department field also makes a selection in the other department field. The disadvantage of that this can be complicated to set up if you want to have a choice over whether or not a selection causes a selection in the other field.

[#3]

But of course

Thanks a lot! I just didn't see that sollution

Best regards

Trond E