How to Retain Your OBIEE Joins When Connecting Power BI to OBIEE Subject Areas

BI Connector Team |

How to retain your OBIEE joins when connecting Power BI to OBIEE Subject Areas

Power BI is one of the leading self-service data visualization tools that can easily complement OBIEE’s data visualization limitations through BI Connector.

When connecting Power BI to an OBIEE (or OAC/OAS) report, the users don’t require to retain the joins in OBIEE Subject Areas as they’re already accessing the reports as a flat table.

However, in the case of connecting Power BI to an OBIEE Subject Area, the users must follow a few simple steps to successfully retain the data relationships in the OBIEE.

The 2 possible scenarios you could encounter are:

  1. Connecting Power BI to OBIEE Subject Areas via Import
  2. Connecting Power BI to OBIEE Subject Areas via Direct Query

Before we dive into the steps, please ensure you download/install BI Connector onto your PC, and configure your OBIEE DSN for the subject Area.

Connecting Power BI to OBIEE Subject Areas via Import

Let’s see the steps for connecting Power BI to OBIEE Subject Area via Import mode: 

  1. Now open your Power BI Desktop, and click on Get Data and select More. Under the Other category, choose BI Connector (Power BI Certified), and click Connect
  2. Enter the DSN name and choose Import as your connectivity mode.
  3. Enter your OBIEE credentials, that you used to set up the selected DSN, and click Connect
  4. In the navigator window, select the Subject Area, and the specific fact and dimension tables you want to analyze.
  5. Then click Transform Data
  6. In the Power Query Editor that opens up, choose your Fact table, and click on Merge queries.
  7. Join the GZID from the fact table to the same column in the first dimension table. Set the join kind as Left outer and click Ok.
    Note: GZID is a virtual column created by BI Connector, that’s appended to all the tables you use for your analysis from the selected Subject Area.
  8. Repeat step 7 for each of the dimension tables you selected.
  9. Now right-click each of the dimension tables in the Power Query Editor, and uncheck Enable Load (as you’ve already merged the data from these tables into the fact table)
  10. Apply filters and/or remove unnecessary columns, if needed.
  11. Now click Close and Apply.

Now your data is ready for analysis and all your OBIEE joins are successfully retained in the flat table you created by merging the queries. 

Yes, it’s that simple!

⋙ Here’s our simplified guide to the comparison of Power BI Vs OBIEE.

Connecting Power BI to OBIEE Subject Areas via Direct Query

Now let’s see the steps for connecting Power BI to OBIEE Subject Areas via Direct Query mode:

  1. Open Power BI Desktop, and click on Get Data and select More. Under the Other category, choose BI Connector (Power BI Certified), and click Connect
  2. Enter the DSN name and choose Direct Query as your connectivity mode.
  3. Entering your OBIEE credentials, that you used to set up the selected DSN, and click Connect
  4. In the navigator window, select the Subject Area, and the specific fact and dimension tables you want to analyze.
  5. Click Load
  6. Navigate to the table relationships window, and join the GZID column from your fact table to the same column in the first dimension table. Then set the relationship as Many to One, and cross-filter direction as Single.
  7. Repeat step 6 for each of the dimension tables you selected. 
  8. Click Save and Close.

Now you’ve successfully retained your OBIEE Subject Area table joins, when connecting from Power BI!

BI Connector Best Practices

Looking to simplify your OBIEE (or OAC/OAS) data visualization with Power BI?
Watch the 9 best practices for connecting Power BI to OBIEE!

Best Practices Connecting Power BI to OBIEE OAC OAS