{"id":4341,"date":"2023-04-24T14:05:30","date_gmt":"2023-04-24T14:05:30","guid":{"rendered":"https:\/\/www.biconnector.com\/blog\/?p=4341"},"modified":"2023-05-18T05:11:17","modified_gmt":"2023-05-18T05:11:17","slug":"bi-connector-gzid-connecting-power-bi-to-oac-subject-areas","status":"publish","type":"post","link":"https:\/\/dev.biconnector.com\/blog\/bi-connector-gzid-connecting-power-bi-to-oac-subject-areas\/","title":{"rendered":"BI Connector GZID: Connecting Power BI to OAC Subject Areas"},"content":{"rendered":"\n
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:<\/p>\n\n\n\n
BI Connector works with:<\/p>\n\n\n\n
A common question we receive from our clients is about how connecting Power BI to OAC subject areas works in BI Connector. <\/p>\n\n\n\n
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. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
In this blog post, we will explore BI Connector\u2019s GZID, its benefits, steps to retain OAC joins, and quick tips. <\/p>\n\n\n\n
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. <\/p>\n\n\n\n
This is especially beneficial because users need not know the data modeling concepts to connect Power BI to their Oracle data. BI Connector\u2019s patented method automatically determines the relationships between the tables in the Subject Areas. It enables users to establish joins by simply selecting the GZIDs. <\/p>\n\n\n\n
BI Connector GZID helps users reap multiple benefits. Here are some of them:<\/p>\n\n\n\n
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.
<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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. <\/p>\n\n\n\n
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. <\/p>\n\n\n\n
With GZID, users unfamiliar with Power BI\u2019s Data Modeling options can retain and reuse the OAC table relationships in Power BI and avoid errors in data modeling.<\/p>\n\n\n\n
When connecting Power BI to OAC Subject Areas, the BI Connector\u2019s standard steps are highly recommended for users familiar with their Subject Areas tables but not with data modeling concepts.<\/p>\n\n\n\n
The steps may vary based on the Power BI connectivity mode you use. First, let\u2019s look at the steps used in the Import mode.<\/p>\n\n\n\n
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:
<\/p>\n\n\n\n
Now your fact table will have the required data from all the dimension tables. You\u2019re all set to load the fact table into your reporting canvas and analyze the data.<\/p>\n\n\n\n
When connecting Power BI to your OAC Subject Areas via Direct Query mode, the OAC joins can be retained using GZID in Power BI\u2019s Data Modeling pane.
<\/p>\n\n\n\n
Please follow the steps exactly as follows:
<\/p>\n\n\n\n
You can start visualizing your OAC Subject Areas data in Power BI using Direct Query mode.
<\/p>\n\n\n\n
Here are some best practices to help you make the most of BI Connector GZID:<\/p>\n\n\n\n
For any data analysis function, it\u2019s 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\u2019re connecting to, especially the tables you want to visualize in Power BI.<\/p>\n\n\n\n
You should clearly understand the tables containing an entity’s attributes, (called Dimension tables). The Dimension tables have the details about an entity. <\/p>\n\n\n\n
For example, a customer can be added only once in the \u2018CUSTOMERS\u2019 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.<\/p>\n\n\n\n
Similarly, for \u2018PRODUCTS\u2019 dimension table, capturing information\u2013including the Product ID, Product Name, and Product Category\u2013each 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.<\/p>\n\n\n\n
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 \u2018ORDERDETAILS\u2019 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.<\/p>\n\n\n\n
If you don\u2019t 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.<\/p>\n\n\n\n
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\u2019ll be able to access your Fact and Dimension tables data (from the Subject Area) as a flat table, in a ready-to-analyze manner.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
As Power BI is star-schema friendly, this tip applies to all data sources.<\/p>\n\n\n\n
We would also like to add that BI Connector is not limited to star schema.<\/p>\n\n\n\n
When connecting Power BI to OAC Subject Areas, if you\u2019re using the GZID to retain the table joins from your OAC, it\u2019s good to stick with the star schema. <\/p>\n\n\n\n
If you\u2019re familiar with how Power BI processes data with other schemas, then it\u2019s your playground. You can use any schema that works for your analysis!<\/p>\n\n\n\n
The BI Connector\u2019s 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\u2019s always good to stick with the BI Connector\u2019s standard steps when using the GZID for retaining your OAC joins.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
If your primary keys do not have confidential info and you\u2019re super-familiar with Power BI\u2019s Data Modeling options, you can avoid GZID and use the actual keys to create joins or merge queries in Power BI. <\/p>\n\n\n\n
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).<\/p>\n\n\n\n
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).<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
BI Connector GZID ensures organizations can keep their primary key information confidential and improve OAC data security.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Interested to know more? Check out the banner below.<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"
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: Oracle Transactional Business Intelligence (OTBI) Oracle Analytics Server (OAS) Oracle Analytics Cloud (OAC) Oracle Business Intelligence Enterprise Edition (OBIEE) Oracle Fusion […]<\/p>\n","protected":false},"author":19,"featured_media":4366,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[120,1],"tags":[],"yoast_head":"\n