Different DB generate two segment SQL - Page 2 — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Different DB generate two segment SQL

Received Response
111
Views
18
Comments
2»

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Good place to start learning how an OBI query gets created and works

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    OBIEE performance suffers with cross-database joins.  Your best bet is to federate the one database into the other and have a single source.

  • User472204-OC
    User472204-OC Rank 5 - Community Champion

    Hi Gianni,

    Could you please tell me how to do it by step? Thanks very much

  • Gianni Ceresa
    Gianni Ceresa Mod

    As said it's ages since I had a SQL Server in front of me and I also don't have any available to test.

    So can't really give you detailed steps. Oracle manage it quite simply as the first object underneath the database itself is the schema, so adding multiple schemas is really easy.

    I really hope SQL Server can also accept that (but being defined as ODBC make me think maybe it doesn't).

    Worst case you have the option to manage that on the DB side: does SQL Server support synonyms, alias or something having the same role?

  • Joel
    Joel Rank 8 - Analytics Strategist

    SQL Server does support synonyms. As I mentioned earlier this is probably your easiest way of getting the BI Server to generate a single physical query and to hopefully make use of your indexes.

    Sent from my iPhone

  • User472204-OC
    User472204-OC Rank 5 - Community Champion

    Hi Joel,

    Thanks for reminding me.
    After I create the synonyms with the following code in SQL SERVER, and I can see the related synonyms tables in SQL SERVER, but do not see them when I import data by RPD. What happen?
    CODE :CREATE SYNONYM [dbo].[DIGL_ORD] FOR [ECP_ODS].[dbo].[DIGL_ORD]

    SYNONYM.JPG

  • Joel
    Joel Rank 8 - Analytics Strategist

    Does the user which the connection pool uses to make a DB connection have access to the synonyms?

  • User472204-OC
    User472204-OC Rank 5 - Community Champion

    Yeah， it is the same user account and password. It is very strange.

    By the way, I only write the following code for testing :Is there any missing steps?

    CREATE SYNONYM [dbo].[DIGL_ORD] FOR [ECP_ODS].[dbo].[DIGL_ORD]