Following is a general tutorial for connecting from Microsoft Excel to OBIEE Subject Areas using BI Connector Desktop Edition.
Pre-requisites
- Microsoft Excel 64-bit.
- BI Connector Desktop Edition for Microsoft PowerBI. (Step-by-Step Installation Guide for BI Connector Desktop Edition for Microsoft PowerBI)
Connecting from Microsoft Excel to OBIEE Subject Areas
Launch Microsoft Excel
Launch Microsoft Excel from your Windows machine.
Navigate to the Data tab on the top menu. Click on Get Data -> From Other Sources -> From ODBC.
The Data Source selection box pops up, choose the Subject Area DSN from the list, which you want to work with, and click OK.
Enter Credentials
Under the Database section, enter the OBIEE User name and the Password. Click Connect.
Connect to the database
- Click on the Database name to connect to the required database.
- Enable the Select Multiple Items checkbox on the top.
Select Subject Area and Tables
- The list of Subject Areas will be displayed. Select a Subject Area from the list.
- Select one or more tables from the list and click on Transform Data button.
Merge Tables
- The Power Query editor will open.
- Select the Fact Table (Order details, in the example shown below), and click Merge Queries.
- Select the Fact table on the top (e.g: Order Details) and the dimension table on the bottom (e.g: Customers) in the Merge window. Make sure to select the GZID column in both the tables to join. Choose Left Outer in the Join kind list and click OK.
Note: GZID is a BI connector virtual column that is used to ensure that Excel uses OBIEE joins.
Apply Changes.
Click 'Close & Load' option to apply changes.
Now your OBIEE Subject Area data is extracted to Excel, as shown in the screenshot below, and ready for analysis.
If you have any questions or issues creating a report, contact us at support@biconnector.com or file a ticket at our support portal. We are just a click away.