You are leaving our main website to go to our chinese website hosted in China. For legal reasons there will not be any links pointing back to the main website.
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.
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
Following a discovery engagement, diving deeper into the business and data challenges, we proposed the following deliverables:
5 Tableau dashboards, each with their own objectives and audience
Published dashboards to Tableau Online for internal users, consuming on-premises data via Tableau Bridge
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.
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:
Understanding the schema within SAP B1
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.
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.
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.
Stay updated on the latest articles, events, and more
Your email address is only used to send you the Keyrus newsletter and for commercial prospecting purposes. You can use the link in our emails to opt-out at any time. Learn more about the management of your data and your rights.
Webinar
Thursday, February 2nd, 2023 @ 12:00PM Eastern Time (Canada)
Blog post
Keyrus Canada supports the Centraide association and launches the “November Together” project This year our team has decided to support the United Way, an association that works locally for vulnerable people by creating more inclusive communities and solidarity projects.
Blog post
In order to experimente the international corporate volunteering, young graduates aged of 18 to 28 come to work in the data’s world at Keyrus Canada.
Success story
Keyrus' team has put in place a solution to support a North American logistics company with more than 75 distribution sites. Find out below how we have fixed and developed the process automation, data extraction and interpretation to increase their productivity.
Podcast
As more and more data sources, platforms, and services become cloud-based the reasons for maintaining infrastructure to host a BI tool become less and less practical. Security and performance concerns are no longer the roadblocks they once were to many SaaS products, and ease of access for end users is significantly improved. This leaves many organizations with on-premises BI tools wondering if the move is right for them.
Webinar
As more and more data sources, platforms, and services become cloud-based the reasons for maintaining infrastructure to host a BI tool become less and less practical. Security and performance concerns are no longer the roadblocks they once were to many SaaS products, and ease of access for end users is significantly improved. This leaves many organizations with on-premises BI tools wondering if the move is right for them.
Success story
As part of its data analytics journey, an insurance company decided to migrate its data warehouse to Amazon Redshift Spectrum and wanted to take advantage of the new warehouse to upgrade its reporting system. Keyrus was brought in to start off this reporting migration and set the client up for success with Tableau.
Event
Tableau embedded analytics is the vehicle that can get your external solutions faster to market and start your journey towards data monetization. With Tableau embedded analytics, any organization can deliver more value to its customers by putting data that matters right at the front.
Press release
Levallois-Perret, October 14, 2021 - An international player in consulting and technology as well as a Data and Digital specialist, Keyrus announces new goals in the market through a renewed and inspiring vision stemming from 25 years of expertise in data exploitation. Keyrus has also redesigned its visual identity, creating a new logo and slogan: “make data matter.”
Event
In this pre-recorded webinar, we conduct a demo on the technical and business benefits of using Alteryx and Snowflake together as a modern strategy for cloud data analytics at scale.