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

• 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.

• 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.

• 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:

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.