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

Query the BI repository itself to return a repository variable?

Received Response
22
Views
4
Comments
jepva
jepva Rank 5 - Community Champion
edited Aug 13, 2024 6:16PM in Oracle Analytics Cloud and Server

I would like to use ODBC to connect to the BI server itself and build a query against a subject area, to create a repository variable.  It looks like under session variables > initialization blocks, you have option to select "OBI EE Server" as the data source, but it expects some weird SQL syntax or naming as no subject area query I put there will work.

I also tried creating an ODBC connection to the BI server, and then import that as a data source into physical layer.  It doesn't seem to recognize the different subject areas, just the tables, and it also expects some weird dialect of SQL here, as no SQL from an analysis in presentation server will work.

Is there another way to accomplish this? There is also very little documentation on this.  Any help/advice would be appreciated.

Thanks

Answers

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

    "Weird dialect" meaning OBI logical SQL which is spoken against the BI Server. It is totally doable to access the BI server through the BI server's ODBC interface.

    Just one question: Why do you want to go through the subject areas to build your query?

    And yes you won't find much on this since it's pretty nifty stuff that you have to understand and get right. But don't worry we have several weirdos in here who do this (nudge @rmoff)

  • jepva
    jepva Rank 5 - Community Champion

    It's not the normal logical SQL..if you take the SQL from the presentation layer that it is sending to the BI server, this SQL doesn't work. 

  • Andrew Fomin.
    Andrew Fomin. Rank 6 - Analytics Lead

    I can't imagine why would you need this. But I checked it, and it works.

    For 11g syntax is:

    select "Field name" from "Table name"

    eg: select "Calendar Date" from "Time"

    For 12c syntax is:

    select "Field name" from "Subject Area"."Table name"

    eg: select "Calendar Date" from "Sample Sales Lite"."Time"

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

    +1 that's it