LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: BI-tools

SAP Analytics Cloud Dashboard Overview

Thu, 20 May 2021 10:18:45 +0300

Our last guide on BI tools was dedicated to QlikSense and today we will have a look at SAP Analytics Cloud. The dashboard has been prepared for us by the head BI consultant at SAPRUN Alexey Salynin.

In this overview, we will touch the following topics: creating a new data source in SAP, adding filters, making a Tree map,
setting up a preview on mobile devices, working with the Smart Insight assistant, multi language feature.

This dashboard has the same structure as our previous dashboards. At the top, we can see the KPI cards and a date filter. Below there is a heatmap that shows top provinces and a chart that shows profit dynamics on the right. In the second part, we can see the products and customers analysis.

Creating a new data source in SAP

To add a data source in SAP, we can either work with models or datasets. Models allow us to work with several data sources at the same time and carry out planning in order to solve complex problems. In our case, it’s a simple Excel file, so we used a normal dataset. As we have chosen only the orders sheet. Here is how the dataset looks:

Some of the columns were created, such as products hierarchy, geo hierarchy and some additional calculations.

Adding a filter

There are a number of ways to add a filter starting from default filtration methods to input fields to a timeline. If you click the edit button, you can see the filter format.

A tree map shows the top provinces by sales. Each province contains information on variation similarly to the KPI cards. The map allows filtering by provinces.

On the right of the tree map there is a profit dynamics chart. The chart has a dynamic axis which allows choosing the time period in time series charts. Time series graphs in SAP can show a forecast. The forecast is done automatically using built-in algorithms. In our case, triple exponential smoothing was chosen. When hovering over the forecasted point, we can see upper and lower confidence bounds.

In the next part of the dashboard we can see the products and customers analysis section.

Here you can see that products are displayed in a list without any hierarchy. To activate the hierarchy we need to change the dimension properties and set the hierarchy.

By default, the first level is displayed. We can change the level and see the products by category. Moreover, the bars are filters for the table on the right.

Smart Insight

One of the interesting features in SAP is smart insight. SAP automatically analyses the value and builds charts to provide more insights. For example, if I want to see what contributed to high level of furniture sales, I can click on smart insights and see the following results:

The feature is very handy as the insights are not simple graphs, but they are connected to each other and show a story. The insights allow us to interact with the data and explore it deeper. We can later add these insights into our visualization. This feature can also be used on a new dataset and empty dashboard to explore the data without building anything.

The whole dashboard was built in a responsive view mode so it will be automatically adapted to different screens. Some visualizations like tables are not adapted for the mobile screen, so we can hide them for mobile screens.

We can save the dashboard in the catalog and add it to favourites. Unfortunately, the functionality of the system doesn’t allow posting dashboards on public sources. But we hope that with new releases the feature will appear.

Last but not least, the dashboard has been created in Russian and when we needed to translate it into English SAP multi language feature came very handy. We can choose the target language and translate all the text. Then the dashboard will be displayed in a language based on the user’s location.

We have evaluated the dashboard together with Alexey (his scores in brackets) and received the following results:
Meets the tasks – 9.8 (10)
Learning curve – 6.5 (7)
Tool functionality – 9.8 (9)
Ease of use – 8.2 (8)
Compliance with the layout – 9.3 (10)
Visual evaluation – 8.6 (9)

Overall: 8.71 out of 10. Have a look at the final results:

QlikSense Dashboard Overview

Tue, 11 May 2021 15:28:04 +0300

Our last overview on BI instruments was dedicated to Redash, but today we will deal with the dashboard in QlikSense using the SuperStore Sales dataset. The dashboard was built for us by Alexey Grinenko, a head of BI section, Business Solutions Development Department at Softline.

Data Sources

To load data in QlikSense we need to use load scripts. This is how the script looks:

Basically, the script lists the columns that we need to import and the link to a file. [Calendar_Order] script makes a custom calendar as all our data is before 2012 and the default calendar creates measures such as *current month* or *previous month* which we don’t need.

Dashboard Structure

As you can see, the dashboard has the same structure as in Tableau with some peculiarities of QlikSense.

At the top, there is a line with KPI cards. The cards compare KPI values in the chosen year with the values in the previous one and show the change in percentage. If the change is positive, the percentage is displayed in green, if the change is negative, values are red.

