InterSystems IRIS
Power BI is becoming increasingly popular among business intelligence professionals and data analysts due to its powerful data visualization capabilities. However, to get the most out of Power BI, you need to connect to various data sources. InterSystems IRIS is a popular database system that is used in many organizations. In this article, we will explore how to connect to the InterSystems IRIS data source from inside Power BI using Power Query M Language code.
What is Power Query M Language?
Power Query M Language is a formula language used in Power Query, which is a data connection and transformation tool that is integrated into Excel and Power BI. With Power Query M Language, you can connect to various data sources, transform and shape the data, and load it into your workbook or report. Power Query M Language is a functional programming language that is easy to learn and use.
What is InterSystems IRIS?
InterSystems IRIS is a data platform that provides high-performance data processing, analytics, and interoperability capabilities. It is used in healthcare, finance, logistics, and other industries where complex data management and integration are required. InterSystems IRIS supports various data formats and protocols, including SQL, REST, SOAP, MQTT, and more.
Connecting to InterSystems IRIS from Power BI
To connect to InterSystems IRIS from Power BI, you need to follow these steps:
1. Open Power BI Desktop and click on the “Get Data” button on the Home tab.
2. In the “Get Data” dialog box, select “ODBC” as the data source and click on “Connect”.
3. In the “ODBC” dialog box, select the InterSystems ODBC driver from the list of available drivers. If the driver is not installed on your computer, you need to download and install it from the InterSystems website.
4. Enter the connection details for your InterSystems IRIS data source, including the server name, port, database name, username, and password. Click on “Connect” to establish the connection.
5. Once the connection is established, you will see a preview of the data in the “Navigator” dialog box. You can select the tables and columns you want to load into Power BI.
Writing Power Query M Language Code for InterSystems IRIS
Now that you have established a connection to InterSystems IRIS from Power BI, you can start writing Power Query M Language code to transform and shape the data. Power Query M Language has a rich set of functions and operators that you can use to perform various transformations on the data.
Here is an example of Power Query M Language code that retrieves data from the “Sales” table in InterSystems IRIS and applies some transformations to it:
let
Source = Odbc.DataSource(“dsn=InterSystems”, [HierarchicalNavigation=true]),
Sales_Table = Source{[Schema=””,Item=”Sales”]}[Data],
#”Changed Type” = Table.TransformColumnTypes(Sales_Table,{{“OrderDate”, type date}, {“Region”, type text}, {“Country”, type text}, {“Product”, type text}, {“Quantity”, Int64.Type}, {“Price”, Currency.Type}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([Quantity] > 10)),
#”Grouped Rows” = Table.Group(#”Filtered Rows”, {“Region”, “Country”, “Product”}, {{“Total Sales”, each List.Sum([Quantity][Price]), type number}}),
#”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“Total Sales”, Order.Descending}})
in
#”Sorted Rows”
This code retrieves the “Sales” table from InterSystems IRIS and changes the data types of some columns. It then filters the rows where the quantity is greater than 10 and groups the remaining rows by region, country, and product. Finally, it calculates the total sales for each group and sorts the results in descending order.
Conclusion
In this article, we have explored how to connect to the InterSystems IRIS data source from inside Power BI using Power Query M Language code. We have also seen how to write some basic Power Query M Language code to retrieve and transform data from InterSystems IRIS. With Power Query M Language, you can connect to a wide variety of data sources and perform complex transformations on the data before loading it into your reports and dashboards.
