Power BI to OBIEE: DirectQuery, Import or ODBC?
To connect and visualize OBIEE with Power BI Desktop using BI Connector, users have three options: DirectQuery, Import, and ODBC. In this article, we will review the details of these three options.
DirectQuery
When you use DirectQuery, Power BI Desktop does not import or copy the OBIEE data. You can view the list of columns in Fact and Dimension tables. As you create visualizations by choosing the columns, data is queried from OBIEE via BI Connector. You always see the current data in OBIEE. DirectQuery works well with large multi-dimensional data sources such as OBIEE.
BI Connector team has worked closely with the Power BI team to optimize the DirectQuery performance for OBIEE. As a result, you can view the OBIEE Subject Areas and Reports metadata quickly even if you have a large data warehouse.
To connect to OBIEE Subject Areas and Reports from Power BI Desktop using DirectQuery, please refer to the following step-by-step guides:
- Connect Power BI Desktop to OBIEE Reports using Direct Query – Step-by-Step User Guide
- Connect Power BI Desktop to OBIEE Subject Areas using Direct Query – Step-by-Step User Guide
Power BI On-premise Gateway supports Direct Query for BI Connector based data sources. So, you can publish your Direct Query report to Power BI service and connect live to data from OBIEE. To manage the security of the imported OBIEE data, use Power BI’s security model.
Import
Import first copies the OBIEE data into Power BI Desktop. You can create visualizations using the imported data. To see the current data in OBIEE, you have to refresh the data.
Import uses Power BI’s in-memory database(cache) for fast analysis. However, you should use filters to import only the required data into Power BI Desktop. Failure to do so could result in query performance issues.
To import OBIEE data, the steps are similar to DirectQuery. In the connection UI, choose Import instead of DirectQuery. Importing OBIEE reports is straightforward. The report is available as a table in Power BI. To import Subject Areas, merge the folders/tables into a single table using GZID which is a BI Connector specific column. You can then import the merged table into Power BI with appropriate data filters.
To connect to OBIEE Reports and Subject Areas from Power BI Desktop using Import, follow the same steps as Direct Query, but choose Import instead of DirectQuery in the connection type.
Power BI On-premise Gateway supports scheduled refresh of datasets for BI Connector based data sources. So, you can publish your datasets and report to Power BI service and schedule daily or weekly refresh of data from OBIEE. To manage the security of the imported OBIEE data, use Power BI’s security model.
ODBC
ODBC connection is similar to import. When you connect to OBIEE, Power BI imports the data into its in-memory cache. You can create visualizations from the imported data. To see the current data in OBIEE, you should refresh.
ODBC connection to OBIEE using BI Connector uses Power BI in-memory cache. Therefore, the performance characteristics of ODBC approach are similar to that of the Import. We strongly recommend that you use filters to bring only the required data from OBIEE into Power BI.
To connect Power BI to OBIEE using BI Connector as a ODBC data source, check out the following step-by-step guides:
Power BI On-premise Gateway supports scheduled refresh of datasets for ODBC data sources. So, you can publish your datasets and report to Power BI service and schedule daily or weekly refresh of data from OBIEE. To manage the security of the imported OBIEE data, use Power BI’s security model.
Summary
When it comes to connecting from Power BI to OBIEE using BI Connector you have the option of using DirectQuery, ODBC or Import. Choose the approach depending upon your business requirement, environment, and expertise level of users.
If you have questions about best practices for connecting Power BI to OBIEE, or would like to see a demo, drop us a note. We will schedule a web conference session at your convenience to answer your questions.
Happy Easy Analytics!