Then you can see a treemap which shows provinces by profit. The color shows profit distribution in the provinces. As all charts in QlikSense, the treemap is clickable and we can choose several provinces and see only the data related to them. On the right of the treemap, you can see area graphs that show profits by month in 4 different years.

At the top right corner of the dashboard, we have a drop down menu where we can choose a month and a year. As QlikSense doesn’t have this feature as default, we had to customly create such a calendar in a load script. First, we loaded the Month and the Year columns and created a third column where we concatenated Month and Year with a dash. This measure will later help us in switching the periods.

In the next part of the dashboard, we can see the analysis of products and customers. On the right, there is a bar chart that shows profit and sales by categories. On the left, the bar chart displays the Top 15 products by profit. In the settings, we can change this number and display fewer products if we wish.

Last but not least, we have a bubble chart which shows the distribution of customers by profit and sales. Each bubble represents a customer and the size of the bubble shows the number of orders. The color indicates the size of the discount.

Conclusions

Together with Alexey (his scores are in brackets) we have evaluated the dashboard on 10 points scale (10 being the highest score) and received the following results:

Meets the tasks – 10
Learning curve – 6.9 (8)
Tool functionality – 9.0 (7)
Ease of use – 7.3 (8)
Compliance with the layout – 9.8 (9)
Visual evaluation – 7.5 (10)

Overall: 8.4 out of 10.

Redash Dashboard Overview

Fri, 07 May 2021 13:24:27 +0300

Redash is an open-source tool that is available in two versions: self-hosted and cloud. If you use the first one, Redash is free of charge, but for a cloud option you’ll have to pay. In both versions you can connect to a numerous amount of databases (including Clickhouse) or other sources like Google Sheets by API.

Redash is a SQL query editor that allows building visualizations. To make a dashboard, we first need to run a SQL query and then build a visualization. After that, we can add the queries with their visualizations to the dashboard. The process makes data investigations easy and simple.

Data preparation

To start working with data we need to click settings and create a new data source.

When working with Superstore dataset we could not directly connect a .xlsx file as redash works only with databases. Thus, we uploaded our .xlsx into a MySQL database.

Building reports and visualizations

In the beginning of the dashboard, you can see the filters. This is the only way to apply filters to a dashboard in Redash and to choose parameters like province interactively.

First of all, we added KPI cards to the dashboard. The functionality of Redash is extremely limited in terms of visualizations, so the only way to build a KPI card was by using so-called counters. A counter is a type of visualization that allows displaying a current and a target value, however, in our case we used the previous year value instead of the target.

For the KPI cards, we used the query below.

It’s a simple query that returns the sum of the profit in current month and the sum of the profit in the previous one. In each query we need to define Year and Month and Province in the WHERE statement, so that all our visualizations are filtered based on the chosen year, month and province. The results are two numbers, curr and prev. Then we click on New Visualization, choose counter as a type and assign curr to the counter value and prev to the target value. We can also change format by adding a dollar sign prefix.

In our previous reviews of BI-tools, we displayed all KPI cards in a single line. In Redash, however, the numbers were too small when displayed in a single line and unnecessary information like the query name and the last update time were cluttering the view. This is another disadvantage of Redash, so we had to make bigger cards and display them in two lines.

To display top performing provinces we used word clouds. The query returned the sum of sales by provinces. Then the sum of sales was used as a frequency column to define the size of the province names.

For Profit Dynamics visualization we used a simple line graph. The query below returned a table with total profits for each month as well as two additional columns that display profit in the current month and the previous one.

select date_format(orders.OrderDate, '%Y-%m-01') as month, sum(orders.Profit) as profit, curr.curr_profit, prev.prev_profit
from orders
left join (
    select date_format(OrderDate, '%Y-%m-01') as month, sum(Profit) as curr_profit
    from orders
    where MONTH(OrderDate)=MONTH('{{Year and Month}}') and YEAR(OrderDate)=YEAR('{{Year and Month}}')
        and ('{{Province}}'='0. All' or Province = '{{Province}}')
    group by 1
) curr on curr.month=date_format(orders.OrderDate, '%Y-%m-01')
left join (
    select date_format(OrderDate, '%Y-%m-01') as month, sum(Profit) as prev_profit
    from orders
    where MONTH(OrderDate)=MONTH('{{Year and Month}}') and YEAR(OrderDate)=YEAR('{{Year and Month}}')-1
        and ('{{Province}}'='0. All' or Province = '{{Province}}')
    group by 1
) prev on prev.month=date_format(orders.OrderDate, '%Y-%m-01')
where ('{{Province}}'='0. All' or Province = '{{Province}}')
group by 1,3,4
order by 1

