The following is a general tutorial for connecting from Tableau to OBIEE using BI Connector Desktop Edition.
Follow the Step-by-Step Installation Guide for BI Connector Desktop Edition to install and configure BI Connector on your PC.
Follow these few simple mantras to quickly become an Expert in the preparation of the OBIEE data using Tableau Prep and BI Connector.
- For your first preparation, choose a simple flow with one or two measures coming from one Fact table and one or two dimension columns coming from one or more Dimension tables.
- As the first step, create a similar report in OBIEE in your Dev/Test/Production environment and view the results. Note down the Subject Area, Fact table & required measures, Dimension tables & required columns.
- Use filters when appropriate to limit the data being queried by Tableau Prep in OBIEE.
A few things you should not do:
- Do not select a large number of tables (facts and dimensions) for your first few flows. Select only one fact table and one or two dimensions. By being selective, you can avoid mistakes such as combining unrelated star schemas. As you get proficient with the BI Connector and OBIEE data model, you can add more tables to your report.
- Do not combine tables from different star schemas in the same flow. This will result in errors both in Tableau Prep and OBIEE.
- Do not create a flow by querying the entire fact table without any filters. This could result in an expensive query in OBIEE and you will hear back immediately from your OBIEE admins.
Step 1: Launch Tableau Prep Builder. Select Other Databases(ODBC) from the list of Connectors.
Step 2: Select the DSN configured for connecting to OBIEE Subject Areas (e.g., OBIEE SA Connector) that you configured using BI Connector in ODBC Data Source Administrator UI.
Step 3: Enter the username and password and click on Sign In.
Step 4: BI Connector will now connect Tableau Prep Builder to OBIEE Subject Areas. Click on the Database drop-down list to connect to the required database.
Step 5: Click on Schema drop-down list to view the list of Subject Areas. Select a Subject Area from the list.
Step 6: Once the Schema is selected, the list of tables present in the schema will be displayed under the Tables pane. Use the Search bar to search for a particular table. Please note that search is case-sensitive. Select the required tables from the list.
Step 7: Drag and drop a fact table to prepare a flow.
Step 8: Under the Input pane, it shows the Default Data Sample to be included in the flow. By default it selects 'Default Sample Amount'. If you wish to include all of your data for preparation, select the option 'Use all data'. Or, if you wish to specify a particular number of rows to be included, select the option 'Fixed number of rows' and specify the number of rows.
The right side will show the columns present in the table and a sample of 3 values present in the table. You can uncheck any of the columns which you wish to not include in your flow.
Step 9: Drag and drop a dimension table to perform a Join with the fact table.
Under the Join pane, the settings applied for the Join will be shown. Under the Applied Join Settings, it shows the default join clause used.
Note: We recommend to use the GZID as the Applied Join clause, to fetch all the data in the join.
Under the Join Type, we can click the circles in the graphic, to indicate the type of Join to be used. The Summary of Join Results shows the number of rows in each table and the number of rows included in the join result.
Under the tab Join Results, it will show all the resulting columns and the range of data values present in the columns. In the bottom half of the tab, it shows a sample set of values for the columns.
In the Join tab, you can remove the columns, that you want to exclude them from the flow.
At this step, you can also apply filters to the columns to select the desired set of values for the flow.
Click on Add Step, to add an additional step to the join.
You can apply filters and create 'Calculated fields'.
At any step we can also preview the data in Tableau desktop. Any step can also be renamed to be more meaningful and understandable.
Now, we can add more tables to join, perform aggregations and calculations and filter to prepare the data. Once we have added the desired tables and prepared the data, we need to add the Output step to prepare the output.
Step 10(a): Once the data is prepared, we have to add the Output step, to view the prepared data in Tableau Desktop or in Tableau Server.
To view the data in Tableau Desktop, choose Save to file and choose the file format to be saved.
Step 10(b): To publish the prepared data as a data source to the Tableau server, choose Publish as a data source and select the server from the Select a server drop-down list. Sign in to the server and choose the corresponding project to publish as a data source.
You have now successfully prepared your first Tableau flow for OBIEE data. Congratulations!