I have three fact tables. First table holds current data, FACT_CUSTOMER_CURRENT. Other two tables hold historical snapshots. For example, one of these table holds last 60 days’ records- FACT_CUSTOMER_DAILY. The other table holds data for the last day of the months.-FACT_CUSTOMER_MONTHLY
I want to add a date prompt. If the user selects yesterday as a prompt value, report should bring value from first table which holds current data (FACT_CUSTOMER_CURRENT). If user enters 28.02.2014, the report should retrieve data from FACT_CUSTOMER_MONTHLY. I tried to use context and aggregate awareness, but I could not be successful.
Can you help me?
We can not use case statement in object definition.
In your example, business objects engine is putting both table1 and table2 into the query. but we want to access only one table, for considering performance.
case prompt_answer when x then tabel1.column when y then table2.column … end
Aggregate_awareness used to work perfect in such a scenario.
So what exactly did you do to setup aggregate awareness in detail?
And where/how does it fail you?
@Debbie:
I believe the CASE WHEN … SQL statement will force a join to both tables, although you are only interested in one at a time. That does impact query performance and more importantly it might affect the result set (inner joins, e.g. new customer ID might not exist in history fact table).
The stuff with making table selection based on @Prompt values will NOT work, I am afraid.
You will have to create 3 predefined universe filters: Current Data, data up to 60 days old, Data older than 60 days. And use these filter objects in you aggregate navigation (rules) to switch between your 3 involved fact tables!
Andreas, “You will have to create 3 predefined universe filters: Current Data, data up to 60 days old, Data older than 60 days. And use these filter objects in you aggregate navigation (rules) to switch between your 3 involved fact tables!”
Can you give detail about how can I use these 3 predefined universe filters in aggregate navigation.
Create 3 different objects and 3 different conditions with the following definitions (pseudo code):
obj1 = FACT_CUSTOMER_CURRENT.column
obj2 = FACT_CUSTOMER_MONTHLY.column
obj3 = FACT_CUSTOMER_DAILY.column
cond1 = @Prompt(‘Choose date’,‘D’,Mono,) = sysdate()-1
cond2 = @Prompt(‘Choose date’,‘D’,Mono,) = ‘28.02.2014’
cond3 = @Prompt(‘Choose date’,‘D’,Mono,) = sysdate()
create report with 3 queries combined with union
q1 = obj1+cond1
q2 = obj2+cond2
q3 = obj3+cond3
When you create a report an sql should look like something like this:
Select column
from
FACT_CUSTOMER_CURRENT
where
@Prompt('Choose date','D',,Mono,,) = sysdate()-1
Union
Select column
from
FACT_CUSTOMER_MONTHLY
where
@Prompt('Choose date','D',,Mono,,) = '28.02.2014'
Union
Select column
from
FACT_CUSTOMER_DAILY
where
@Prompt('Choose date','D',,Mono,,) = sysdate()
When you run it only 1 condition will be true and only 1 query will retrieve
data from the table you want.
Thank you Marfi for your solution.
The company wanted an easier solution so I could not reply so far:(
I am trying your solution. I build a combined query. Each query has one dimension and two measure objects. Two measure objects are being calculated in two different contexes. So when I try to union these two queries, I am getting the error message below:
“This combined query cannot run because one of the queries contains incompatible objects.”
Are there any solution for combining queries which involves two or more sessions?
https://bobj-board.org/t/31425
I looked at this post, as I saw it is not possible but I am not sure.
Do you have any idea?
Debbie and Andreas, thank you also for your help. I read a post of Dave ( dagira.com ) As I understand, combining queries from multiple contexts is not possible.
(BOB member since 2012-06-01)
(BOB member since 2005-03-01)
(BOB member since 2005-03-01)
(BOB member since 2002-06-20)
(BOB member since 2006-12-18)