Using Hubway to extract data from an OutSystems Database into Excel

In this step-by-step guide, we will show you how to use Hubway to extract data from an OutSystems Database into Excel, so that you can employ it to gain valuable business insights.

For this demonstration, we will use the Order Management sample app, available in 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. We are assuming this Forge application has been installed in your OutSystems environment, including all the entities listed within. By the end of the demonstration, we will have created an Excel dashboard to list the Total Order Amount against Products grouped by Product Category.

But first, here is an intro to Hubway Connect…

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. The data hub reduces the need for manual development and maintenance of APIs.

Hubway 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 get started!

• Launch the Hubway Builder 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 if required.

• Navigate to the Entities tab of the Project. Search for OrderManagement. This will display all of the OutSystems entities within the Order Management module in the left pane.
• Tick the checkbox next to the following entities: Order, OrderItem, Product, ProductCategory and OrderStatus. Then click Add selected. 

• Alternatively, you can also click the (+) icon on the right side of each entity and add them one by one. This will add the entities to the Exposed Entities section on the right pane. 

• For this demonstration, you can leave the security settings as default and allow the service to be accessible to any valid user account.
• Click the Publish button. This will generate the 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 your data consumers/data analysts each time you publish a new version.
• 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 for your data consumers/data analysts. They 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 copy the link.

• To get the generic service endpoint URL, you can click the “Current Published link” in the header. Then copy the link.

• Open Microsoft Excel and create a Blank Worksheet. Go to Data → Get Data → From Other sources → From OData Feed.

• In the OData feed popup, select the basic radio button, and paste the service runtime URL you copied from the Hubway Builder project. Enter your OutSystems runtime username and password when prompted.

• Excel will now connect to Hubway and seamlessly fetch all of the entities that are part of your project. Select all the entities that are needed for your analysis and click Load.
• You can establish relationships between entities within Excel by going to the Data → Relationship icon under the Data Tools section of the menu.

• You can create new relationships between entities by selecting the Related Tables and their Primary and Foreign Key columns. Create the relationship between Order Management entities as follows:

You are now set up and can build insightful analytics leveraging the power of Hubway Connect.

• Build a visual of the Total Order amount per Product Category by going to Insert → Pivot Charts → Pivot Charts and Pivot Table under the Charts section of the menu.

• In the Pivot Charts Field, select the related fields to build the chart as below:

You now have the flexibility to use Excel – backed by the power of Hubway Connect – to access your OutSystems data for extensive analysis, business insights and reporting… all without the need to write code.

Aishwarya Venkataraman is the Product Owner for Hubway Connect at PhoenixDX and has been working in the software industry for over 16 years. She has worked with OutSystems for 3.5 years as a Design Lead, Business Analyst and Engagement Manager and on large enterprise applications with multiple system integrations.

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.