BusinessObjects Board

Two contexts -user wants data in one table

building semantic layer
#1

Hi,

Objects coming from two different contexts are producing two different tables in reports. Specifically, since some objects are not common, a master detail report is being produced with two tables side by side related by the master.

The user wants to see all the columns some 70 or so(can you believe this!!) in one table. So what can be done to make data appear in one table even though they come from two different contexts?

Any suggestions?

Thanks,
ajq

anushajq (BOB member since 2005-02-22)

Passing on the power of contexts / design question
#2

You have to make sure that the data in both contexts is being queried at the same level of granularity. In other words, ALL dimensions must be common to both contexts.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#3

You should look at the SQL code they are getting. If the queries are "Join"ed , then you’re good to go. If they are "Synchronize"ed, you’re in trouble.

What product are you using? WebI 6.1 cannot handle multi context queries on it’s own.

Steve Krandel :us: (BOB member since 2002-06-25)

#4

I am using Deski.
There are two separate SQL being produced i.e they are synchronized- which I suppose means that there are coming from two different contexts. Does that mean that I cannot have data appear in one table?
What does “in trouble” mean?

This is a problem I have been facing off and on not being able to get data in one table. What do I do at the Designer level to make data appear in one table if data comes from two different contexts and not all dimensions are common between the two contexts?

Also, if data comes from two different contexts , is it always true that two separate SQL will be produced i.e synchronization will take place?

Also, if Webi 6.1 does not handle multicontext queries on its own, what do you have to do, for Webi?

anushajq (BOB member since 2005-02-22)

#5

You can have all data appear in one table, if you can get both queries to the same granularity. In other words, if context contains Year, Month and Week, and the other context only contains Year and Month, then you can’t get week into the same table with Year and Month, since Week will not appear in bith SQL statements. You need to have all dimension objects common to both SQL statements.

However, if you are sure that you willnot get inaccurate results by combining all objects into a single SQL statement, you can go into the universe parameters, SQL tab, and uncheck “Multiple SQL statements for each context.” This will allow you to get all data into a single table. Check the results carefully, as they may not be accurate.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

#6

If both queries are built with the same universe, you can use a UNION within the query pannel.

You will have to add “dummy” columns in the query that has less result columns. These columns have to be of the same type as the other query. The object names in the report will be those of the first query, but the data of the second query will be added in the cube.

I suggest that you add some column that identifies both queries. This way you can use that to show/hide the dummy values in your report.

Just ask if this is not clear enough.

Greetz

Andy

andy_v :belgium: (BOB member since 2006-02-21)

#7

Union would be a good idea. I just needed one clarification,though. How do you add “dummy columns” ? Do you do it in Designer? What would the dummy object’s definition contain in Designer? Could it contain “xyz” for a string constant or 123 for a number… but then in union these values will be picked up …so how would it work?
“I suggest that you add some column that identifies both queries.” What does this statement mean?

let me know asap :x

anushajq (BOB member since 2005-02-22)

#8

This means adding an object to the query (we call it Index1 or Index2) that is a constant (1 or 2) This can be used to identify which part of the query (main or union) that the data came from.

As for the other part, it depends on what you are trying to show in the report. If the numbers are amounts, you’re dummy objects should be 0. That way when things are added up your totals won’t be affected. For dimensions, you should hardcode your dummy objects to be equal to real data points. Then it all rolls up and nothing looks funny.

Steve Krandel :us: (BOB member since 2002-06-25)

#9

For example I have orders from different companies that appear in the same report, say France and Germany.
Now if I just show order number and value the user couldn’t differentiate between France and Germany, ne c’est pas?

So I put a “fixed” field in both facts that just reads “Germany” for German orders and “France” for French orders. Both fields are called simply .
Now bringing this simple field into the report allows the report to differentiate between German and French orders.

KMB :uk: (BOB member since 2004-02-11)

#10

In fact, make the SQL in the object Sum(0), so the object doesn’t accidently end up in the Group By.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)