use Dimension like a fact

I have a scenario:

let me create that with simple Prod/Cus/Ord tables so that everyone can understand [instead of using my business terms].

Product table:
prod id
prod name

Staff table:
staff id
staff name


Customer Table:
cust no
customer name
staff id ---> This is the staff who created this customer or assisted this customer [before placing an order] [joined to staff table by staf id]

Order Fact table:
prod id --> Joined to Products table by prod id
cust no --> Joined to customer table by cust no
staff id --> Joined to staff table by staff id
ord no
ord date
qty
amount



I am joining staff id to customer [instead of going thorugh Order fact table] as a customer is created by one staff but actual order is created by another staff. So, my clients would need to who know created the customer and who created his orders.

My question is, is this a proper design? because with this design tools are not able to understand how to formulate a query. Cognos query studio just takes which ever path it likes [not sure if there is a way to change that] and BO creates a loop and looks for Contexts [our customers don't like context as these scenarios are plenty in our database]

another eg:-
there could be dates that is common in both Customer and Order fact tables:

customer created date,modified date and order created date, order modified date. analysts would need ot know customer created date/order modified date etc..

Hope you understood my question..
Thanks,

It is a perfectly valid design. In fact, dates such as Customer Created Date shouldn't really go on the Orders fact table - it is an attribute of the customer dimension.

If you define a view over the Staff table and use it in your BO Universe to join to the Customer table, you will get rid of the loop (and hence the contexts).

Thanks Jermy for your reply.You are correct, by creating a View, loops it can be avoided. But the sample I provided above is just that, a sample. Actual Database we have, has plenty of such scenarios and if we go that route, we will end up 20 different views and hence 20 different classes at Model [or Universe] level. I want to avoid that as it'll be a maintenance headache at a later stage. Contexts are good but some people have negative thoughts about it ...so trying to see at the design level, can we do something to not get into such situation at all..

Having the FK off the customer dimension creates a snowflake that is unnecessary. You can keep the Staff FK value on the customer for reference purposes, but I would place it in the fact table with a role name to eliminate the snowflake.

As far as BO goes, yes, you need to alias the staff dimension for the second reference. Its no big deal.

We are already into snowflake schema. We started with Star but could no longer keep the same. it emerged into a snowflake now :-) Staff dimension has few child tables as well [Address => Multiple, Permissions=> Multiple etc..] so creating Alias for Staff would demand Alias for all the related tables as well..

You would not need to alias everything. Context will take care of the joins.

Given the staff dimension has two versions, S1 & S2 and you had two fact tables A & B, one uses both versions and the other only one, you would have one context with A->S1 & A->S2 with another context of B->S1.

You only need to create as many alias as there are dimension roles.