One reason why more businesses are not building a Business Intelligence Solution

I have been asked many times by my clients and by peers why do you think more business are not implementing a business intelligence solution. It is a great question and I am sure many executives, directors, and managers have discussed this in reference to their own businesses and have their own reasons why a business intelligence solution never got started.

One of many reasons is there is no understanding of data that they are collecting. I have found that some of the businesses that I have dealt with do not understand what data they are collecting, why are they collecting that data, where that data is being stored, and when they are collecting it. To the what data, before business start to collect any data from any source (ERP, CRM, financial, web site, etc), I think they have to ask themselves are they collecting the right data? Remember that data can and should be analyzed to make sure your business is not only going in the right direction but help maintain that direction in the future and shift or pivot if need be. Asking solid questions is one of the primary ways of collecting data. As author Edward Hodnett noted, “If you don’t ask the right questions, you don’t get the right answers.” Questions asked in the right way often point to its own answers. Asking questions is the A-B-C of diagnosis. “Only the inquiring mind solves problems.” You are probably saying to yourself that is easy. Well it is easy but like everything else if you do not keep practicing your skills in asking questions you will never get better.

Like most businesses their primary business is most likely not collecting data. Sure, great questions are being asked and are being pinpointed to the problems that your business is trying to solve. You will find your data will be more useful and meaningful to what the mission the business is trying to accomplish as well deriving actions from a business intelligence solution. Identifying all the reasons why a business needs data upfront will not only help with the reporting of that data but significantly with the data collection. If you understand fully all the factors such as location, time, internal and external resources, vendors, etc. you will have the WHY knowledge of the data points that are important to the business and the data you will be collecting will be extremely valuable. The last thing that a business wants to do is to repeat the data collection process over and over because they have failed to think about the data factors in the beginning. Not only will this add time to obtaining valuable information but the data that you have been collecting might have pointed the business in the wrong direction.

Once you have defined the factors, developed the questions, and you are now collecting that data, where are you going to store it? Say you are a business that is collecting data from multiple applications which are important to your business such as custom web application, a ticketing system that helps customer service, and you have some sort of external data coming into a SFTP server. Each one of these applications are storing data in their own way whether it is contained in a data repository or in a flat file. Yes you can extract information from each one and get some type of analysis out of each source of data that can help the business but think about the insight you can get if you could join the data. Each one of these applications or process is a silo and does not know the other exists. Let’s take for example that your custom web application is used to manage projects and crews. Then you have data feeds from an external data source that has data from vendors related to this project. Your project manager looks at one set of data points from the web application and another set of data points in a spreadsheet application. That project manager then has to merge the data manually in some way like a spreadsheet. Moving the data is usually done by copying and pasting the web application data into another tab of the spreadsheet that they are viewing the vendor information from. Once they are done with that they now create another tab to merge the data points they want to see together. Then they will create another tab for graphs and calculations. Think about all the disparate source systems that you have in your network and then picture yourself repeating the above process in spreadsheets over and over. It is error prone, it is not scalable, and it is unmanageable. If that information is invaluable to the business then you need to kick start your business intelligence solution and then watch your business get meaning from that information with one simple point and click of a mouse. I can tell you one thing that a business does not want and that is having each one of their employees being their own data collector (their own single source of truth) because the business as a whole will not be able to identify any of the areas of the business’ shortcomings as well as the areas that they are excelling. The business is flying blind and are making decisions without seeing the full picture or the wrong picture.

SQL Right Joins

RIGHT JOIN Keyword

Since I did LEFT JOIN the other day, I figured I might as well do a blog on the Right Join to keep it fair.

Define


In SQL the RIGHT JOIN keyword will return all records from the right table (table b), and the matched records from the left table (table a). The result is NULL from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_names(s)
FROM table_a
RIGHT JOIN table_b ON table_b.column_name = table_a.column_name

Instructional


In this instruction we will use two tables to demonstrate using RIGHT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM Order
RIGHT JOIN User ON Order.User_Id = User.Id
ORDER BY User.Name DESC;

SQL Left Joins

LEFT JOIN Keyword

I wanted to write a quick blog on LEFT JOIN because I get so many questions about this topic from young DB Admin’s and developers as well as Business Managers.

Define


In SQL the LEFT JOIN keyword will return all records from the left table (table a), and the matched records from the right table (table b). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_names(s)
FROM table_a
LEFT JOIN table_b ON table_a.column_name = tableb.column_name

Instructional


In this instruction we will use two tables to demonstrate using LEFT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM User
LEFT JOIN Order ON User.Id = Order.User_Id
ORDER BY User.Name DESC;

Your Single Source of Truth

Purchase The Kindle Edition
Purchase The Paperback Edition
Your Single Source of Truth is a quick-read for busy business and IT professionals struggling to create a Business Intelligence solution. Packed with advice, proven methods, and real-world uses cases, this book provides the knowledge to get you not only started but to keep your Business Intelligence solution going.

This book is intended to help you understand how a business can deal with their epidemic data problems and see a bigger clearer picture from the data they are collecting. There are mountains of data being collected in many different departments each with their own transactional system (silos). And each silo is not being joined to give a bigger and clearer picture to the business. This is a data centric world and businesses are collecting and saving data at an enormous rate but most are doing nothing with that data. They are not learning from the data and not making actionable and informed decisions from the data.

Business Intelligence and silos of data is not just a small business issue — it’s an issue that all different size businesses are facing and are having problems getting their arms around. Whether it is lack of resources, low priority, or a lack of understanding that there is a problem. I believe if I can explain the issue, analyze it and point companies in the direction in solving their Business Intelligence issues then I would get to see many businesses grow and flourish. I want to help businesses answer those questions that I believe every business wants to answer: How is my business doing right now? How is my business doing compared to how it did in the past? Are all my areas of my business performing well? Which areas can have better efficiencies? What are my customers thinking and how can I better serve them? This is just a very small sample of questions that I know a business intelligence solution can help businesses answer and this book will help get you started.