BusinessObjects Board

Synchronization & Contexts Issue

building semantic layer
#1

Hi Gurus

I have an issue designing the universe and I have been searching the forum but still I don’t think I could resolve the issue.

I have an universe built with multiple fact tables and multiple conformed dimensions. I have created contexts for each fact table and also I have the option “multiple SQL statements for each context” checked in universe parameters. if i do an integrity check I dont see any errors
While creating the report, I see synchronization queries. If measures from two facts are involved in the report, I see two queries in synchronization but the problem here is the data displayed on the report is an outer join of those two queries instead of an inner join. I know there is a workaround on the report by adding filters to display the data in the desired manner.

But

Q1.Even applying a filter on the report, I see the data correctly being displayed but when summarization is used at the end of the report for Sum/ count on the measures its still displaying the data based of select statement and not the data displayed on the the report.
Q2. Is there a way I can make an inner join on the two select queries from syncronization?
Q3. If I include a third measure from a third fact table, I see 3 select queries in synchronization but the report displays “Incompatible#” & “Context#” errors.

Any thoughts?

Thanks
raj

Raj_Konig :india: (BOB member since 2007-07-19)

#2

A) Are your facts/measures defined properly, meaning with a SQL agrregate function (see Universe Designer, object properties, in the SELECT BOX)? See here for details.

B) Are you including any dimensions, which are not shared/common across all your involved fact tables?

Andreas :de: (BOB member since 2002-06-20)

#3

Mark

Thanks for the reply.

I have seen that post. But in my situation My fact tables are at the same grain. So I dont need to do the sum(). Also for your other question I do have some other dimensions that are not conformed.
Let me explain my problem in detail. If I do have report involving multiple fact I see synchronization and this how it looks:

Select1

Select col1, col2, col3, col5
from table1, table2
where col3 = 10

Select2

select col1, col2, col4
from table1, table3

Lets say for col3 = 10 we have values for col1 as ‘xyz’ but select2 has values as ‘xyz’ and ‘abc’ for col1. In this case I expect the report displays only records for ‘xyz’ and not ‘abc’. But the report displays both xyz and abc records.

thanks
raj

[Moderator Edit: Added code formatting - Andreas]

Raj_Konig :india: (BOB member since 2007-07-19)

#4

Yes, you need to do the SUM :!: Try and see for yourself, please.
Look at suggestions A) and B) I’ve made.

Also note: If you are using dimensions, that do not exist across all involved fact tables you always will get a synchronization, since one cannot merge facts of different granularities into one cube/microcube, period.

Finally: For your requirement implore the query on query feature in Webi.

… And who is Mark… :roll:

Andreas :de: (BOB member since 2002-06-20)

#5

Andreas

I didn’t see much of a difference adding SQL aggregates to the measures.

Other thing I need to do to make my report work is changing the dimension objects coming from the fact tables as detail in the report.

Also it looks query on query feature is available from 3.1 onwards and I’m still using XIR2.

And sorry abt the Mark thing. I’m not sure where I got that from. was researching hell a lot of threads to solve the issue. Might have mixed up with names.

  • Raj

Raj_Konig :india: (BOB member since 2007-07-19)

#6

Thought I’d better reply to add to the name confusion. :lol:

Andreas is right. You should have aggregates for measures.
Say you sold a million widgets in units of 1-5, you’d have between 200,000 and 1,000,000 rows returned by using

select prod_dim.product_name, sales_fact.sales_quantity, sales_fact.sales_value
from prod_dim join sales_fact on prod_dim.product_sk = sales_fact.product_sk
where prod_dim.product_name = 'Widget 1'

However, if you defined Sales Quantity as sum(sales_fact.sales_quantity) and Sales Value as sum(sales_fact.sales_value) you’d end up with the following sql generated:

select prod_dim.product_name, sum(sales_fact.sales_quantity), sum(sales_fact.sales_value)
from prod_dim join sales_fact on prod_dim.product_sk = sales_fact.product_sk
where prod_dim.product_name = 'Widget 1'
group by prod_dim.product_name

This would return one row. I know which I’d rather work with.

Now, on to #CONTEXT

Let’s say you’re working with selling, buying and renting products

Supplier would be a conformed dimension for Sales_Fact (where we sourced the product from), Order_Fact (our product buying transactions) and Rental_Fact (where we sourced our rented items from)
Product would be similarly conformed.
Sales Person would not. A sales person only carries out transactions with customers, so they are only relevant to Sales_Fact and Rental_Fact, not Order_Fact. Thus you could bring in Supplier Name, Sales Person Name, Product Name, Volume Ordered, Volume Sold, Volume Rented, Order Value, Sales Value, Rental Value in one query and it would synchronise across the three contexts. If you remove Sales Person, you would see the synchronisation change to Join because all dimensions are relevant to all dimensions. So, if you try to put Sales Person and Order Value in the same block, yep, you guessed it, they are incompatible - Sales Person is not relevant in the context of order discussion.

#7

Mark, (Hope I mentioned it right this time :yesnod:)

Thanks for the reply.

Your example is spot on.

Addition to that if the report has a filter / prompt, lets say on sales person group which could limit the products (which is conformed dimension) and supplier (which is also conformed) coming from one synchronization query but not from the others. the resulted report should display only the products for the selected salesgroup but not the other products. Which is not happening in some cases.

In this case #Context & #Incompatible Issues are about to happen.

How do you tackle this?

Thanks
Raj

Raj_Konig :india: (BOB member since 2007-07-19)