using Dimension as a fact

[Former Member]

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 to know customer created date/order modified date etc..

Hope you understood my question..

Thanks,.

[Former Member]

Firstly, you need to look at why your customers don't like contexts. They are one of the best parts of universe design. How else do they expect you to avoid chasm traps, with a separate universe for each fact table? Believe me, I've heard about some poor universe designers doing this because they didn't understand contexts!

However, what you have here sounds like a candidate for aliases.

This is because you have two different uses for the staff table:

1/ Staff create orders

2/ Staff create customers

So, create one alias of staff as a_cust_create_staff (the a_ to denote that it's an alias) and one as a_order_taker_staff

Join a_cust_create_staff to customer and join a_order_taker_staff to the order fact table.

You will now need to create two different objects for the staff to reflect their two different roles.

Same applies with your dates. Create aliases for each use of date - if you use just one date table you will only get rows back where the order is created and modified on the same date that a customer is created and modified!

If you only have one fact table then you don't need to use contexts - if none are defined it assumes that all joins can be used together. If you have more than one fact and all your joins are one-to-many, then you can choose to detect contexts and accept the suggested contexts.

Regards,

Mark

[Former Member]

Thank you so much for your reply. Yes, I agree with you regarding Contexts and explaining users why they are being used. And our users are quite understanding as well..but if you look at our universe, there are quite a few scenarios like that and there will be a lot more contexts to choose from. Sometimes they will pick wrong one and scream report is not showing correct data..But if there is no other way, we would have to stick with context option. i wouldn't prefer Aliases..it's duplication of same data and any change in that dimension demands changes on all the duplicated classes..

My question was mainly on design point of view..how else one would design if given a scenario like this..on the database level..would you change the table structures so that you will not get into such situations? I personally don't see it how, though...

[Former Member]

If they're grumbling about contexts, there are only a small number of reasons. This would occur if they haven't selected a measure in their query - the inclusion of a measure in a query will automatically determine the join path in 99% of cases. Only special circumstances (or badly designed databases) mean you'd have to choose a context if a measure is selected.

If they haven't selected a measure and want a list of something then you'll need a bit of user education, or some predefined conditions that force contexts. If the same objects are covered by more than one context, then setting the SQL parameter COMPARE_CONTEXT_WITH_JOINS to Yes will help you.

It's not about whether you prefer to use aliases or not. You must use aliases when tables are used for more than one purpose. They are as important a tool as contexts when building universes. You're not duplicating data, you're creating two different uses of the same data. If staff have a different relationship to customers than they do to orders then you absolutely have to use aliases. This correct use of aliases means that there is no loop in the order fact section of your data schema.

You're not duplicating data - the data is held once at the database level. The semantic layer (universe) contains no data itself, just metadata. You have to set up two classes because they mean two different things. If you fail to embrace aliases and their correct usage, you're letting your end users down.