How can I reuse SQL code generated in OTBI in a Data Model (DM)

Kauan_Bohn

Here’s the translation to English:

I have a question regarding the execution of SQL generated via OTBI. I would like to reuse this report but include some additional elements. I want to execute it through a Data Model report.

I saw that we can run this report through a data model using the ORACLE OBI EE method.

Given this, I would like to know: do the limitations remain the same as in OTBI, or do they increase with the Data Model?

Can I create prompts or filters?

Is it possible to better relate tables through SQL in some way?

MandeepGupta

Hi @Kauan_Bohn ,

You may create a different data set in your data model based on tables and then try to join it with data set created using OBI EE source. In the same data set, you will not be able to add columns from direct tables.

Thanks.

Kauan_Bohn

I have tried this, but it's difficult to find a FK in OTBI side.

I'm thinking if that is the best way to execute, or if i should do it by query

JohnW-Oracle

Hi @Kauan_Bohn,

To confirm, you want to access your transactional FA data from FDI?

You should try to use the FDI Subject Areas first and augment them as required.

The OTBI logical SQL should provide some clues on the joins and keys.

Regards,

John

MandeepGupta

Hi @Kauan_Bohn ,

If the LSQL from SA doesn't satisfy the requirements, I normally prefer to go via SQL using tables as that :

1. Gives me more control on filters/attributes
2. No need to create different data sets for diff data sources.

Thanks.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/23721/how-can-i-reuse-sql-code-generated-in-otbi-in-a-data-model-dm

You can use an OTBI Report as a Data Model. There are 2 ways to do that.

• Directly calling that Report.
• Use the Logical SQL of the Report in the Data Model.

If you want to overcome the OTBI Limitations of 25K (in Excel) or 75K (in CSV) data, then you can use any of the above method and it will also follow the OTBI security model.

As @MandeepGupta suggested with SQL you might have some more flexibilities like if any field is not available in OTBI you can still get the field from tables or in case you wanted to change joins etc. but you need to think about security when using direct table approach as there's no ootb security for this.

Hope this help.

Cheers,