- 1. Introduction →
- 2. The 9 Best Practices of BI Connector →
- 2.1. Know your OBIEE →
- 2.2. Start with OBIEE Reports →
- 2.3. Choose the right connection type →
- 2.4. Apply Filters →
- 2.5. Use GZID for Retaining OBIEE Subject Area joins →
- 2.6. Stay within the schema →
- 2.7. Fine-tune for long-running and large queries →
- 2.8. Troubleshoot connectivity issues in a click →
- 2.9. Uncheck the auto update worksheet →
- 3. Conclusion →
Oracle Business Intelligence Enterprise Edition (OBIEE) is a traditional BI platform used by enterprises for over a decade. One of OBIEE’s major drawbacks is its lack of self-service capabilities.
Many organizations complement OBIEE’s data visualization limitations by connecting self-service analytics tools such as Tableau to OBIEE in a bimodal approach.
Some organizations, despite moving to Oracle Analytics Cloud (OAC) are connecting Tableau to OAC as well, as a significant portion of the users are already well-acquainted with Tableau, and prefer sticking to it.
BI Connector, an ODBC-based connector, helps organizations connect Tableau to OBIEE and OAC securely and seamlessly, in minutes.
In this eBook, we’ll cover 9 simple best practices for connecting Tableau to OBIEE through BI Connector. These best practices were developed based on careful research and numerous interactions with many BI Connector users across a broad range of industries.
2 The 9 Best Practices of BI Connector
Here’s the list of the 9 best practices:
- 1. Know your OBIEE
- 2. Start with OBIEE Reports
- 3. Choose the right connection type (between Import and Direct Query)
- 4. Apply filters
- 5. Use GZID for retaining Subject Area Joins
- 6. Stay within Star Schemas
- 7. Fine-tune for long-running and large queries
- 8. Troubleshoot connectivity issues in a click
- 9. Uncheck the auto update worksheet
Now let’s take a look at each of them in detail.
2.1. Know your OBIEE
OBIEE, as an Enterprise BI platform, comprises several subject areas and reports. One of the major challenges for organizations using OBIEE is to help users strengthen their Data Literacy.
A strong Data Literacy of the data source is essential before you analyze the data. It is impossible to derive insights out of a dataset if you’re not clear about the dataset.
Hence, BI Connector users must have a solid understanding of the OBIEE data they want to analyze in Tableau. The users must differentiate between the fact and dimension tables and be clear about the data formats used in their OBIEE.
We at BI Connector strongly recommend the users to follow a fail-safe approach. First open a report in OBIEE, and view the same report in Tableau (by connecting to OBIEE through BI Connector) side by side. Verify if the data in OBIEE gets tallied in the Tableau end.
You may take a look at this quick video and learn how to configure OBIEE DSNs in BI Connector.
2.2. Start with OBIEE Reports
The Subject Areas in OBIEE consists of several fact and dimension tables. The relationships between these tables will be defined in OBIEE.
When you connect Tableau to OBIEE Subject Area through BI Connector, you’ll require to use a few additional steps to retain the OBIEE joins for your analysis.
However, when you connect Tableau to OBIEE reports, no additional steps are needed for retaining joins, as OBIEE reports already use the existing joins, along with aggregations, if any.
Therefore, it is highly recommended to start with connecting Tableau to OBIEE reports first. Once the users get familiarized with BI Connector, they can easily learn the additional steps required for connecting to OBIEE Subject Areas.
In case if the data you require is not available in any existing OBIEE report, please create a new report in OBIEE with the required dataset, and then connect to that report from Tableau.
Here’s a quick video on connecting Tableau to OBIEE reports through BI Connector:
2.3. Choose the right connection type
The users must make the right choice between the Live and Extract query modes of Tableau. Live queries work best if the dataset required for your analysis is large.
On the other hand, the Extract option works well if faster performance is required. However, it is good to limit the extracted dataset by narrowing down to the data you need for analysis. You can do this by applying filters and reducing the number of columns.
If you’re planning to connect to OBIEE in Live mode and extract the data from other data sources or vice-versa, you must carefully ensure the composite connectivity model works for you.
2.4. Apply Filters
The lesser the dataset, the better the performance of Tableau. OBIEE, as a data source could have millions of rows, and hundreds of columns.
But the users will not need all the data to perform their analysis or to answer the questions they have.
Hence, it is easy for the users to boost Tableau’s performance just by applying filters (apart from reducing the number of columns) and narrowing down to the required dataset for analysis.
You can apply filters based on time, location, or any other dimension applicable to your data, based on your need, and sense a drastic improvement in Tableau’s performance.
2.5. Use GZID for Retaining OBIEE Subject Area joins
When seeing the title of this best practice, you would have got a question – What’s this GZID?
Well, GZID is a virtual ID created by BI Connector. GZID is appended as a separate column to each of the Fact and Dimension tables of the connected Subject Area. The GZID column shows the serial numbers for the rows in each table.
Ok, but how GZID helps?
It helps users to avoid recreating joins in Tableau from the scratch by enabling them to retain the existing joins in OBIEE. GZID also helps to avoid exposing any primary key-foreign key relationships in the OBIEE presentation layer.
Here are the steps to retain OBIEE joins in Tableau using GZID:
All you need to do is to create an inner join using the GZID column in the Fact table to the GZID column in each of the dimension tables.
Even if Tableau automatically creates joins using a different column, you must change them to the GZID column. This is the only additional step you need to take when connecting to OBIEE Subject Areas.
2.6. Stay within the schema
Some Subject areas in OBIEE could have more than one fact table. In that case, each fact table could be joined with a different set of dimension tables in the OBIEE end.
In the above image, you can see that fact 1 in the OBIEE Subject Area is joined with dim1, dim2, dim3, and dim4. Similarly, fact 2 is joined with dim5, dim6, dim7, and dim 8. You can also notice that Fact 1 and Fact 2 are in the same Subject Area.
In this case, you must ensure that you stay within the star schema in the Tableau end when creating joins using the GZID column. In other words, you must not create a join between fact1 to dimensions 5 to 8 or between fact 2 to dimensions 1 to 4, in order to avoid errors in your analysis.
Hence, if there are more than one Fact tables in a Subject Area, the user must be aware of the dimension tables joined with each of the fact tables in the OBIEE end. This awareness would help them stay within the Star Schema when creating the joins in Tableau using the GZID column.
2.7. Fine-tune for long-running and large queries
The users can optimize the performance by just adjusting certain parameters in BI Connector’s Advanced Settings window.
If a query runs for several minutes and returns an empty dataset, you’ll need to check what time the same query takes to get executed in your OBIEE. If the time taken is more than the custom time-out setting in BI Connector, you’ll just have to increase the value accordingly to avoid errors in future.
When dealing with a large volume of data, the users can also adjust the maximum number of rows and maximum rows per fetch for analysis.
For example, consider a table with more than a million rows, and you just need the first 100,000 rows for analysis. In this case, you can just set the maximum number of rows as 100,000. Additionally, you can also adjust the maximum rows per fetch, say 25,000. Then BI Connector will fetch the data four times at 25,000 rows per fetch, in order to fetch all the 100,000 rows you need. The maximum rows per fetch is very useful to avoid OBIEE time-out errors.
You can also optimize the performance by adjusting the maximum number of columns per query.
2.8. Troubleshoot connectivity issues in a click
The users sometimes face connectivity challenges when setting up the DSNs. When there’s a connection error, just click the Run Diagnostics button.
Then you can see the cause of the connectivity error, along with the instructions to fix the error. The potential causes of error are usually invalid credentials, wrong server name or port number etc.
The Run Diagnostics button is like a handy troubleshooting assistant, that helps you troubleshoot in DIY mode.
You can learn more about the Run Diagnostics feature here.
2.9. Uncheck the auto update worksheet
When you create reports in Tableau, the underlying data gets automatically updated every time you drag a measure or dimension column into the worksheet if the Auto Update worksheet feature is enabled.
Hence the report creation process is delayed. You can avoid this delay just by unchecking the Auto Update worksheet option.
With these simple best practices, your organization can experience hassle-free visualization of OBIEE and OAC data with Tableau through BI Connector.
And here’s what you can do next:
Try BI Connector free trial.
Use with Power BI, Qlik, Tableau Desktop edition.
Request BI Connector Demo
Get to see BI Connector in action! Know more about BI Connector.
Already using BI Connector?
Here’s your complimentary onboarding session!