Query the BI repository itself to return a repository variable?

Question

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

[Deleted User] · Answer

+1 that's it

Andrew Fomin. · Answer

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"

jepva · Answer

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.