Five Things to Consider Before You Extract a Large Number of Rows from OBIEE
If you are thinking of fetching a large number of records from OBIEE into Tableau or Power BI or Qlik Sense, you need to first evaluate the following:
1. Do you really need to fetch large volume of OBIEE data into Tableau or Power BI or Qlik Sense?
It is a best practice to use OBIEE for aggregation and bring the aggregated data (lower volume) into Tableau or Power BI or Qlik Sense for additional analysis. OBIEE is a very effective tool for processing large volumes of complex data and therefore preprocessing in OBIEE is an efficient approach.
2. Is your OBIEE instance capable of exporting a large volume of data?
OBIEE environments have a restriction on the maximum amount of data that can be exported. This depends on the following:
- Number of Columns: More columns --> more data
- Column size: Large column size --> more data. eg., description columns or multi-byte columns are often large
- Number of Rows: More rows --> more data
So, if you see an error or a fetch returns zero rows when you try to extract data, it is often because your OBIEE is unable to handle the large data volume export. So consider the above factors when you extract a large volume of data.
Similarly, if your OBIEE instance or specific report has performance issues, perhaps your OBIEE instance is not sized or configured to handle a large volume of data. In such a scenario, fetching a large volume of data from OBIEE is not advisable. It could significantly slow down your OBIEE environment.
3. Another aspect to consider is that when you extract/import, Tableau/Power BI or Qlik Sense will extract all the columns from the table/folder/report. This could lead to a very large dataset thereby resulting in an error in OBIEE. To limit the data size, it is strongly recommended that you restrict the number of columns that are being extracted.
4. To fetch a large number of rows from OBIEE into Tableau or Power BI or Qlik Sense, BI Connector provides you with a convenient option to break down a large number of rows into a smaller set of records. For example, if you need to import 500,000 rows from OBIEE, BI Connector will break it down into 10 queries each fetching 50,000 records at a time. The first query will fetch the first 50,000 records, the second query will fetch the next 50,000 records and so on. This is designed to minimize the load on OBIEE and fetch the records effectively.
However, this approach overrides the maximum number of rows per fetch/query defined in OBIEE by your administration team. Therefore you need to test and verify that your query doesn't fetch millions and millions of rows which could significantly impact the performance of your OBIEE environment.
5. Finally, If you still need to fetch a large number of rows from OBIEE into Tableau or Power BI, please take the following approach:
a. Test your query in a Dev or Test environment. Do not run it on a production instance until your query is thoroughly tested for volume.
b. Identify the volume of data being fetched by your query/report from OBIEE.
c. Consult your OBIEE Administrators if your OBIEE production instance can handle the large volume of data request for one query.
d. Work with your OBIEE team to optimize the data that is being fetched into Tableau.
How to Configure Maximum Number of Rows Per Fetch
BI Connector provides you with a convenient option to break down a large number of rows in a Subject Area table / report into a smaller set of records. By default maximum number of rows per fetch from OBIEE server using BI Connector is set to be 50,000 rows. If you want to increase/decrease the number of rows to be fetched from OBIEE, you can follow the below steps.
Step 1: Open “ODBC Administrator” and click on “Configure” button of the DSN that you created for connecting Tableau with OBIEE.
Step 2: Click on Advanced Settings icon to open the advanced settings dialog box.
Step 3: Enter the number of rows that you want to configure the Maximum number of rows per fetch textbox.
Note: The value entered in the Maximum number of rows per fetch field must be less than the ResultRowLimit parameter in your OBIEE's instance config file. Your OBIEE administrator will be able to share the value of the ResultRowLimit parameter.