HI there, this is a simple question that I should know the answer to.
I have 2 tables. One is a DATE_DIM table and one is an ORDER_FACT table. In some circumstances I want to join on ORDER__RAISED_DATE and in others on ORDER_SHIPPED_DATE. I think it’s valid to have 2 contexts and to let the user decide which is the one they want to use when the report is run.
But having set both contexts up, if I choose objects from both tables, I’m not prompted to choose a context. Webi just picks one for me.
Is there a parameter I need to set? Or something fundamental that I’ve missed.
Headline solution:
Create two aliases of DIM_DATE, one for ORDER_DATE and one for SHIP_DATE. Add both joins to the ORDER_FACT context. Then create two classes and objects accordingly (drag and drop should get you started, then edit accordingly).
Reason:
The problem you are facing is the classic dual usage of the date dimension. Adding two joins between the same table also leaves you open to the risk of only reporting on orders that were shipped the same day, massively under-reporting your orders.
By using an alias for both, you have the added bonus of being able to read the generated SQL more easily.
Mark sorry for the late reply. Thank you for responding. Yes I was going down the alias route althought there are a lot of objects to replicate. It is valid to have it as a context so that the user can deternine which date to use when the report is run. And I was just scratching my head how to do it. S even if I do go take the alias option, I’m still anoyed that I can’t get the context option to work and wondering what I need to do!!
Having multiple dates in a (fact) table joining to a date dimension is always causing discussion.
I always recommend to alias the fact table and not the date dimension. This because then you can create different measures based on the time join you are using.
In your example you would then be able to create in one user query (BO could create multiple SQL statements towards the database), that would result in: Per Date , Number of Orders Raised and Number of Orders Shipped.
If you would decided to alias the time dimension, the user needs to create 2 user queries and create merge dimensions in WebI.
With due respect @martensnl, why would you alias the fact table?
The whole point of aliases is to show that a dimension table has different uses. Most classic is the date dimension but this also applies to the customer dimension (e.g. Billing Customer v Delivery Customer)
The whole point of contexts is that the universe designer manages them so that the end user just needs to drag and drop. The intelligence is meant to be built into the semantic layer, not hoped for in the end user!
When talking about billing customer and delivery customer I would also alias that dimension. Date/Time is something else.
When aliasing the fact table you will need context, so for each fact an alias. The end user does not need to know that their are multiple context behind.
Having an alias for the date dimension then the user can’t query for example Per Date , Number of Orders Raised and Number of Orders Shipped. He/She should then create:
Order Raised Date, # Orders
Order Shipped Date, # Orders