We then used the a line graph to display the profit column and a scatter plot to display curr_profit and prev_profit columns as both of them had only one observation.

Profit and Sales by Category visualization shows a SQL query table that returns profits and sales by category and sub-category of products.

Last but not least, we have pivot tables for top products and top customers by profit. Pivot tables in Redash allow grouping elements by using aggregate functions. In our case we grouped products by profit. I do not recommend using this feature for a large amount of data as if you change the aggregations on the fly in the browser, the browser might slow down and even crash.

Conclusions

You can find the final dashboard here.
Our team has evaluated the dashboard and the following scores on 1-10 scale 10 being the highest were given:

Meets the tasks – 7.3
Learning curve – 7.5
Tool functionality – 5.5
Ease of use – 7.5
Compliance with the layout – 6.0
Visual evaluation – 5.2

Overall: 6.5 out of 10.

PowerBI Dashboard Overview

Wed, 05 May 2021 18:38:05 +0300

We continue the series of materials on BI-systems and today we will have a look at the dashboard prepared in PowerBI using the SuperStore Sales dataset. We will cover how to connect the data to the system, set custom colors for visualizations and create new measures, implement switching between charts using bookmarks and we will discuss the challenges that we faced when building the dashboard.

This is the how the final dashboard looks like:

The most notable feature of the dashboard is data cards that show the company’s KPI. The cards compare the parameters to the same period in the previous year and show the previous year’s dynamics in the background.

Below we can see the chart that shows top-performing provinces. The bluer the rectangle the more profitable the province, the more orange the rectangle the more losses the province sustains. The size of the rectangle corresponds to the quantity of sales. We can click on rectangles to see more detailed information about profits and sales dynamics in the region on the graph on the left and their KPI at the top. On the graph, there are green and blue points that indicate the month of the current year and the previous year respectively. Hovering over these points, you can see a trend line.

The next part of the dashboard shows product and customer analysis. This part allows us to answer questions such as “which products were the most profitable or unprofitable” or “which customers contributed to most of the profits or most of the losses”.

Data collection

To connect the data we used an Excel file. PowerBI offers a number of sources to connect your data from such as Excel, csv, json files and various databases.

Configuring reports and visualizations

When building a dashboard in PowerBI we wanted to copy the color themes from Tableau. To do this, we have created a JSON file with the list of colors that we want to use. You can see the content of our file below. Then in the views tab, we clicked on the “browse for themes” button and uploaded our colors.

{
	"name":"Orange-Blue Diverging",
	"dataColors": [
		
		"#1c5998",
		"#1c73b1",
		"#3a87b7",
		"#67add4",
		"#7bc8e2",
		"#cacaca",
		"#fdab67",
		"#fd8938",
		"#f06511",
		"#d74401",
		"#a33202",
		"#7b3014",
		"#F07C28",
		"#2B5C8A",
		"#94C6E1",
		"#87d180",
	]
}

Then we have created a separate table called Calendar and populated it with all order dates. After that, we created a column with just a month and a year to create a filter based on it.

Creating necessary measures

When creating a dashboard with PowerBI we often need to create new measures. For the data cards, we created such measures as Total Profit, Total Sales, Total Orders, Total Clients and so on. The arrows that you can see in the data cards are also customized and a measure was created for each of them. To apply the color to arrows we formatted the color by rules and indicated red if the value is less than 0, green if the color is more than 0.

Adding bookmarks to switch between charts

To switch between charts, we added bookmarks for sales and profits. For the sales chart, the profits bookmark is hidden and vice versa. The button was downloaded from the internet and added to the respective bookmarks.

Interesting features and challenges we faced when building the dashboard

