BusinessObjects Board

Joined VS Synchronized queries!

building semantic layer
#1

Hello.
I am a little confused with joined and synchronized queries. I am having many Chasm traps in my universe, which are resolved by using contexts. There are dimensions and measures coming from both Fact tables. When I run the query using dim from dim table, dim and measure from Fact 1 and measure from Fact 2, BO create two queries and are synchronised. I am getting two seperate blocks in my report.

  1. Why doesn’t it join.
  2. Is there a way I can do joining at report level?
  3. What should I do at the universe level rather than report level to get everything in one block in report, whether measures or dimensions are selected by the users from any of the Fact tables.
    I went through the threads typing “Synchronized queries”, but I am still not very much clear about the common dimension that BO uses for linking.
    Please throw some light on this synchronization issue.
    Thanks in advance.

ASP (BOB member since 2004-04-19)

how to join 2 microcubes
#2

Did you check out this thread in the Designer FAQ?

Cindy Clayton :us: (BOB member since 2002-06-11)

#3

You are selecting a dimension from Fact1, which I assume only exists in one context --> two synchronized SQL statements

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

#4

You are correct Andreas. I am pulling dim from Fact 1 which exist in only one context. So do I have to put dim–Fact 1 join in second context also to get joined queries?
Thanks

ASP (BOB member since 2004-04-19)

#5

No, then you would just re-create your chasm trap.

See also:
Tips & tricks section from Business Objects
SQL Challenges by Integra Solutions (start with page 16)

Sometimes you can force your results from a synchronized query into one block after the initial run. You just have to be careful how to interpret the data:

For example (the following is a fan trap scenario, from memory so there might be some flaws):

Order header table

Order Nbr    Customer    Amount
--------------------------------
005            Bobette     100
007            Bob          50

Order detail/line table

Order Nbr       Order Line    Product                     Nbr of Items
-----------------------------------------------------------------------
005                1          Book: My Idaho                      1
005                2          Plasma screen                      10
007                1          Book: History of the Corvette       1

Data provider
Result objects: Order Nbr (dimension), Amount (measure), Order Line (dimension), Product (dimension)

Result (with contexts to avoid a fan trap):

Order Nbr 005 (Master/Detail section)

   Table 1                           Table 2
   Amount                  Order Line   Product


Order Nbr 007 (Master/Detail section)

   Table 1                           Table 2
   Amount                  Order Line   Product

You receive two tables, because you are asking for information (Order Line and Product) for which the Amount cannot be spread across (Order Amount is not stored at the Order Detail table level).

You can force all the info into one block/table (manually re-formatted after initial run):

Table
Order Nbr     Amount    Oder Line     Product                  
---------------------------------------------------------------
005              100        1         Book: My Idaho
005              100        2         Plasma screen
007               50        1         Book: History of the Corvette
          Total: 150

If you would not use contexts at all the following would happen:

Table
Order Nbr     Amount    Oder Line     Product                 
--------------------------------------------------------------
005              100        1         Book: My Idaho
005              100        2         Plasma screen
007               50        1         Book: History of the Corvette
          Total: 250

Notice the total of 250 for Amount is incorrect (as a result of the unresolved fan trap).

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

#6

Thanks very much Andreas. The notes really helped and the example about too. I tried forcing, but I am facing a problem with forcing.
I am getting a question with Chasm Trap though.
Consider a fan trap example you mentioned
CASE 1: With reference to fan trap solution in the example, I would alias the ORDER_HEADER table and then create two different contexts, pulling measures from the alias table. So can forced into one table after initial run.
CASE 2: In my case of Chasm trap, what I am facing is I have both dimension and measure objects coming from both Fact tables and they are in different contexts. So when I try to force the measure objects into one block, it lets me do it, but when I try with dimension objects, it is giving me “Variables not compatible” error. Why it’s not allowing the dimension objects.
Thanks in advance.

ASP (BOB member since 2004-04-19)

#7

Since you are dealing with dimensions which are not common to both contexts start here (although you are not using multiple data providers, your data provider is generating multible cubes, which essentially is the same):

S. Bickerton “Solving the Mystery of Data Provider Synchronization” from the Business Objects user conference 2002 in Miami: http://www.bonymaug.com/reporter.htm or http://www.bonymaug.com/Presentations/synchofdataprov1.zip

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

#8

Thanks Andreas. The read the word document was really good. I have one question? Is it that I can do something in the universe to get joined queries, so users can select objects and get it directly. If its done in universe users would certainly prefer that.
Please throw some light onto this.
Thanks in advance.

ASP (BOB member since 2004-04-19)