Connect IBMi Db2 with Power BI

Introduction 

IBM i Db2 is a relational database management system providing enormous functionalities to users for efficiently storing, retrieving, and managing their crucial and confidential data. It also can provide a secure environment to handle large amounts of data.  

On the other hand, Power BI is a powerful tool that provides services to reshape our data in a more meaningful, coherent, and visually representative manner by creating dashboards & reports. Thus, it assists users in easily and precisely analyzing their data. 

By learning to connect IBM i Db2 with Power BI, we can incorporate their capabilities together & handle our crucial business data more adequately. 

Power Query is an important component of Power BI to establish a connection with the database.    

Connecting IBM i Db2 with Power Query Desktop 

First, let’s gain an understanding of the step-by-step process of connecting IBM i Db2 with Power Query Desktop. 

1. The initial step is to open Power BI & select the ‘Get Data’ option.

2. Next, click ‘Database’ and select the ‘IBM Db2 database’ among all the available database choices.

3. Now, indicate the following IBM i Db2 details:
   • Server Name or if Port number is to be mentioned then ‘Server Name: Port’. 
   • The database name to be accessed
   • Data connectivity mode. Choices available for this option are ‘Import’ or ‘DirectQuery’. 

   Note: The directQuery option does not allow us to use the ‘SQL Statement’ advanced option.

4. Click on OK.

5. Enter the authentication details – IBM i username, password, and the level of authentication to be applicable. 

Note: Authentication details are required only when we connect for the first time. 

6. Click on ‘Connect.’ After this, the IBM i Db2 connects to Power BI.

Note: If the following prompt comes into view, then an encrypted connection is not possible, and we can click on OK to continue with an unencrypted connection. 

7. After establishing the connection, use Navigator in Power BI to select the database table we want to load and transform.

Connecting IBM i Db2 with Power Query Online 

Now, let’s explore the step-by-step process for connecting IBM i Db2 with Power Query Online. 

1. Step 1: Open the Power Query ‘Connect to data source’ page.
2. Step 2: Select the ‘IBM Db2 database’ option.
3. Step 3: Identify the IBM i Db2 details:

• Server Name or if Port number is to be mentioned then ‘Server Name:Port’. 
• The database name to be accessed.
• Data Gateway.
• Authentication type. We can select the option ‘Basic’ to establish the connection using IBM i credentials. 

Note: Authentication details are required only when we connect for the first time. 

4. Step 4: Enter the IBM i user and password.
5. Step 5: Select the ‘Use Encrypted Connection’ checkbox if you want to establish an encrypted connection or leave it un-checked and an unencrypted connection will be established.
6. Step 6: Click on Next
7. After establishing the connection, use the Power BI Navigator to select the database table you need to load and transform.

Ascertaining Database name from IBM i 

To find out the IBM i database name to be used when connecting IBM i Db2 with Power BI, run the command DSPRDBDIRE on IBM i. Take option 5 on the entry with *LOCAL remote location, and the screen below will be visible. 

In this example, P1379XPV is the database name we will use.  

Ascertaining Port number from IBM i 

To locate the IBM i port number mentioned while connecting IBM i Db2 with Power BI, run the command WRKSRVTBLE, and the screen below will be visible. 

Among all the service table entries, search for ‘drda,’ the corresponding port number is the number we can use. Also, a notable piece of information is that Port 446 is the default port number for the ‘drda’ service.  

Advanced options in Power BI 

Power BI also provides several advanced options for connecting with IBM i Db2.  

A primary use of advanced options is to specify a subset of tables and data to analyze within a database containing numerous tables and large amounts of data. 

The option for ‘SQL Statement’ is provided to specify a SQL query to fetch only those database details we want to analyze.  

The ‘Include relationship columns’ check box can be selected if we also want to include columns correlated with other tables. 

Similarly, a ‘Navigate using full hierarchy’ check box can be selected to see the full hierarchy of the tables. 

Conclusion  

For all the individuals maintaining the database over IBM i Db2, using Power BI to visually represent the database in a manner that is easier to analyze and sort is worthwhile. By understanding the process of connecting both platforms, we can seamlessly utilize its functionalities and make better business decisions.