We have created custom data cards for KPI which are different from the default ones offered by PowerBI. The original features of cards include the background trend, the name and value while the arrows and changes are a custom feature. Another interesting feature that we used is cross filtration which allowed us to apply the filter to both the profits/sales chart and KPI cards.

One of the challenges that we have faced was the inability to build a bar chart with 2 categories. This feature was not implemented in PowerBI at the moment of writing this overview (maybe it is implemented now), so we had to create a table and add bar charts into it. Similarly, we inserted bar charts into the Top Customers table.

Conclusion

Our team has evaluated the dashboard and has given the following scores from 1-10 scale (10 being the highest) to this dashboard:

Meets the tasks – 9.8
Learning curve – 3.0
Tool functionality – 9.5
Ease of use – 7.5
Compliance with the layout – 9.5
Visual evaluation – 8.8

Overall: 8.0 out of 10. Have a look at the final dashboard here.

Comparing Tableau and PowerBI training programs

Mon, 26 Apr 2021 15:58:17 +0300

This year I succeeded in becoming a Tableau Desktop Certified Associate. When I was thinking about how to prepare for the exam, I came across e-learning courses from Tableau that turned out to be free for 90 days.

I decided not to waste such an opportunity and complete all the 3 modules in Fundamentals at a fast pace. When I got certified, I was wondering which programs are offered by other producers of BI tools. First things first, I decided to study training materials on PowerBI. In this small article, I would like to compare Tableau and PowerBI training programs.

Disclaimer: in the end, I have formed an unfairly prejudiced and positive attitude towards Tableau, so PowerBI supporters may not like this article and find it biased (in all fairness, there are also words of praise for PowerBI).

After having studied the training materials, I can finally state the reasons why I am definitely in favor of Tableau as a tool for data analysis and visualization.

First of all, there is a huge gap in the approach to materials and the assessment of their understanding. Although Tableau training materials are more technical and pay less attention to design, by studying through their videos you can do excellent visualization. After completing all three steps of Tableau training, a strong desire to create new stunning reports with the use of LOD Expressions, Filter Actions, and make convenient interfaces arises. However, after watching all the materials on Power BI the only question that remains is why did I waste my time?

Emotions aside, there are several key points that turned out to be important after having studied the material.

This is a good dashboard according to Microsoft

The quality of content and training examples

If you consider the way training videos are presented in Tableau and the questions in a quiz format that are posed at the end of the covered material, you start understanding the idea of the software. But in the case of Power BI, you will be totally disappointed. Have a look for instance at the material for identifying outliers, here Microsoft suggests building a scatter plot and visually identifying all the outliers.

Design of reports and dashboards

There is some objective criticism towards Tableau training materials on the topic of graph design and control elements, but they are still neatly and beautifully made. Now have a look at the dreadful thing that Microsoft suggests as the result of the analyst’s work. And this is a well-built dashboard according to Microsoft.

Assessment of the knowledge gained during the training

During the training at Tableau, immediately after a small lecture, you learn by applying the part of the studied material in practice. You need to click certain buttons in the interface to solve a problem. Power BI offers “labs” that are supposed to be launched from a remote machine. I didn’t manage to start a single lab; I wrote to the support 3 times and the support couldn’t solve my problem so I didn’t manage to experiment over the PowerBI tasks.

The results of the analyst’s work according to Microsoft.

Other points are mostly related to the software rather than the training program.

Cross-platform support

I have been working with Tableau for a long time and 4 years ago I switched to Mac. After the transition from Windows, my experience of using Tableau did not change. In fact, Tableau was developing and I was developing with it, but the team did not change the key elements of the interface. I have been experimenting with building reports in PowerBI, but I was uncomfortable with different Microsoft archaisms like publications through some share-portal where you need to have an MS account and configure something through the administrator. All of this was a terrible headache.

However, what struck me so much was that I could not use PowerBI on a Mac. There is absolutely no way and this is a principled stance of Microsoft which is not expected to change in the future. From my point of view, such software belongs to a B2B segment in the field of analytics, assumes the connection to all kinds of DBMS, but denies the existence of an alternative operating system which could be used by a number of potential consultants that could use and promote PowerBI as an analytical tool.

Most certainly, there are some rational reasons why any software from Microsoft doesn’t work very well on Mac, but the simple truth is that for me the software remains inaccessible. Nevertheless, I wasn’t looking for an easy way out and installed PowerBI through Parallels in order to honestly consider the tools again taking into account the training materials.

