Updated: Aug 29
I will sometimes provide a quick summary at the beginning of my blog posts so that you can decide if this article is for you or not.
Follow these steps in this particular order to build the best business intelligence system ever ...
Write down your KPIs (and Metrics) in the KPI Library and next to each KPI write down the formula and the description (+ abbreviation). Limit them to 30 or 50 max at the beginning.
Choose your BI technology and tools for Data Integrations, Data Warehouse, Data Modeling, Data Visualisation
Bring all your data into your Data Warehouse at the lowest granular level for each data source
Apply the formulas from the KPI Library to your data
Visualize the modelled data according to the best practices of Data Visualization
Add Data Validation and Monitoring to ensure data remains accurate, timely and consistent
Add Machine Learning and Data Science into the mix to forecast and predict
Improve and iterate
Do you want a more detailed guide on how to set up the best Business Intelligence system? Fantastic, let's dive into it 💪
This guide is for
Company founders with technical aptitude who want to know what drives their business
For data teams who already have some Business Intelligence system and want to improve
For data teams who may want to create a brand new Business Intelligence system in the future
The Business Intelligence System
This Business Intelligence system is a system designed for automated data processing and I will walk you through it today.
It is the system I have personally set up and improved for over 15 years, removing and changing the parts that were outdated and replacing them with new and better tools to make sure that in the end, I have the best and most optimal design for a Business Intelligence system.
This design is flexible and scalable and works extremely well for small startups with just a few data sources as well as for companies with strict data guidelines and large data repositories, such as Coca-Cola, HP, and Skrill.
Ready to dive in?
Let's do it!
Here is a simple diagram, representing the well-designed Business Intelligence system.
Do you know why this Business Intelligence design is the best one?
It is a design that will grow and scale up with your business
It is not expensive to start with
It is modular. This means you can add, replace, remove parts based on your needs, budget, and technical capabilities
1) Define your KPIs in a KPI Library
Promise me something first ...
Your goal is to set up the right KPIs and have clarity on what they stand for.
Before you jump into data analysis...
Before the investors start seeing those awesome reports...
Just pure management awesomeness...
Defining your KPIs and writing them down may be a time-consuming exercise, but it is well worth it in the end, as it brings all of your teams to a common understanding of what is measured and how it is measured.
This is how I start to build all my Business Intelligence platforms.
Your KPI Library is your North Star for building a BI system.
Let's talk about KPIs and Metrics.
Why not start with building a Business Intelligence platform right away, before having your KPIs Defined?
You could do that. But you might end up wasting time and money.
"The use of KPIs is meant to improve and transform organisational performance." - Pearl Zhu, Performance Master: Take a Holistic Approach to Unlock Digital Performance
This is why I start by putting together a KPI Library first and after that move on with technological and architectural implementations of the actual Business Intelligence system.
If you understand what you want the Business Intelligence system to do (measure your KPIs), it’s much easier (& cheaper) to build the Data Analytics system that does just that - Measures your KPIs accurately and consistently.
This is how I do it...
I put together the KPI library by going to the department heads and management and asking them how do they measure their success. I write it all down in a google doc or Excel. Then I gather everyone for a meeting or two to agree on the formulas for each KPI.
To start, you should not have more than 30 or 50 KPIs to make this task practical. Take the most important and critical KPIs. The one you want to see EVERY day.
My KPI library drafts have usually up to 50 KPIs and metrics and each row contains the KPI short form, the KPI long name, description, formula and sometimes I add a specific department that may be responsible or using it.
You can borrow my Google Doc KPI Library template (no email required)
When creating the KPI Library, I go for quality over quantity. KPI Library is something that always evolves with the organization so no need to get it perfect from the start. As long as you know what you want to measure and how - it is good enough to start with.
2) Choose your BI technology
I call this part "Choosing your Ammunition and Weapons" 😊.
In this section, I will share with you the 5 strategies I am using to choose the technology for the Business Intelligence system.
You will need to choose the technology for:
Data Warehouse (maybe Data Lake)
Programming language for non SQL code snipets
Disaster Recovery and Backup
Continuous Integration / Continuous Deployment
2.1) Current IT Infrastructure
If all of your IT backend infrastructures run on Microsoft cloud, should you choose AWS or Google for your Business Intelligence infrastructure?
I would not.
...unless there are compelling reasons for you to go with Google or Amazon.
Business Intelligence will always have a tight coupling with IT, DevOps, and SysAdmins teams, as well as some (many) backend processes and technologies.
Having it all under the same roof makes it a lot easier to join them together and orchestrate flawless data transportation.
2.2) Skills within the company
Let me explain with an example. If many developers in your company have experience with Python, there is little sense to introduce alternative technologies that only a few people in the company understand.
Very often I see companies hiring their first Data Scientist 🤓 superstar, who comes with a 3-page-long resume and a truckload of references and only works with R for data science.
The company lets that person introduce R for data science, while the rest of the company uses python for other tasks.
That creates a scenario, where only 1 person in the company knows about a key function and there is no backup. 😱
Of course, I understand that there are specialized tools for certain tasks, but generally speaking, there are usually multiple alternatives to choose from.
When multiple people in the company understand a particular technology, there is a cross-pollination of knowledge, sharing of experience, and backup. 🥳🥳🥳
2.3) BI Tech Stack Ecosystem
Look at these results from doing a search for "Tableau" in Linkedin.
What do you see? I see close to a million people worldwide knowing something about Tableau. Many of these people are Tableau developers, users, employees etc.
Some of these people are writing articles, blogs, how-to manuals for many things related to Tableau.
This makes it easier to find answers, fix problems, and hire people who know about Tableau. It also makes it more affordable to find talent.
As the unofficial law of programming language expertise states:
Programming language experts of certain languages are rare
Rarity means value
Value means getting paid more
but you may ask
How can I outperform the competition if I use the same tool as some Data Analysis tools as of them? Should I not use something novel and cutting edge?
The key ingredient here is agility. Having the right tool that can deliver accurate data and informative insights is one piece of the puzzle.
The other piece of the puzzle is YOU 😊
Knowing how to leverage the insights you get from data is key to outperforming your competitors.
When I talk about modularity, I talk about components of the BI system that can be swapped for other components with little impact on the overall performance of the Business Intelligence system.
Let's consider our old friend Tableau as a data visualisation tool. You can use Tableau to connect to any RDBMS database as well as many other data sources. But if you choose to replace Tableau with another tool, for example, SuperSet, which is free and open-source, you can do that without affecting the other parts of your Business Intelligence system.
Sure, you may need to migrate a lot of reports, but this is outside of the scope of this argument for modularity of the Business Intelligence system.
I left cost as the last consideration for a reason. Given the importance of time for any company, it makes sense to have a solid and accurate Business Intelligence system running sooner rather than later.
The real important cost you should consider is TIME, not MONEY 🧐
That does not mean that you should go on a shopping spree and mortgage your house to build the best Business Intelligence system ever! (Although there may be some merit to this argument)
Let's consider a scenario:
your business makes €10,000 per month
that is €60K every 6 months or €120K per year
having accurate, consistent and timely insight from data can help you double your business in 4 to 6 months. Yes, I have even seen 200% to 500% growth in the first 8 months fueled by a smart decision supported by accurate data
a good Business Intelligence system takes 3 to 6 months to build. Sometimes, even longer. And that is if you get it right the first time. Check out some of these posts on Quora. My favourite is: If you want a Data Warehouse with your BI solution (which I recommend), one dashboard with around 20+ KPIs will take you 8 months - 1 year (4 months variance).
remember the triangle of Cost, Quality, and Time from MBA 101?
Assuming you want a high fidelity Business Intelligence system, would you pay €30,000 to have it done in 30 days? I would even pay €50,000! It takes 4 to 6 months to build a good BI system and you are losing €10,000 every month it is being built not having accurate insight!!!
So, based on the math above, a Business Intellgience system built in 30 days is worth at least €50,000! Because your business is losing €60,000 over 6 month NOT having the insights to make smart decisions!
What if your Business makes €100,000 per month?
Are you losing on opportunities to make it €200,000 per month?
Are you wasting money on advertising spending €10,000/month on ads not really sure what is the best channel that bring you the best customers?
I think you get the point... 👍🏻
3) Bring all your data together
Start off your Business Intelligence data modeling process by bringing all of your data together into your Data Warehouse or your Data Lake
Bringing all of your data at its lowest granularity is important for 2 reasons
it always gives your analysts the lowest denominator to which they can drill down to find answers to the business questions
having granular data enables you to conduct analysis on historical data as your business assumptions change
Having detailed data enables the company to use this data as a strategic resource.
Let's imagine that after a certain period of time you will hire a Data Scientist whose job will be to improve your conversion rates.
If the data scientist has access to detailed data from the past, the formulas and algorithms that will be created by that person will be more accurate from the start because there would be detailed historical data on which to build those algorithms.
So you can experience a positive impact on your bottom line right away!
Without having to wait 4 to 6 months for the data to be accumulated upon which the models will later be built.
4) Apply the formulas from the KPI Library
Let's talk about KPIs...
Remember that KPI Library we discussed in Step 1? Well, this is where it comes very handy!
Once you have your detailed data in your staging environment or in your data lake, you will
combine that detailed data with other detailed data
and then aggregate it
and merge it together
and apply calculations that will
eventually, produce your KPIs
do you see it? 🤓
Having a KPI library will ensure that all your departments and teams will understand this complicated process and speak the same language
Make sense, right?
But here is the thing...
You first need to get everybody to agree on the formulas and definitions for the KPIs in the KPI Library.
This is why having a smaller KPI list initially makes sense. Baby Steps!
5) Visualize the data
Visualizing your data is more of an art than a science.
I am a very big fan of telling stories with Tableau. They also have a great blog on how to tell stories with data visualizations done in Tableau.
Data visualization uses shapes and styles to make data comprehensible for the user. Various attributes can be used in order to develop a context for the data.
Graphical Elements This includes, but is not limited to, shapes and sizes of the subjects in the chart. Images can be included in this category.
Typography This includes the type of word representation, such as titles and labels.
Iconography This improves a chart’s overall usability by differentiating categories, defining states, and providing UI controls.
Axes and Labels Proper axes and labels give a good representation of the data that we want to show. Sometimes it is really important to show the axes; sometimes we can hide them, so, in practice, it depends on a use case.
Annotations and Legends This describes a chart’s information, with annotations pointing to a specific part to provide more emphasis.
Please keep in mind about the Data Visualization tools:
Many companies mistakenly think that data visualization tools can be used as a substitute for business intelligence tools. The onus can be partially shared by the tools themselves, which often position themselves as BI products in order to capitalize on the use of these lucrative keywords in digital marketing, The truth is, many of them specialize in data visualization and have minimal strengths in other areas of BI.
6) Add Data Validation and Monitoring
Having accurate data is one of the hardest and one of the most important things to have in a Business Intelligence system.
Because just like building a house, if your foundation is crap, everything you build on top of it will not last.
With poor quality data, all the analytics and data science that you will put on top of that data will not be accurate because the underlying data is of poor quality.
One of my favourite saying that applies to the topic of data more than any other, states:
Garbage in, garbage out
7) Machine Learning and Data Science
I will not spend too much time on Machine Learning and Data Science, except to say that this is where you can really outsmart the competition.
All this magic must be built on a really solid foundation of
Data Completeness (all data)
Imagine what kind of data prediction algorithm you will have if you suddenly realize that you have some data duplication in one of your source tables that is used by the Machine Learning algorithm?
You must have accurate and timely data first before you build sophisticated Machine Learning algorithms on top of it
8) Improve and Iterate
I like to think of the Business Intelligence system as a living and evolving organism that grows and changes as the company grows.
It will change, adapt and grow daily and weekly so think of it as a process, rather than a goal.
Starting right, is more imporant than starting wrong or not starting at all.
You definitely do not want to find out 3 months into your Business Intelligence implementation project that you have been doing something wrong and you need to redo some things.
You would have wasted your time
You would have wasted your money
You would have wasted the opportunities for smart decisions that would fuel your growth
Ready. Set. Go.
May the good insight be with you!
NEED HELP? Get a 30 min 100% Free Consultation with me on how you can build a Business Intelligence system for your business.