How to: Use the OData Feed with Microsoft Power BI

This article illustrates how to expose SmartObject data through the SmartObject OData API. Using the OData service, you can build custom reports and other interfaces based on SmartObject data. In this demonstration, you display SmartObject content in a Microsoft Power BI chart. For a demonstration on exposing data in Microsoft Excel, see How to: Use the OData Feed with Microsoft Excel.

Enabling the SmartObject OData API in Management
Enable SmartObject OData API

The screenshots and instructions for third-party software are accurate at the time of writing. Third-party vendors may have changed or updated aspects of their systems (such as user interfaces, functionality, and security). As a result, this content may be outdated.

Scenario

You work in IT where you create custom reports for various departments. One department requires a Power BI report with data from another LOB system. To meet this need, you enable the SmartObject OData API and configure a connection to it from Power BI.

Steps

Begin by creating a SmartBox SmartObject and adding some data. This SmartObject represents the LOB system data you need to expose and can be any SmartObject. Enable the SmartObject OData API service using the Management site. In Power BI, configure a connection to the OData service, then create a chart using the exposed data.

Setup Steps

The following steps set up the scenario from start to finish. If you want to skip the setup steps and view the topic step, go to Configure an OData connection in Power BI.

  1. In the Designer, create a SmartBox SmartObject called Power BI Sample with the following properties: Name (Text) and Items (Number). (Optional) Create categories first to keep your work organized. In the Management site, add at least three items in the SmartObject.
    Using the word OData in your SmartObject name could cause errors when configuring the data feed connection in Power BI. Make sure your SmartObject name does not contain the word OData.
    1. Launch the Designer.
      If you do not know how to access the Designer, see Accessing Sites.
    2. Create categories to keep your work organized. Use the image below as a guide if necessary.
      Create Categories
    3. From the OData Power BI category, create a new SmartBox SmartObject called
      Power BI Sample
      then add two properties
      Name (Text) and
      Items (Number).
      Using the word OData in your SmartObject name could cause errors when configuring the data feed connection in Power BI. Make sure your SmartObject name does not contain the word OData.
      Create SmartBox SmartObject
      Add Properties
    4. Next, add items to the SmartObject so that you have data to work with later. Launch the Management site in a new browser tab.
    5. Expand the Categories > How To > OData Power BI categories and select the Power BI Sample SmartObject.
      Select SmartObject
    6. In the central pane, locate the Methods section. Select the Create method and click Execute.
      Create Method
    7. Enter a Name value and enter a number for the Items value. Click Execute once again. Click Done to exit the Execute SmartObject Method screen.
      Create Item One
    8. Create at least two more items in the SmartObject. If you like, execute the Get List method to see your data.
      Get List Method
  2. Enable the SmartObject OData API service. Select the Power BI Sample SmartObject as the only data source. Copy the API URL as you need it when configuring the connection in Power BI.
    1. Still in the Management site, expand the Integration > APIs node. Select SmartObject OData.
      SmartObject OData API
    2. In the central pane, enable the SmartObject OData API.
      Enable SmartObject OData API
    3. Copy the OData URL for later use. Click the Copy Link icon. (If necessary, paste the link into an editor such as Notepad. You need it to configure the feed connection in Power BI.)
      Copy OData URL
    4. Next, you must expose the SmartObject as an OData endpoint. Under the Which SmartObjects heading, select the Selected SmartObjects option. Click the Add(+) icon. Navigate to and select the Power BI Sample SmartObject. Click Select.
      Select SmartObject
    5. Click Apply Changes at the bottom of the page. You are now ready to add the connection in Power BI.

Configure an OData connection in Power BI

In this step, you configure a connection to the system using the OData Feed in Power BI. You display this data in a chart.

  1. In Power BI, configure an OData feed connection using the settings below. (The images in this demonstration reflect Power BI Desktop.)
    (Location: Home (tab) > Get Data > OData Feed)
    PropertyValueNotes
    URLhttps://k2.denallix.com/api/odata/v3/This is the API URL you copied earlier. The value displayed is the standard K2 link. Depending on your environment, the link may be different.
    Log on credentialsUse the Basic log on.
    K2-provided VM:
    User Name: Denallix\Administrator
    Password: K2pass!    		Enter the credentials of the administrator that enabled the OData service. This could be yourself or the Administrator account if you are working on a K2-provided VM.
    Select TablesPower_BI_Sample 
    Chart FieldsName
    Items    		 
    1. Launch Power BI. (The images in this demonstration reflect Power BI Desktop.) Create and save a new, blank Power BI file.
    2. From the Home tab, expand the Get Data drop-down list and select OData Feed.
      OData Feed
    3. On the OData feed screen, paste the API URL you copied earlier into the URL text box. It should look similar to: https://k2.denallix.com/api/odata/v3/. Click OK.
      OData Feed URL
    4. Next, you see a screen for log on credentials. Click the Basic tab in the left column. Enter your account credentials. If you are working on a K2-provided VM, use the following:
      User name: Denallix\Administrator
      Password: K2 pass!
      and click Connect.
      Enter Credentials
    5. From the Navigator menu, select the Power_BI_Sample SmartObject table. You see a preview of the table and data in the central pane. Click Load near the lower right corner of your screen.
      Select Table
    6. The model connects and loads. On the right side of your screen, select the Items and Name fields to include in the chart.
      Select Fields

      7. The chart displays data from the Power BI Sample SmartObject. The data is in real-time, meaning if it changes, you can refresh the chart and immediately see the updated content.
      Power BI Chart

  2. (Optional) Update the Power BI Sample SmartObject by adding a new item. Refresh the chart data and review the results.

    In this optional step, you update the Power BI Sample SmartObject by adding a new list item. You return to the Power BI chart and refresh the content.

    1. In the Management site, execute the create method on the Power BI Sample SmartObject and add a new list item.
    2. Return to the Power BI chart. Click the Refresh icon in the Home ribbon.
      Refresh Data

      3. The chart reflects the change in real time.
      Chart New Data

Review

In this demonstration, you enabled the SmartObject OData API and configured it to point to the sample SmartObject created for the topic. Using OData services, you can bring SmartObject data, whether it's system data or data from an LOB system, into other tools that can work with OData connections such as Microsoft Excel and Power BI.