End-to-End BI Project: Strategy, Steps, Processes, and Tools [Part-01]

I have come up with an idea to create a series of articles covering a small-scale end-to-end Business Intelligence (BI) Project. I will cover both the business and technical sides along this journey. And I will try my best to elaborate and simplify WHAT, HOW & WHY we are doing these.

Making data-driven decisions requires a bird’s eye view of all the aspects of your business, even those you didn’t think of. But how to turn unstructured data chunks into something useful? The answer is business intelligence.

What is Business Intelligence (BI)?

First thing first, let’s begin with a definition: Business Intelligence or BI is a set of practices of collecting, structuring, analyzing, and turning raw data into actionable business insights. The main purpose of BI is to provide actionable business insights and support data-driven decision-making.

Technical Side (image reference: snowflake)

To address business requirements we will be harnessing technology and determine HOW can we build an end-to-end solution. According to my past experience from the technical end, a BI project can be divided into five phases:

• Data gathering
• Data preparation
• Data storage
• Data analysis
• Data storytelling

For covering the business side, I will be applying the data analytics framework suggested by PwC in this project. This framework will answer WHAT are we going to deliver and WHY are we delivering these. There are four aspects:

• Discovery
• Insight
• Actions
• Outcomes

Business Side

We need a clear picture of what we’re trying to accomplish, how we’re going to accomplish it, and how we know when that has been accomplished. Let’s define “project goal” so that we can start to figure out what our project team needs to do to reach it. A project goal is the desired outcome of the project. It’s what we’ve been asked to do and what we’re trying to achieve.

To do this, we might need to get more information from our stakeholders. Talk to them about their vision for the project. Ask how this aligns with the company’s larger goals and mission. This is the starting point of the discovery phase.

During the discovery phase, we need to define the problem, develop a hypothesis and collect, prepare and explore data. During insights, we will perform the data analysis.

Next comes actions where we will connect the dot. We will conduct data storytelling to link insights to actionable recommendations and an execution plan. Finally, we will review the outcomes that have transformed the business or business unit or the industry.

We will cover the WHATs, the HOWs, and the WHYs for both the business side and technical side along with these phases in this article series.

Project Overview & Objectives

First things first, to set up a project for success and to make our job easier, we want to figure out what needs to be done before we actually get started. We need to define exactly what our goals and deliverables are so that we’ll be able to tell our team members what to do.

Let’s assume that we are working for Super Store. The ideal outcome of this project is to meet the Super Store’s objective of developing a strong data-driven business decision-making culture and systems.

Objective 1: Improved decision-making through easier access to key metrics in the digital channel. Link insights to financial and operational metrics to specify impact and aid decision-making.

Objective 2: Ensure the plans meet long-term business objectives and help refine solutions in the future. Prioritize insights to build actionable plans and provide solutions/recommendations that help Super Store to address future challenges related to their sale channels.

Objective 3: Reduced time spent by the existing data analysis team on repetitive reporting tasks — gathering data and generating reports — manually. Have more time for generating insight and analyzing information.

Objective 4: Consolidating all the data together as the single source of truth which enables the key managers and support staffs to make their data-driven decision through the right support and access to data.

Project Deliverables

Once you have the goals nailed down, it’s time to examine the project deliverables. Project deliverables refer to the tangible outcomes of the project. In other words, a deliverable is what gets produced or presented at the end of a task, event, or process.

Deliverables are usually decided upfront with the stakeholders or clients involved in the project. They hold everyone accountable and are typically a big part of achieving the goal.

For this project, we will deliver the followings for Super Store:

1. Non-deliverable: I will skip engaging with stakeholders as I cannot demonstrate this part. In a real-world project, we have to come up with a project charter, cost-benefit analysis, and proof of concept, and many other project management-related deliverables.
2. Deliverable: In this project, we will start from data gathering and data exploration to understand how they would help in accepting or refuting the key metrics.
3. Deliverable: Design and develop a data collection pipeline (aka ETL Workflow). We will also perform data preparation. This process includes data cleaning data preparation data enrichment and data validation.
4. Non-deliverable: I will not deliver a Data Warehouse for this project. I assume this would require a lot more explanations and that will overwhelm the reader to oversee the purpose of this project. I consider this is out-of-scope for this small-scale project.
5. Deliverable: Convert outputs into user-friendly formats and visualizations that will help stakeholders understand the analysis. Deliver a dashboard.
6. Deliverable: Use actionable data-driven insights to explain the performance and link insights to financial and operational metrics to specify impact and aid decision-making. Deliver a quarterly report.
7. Deliverable: Craft an SOP, data definitions, README, retrospective and user guide, training presentations for closing the project.

Setting Expectations

Before we begin our journey, I just want to take a minute to set some expectations. This article series is designed to get you up and running with a small-scale BI ecosystem with open source tools. I may simplify some concepts and I won't cover some of the advanced topics of these tools.

This article series is suitable for readers who have some basic analytical thinking, problem-solving skills, and curiosity in tools and technology. I may not cover all the foundational knowledge of the tools I will be using in this journey.

Having said that, I am confident that the concepts, tips, best practices covered in this article series will give you an excellent foundational understanding. If you are a master-level user of these tools (or) a senior BI, feel free to skip ahead.

Setting Prerequisites

For this journey, I assume you have the following:

• Download and install KNIME Analytics Platform
• Download and install Power BI Desktop
• Access to a Power BI Service
• Access to a Google account &
• A curious mind

KNIME Analytics Platform is a free, open-source software for the entire data science life cycle. KNIME’s visual programming environment provides the tools to not only access, transform, and clean data but also train algorithms, perform deep learning, create interactive visualizations, and more. Please start exploring this KNIME Quickstart Guide.

Power BI Desktop is a free application you install on the local computer that lets you connect to, transform, and visualize your data. With Power BI Desktop, you can connect to multiple different sources of data, and combine them (often called modeling) into a data model. Please access this documentation “Get started with Power BI Desktop” and start exploring this.

Power BI Service, sometimes referred to as Power BI online, is the SaaS (Software as a Service) part of Power BI. In the Power BI service, dashboards and reports connect to datasets that bring all of the relevant data together in one place.

Key Takeaways

• I strongly recommend you take part in the entire journey. Make the most of the readings and additional resources throughout the program. They’re designed to support your learning.
• Take part in all learning opportunities to gain as much knowledge and experience as possible. If something is confusing, don’t hesitate to reach me.

Congratulations on choosing to take this first step. Enjoy the journey!