InterSystems IRIS Connector for Power BI
This page describes how to work with the InterSystems IRIS® Connector for Power BI.
Introduction to the Connector
The InterSystems IRIS Connector for Power BI is a custom connector for InterSystems IRIS and it allows you to access and create reports on regular relational tables as well as InterSystems IRIS Business Intelligence cube data from Microsoft Power BI. It includes full DirectQuery support when querying either type of data. The Connector is included with Power BI Desktop, starting with Microsoft’s April 2019 release of Power BI Desktop.
Connect to InterSystems IRIS
Prior to connecting to InterSystems IRIS from Power BI Desktop, ensure that you have an InterSystems IRIS ODBC driver installed on your system.
In order to connect to InterSystems IRIS from Power BI Desktop, do the following:
Open Power BI Desktop and click Get Data > More... > InterSystems IRIS (Beta).
Enter connection information for your InterSystems IRIS instance. Here, Host (IP Address) is the IP address of the host for your InterSystems IRIS instance, Port is the instance’s superserver port, and Namespace is the namespace where your Business Intelligence data is located. Accept all other options as default.
Upon your first connection to an instance of InterSystems IRIS, an authentication dialog will appear. Choose Basic and enter your InterSystems IRIS credentials.
Browse Your Data
If you have successfully connected to InterSystems IRIS, Power BI will display the database Navigator dialog. You can browse relational tables by selecting Tables. You can expand packages in the left pane to select tables and/or views that you want to include in your Power BI report.
Alternatively, you can view available InterSystems IRIS BI cubes by selecting Cubes in the left pane. Expanding the Cubes option lists all available InterSystems IRIS Business Intelligence cubes in the current namespace. Note that cubes or subject areas with certain features that cannot be supported through SQL access, such as programmatic filters, are excluded from the list.
When you expand a cube, you will see the star schema representation of the cube, including regular dimensions and a fact table with all regular measures for the cube. Note that some columns with internal identifiers are removed.
Publish Reports and Dashboards Using Your Data
Using the Power BI cloud service, you can share reports and dashboards which incorporate data from your InterSystems IRIS cubes and tables. To do so, install and configure a data gateway according to the instructions provided by the Microsoft documentationOpens in a new tab. Your data gateway and your data sources (including InterSystems IRIS) must be registered on the Manage connections and gateways page of the Power BI service.
After you Publish your report or dashboard using Power BI Desktop, access the Gateway connection settings for the associated dataset in the Power BI service and manually add a mapping to your InterSystems IRIS data source.
Troubleshoot the Connector
This section provides guidance regarding some common problems you may encounter when using the InterSystems IRIS Connector for Power BI.
Missing Tables in the Navigator
The InterSystems IRIS Connector for Power BI excludes system tables and tables associated with InterSystems IRIS Business Intelligence cubes from the regular Tables menu. Scrubbed and annotated versions of the latter are available through the Cubes menu. If you need access to a table or a field not listed in the Navigator, you can add it manually with a custom queryOpens in a new tab or use Power BI’s generic ODBC connector.
Missing Cubes in the Navigator
The InterSystems IRIS Connector for Power BI leverages the relational projects of InterSystems Business Intelligence cubes to make them available for use in Power BI. Some cube features, like programmatic filters, cannot be supported through these projections and are therefore left out of the list. Please contact the WRC if you encounter a cube where this behavior is not appropriate.
Dimension Hierarchy Not Appearing in the Report Designer
Power BI does not currently allow seeding dimension information from a connector.
Multilevel Dimension Hierarchy Not Functioning Correctly
When a dimension has multiple levels, these levels are usually represented by separate dimension tables (snowflake schema). While foreign key relationships exist between the fact table and each dimension level and between the different levels of the dimension, Power BI can only choose one path from a fact table to a higher dimension level as the “active relationship”, and may choose the wrong one, leading to unexpected query results. To fix the active relationship, click Manage Relationships in Power BI Desktop and de-activate the direct links between a fact table and higher-level dimension tables. Then, activate the correct relationships one by one. For more information, see the Microsoft documentationOpens in a new tab.
Date/Time Dimension Table Not Appearing in Navigator Dialog
Power BI includes various features for working with date/time values that are incompatible with the date/time dimension table indexes in InterSystems IRIS. Consequently, any date/time dimension table in InterSystems IRIS is converted to a date/time column in the corresponding fact table for the cube.
Access Denied Error Appears When You Attempt to Connect to InterSystems IRIS
To connect to InterSystems IRIS from Power BI Desktop, you must have EXECUTE privileges on the following stored procedures:
An administrator can use the GRANT command to grant privileges to you.
Power BI Service Cannot Retrieve Data for a Report or Dashboard
At this time, to populate a published report or dashboard with data from the InterSystems IRIS Connector for Power BI, you must manually create a mapping between the associated dataset and your InterSystems IRIS data source, as described in Publish Reports and Dashboards Using Your Data. Note that if you have already registered InterSystems IRIS as a data source within the Power BI service, this action may create a duplicate entry for your InterSystems IRIS data source. After you have successfully established access to the InterSystems IRIS data included within the dataset, you can specify either entry and then remove the duplicate.