Multi Source Universe generating multipath query and results are seperated

[Former Member]

Hi,

I have created a multisource universe  using IDT 4.0 SP4 over SQL server.   Databases that I am accessing are on two different servers (2008 and 2005) as following .

Server  Database

2005      DB1

2008      DB2

2008      DB3

I have joined  the tables across databses eg : DB1.sch.tbl.col=DB2.sch1.tbl1.col1 .

Issue is , When I am trying to generate sample repotrs by selecting objects from different DBs(DB1 and DB2) I am seeing Multipaths  query is being generated and in WEBI report that results are separated to two different tables as opposed to be combined in single table.

Can you suggest how can I able have universe generated single query path when selected objects across DBs/Sources and results in WEBI are combined in single result set /table.

Thnnk you !

Raja

[michael_melters]

When you get multpath sql whenever you have multiple measure objects coming from your two database, then it is because of an option where you can specify that multiple SQLs should be generated, because of multiple measure objects. You can find this option in the query options properties of the business layer.

[Former Member]

Hi Michael,

I have defined context and enable mentioned property as well. I am just using two dimension objects from diff tables/servers/DB.

[michael_melters]

OK, I had'nt realize that. Then of course it is very likely that your contexts are responsible for multipath sql. If the solution lies in redefining your contexts, I think we cannot help without a screenshot of those tables of your datafoundation which define the path from one object column to the other and the columns that you use in your two objects. 

[Former Member]

Hello Raja,

Could you please paste the sql generated from webi?

Thanks,

Madhumitha

[Former Member]

HI Madhumitha,

Thank you for the response. I just have selected two dimension objects from two diff tables which are off different servers/DBs. SQL generated is simple select column from table with multi-flow.with two result sets

[Former Member]

Hi Raja,

You need to ensure that the objects that will be used in a 'single' Webi report query need to be available in one 'context' and there has to be a join path between the tables they are coming from.

If, for some reason this is not possible due to design considerations then you need to put these objects in separate queries(at webi report end) and merge the 2 queries on a common dimension.

Thanks,

Prathamesh