Visualization options

Both Tableau and PowerBI offer stunning visualization options. In fact, in this regard, PowerBI offers a video with a little more information than usual. So, on this matter, the tools are presented equally well.

Functionality

Here I want to give credits to the functionality of PowerBI. In fact, the variety of tools is extremely wide even without connecting third party libraries. For example, automatic clustering, decomposition tree, data profiler and setting filters on a graph.

Internal language syntax

To work with PowerBI you need to learn DAX. It is not a programming language, but a functional language. You won’t be able to write your own code, however, you won’t even need it – all the functions are already implemented, so you should only learn how to use them. Microsoft tells about DAX quite well in the manual. Definition of a new measure in DAX looks like this:

Revenue YoY % =
DIVIDE(
	[Revenue]
		- CALCULATE(
			[Revenue],
			SAMEPERIODLASTYEAR('Date'[Date])
	),
	CALCULATE(
		[Revenue],
		SAMEPERIODLASTYEAR('Date'[Date])
	)
)

Preparing data for the analysis

Inside PowerBI there is a Unpivot feature that allows bringing the data in columns with time periods into the form that is convenient to use in pivot tables.

However, an ETL tool for data cleaning and wrangling in Tableau Prep also has this feature implemented.

Conclusions:

1) The training programs are built in completely different ways, the methodology of immersion into Tableau tools is more elaborate and efficient. There is an opportunity to get practical experience of solving problems and get feedback (albeit automatic).
2) Reports and dashboards design in training materials from Microsoft hardly look professional while Tableau’s implementation is much better.
3) Knowledge assessment at Microsoft is implemented at the abysmal level (absolutely perfunctory tests like in a bad school) while at Tableau it’s much better implemented, you dive into the problem, think about the answer and solve it.
4) Cross-platform support is not PowerBI’s strongest point, however in the case of Tableau it’s an excellent competitive advantage.
5) The functionality and capabilities of the tools are certainly at the highest level, and in some points, PowerBI wins.

Have a look at our dashboard reviews in Tableau and other BI tools.

Tableau Dashboard Overview

Fri, 06 Nov 2020 12:07:00 +0300

In the previous article, we focused on the problem statement, designed a layout, shared our goal to build a Tableau Dashboard for Superstore dataset. The dashboard should provide insights on most profitable regions, products, customer segments and estimate key performance indicators (KPIs) over the past time.

The data in SuperStore Sales reflect sales and profit of the retail chain in Canada. It includes information about customer orders, refunds, sales and geodata. But we’re mostly interested in sales data, as our main goal is to create an executive dashboard to understand company’s operating margin, find most and least lucrative product categories, and customer segments.

So here’s how the dashboard looks like:

All dashboard elements are placed into containers, we can easily resize or change their hierarchy, this enables to optimize the dashboard and make it more mobile/tablet friendly. We can also filter the data by time periods and choose a specific month and year in the top right corner, and all the charts will be redrawn automatically.

The next field shows key factoids on the company performance: profit, sales, orders count, average discount, customers and sales per customer. Each of the indicators displays YOY, a statistical measure to evaluate a company’s financial progress over time. If the indicator shows positive change, a green arrow will be added, if negative – red.

Below are two core charts, displaying regions (colored based on profit) and profit dynamics. We can click on a specific one to view its stats in-depth.

The green dot on the right chart represents data for a selected month this year, while the blue dot displays the same month last year. When hovering these points you can see a trend line, that facilitates assessing how the company’s doing today.

Let’s move to the second part, here we placed company’s products and customers onto 3 charts. The first one, starting from the left, called bar in bar chart, where you can easily explore product efficiency. For instance, Tables is one of the most inefficient categories, with Breford CR4500 that resulted in significant losses.

Bar in bar chart implementation

Then goes the chart with company’s customers, by default they are sorted in descending order by profitability. The chart is linked with Top Performing Provinces, so if we want to discover best or worst customers for the selected province, the data will be redrawn automatically.

Dashboard Evaluation

We evaluated this dashboard according to the criteria below. On a scale of 1 – 10, 10 being the highest, it gets the following scores from our team :

