1. Home
  2. Knowledge Base
  3. Articles
  4. Quickly build Power BI reports from your OutSystems Data with Hubway Connect

Quickly build Power BI reports from your OutSystems Data with Hubway Connect

Getting access to OutSystems data for reporting and analytics purposes is an ongoing requirement. One of the most valuable assets of your web and mobile apps is the data they collect. However, if you canโ€™t quickly and easily access this data, youโ€™re not realising the true value of your applications.

Hubway Connectย is the ultimate solution to enable non-developers to quickly build reports using Business Intelligence (BI) tools like PowerBI and Tableau.

Hubway Connect is a tool that provides OutSystems business users with the ability to easily configure and safely expose data through RESTful APIs. It gives users, via BI platforms, access to a well-defined data set, along with the flexibility to join, merge, calculate and analyse data, without the need for any coding.

Sounds good, but how can I do it?

In this step-by-step guide, we will show you how to use Hubway to extract data from an OutSystems app intoย Power BI, so that you can use it to create stunning dashboards with rich visualisations.

For this demonstration, we will use theย Order Managementย sample app, available in theย OutSystems Forge. The Order Management application is a mini CRM that can be used to control the process of receiving, tracking and fulfilling customer orders. Please note, this Forge application must be installed in your OutSystems environment, if you want to replicate this demonstration.

By the end, we will have created a Power BI dashboard with charts that display indicators such as the number of open orders, total revenue by country, top selling products and sales per product family.

But first, here is an intro toย Hubway Connect.

Hubway Connect – a quick overview

Hubway Connect is a no-code tool that allows non-developer users to create, configure and manage data connections (APIs) to quickly and easily access data held in OutSystems apps. Hubway Connect reduces the need for manual development and maintenance of APIs, while enabling Product Owners, BAs, and BI Analysts to self-manage their data landscape.

Seamless integrationโ€‹

Uses Industry-standard technologies, RESTful and OData, supported by the most popular analytics tools.

Reliabilityโ€‹

Hubway Connect was built to work with OutSystems’ data layer and metadata, designed with the guidance and supervision of OutSystems product management.

Security

Robust security features allow customers to design APIs according to their established policies and security guidelines

 

Hubway Connect adopts industry-standard technologies such as RESTful APIs and theย OData protocol.ย It seamlessly integrates with Enterprise Standard tools like Excel, Tableau and Power BI. You can use Hubway Connect within Excel to quickly develop powerful analytics.

Letโ€™s dive inโ€ฆ

Step 1: Create a Hubway project with the Order Management entities

  • Launch Hubway on the browser and create a new project.
  • Enter a project name in the friendly name field. The service name will be auto-generated and you can choose to modify the service name later if you want.

Hubway Connect
Create a Hubway project with the Order Management entities
  • OutSystems entities are elements that allow persisting information in the database. You can think of them as database tables.
  • Navigate to theย Entitiesย tab and search forย OrderManagement.ย This will display all of the OutSystems entities within the modules belonging to the Order Management app in the left pane. In theย Show itemsย dropdown menu, change the number of items displayed from 10 to 15 to make it easier to select all the entities at once.
  • Select the following entities:ย Account, Country, Order, OrderItem, OrderStatus, Productย andย ProductCategory.ย Then clickย Add selected.ย Alternatively, you can also click theย (+)ย icon on the right side of each entity and add them one by one.
ย 
Hubway Connect
Create a Hubway project with the Order Management entities

 

  • The added entities should be displayed in theย Exposed Entitiesย section on the right pane.
Hubway Connect
Create a Hubway project with the Order Management entities

 

  • For the purpose of this demonstration, you can leave any additional settings as default.
  • Now click theย Publishย button on the top right corner to get a snapshot of your first project.

Step 2: Service Endpoint URL

  • There are two types of service endpoint URLs: version specific and generic.
  • Aย version-specific URLย will expose the entities that are specific to the version tag specified in the URL, and it will have a version number tied to the URL. You must share the latest URL with data consumers/data analysts each time you publish a new version with different entities.
  • Theย generic URLย will automatically expose the entities contained in the latest published version. So each time you publish a new version, you do not have to share the latest URL with your data consumers/data analysts. They will automatically get access to the entities of the latest version because they are using the generic link.
  • To get the version-specific service endpoint URL, you can select the version of your choice from the dropdown menu and clickย Copy link
Hubway Connect
Service Endpoint URL

 

  • To get the generic service endpoint URL, you can click on the link icon next to the projectโ€™s name in the header. Then clickย Copy link.
Hubway Connect
Service Endpoint URL

 

Step 3: Import OutSystems data from Hubway into Power BI

  • Open Power BI Desktop and create a new report. Go toย Home โ†’ Get Data โ†’ OData Feed. If OData Feed isnโ€™t displayed in the Common data sources, clickย โ€œMoreโ€ฆโ€ย and browse to OData Feed and then clickย โ€œConnectโ€.

Hubway Connect
Import OutSystems data from Hubway into Power BI
ย ย 
  • In the OData feed popup, select theย Basicย option, and paste the service endpoint URL you copied from the Hubway project created in the first step.

