Different DB generate two segment SQL

User472204-OC

Hi Experts，

There are two tables in SQL Server 2014 for one machine, but both of them are in different database(such as ECP_LCL and ECP_ODS).

After I configure the two ODBC and import the related tables into physical payer,then make inner join these tables.

However, it will generate the separate SQL in Analyticd.So my question is as below:

1. Why it will has the separate SQL by system automatically?

   I think it is the different database.If it is yes, so I want to know how to design the RPD to combine these code,because the  program need be execueted index and  improved performance.

2. Because it the two segment SQL, and do not see the relationship for two tables, so I am not sure whether they will be executed through index.

Gianni Ceresa

If your physical objects are related to 2 different database objects in the physical layer you will have 2 different queries and the BI Server will then merge the result because your data comes from 2 different connection pool, so OBIEE can't do anything else as 2 different databases using 2 different connection pool (even if maybe with the same connection data) are 2 separate and independent things. You can't expect OBIEE to act in a different way based on your current design.

User472204-OC

Hi Gianni,

Thanks for your kindly help.
If it will separate into 2 segment,does it execute the index in SQL ?
I cannot copy the sql into SQL Server,becuase it is not the whole SQL and I can not see the relationship between tables.
Is it the suggestion or workaround to solve this issue? Thanks.(Exculde moving one table into the same database in original system)

Gianni Ceresa

Hi,

what index? The one supposed to be used by the join between "calendar" and "dig_ord" ?

Of course not as the join is done by the BI Server, and the BI Server isn't a database first of all and it does the join based on data it got back from the database, so there isn't any index there.

The relationship between the 2 tables it's the one you defined in the physical layer of the RPD, the BI Server will use that relationship to put the 2 resultsets together.

As workaround I would say the best would be to get the 2 databases under the same connection pool in the RPD. For example with an Oracle database you create 1 database object, 1 connection pool and you can then import objects from different schemas as long as the users you use to connect can access them. Can't you do something like that by using a SQL Server connection instead of ODBC? (last time I had a SQL Server in front was ages ago ...)

Joel

As far as OBIEE's BI Server is concerned, these are 2 different data sources (that just happen to be on the same server in this instance) which could be 2 different databases on 2 different servers potentially.

The BI Server is dealing with this by issuing 2 physical SQL queries and federating the results in memory. Why do you need a single SQL query to return your data?

User472204-OC

Hi Joel,

Because I want to see the SQL execution plan and want to confirm whether the whole SQL execute the column Index for  imporving the performance

Joel

In that case, you'll need to create a synonym in your database for one of these objects. This will allow both objects to be accessible from one database and therefore one connection pool. The BI Server will then be able to generate a single SQL statement.

[Deleted User]

Back2 basics - A Day In The Life Of An Oracle Analytics Query

User472204-OC

Hi Gianni,

They are the column index in tables for improveing performance.
'dig_ord' in ECP_ODS database is fact table and I do not have right to create any tables,but 'calendar' in ECP_LCL database is dimension and I have some right to modify anything.
For some reason, DBA have to creat two database name in SQL Server, not two schema.
I think one connection can contain different shcema, but not database name, right? If two tables in different schema, it will be OK.Unfortunatly, it is not.
I only know to use ODBC to configure and  connect SQL Server and import data into physical layer. Is there any connection method? Thanks.

Gianni Ceresa

The wizard to import metadata only know a limited number of sources, but you can set many more in the database object itself.

User472204-OC

Hi Christian,

I am sorry. I do not understand what you mean.