Meets the tasks – 10,0
Learning curve – 5,5
Tool functionality – 9,0
Ease of use – 8,5
Compliance of the result – 10,0
Visual evaluation – 9,7

This Tableau Dashboard scored 8.8 out of 10 from the team! In our perspective, the dashboard fully meets the requirements and facilitates understanding of business performance over a reporting period. We can assess profit dynamics in general or for the selected region, and effectively leverage products and customers data in measuring monetary results. The final version is available through this link.

Please let us know your thoughts in the comments down below, how would you rate this dashboard?

Animating sports data in Tableau

Wed, 28 Oct 2020 09:45:36 +0300

Previously we shared how to visualize your sports data from the SwingVision app in Tableau , using custom background and shapes. This time we are going to animate our dashboard to watch how landing locations of tennis shots changed over the match. Such an animation can be exported into a video file for later use. That’s what our result looked like in Tableau earlier:

The chart shows landing coordinates of tennis shots on the court. Forehand shots are marked in red, backhands are in orange, the x marks for shots went into the net. We can also use filtering and get expanded tooltip info on hover.
Tableau enables us to create pages to flip through members of a field, changing and animating the analysis. In this case, all we need to is simply drag-and-drop the Shots table to the Pages shelf and click on the Play button.

Let’s switch to the dashboard and try adding the Pages shelf, just click on Worksheet -> Show cards and apply to the current page.

Next, create a new vertical container, drag the panel and minimize the view:

Now after clicking on the Play button, the first part is done:

If you’re a macOS user, it won’t be a problem to make a video from this animation by pressing ⌘ + Shift + 5 and choosing a specific part of your screen. In other cases, you may need to download third-party software for screen recording.

Custom visualization of sports data in Tableau

Fri, 23 Oct 2020 14:28:53 +0300

Being a tennis fan, I recently discovered a new app created to help players to assess their game skills – SwingVision. The app can recognize tennis shots in real-time and display its landing coordinates. The author of this app is Swupnil Sahai, currently he is a Lecturer at UC Berkley.

My tennis stats, shown by the app

SwingVision also allows you to view your “rallies” and specific shots, assess the average shot speed and error rate. Moreover one can easily export its stats as an Excel Table.

Example of exported table

In today’s material, we are going to create a custom Tableau chart that would reproduce stats from SwingVision and display the landing location of my shots on the court. First, we need to find a suitable tennis court image (top view), like this one.

Next, we need to import the data stored as an Excel Table into Tableau, set values for both coordinates using the Shot Placement (x), and Shot Placement (y) columns, and remove the aggregation of measures to get something like this:

After filtering shots by player the chart somewhat resembles the upside-down version of the actual image:

To reverse the image, we need to change the values of current x and y from positive to negative by creating new measures, add some color and everything will start to line up:

The X marks on the chart represent all shots that hit the net, we can hide them from view and set a constant value for Y =- 11,89, which corresponds to the length of a half-court.
Then when we try adding the background image, however, this will cause a warning, because the image is not scaled properly:

This means that we need to calculate the ratio of our image to the real-size court. In our case, for instance, the image is 913px in width, while the court itself is 10.97 meters wide, so by calculating 913 over 10.97, the ratio for x will be 83.227.

The middle of the court will be considered as the origin (0, 0), and will divide the court vertically into halves of 456.5px.
Remember that the image itself has margins, both to the right and left that are equal to 143.3px each. Just create new measures for x and y, substituting with the following values:

After these steps, our image should be as follows:

As finishing touches, we set a custom icon for each point on the chart and add filtering options.

To sum up, the dashboard displays everything we need: landing location of shots, their speed, types of strokes and expanded tooltip info on hover:

Defining a problem statement for Analytical Dashboard

Wed, 14 Oct 2020 15:59:31 +0300

In our previous post, we announced the beginning of a new series about modern Business intelligence (BI) tools. As the adage goes, “problem first, solution second” – today we’ll start by defining our problem. Let’s consider a fairly common scenario for a large company, one that almost every company, where I happened to work encountered with. Suppose that a top management team holds monthly meetings to review the results of the past month. Their key goal is to maximize the company’s dividends and profits.
Hence the team needs a tool that would display the historical profit trend with some other key indicators for the reporting period. The tool is needed to understand where and how profit is formed, and what are the main drivers for profit growth. We suggest using an analytical dashboard as such a tool.

