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.
- 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.
- The added entities should be displayed in the Exposed Entities section on the right pane.
- 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
- 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.
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”.
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.
- In the authentication popup, select Basic and enter your OutSystems credentials.
- 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.
- 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.
- 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:
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])
Still in the Order table create another column named Status with the order’s status, by typing: Status = RELATED(OrderStatus[Label])
- 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
- 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.
- 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.
- 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.
- 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.
- 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.
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.
