Following is a general tutorial for connecting from Microsoft Excel to OBIEE Subject Areas using BI Connector Desktop Edition.
- Microsoft Excel 64-bit.
- BI Connector Desktop Edition for Microsoft PowerBI. (Step-by-Step Installation Guide for BI Connector Desktop Edition for Microsoft PowerBI)
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.
Under the Database section, enter the OBIEE User name and the Password. Click Connect.
- Click on the Database name to connect to the required database.
- Enable the Select Multiple Items checkbox on the top.
- 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.
- 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.
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 email@example.com or file a ticket at our support portal. We are just a click away.