How to: Use the K2 OData Feed with Microsoft Power BI

Exposing K2 SmartObject data through the SmartObject OData API provides powerful integration with third party tools like Power BI. This topic shows you how to access the service in Power BI, so that you can analyze and chart SmartObject data using Power BI.

Using the OData service, you can build custom reports and other interfaces based on SmartObject data. Create custom reports using workflow reporting data and data stored in a line of business system as long as you have SmartObjects created for that system.

Some third-party software (for example PowerBI and Excel) have alternative OAuth implementations and do not work with the standard OAuth mechanism used in K2. In these cases, you will only be able to use Basic Authentication when connecting the software to the SmartObject OData Services.

Before You Begin

You must enable the SmartObject OData API and configure SmartObjects first. See Configuring the SmartObject OData Service. Copy the URL from the Management page for use in the Power BI OData Feed configuration.

For Power BI, you must configure the OData connection in Power BI Desktop. Once configured you can use the web and mobile interfaces of Power BI.

Steps

1. Click on the Get Data Down Arrow and select OData Feed from the menu.
   
   
2. Access to the OData Feed configuration can also be found by clicking on the Get Data icon and using the Search option, or navigating to Other.
   
   
3. Paste the K2 OData service URL into the URL text box, then click on OK. The feed URL is on the K2 Management page:
   
   
   You can pre-select a SmartObject in the OData feed by adding the name of that SmartObject to the end of the OData URL. For example: https://k2.denallix.com/api/odata/v3/MYSMARTOBJECTNAME
   
   
4. Select Basic authentication in the Access to OData feed page, then add your Username and Password.
   

   The OData API is an IIS-hosted app and configured with Basic authentication that requires your Active Directory username and password (or Azure Active Directory if K2 is in the cloud).

   IIS performs the initial authentication and authorization and then pass the user’s credentials to the K2 server. At this point, the user is authenticated again.

   
   
   
5. Click on Connect.
6. Select the SmartObjects from the OData feed that you want to analyze in Power BI.
   
   
7. Click on Load. Power BI displays the progress of accessing the SmartObject data.
   
   
8. The SmartObject data shows in the Fields column. Select a visualization and configure it by dropping fields from the SmartObject. Now that the data is available in Power BI, you can use standard Power BI features to design and build your reports.
   
   

Updating Data

Power BI caches the SmartObject data. To update these data use the following steps:

• Perform a new Get Data in Power BI Desktop.
• Use the Refresh option in the Power BI web interface.

Setting Credentials for Power BI on the Web Interface

You must create the connection to the K2 SmartObject OData API in Power BI Desktop. Once the data is cached there, you can use the Power BI web and mobile interfaces to design reports. Configure the credentials for refreshing the data using Basic authentication as shown below:

Enter the Active Directory User name and Password.