When you use Data Gateway or Remote Data Connector with Oracle Analytics Cloud there is a small overhead on the duration of each query execution. You won’t notice the additional duration when you run a report because it’s less than 0.3 seconds. However, you might experience performance issues if you run lots of (the number depends on your performance requirements) initialization blocks whenever someone signs-in because initialization block queries are executed serially. Using the deferred option for initialization blocks doesn’t fix the issue, because initialization blocks are executed when you open the first dashboard page.
This is an issue if you use Oracle BI Applications, because it runs more than 200 initialization blocks.
For Oracle Analytics Cloud, the best way to avoid these performance issues is to reduce the number of initialization blocks.
Here are my recommendations on how you can reduce the number of session initialization blocks.
For example, in Oracle BI Applications, disable initialization blocks that refer to Oracle BI Application modules that you don’t use anymore.
Init block 1: query1
Init block 2: query2
Merged init block: query1 union all query2
HR xxx Attribute yyy
Init block 1 is based on query1: select colA from tableA where….
Init block 2 is based on query2: select colB from tableB where….
You can merge them into a single initialization block using a query such as:
Select MAX(colA), MAX(colB) from (
select cola as cola, null as colB from tableA where….
Select null, colB from tableB where…) tmp
You can do as many unions as required to retrieve all the variables from the same connection pool in one single query.
This isn’t easy to implement or maintain, and you risk making mistakes when you create the queries and assign all the variables to a single initialization block. If you carefully implement and maintain your initialization block queries and variables, you can significantly reduce the time it takes to sign-in and display the first dashboard page.