using Dimension as 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).
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
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)
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 to know customer created date/order modified date etc..
Hope you understood my question..