i View

Microsoft 365 and IBM Db2 on i

Posted on June 28, 2022 by Bill Langston

When Microsoft delivers a new way to do something, it often becomes an industry-standard practice - especially if it uses a feature of the Microsoft 365 (formerly known as Office 365) product line. The company introduced Power Query as an add-in to Excel in 2010. From there, Microsoft continued to improve and incorporate this technology:

• Updated Power Query in Microsoft 365
• Added it to Excel 2016 and 2019
• Later added it to Excel for Microsoft 365 for Apple Mac

Power Query can access a variety of common data sources through a proprietary Microsoft scripting language called M (short for "mashup"). When you use the Power Query Editor in an Excel workbook to transform and load data, an M script is embedded in the workbook.

So technically, a Microsoft 365 user can connect to Db2 on i through the IBM Access Client Solutions (ACS) ODBC driver. The ODBC driver is included in IBM ACS’ platform-specific package for Windows, and Microsoft’s architecture makes these assumptions:

• ODBC is correctly implemented to support secure database access.
• Microsoft 365 users understand the Db2 on i database.
• These users can write queries that run efficiently and return the right data.

But in practice, few Microsoft 365 users understand their company’s Db2 on i database well enough to join tables, select columns, and format the data they need. They often require extensive technical support which grows if you provide the software to more people.

If your company runs on IBM i and has Microsoft 365 users who need to see, visualize, and share operational data, we encourage you to watch our video where we discuss the challenges of using Power Query and present our NGS-IQ as a possible alternative.

Posted in Education | Comments