whatsapptwitterteamslinkedinfacebookworkplace

Success story

Developing Tableau Dashboards for a fast-growing global furniture company

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.

newsletter.svg

Never miss an insight

Stay updated on the latest articles, events, and more

Your email address is only used to send you the Keyrus newsletter. You can use the unsubscribe link in each newsletter sent at any time. Learn more about the management of your data and your rights.

Continue reading

Press release

Keyrus celebrates its 25th anniversary by offering businesses an inspiring new vision

October 13, 2021

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

Alteryx + Snowflake = Driving Analytic & Data Science Outcomes in the Data Cloud

July 28, 2021

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.

Event

Using Tableau To Visualize Canadians' Mental Health & Substance Use During The Pandemic

July 28, 2021

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.

Press release

Keyrus will be a guest speaker at the "Women & Diversity in Data Science" round table organized by HEC Montréal Data Science Club in partnership with IVADO

July 28, 2021

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.

Event

Optimize your customer loyalty program with the right pricing strategy

July 28, 2021

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.

Press release

Keyrus Canada collaborates with Maison Marie Saint Pierre to provide its employees with reusable protective mask

July 28, 2021

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.

Success story

Handling Semi-Structured Key–Value Pairs in Tableau

July 28, 2021

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.

Press release

The Keyrus Group makes a strategic investment in Impetus Consulting Group - a leading player in EPM in the United States and a specialist in Anaplan®

July 28, 2021

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.

Press release

Training and learning showcased by the new partnership between CodeBoxx and Keyrus

July 28, 2021

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.

Press release

Keyrus and Moov AI, announce a strategic partnership for data transformation and artificial intelligence

July 28, 2021

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.