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
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.”
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.
Watch this 30-minute webinar showcasing how the Centre for Addiction and Mental Health (CAMH) used data and visual analytics to better understand and communicate the impact of COVID-19 on Canadians’ mental wellness.
This second year of partnership between Keyrus Canada and HEC Montreal Data Science Club is off to a great start, with the participation of Elodie Palluet to the ''Women and Diversity in Data Science'' panel, organized in collaboration with IVADO.
Watch this webinar on-demand to learn how we use Alteryx to enable non data scientists to bring together different datasets and run those through machine learning algorithms to ensure the right promotional strategy is adopted across dynamic and complex retail systems without any coding.
Keyrus is proud to announce a partnership with the Quebec luxury brand, Maison Marie Saint Pierre. By collaborating with a brand applauded internationally for its savoir-faire and innovation, Keyrus ensures that its teams are provided with reusable protective masks of the highest quality.
We recently encountered this with one of our customers where some of the more common strategies to deal with this type of data storage didn’t cut it – here is how we solved it.
An international player in the fields of Data Intelligence, Digital, and Management and Business Transformation Consulting, the Keyrus Group announces that it has made a strategic investment in Impetus Consulting Group, a leading Enterprise Performance Management (EPM) services firm based in the United States.
In the continuity of its academic involvement, Keyrus is proud to partner with CodeBoxx. This school of technology offers an accelerated training program for IT professions. With a brand new campus opening in Montreal, it is quite naturally that Keyrus and CodeBoxx have entered into a partnership aimed at training and integrating graduates into the job market.
In keeping with its “TodaytoAI” offer, Keyrus is now entering into an expertise partnership with Moov AI, the flagship of artificial intelligence in Quebec. Thanks to this partnership, companies will accelerate their digital transformation by having concerted access to expertise in artificial intelligence and data valorization.