Recently, one of our customers approached us with a challenging problem: The customer, who has about 15,000 employees worldwide, has a fairly large OBIEE environment with all the transaction apps feeding data into it. Until they started using BI Connector to connect from Tableau to OBIEE the users were downloading excel files from OBIEE and uploading to Tableau. This provided a true in-memory instantaneous experience but came with a big cost of manual work and data redundancy along with a myriad of security problems.
BI Connector removed a lot of these issues by providing direct connectivity from Tableau to OBIEE. But latency when connecting in a live mode from Tableau to OBIEE was frustrating the Tableau users using BI Connector to create dashboards from OBIEE data.
The customer asked if we can help architect a solution where he can alleviate the OBIEE performance issues for his Tableau users connecting to OBIEE.
Never afraid of a challenge, we immediately jumped on a webex session with the customer to troubleshoot this issue. End users in his organization were using BI Connector to connect to live OBIEE data and create dashboards. We looked at the specific use case where the customer was creating a worksheet based on 7 different tables/folders from a large subject area. The time to query data was about 10 minutes. We took a step-by-step to troubleshoot the root cause of the issue and devise an appropriate solution.
Query Performance and BI Connector
As the first step, we verified that BI Connector is not adding an overhead to the latency. BI Connector is built for high performance with several optimizations and is designed to minimize performance overheads on the underlying OBIEE data source. So, if a query takes 10 seconds to execute in OBIEE, querying through BI Connector should take almost the same time.
We ran a query from Tableau to OBIEE using BI Connector. We opened OBIEE Administration Console and tracked the query execution time. The query joining 7 different tables ran for ~10 minutes in OBIEE. The query took the same time from Tableau also. This validated that BI Connector is not adding any overhead to OBIEE queries from Tableau.
What’s slowing down the queries?
We tested a few simple queries (joining one or two small tables) from Tableau to OBIEE using BI Connector and found that the query performance was reasonable. It is important to note that OBIEE per se might not be the performance bottleneck. Rather, it could be due to the joins in the query or the number of columns being queried or the volume of data being queried or the specific tables involved. If you are doing complex joins (OBIEE data models often consist of complex joins), or fetching tens of hundreds of columns, or querying data from fact tables with millions of rows without applying filters, you are bound to hit latency. That is just the way most databases work.
Can you fix it by connecting directly to the data warehouse?
Some consultants who are not familiar with OBIEE or databases incorrectly recommend customers to bypass OBIEE and connect directly from Tableau to the underlying database. If the problem is with the tables/joins/columns/volume of data, going directly to the database is not going to solve the performance issue. Query performance will remain more or less the same whether you execute the query through OBIEE or directly in the database!
In addition to not solving the problem, by going directly to the data warehouse, you are opening a Pandora’s box by not using the business data model and security model in OBIEE. Just imagine the cost of recreating all this logic (if at all possible), maintaining it in the database layer, and enhancing it as your business needs grow. To learn more on why connecting directly to the data warehouse is not a good idea, refer to this blog.
The customer recognized that going directly to the database is not going to solve his problem. Instead we brainstormed for a solution that will address the root issue: Poorly performing data base queries. After considerable testing, we came up with a solution that effectively eliminated OBIEE performance problem for his Tableau users.
In this approach, the customer combined BI Connector with Tableau Extract with incremental refresh feature to create a high-performance in-memory query layer for OBIEE data. Here is the architecture diagram for this approach.
Below is the step by step approach for this solution.
- Install and configure BI connector in your PC with Tableau Desktop. Refer to the step-by-step installation guide for BI connector
- Install and configure BI connector in your Tableau server. Refer to the step-by-step installation guide for BI connector server edition.
- Connect from Tableau to Oracle BI Server (OBIEE) using BI Connector and create your worksheet as needed.
- In the data source sheet in Tableau, switch to Extract instead of Live.
- Choose Edit (next to Extract).
- Apply filters to select the required data
- Use “Hide Unused columns” to eliminate columns that are not needed.
- Select incremental refresh and choose the field that you would like to use for refreshing with new data. All of the above features will help accelerate your query.
6. Now go to the Worksheet. It will take some time to query the initial full data from OBIEE. Depending upon the type of query, it could take some time. So, patience is needed here.
7. Once you are in the Worksheet, you can publish the data source and worksheet to Tableau server. Set the authentication as embedded password so that Tableau server can automatically refresh the data. In addition, set the refresh cycle for the data as needed.
8. From here on, Tableau will take care of automatically refreshing and updating worksheet with the latest OBIEE data. End users can enjoy ultra-low latency for their visualization queries since data is being served from Tableau in-memory database instead of hitting OBIEE for every query.
How to execute incremental refresh in Tableau Desktop
It is important to know the correct way to refresh data in Tableau Desktop. Otherwise, you could end up spending several minutes doing a full refresh every time you open a workbook with OBIEE data extract.
In Tableau, whenever you go from Data Source page to Worksheet, Tableau always does a full refresh of the extract. This is just how Tableau seems to work. So, whenever possible, avoid going to the Data Source sheet when using an Extract based worksheet.
To execute an incremental refresh, you should open the worksheet directly (when you open an workbook) and not go to Data Source. Execute incremental refresh as follows:
From the Worksheet menu .–> Choose Data –> <Choose the Data Source Name> —> Extract —> Refresh (Incremental). This will refresh the incremental data.
You can verify that it has executed an incremental refresh by viewing the Extract History as follows:
From the Worksheet menu .–> Choose Data –> <Choose the Data Source Name> —> Extract —> History
Fixing OBIEE performance issues is often a daunting task. But, BI Connector provides an easy and effective approach to alleviating OBIEE performance issues for end users. It allows users to visualize OBIEE data faster by using Tableau’s extract with incremental refresh feature to turbo charge queries for OBIEE data. By combining BI Connector with Tableau, you can reduce the time to visualize OBIEE data and provide end users with a high performance self-service BI environment for OBIEE data.
Refer to the step-by-step tutorial to help you get started with this approach.
Share with us your comments/feedback on this approach.