BACKGROUND

Our client is a furniture distributor headquartered in Toronto. Their customers range from professional design firms to large retailers such as Costco and Wayfair. The company sources from furniture manufacturers around the world to sell to retailers mostly in North America. They have their own warehouses in Canada and the US, but they also have a lot of drop shipping business. Their growth over the past few years has increased the complexity in their ERP, brought more logistical challenges, and given them more demand for reporting and analytics in general.

CHALLENGE

In February 2020, the client created a new US company to better address their US business. This created new business processes, as well as a new instance of their ERP (SAP Business One, hereafter SAP B1). Consequently, their previously manual reporting process could no longer capture their entire business (Canada + USA). Compounding that with a lean IT team that has limited capacity to maintain complex data warehouses, and the demand for more sophisticated reporting, they were now in need of something more powerful, and considering Tableau as an option.

All information required for reporting is within their SAP B1 application database, with the exception of historical and current exchange rates. Some high-level requirements from them were:

• Executive dashboard to show sales trends by customer type, by product group, and by supplier

• Detailed analysis of product attributes

• Logistical prioritization tool

• Specific vendor performance analysis

• Sales employee performance analysis

• Overall financial performance across multiple entities and currencies

OUR SOLUTIONS

Following a discovery engagement, diving deeper into the business and data challenges, we proposed the following deliverables:

1. 5 Tableau dashboards, each with their own objectives and audience

2. Published dashboards to Tableau Online for internal users, consuming on-premises data via Tableau Bridge

3. New exchange rate data source that allows reporting on both CAD and USD business in each of the 5 dashboards

Some requirements affecting all above deliverables are:

• All dashboards must have the ability to report on both the Canadian and USA entities independently or simultaneously, and

• No additional data warehousing layer must be used.

Below are some technical highlights of our development process.

Data modeling:

We expected data modeling to be challenging from the start, because we were required to connect directly to the ERP’s application database with no additional warehousing or “analytics” layer. In addition, we needed to connect to 2 similar but not necessarily identical databases (Canada and USA).

The 2 main difficulties that emerged were:

1. Understanding the schema within SAP B1

2. Achieving significant data transformations without a data warehouse

Fortunately, the 2 instances of SAP B1 shared the same database server. To avoid creating a data warehouse, the solution we came up with had to rely on extensive ”Initial SQL” in Tableau, which creates and queries temporary tables. This would have been impossible had the 2 databases been in different servers.

Exchange rate data source:

The client conducts business in both CAD and USD. Before we were involved, the monthly financial reporting was the only time the currency of transactions was taken into account. This involved a manual conversion between currencies using the Bank of Canada’s published monthly average exchange rate. We needed to come up with a way to retain the practice of using the monthly average exchange rate, but also allow the conversion to happen any time the data is displayed in the dashboard.

Leveraging the Bank of Canada’s free API, we built a simple script to update a csv file daily with the historical and latest monthly average rates. In a cloud implementation or with a data warehouse available, we could have more easily used a data lake or a table to store this data, but a csv file on the client’s server was sufficient in this case.

RESULTS

We published 5 dashboards to the client’s Tableau Online site, with data refreshed daily. As a result of these new dashboards, the client can forgo the laborious routine of manually generating and distributing many regular summary reports. In addition, the new dashboards allow the client to examine their entire business across countries and currencies, where the reporting was previously either unavailable or fragmented.

The published dashboards also introduced new monitoring capabilities in the client’s logistics, vendor relations, and product development departments that greatly improved their ability to highlight issues and opportunities.

The visual design of the published dashboards allows the client to identify insights more quickly and create more streamlined action plans. In fact, as we were handing off the end-product, the client was already creating departmental business processes to monitor and action around these new dashboards.