Hubway Connect
Import OutSystems data from Hubway into Power BI
ย 
  • In the authentication popup, selectย Basicย and enter your OutSystems credentials.

Hubway Connect
Import OutSystems data from Hubway into Power BI
ย 
  • Power BI will now connect to Hubway and seamlessly get all of the entities that are part of the selected project. Select the required tables for your Power BI report and clickย Load.ย For the purpose of this demonstration, we will select all of the fetched tables.

Hubway Connect
Import OutSystems data from Hubway into Power BI
ย 
  • Power BI will do its best to auto-detect (if enabled) the entitiesโ€™ relationships by introspecting theย OData metadata.ย This means that incorrect relationships may be created. You can easily define or fix the relationships manually within PowerBI by going to the Data icon on the left side and navigating on the top menu toย Table Tools โ†’ Manage relationships.

Hubway Connect
Import OutSystems data from Hubway into Power BI

 

Hubway Connect
Import OutSystems data from Hubway into Power BI
ย ย 
  • You can create new relationships between entities (tables) by selecting the linking related columns. Usually a foreign key from the source table and the primary key of the target table.
  • Create the relationship between Order Management entities as follows:
Hubway Connect
Import OutSystems data from Hubway into Power BI

 

Step 4: Build insightful analytics

Now that youโ€™re set up, you can build insightful analytics leveraging the power of Hubway Connect. First, we will create useful columns that will make building easier.

  • In the Data tab, open the Order table and click onย Table tools โ†’ New column.ย This new column will be named Country which will get the accountโ€™s country from which the order was requested. Type the following DAX query and click on the check to create the column: Country = RELATED(โ€˜Country'[Name])
Hubway Connect
Build insightful analyticsย 

 

  • Still in the Order table create another column named Status with the orderโ€™s status, by typing: Status = RELATED(OrderStatus[Label])
Hubway Connect
Build insightful analyticsย 

 

  • Now on the OrderItem table add two new columns, the related product name and its respective category name. For that, use the following queries: Product = RELATED(โ€˜Product'[Name]) Category = RELATED(โ€˜ProductCategory'[Name])
  • Once the above columns have been created, navigate to theย Reportย tab. In this report, we will display 4 different visuals: the total revenue by country, the number of approved orders, the top 5 selling products and the sales per product family.
  • To build the Total Revenue by Country chart, on the rightmost pane check the TotalAmount and Country fields under the Order table. Select theย Filled mapย visual to change the chart type

Hubway Connect
Build insightful analytics
  • ย To rename any chart, click it in the dashboard and format the visual under theย Visualisationย pane on the right. Selectย General โ†’ Title โ†’ Textย and add the new title. Rename the built visual to Total Revenue by Country.
Hubway Connect
Build insightful analytics

 

  • To display the total number of approved orders, check the Id field under the Order table on the rightmost pane. Select theย Cardย visual. Drag the Status field from the Fields pane to the Filters pane underย Filters on this visual โ†’ Add data fields hereย and check the Approved & Closed. In theย Visualizationsย pane, rename the Count of Id field to Approved & Closed Orders.
Hubway Connect
Build insightful analyticsย 

 

  • To include the top 5 selling products, check the Quantity and Product fields from the OrderItem table on the rightmost pane. If not selected by default, pick theย Clustered column chart.ย In theย Filtersย pane expand the Product card, and selectย Top Nย as the filter type to show the top 5 products. Drag the Quantity column to theย By valueย section and apply the filter.
Hubway Connect
Build insightful analyticsย 

 

  • Lastly, to display the sales per category, check the Amount, Category and Product fields under the OrderItem table on the rightmost pane. Select theย Stacked bar chartย visual and rename the title to Sales per Product Category.
Hubway Connect
Build insightful analytics

 

  • You have successfully created a Power BI dashboard with charts that display indicators such as the number of approved orders, total revenue by country, top 5 selling products and the sales per product category.
Hubway Connect
Build insightful analytics

 

Unlimited possibilities

With the growing relevance of OutSystems applications in enterprise landscapes, it is fundamental that data is made available and interoperable at the speed of low-code. This integration is be made possible with Hubway Connect. You now have the flexibility to build Power BI reports using your OutSystems data so that you can find meaningful insights that will help you make better business decisions. All this is possible using a simple no-code approach, in minutes.

About the authors

Rita Fernandes is a Software Engineer and has been the OutSystems Developer of Hubway Connect from its inception. She put the first building block together when it was just an idea presented as part of her Master Thesis. Ever since she has been the lead developer involved in all sorts of technical aspects, from backend development to automated testing.

Joรฃo Meloย has worked in the software industry for over 20 years, including roles as a Software Developer, Product Owner, Solution Architect and Tech Lead. He has helped companies achieve results with a pragmatic approach to software development. An OutSystems MVP for over 7 years, Joao is the Head of Product Delivery at PhoenixDX, the builder of Hubway Connect.

Was this article helpful?

Related Articles