InterSystems IRIS Connector for Power BI
This article describes how to work with the InterSystems IRIS Connector for Power BI. It contains these sections:
Introduction to the Connector
The InterSystems IRIS Connector for Power BI is a custom connector for InterSystems IRIS® data platform. The InterSystems IRIS Connector for Power BI allows you to access and create reports on regular relational tables as well as InterSystems IRIS Business Intelligence cube data from Microsoft Power BI, and 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.
Connecting 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.
Troubleshooting the Connector
There are 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 query or use Power BI’s generic ODBC connector.
There are 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 Won’t Show Up in the Report Designer
A dimension hierarchy is not showing up in the report designer.
Power BI does not currently allow seeding dimension information from a connector.
Multilevel Dimension Hierarchy Not Functioning Correctly
A multilevel dimension hierarchy is 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 documentation.
Date/Time Dimension Table Not Appearing in Navigator Dialog
The Navigator dialog does not list a date/time dimension table that you defined in InterSystems IRIS.
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
When you attempt to connect to InterSystems IRIS from the Get Data dialog in Power BI Desktop, the Access Denied error message appears.
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.