Travel distance calculation using Google maps and OneStream

Planning logistics can be a tricky thing to do. But what if there was a dashboard that showed you where your employees are and how far they are from the office. This could be very useful to calculate the emission per employee. In this blog we will show you how you can connect google maps to your dashboard and let it automatically calculate the distance between the employee and the destination.

Dimensions

First, we need to assign the city of every employee. This will be done in Text 1 properties. Enter per employee the city where they live.

‍

Afterwards we will go to UD1 where we enter the locations. These locations will be the destination points of the employees. We will again put the name of the city in Text 1.

‍

Coding

After entering the cities you will need to create two Business Rule that retrieves a google API and stores it into a data table. Name the Business Rules “FXT_TravelDistance_FTD” and “MapPinPoint”. You can find the scripts here.

‍

Data adapters

After creating the Business Rules you will need to create two data adapters. One for the map and the other one for the location. You can find the SQL Query for da_GoogleData here.

‍

BI Viewer

After creating the two data adapters you will create a BI Viewer named biv_Map_PD. This BI viewer will have the two data adapters in it. Afterwards you will need to design the BI Viewer. In our BI Viewer we have can set how far of a distance we want to see between the destination and the employees. This is a calculated field.