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.
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 Subject Areas using Direct Query – Step-by-Step User Guide
- Connect Power BI Desktop to OBIEE Reports using Direct Query – Step-by-Step User Guide
Power BI On-premise Gateway does not yet support DirectQuery with BI Connector. As a result, you will not be able to publish your DirectQuery based report to Power BI service. To share a report or dashboard with another user, you can either share the .PBIX file or .PBIT(Template) file via Sharepoint.
Microsoft Power BI team is actively working on adding DirectQuery support for custom connectors. To learn about the ETA for DirectQuery support in Gateway, please contact your Microsoft Power BI Representative. Let them know about your requirement for using Power BI with OBIEE.
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.
You can publish a import based report to Power BI Service. Since Power BI On-premise Gateway does not yet support DirectQuery with BI Connector, you will not be able to schedule automatic refresh of the datasets in Power BI Service. You can manually refresh the datasets using Power BI Desktop. If you prefer automatic refresh, we recommend that you consider the ODBC approach.
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.
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!