BI Connector is a one-of-its-kind solution that empowers Oracle users to securely connect Power BI to their subject areas and analysis reports. The powerful tool helps bring data from these platforms to Power BI:

1. Oracle Transactional Business Intelligence (OTBI)
2. Oracle Analytics Server (OAS)
3. Oracle Analytics Cloud (OAC)
4. Oracle Business Intelligence Enterprise Edition (OBIEE)
5. Oracle Fusion Analytics (ERP, HCM, CX, and SCM)

BI Connector works with:

1. Power BI Desktop and Service (or Report Server)
2. Direct Query and Import Connectivity modes

A common question we receive from our clients is about how connecting Power BI to OAC subject areas works in BI Connector.

The users with Power BI Pro and Premium accounts can query and interact with their OAC data on a web browser via the On-premise Gateway.

When connecting Power BI to OAC (or OTBI, OBIEE, OAS, Oracle Fusion Analytics) Subject Areas, the BI Connector retains the OAC joins in Power BI. BI Connector does this job through a specific field called the GZID.

In this blog post, we will explore BI Connector’s GZID, its benefits, steps to retain OAC joins, and quick tips.

What is GZID in BI Connector?

GZID is simply a virtual ID in BI Connector. It gets appended as a separate column to each subject area table you connect from Power BI. Compatible with both Import and Direct Query modes, the GZID allows users to retain and reuse the Oracle BI and Analytics joins between the tables.

This is especially beneficial because users need not know the data modeling concepts to connect Power BI to their Oracle data. BI Connector’s patented method automatically determines the relationships between the tables in the Subject Areas. It enables users to establish joins by simply selecting the GZIDs.

Benefits of Using the BI Connector GZID

BI Connector GZID helps users reap multiple benefits. Here are some of them:

Enhanced OAC Security

Owing to security concerns, many organizations refrain from exposing the primary keys (of the critical tables in the subject areas) to external applications such as Power BI. This can create issues with visualization. In Power BI, when no common column is available to connect two tables, the tables remain independent, and the users cannot visualize the data after combining.

BI Connector GZID effectively eliminates this issue. It helps companies to keep their primary keys secure and enables users to retain the joins underlying the OAC tables via the GZID.

User-friendly process

GZID addresses two pain points of users: the difficulty in looking for the exact connecting key and the manual effort of creating joins. This ensures that technical and non-technical users can connect tables in Power BI without a hassle. 

Time and cost savings 

BI Connector GZID provides a standard and automated way to create relationships between your OAC fact and dimension tables in Power BI, improving time and cost savings. 

Avoids Data Modeling Errors

With GZID, users unfamiliar with Power BI’s Data Modeling options can retain and reuse the OAC table relationships in Power BI and avoid errors in data modeling.

BI Connector’s standard steps for Using GZID

When connecting Power BI to OAC Subject Areas, the BI Connector’s standard steps are highly recommended for users familiar with their Subject Areas tables but not with data modeling concepts.

The steps may vary based on the Power BI connectivity mode you use. First, let’s look at the steps used in the Import mode.

Steps to retain OAC Joins in Power BI via Import Mode

When connecting Power BI to OAC Subject Area in Import mode, you just need to merge the queries between the fact and dimension tables. Once you bring the necessary OAC tables into Power BI, follow these steps:

1. In the Power BI reporting canvas, click on Transform Data. This opens up the Power Query Editor.
2. In the left pane of the Power Query Editor, select the OAC Fact table, and click on Merge Query option at the ribbon. This opens a dialog box.
3. Now Select the first Dimension table and map the GZID from the fact table to the GZID in the dimension table. Leave the Join kind as Left Outer.
   (Anybody familiar with the Power Query would think this step is wrong. But don’t worry, we take care of the logic behind it. You can blindly follow the steps to retain the joins in your OAC between the tables)
4. Repeat steps 2 and 3 for all dimension tables.

Now your fact table will have the required data from all the dimension tables. You’re all set to load the fact table into your reporting canvas and analyze the data.

Steps to Retain OAC Joins in Power BI via Direct Query Mode

When connecting Power BI to your OAC Subject Areas via Direct Query mode, the OAC joins can be retained using GZID in Power BI’s Data Modeling pane.

Please follow the steps exactly as follows:

