BIP question... using logical sql query in email bursting

kalikhan

I am trying to use Oracle OAC pixel perfect (or BIP)  email bursting functionality.

I created a datamodel/dataset using logical sql query with data source 'Oracle BI EE', and then a BIP report from it. It works fine, and I can even manually email that BIP report to other users. So email functionality works that way.

But then I added a bursting query in datamodel, where I also used logical sql query.

This logical sql query (for bursting) has all PARAMETER1, 2, 3, ... in column names with appropriate value.  But when I trying to schedule the BIP report (where it uses the bursting query) it is failing. 

According to log, the report ran fine, but it is failing in delivery, which is strange as email works fine (from BIP) without bursting.

If I create a BIP report by using physical database source in Datamodel/Dataset and also in bursting query then email bursting works fine for me.

My questions are:

(1). If the datamodel/dataset has logical sql query, then can we NOT use email bursting feature for that BIP report?

(2). If the datamodel/dataset has logical sql query, then should the bursting query still use a physical database source or can it use logical sql query?

I did not find any example in youtube or other places, where somone used logical sql query in BIP datamodel and then did exmail bursting. All examples are using physical data sources in BIP to create datamodel and email bursting.

Appreciate your help.

Thanks

Nasar

Federico Venturin - Oracle

Hi @kalikhan ,

Since the Oracle BI EE data source is available for selection, I expect it to work when defining a bursting query.

Can you please share the full log message and your bursting query?

I also suggest to create a new analysis by selecting the "Create Analysis from Simple Logical SQL" option and copy/paste the logical SQL from your bursting query. This is to confirm that your code is correct and runs properly.

kalikhan

Federico,

Thanks for your reply.

I was able to make it work.

Looks like even if we define a datamodel/dataset using logical sql query, the bursting query cannot be defined as logical sql query.

Bursting query has to be a SELECT statement from a direct physical database. 

So I created a new table "DELIVERY_CONTROL" in the database, and defined a new jdbc connection in BIP and then queried that table in bursting query.

-------------------------------------------

For anyone interested in more details, here is what I did:

Data model created by selecting 'Oracle BI EE' datasource and writing logical sql query using a subject area rpd objects. After that I created a BIP report using that data model.

Then I took the connection pool information of that subject area (by looking at rpd file in BI Admin tool) and defined a new jdbc physical database connection in BIPublisher.

Defined a table "DELIVERY_CONTROL" (as explained in oracle documentation) in that same data source using TOAD:

https://docs.oracle.com/en/middleware/bi/analytics-server/design-publish/create-table-use-delivery-data-source.html#GUID-5F2302E9-94A9-4379-94F9-DF497870A271

Opened up the datamodel again and added a bursting query where I used the new jdbc physical database connection.

All the columns were already defined and populated in the new "DELIVERY_CONTROL" table, so I just did a SELECT statement from "DELIVERY_CONTROL" table.

Then I scheduled the above BIP report and submitted the job. No email addresses were provided during scheduling as it picked up email addresses (and SPLIT BY KEY) from the bursting query.

In short, logical sql query can be used in BIP data model, but for bursting query we have to use physical database connection and it should be the same database (and schema) which was used by logical sql query.