BusinessObjects Board

Pulling objects from different contexts

building semantic layer
#1

I have 3 fact tables and using in built context creation, i have created three contexts ( all dimension table joined to the fact as one context).

Now i have 3 measures, one each in one fact table which i need to pull in front end.
The measures itself are from three different contexts and the query in the front end gives me the option of selecting only one context.

What can i do? As for a temporary measure, we have dumped all the measures in one fact table itself. Is there any better way to do this ?

praekgp (BOB member since 2013-03-18)

#2

The query shouldn’t give you a context selection prompt at all if you have chosen measures and your contexts are correctly defined.

#3

Hi Mark P,

I am using star schema. So, I am basically including all joined dimension tables to the fact as one context.

So, what changes do i need to make in the universe to bring all those three measures (in different fact tables) under one context ?

praekgp (BOB member since 2013-03-18)

#4

General rule of Thumb, one context per fact. You should then see joined SQL statements if you are using conformed dimensions in the query.

Mak 1 :uk: (BOB member since 2005-01-06)

#5

Hi Mak,

You should then see joined SQL statements if you are using conformed dimensions in the query

I am new to BOBJ. So, it will be really helpful if you guide me a little.This is what I have done till now -
1)defined cardinalities
2)integrity check - automatic alias creation - made an alias
3) detect contexts - made one context each fact table -(as it is a star schema, it detects all dimensions joined to a fact as context)
4)checked - whether all loops are covered either by alias or context -all loops covered

Now, the requirement is to pull two measures in the report, but unfortunately they reside in different fact tables.

So, what can be done to resolve this problem?

P.S. I am a newbie :oops:

praekgp (BOB member since 2013-03-18)

#6

Are you sure you needed this alias? Whats it doing?

Have you put a SQL Aggregation function around your measure?

Sum(YourTableName.YourFieldName)

If its a start schema uncheck generate multiple SQL statements for each measure on the SQL tab, under universe parameters.
If you are using Oracle set the connection parameter Join_by_SQL to Yes.

Build a simple query with conformed Dimensions and both measure.

Mak 1 :uk: (BOB member since 2005-01-06)

#7

Basically i am making an alias of date dimension. This dimension is joined to two fact tables
Date key in date dimension joined to two keys (start_date and end_date) present in both FACT TABLES

Is it correct to make two joins on the same fact table ?

AND about the measures… i have kept projection as SUM.

MULTIPLE SQL statements for each measure is UNCHECKED.

praekgp (BOB member since 2013-03-18)

#8

You would usually join one calendar to Start and one calendar to End.

Yes, but in the select part of the object have you defined this as follows:-

Sum(YourTableName.YourFieldName)

Thats the important bit 8) .

Mak 1 :uk: (BOB member since 2005-01-06)

#9

If we have to write sum again in select statement, then why do we use projections?

I have one measure with projection as average. Will it work work correctly if i write average in select statement?

praekgp (BOB member since 2013-03-18)

#10

Thats how the measure will be treated when aggregated by the Webi calc engine.

Averages are best calculated in report variables IMO, as this allows slice and dice on the report.

Mak 1 :uk: (BOB member since 2005-01-06)