1.  In the Power BI Data Modeling window, join the GZID from the fact table to the GZID in the first dimension table. A pop-up window will open now.
2. In the pop-up window, click on the ‘GZID on the fact table’ (at the top), and then click on the GZID in the dimension table (at the bottom).
3. Below the table at the bottom of the pop-up, make the cardinality as Many-to-one and cross filter direction Single.
   (Again, users familiar with Power BI Data Modeling options might think this step is wrong. We assure you that the logic is effectively handled at the back end. You can blindly follow these steps to retain the joins between OAC tables).
4. Repeat steps 1 to 3 for the rest of the dimension tables.

You can start visualizing your OAC Subject Areas data in Power BI using Direct Query mode.

3 simple tips to avoid errors and warning messages when using GZID

Here are some best practices to help you make the most of BI Connector GZID:

1. Know your OAC Subject Area

For any data analysis function, it’s essential to have a clear idea of the data you want to analyze. In the case of connecting Power BI to the OAC Subject Area, you should have a good understanding of the Subject Area you’re connecting to, especially the tables you want to visualize in Power BI.

You should clearly understand the tables containing an entity’s attributes, (called Dimension tables). The Dimension tables have the details about an entity.

For example, a customer can be added only once in the ‘CUSTOMERS’ table, which contains their information, such as CustomerID, Name, Address, Email, and Phone number. You cannot add the same customer in more than one row of the CUSTOMERS table.

Similarly, for ‘PRODUCTS’ dimension table, capturing information–including the Product ID, Product Name, and Product Category–each product will be stored only once in the Product table. You cannot add the same product in more than one row of the PRODUCTS table.

Users should be able to identify the transactional data table (called Fact table). The Fact tables in OAC Subject Areas usually have a couple of measures and calculated columns. For example, the table ‘ORDERDETAILS’ contains the list of orders a company processed over time. It includes line items of each order and metrics such as revenue and discount. In a fact table, each order from the same customer can be recorded in a separate row.

What should I do if I’m unfamiliar with my OAC Subject Areas?

If you don’t know your Fact and Dimension tables, creating an Analysis report on the OAC first is better. Then you can connect to the Analysis Report in Import or Direct Query mode from Power BI via BI Connector.

When you connect to the Analysis Reports that already exist in your OAC (or the ones you create newly based on your analysis needs in Power BI), you’ll be able to access your Fact and Dimension tables data (from the Subject Area) as a flat table, in a ready-to-analyze manner.

2. Use GZID on a Star Schema as much as possible

The next tip is to use GZID in a star schema. A star schema means your OAC Fact table is at the center of all the dimension tables you want to analyze.

As Power BI is star-schema friendly, this tip applies to all data sources.

We would also like to add that BI Connector is not limited to star schema.

Does GZID work only with Star Schema?

When connecting Power BI to OAC Subject Areas, if you’re using the GZID to retain the table joins from your OAC, it’s good to stick with the star schema.

If you’re familiar with how Power BI processes data with other schemas, then it’s your playground. You can use any schema that works for your analysis!

3. Stick with BI Connector’s Standard Steps for Using GZID

The BI Connector’s standard steps for using GZID are proven and battle-tested even for users unfamiliar with Power BI data modeling options or the data modeling concepts in general. Hence, it’s always good to stick with the BI Connector’s standard steps when using the GZID for retaining your OAC joins.

Can I avoid the standard steps and choose my path?

The Power BI super users always ask this question. The short answer is yes, but not recommended, as it could create security issues by forcing your company to expose the primary keys to an external application.

If your primary keys do not have confidential info and you’re super-familiar with Power BI’s Data Modeling options, you can avoid GZID and use the actual keys to create joins or merge queries in Power BI.

Alternatively, you can take your own approach in using GZID, and use your own data modeling tricks on Power BI (instead of exactly following the standard steps).

Your own path can work well, especially when familiar with the Cardinality, Cross-filter direction (when creating GZID joins on the Power BI Data modeling window for Direct Query mode), and Join Kind options (in Power Query editor when merging query for Import mode).

Conclusion

With BI Connector, users can bring their OBIEE, OAC, OAS, OTBI & Oracle Fusion Analytics (CX, ERP, HCM, &SCM) data to Power BI. The powerful tool helps connect Power BI to your OAC Subject Areas in minutes and reuse the OAC table joins in Power BI via the GZID.

BI Connector GZID ensures organizations can keep their primary key information confidential and improve OAC data security.

With GZID, you need not build the model from scratch in Power BI, resulting in time and cost savings. Further, the GZID helps users to avoid data modeling errors and arriving at wrong insights and decisions.

Interested to know more? Check out the banner below.