Problem Statement

Our goal is to design and create a Dashboard using the Superstore Sales data (which is really close to reality) to provide answers to the following questions:

What are the performance indicators values for the past month? It’s necessary for stocktaking and comparing it against the same period last year.
What key factors do affect profit growth?
What categories, subcategories, products and clients generate more profits, and what ones that bring losses?

Reviewing Data

The data contains information about customer purchases (Orders list) and returns (Returns list). The purchasing data includes all available information on orders: record ids, order dates, order-processing priority, number of items, sales and profit margins, discounts, shipping options and prices, customer data, and other useful information. But are only interested in the Orders list.

Snippet of the Orders list

Designing a Layout

We’ll position the header with a brief description on top of the page. Then, goes the time-based filter on par with the header. And the subheading “KPI” on the next line.

First of all, we want to generalize key changes according to the factoids:

Profit and YoY growth
Sales and YoY growth
Orders count and YoY growth
Avg Discount and YoY growth
Number of customers and YoY growth
Sales per Customer and YoY growth

Below will be a graph presented as a tree-like map (or equivalent) with top regions by sales count. It will be comprised of different rectangles, the size will correspond to sales volume while the color to profits made. This brings more clarity and helps understand which regions are most effective. It would be great if the reviewed BI tool would provide expanded information upon clicking on a region so that we could see the difference between regions.

More to the right will be a graph with a historical profit trend, displaying how profits change over time. We will try to dot the reviewed month and the same month last year to trace a trend.

Next is products and customer segments. The horizontal bar chart on the left side will be displayed sales volume and profits arranged by categories and subcategories. And try adding a filter for top product names by profit if the BI tool functionality allows so.

Learn more about how to build an interactive waterfall chart

On the right is a horizontal bar chart with top products sorted by profit
volume.

On the bottom of the page, there will be a horizontal bar chart displaying most lucrative clients. It’s very similar to the previous one, but instead of product names will be shown names of customers grouped by their segment and amount of generated profits.

To sum it up, our dashboard layout will look something like this:

Dashboard draft layout

Guide to modern Business Intelligence Tools

Fri, 09 Oct 2020 08:57:38 +0300

In our new series, we will try to give a detailed representation of several BI tools using the SuperStore Sales dataset. The data in SuperStore Sales reflect sales and profit of the retail chain in US dollars.

In the upcoming blog post, we will discuss a real problem statement that could arise when creating a dashboard based on the SuperStore Sales data and design a functional layout to provide clear answers. Throughout this task, we’ll stick with a predefined set of colors to make the comparison more unbiased.

Next, we’re going to create a dashboard that would assist in data-based decision-making with each of the BI tools. We also plan to involve industry experts to learn from their experience.

A complete list of BI systems and tools to be tested in our experiment is provided below. I want to welcome everyone who is willing to help us in solving this challenge to message me on Telegram – @valiotti. I will be glad to hear from you. Although it’s a non-profit project, it’ll be really useful for the open-source community.

We plan to cover the following list of tools:

Free Open Source:

Metabase
Redash
Apache Superset
Dash / Plotly

Free Cloud-Based:

Google Studio
Yandex Datalens

Paid Cloud-Based:

Mode
Cluvio
Holistic
Chartio
Periscope
DeltaDNA
Klipfolio
Count.co

Paid:

PowerBI
Tableau
Looker
Excel
Alteryx
Qlik Sense
Qlik View

The final goal is to evaluate the BI tools against the following criteria:

learning curve of BI tool (1 — too hard to learn, 10 — easy)
tool functionality (1 — very poor functionality, 10 — multifunctional)
ease of use (1 — very inconvenient, 10 — super convenient)
compliance of the result (1 — far from the designed layout, 10 — too close to the designed layout and objective)
visual evaluation (1 — poor appearance, 10 — great visual appearance)

An integral weighted score for each tool will be calculated based on the internal estimates.

The results will be posted to our Telegram channel @leftjoin_en and followers will also be able to share their thoughts on the experiment.
By the end, each tool will be represented as a point in the plane, which will be divided into 4 parts.

This article will be updated with links and ratings as we new posts come out.