Descriptive, Predictive and Prescriptive Analytics

Posted on by Paul Kukk

What I have been seeing with all my clients over the last three years is them trying to get their arms around their data, cleaning it, gathering it into a central location which then they typically create dashboards and reports to see how their business did in the past but some are looking at how they are doing right now. So, the way most of my clients are looking at their data is called descriptive. Descriptive data analysis gives businesses insight into the past. Descriptive looks at the data, summarizes the data and then interprets that data into human readable format to give us analytics of the past. The vast majority of the statistics we use fall into this category. (Think basic arithmetic like sums, averages, percent changes). Most often, the underlying data is an aggregate or count of a filtered column of data to which basic math is applied. For all practical purposes, there are an infinite number of these statistics. Descriptive statistics are useful to show things like total stock in inventory, average dollars spent per customer and year over year, or even change in sales.

When I talk about Predictive data analysis I am looking to understand the future. Predictive analytics want to look at the data and then predict what can happen in the future. Predictive analytics want to give actionable information to its owner on what could be coming. Currently there is no predictive data analysis that can give you with a 100 percent accuracy on what the future holds. A business should take and read the results on what might happen in the future and decide on the path based on that knowledge.

These two statistics — descriptive and predictive — try to take the data that you have, and fill in the missing data with best guesses. They combine historical data found in CRM, ERP, HR and POS systems to identify patterns in the data and apply statistical models and algorithms to capture relationships between various data sets. Businesses use predictive statistics and analytics anytime they want to look into the future. Predictive analytics can be used throughout the organization from forecasting customer behavior and purchasing patterns to identifying trends in sales activities. These statistics also help to forecast demand for inputs from the supply chain, operations and inventory.

The last analytic option we will talk about is prescriptive data analytics. Prescriptive data analytics is when you want to be guided on all the possible outcomes. The relatively new field of prescriptive analytics allows users to “prescribe” a number of separate actions to and direct them towards a solution. These analytics are all about providing direction. Prescriptive analytics attempts to quantify the effect of future decisions in order to advise on all the possible outcomes before the decisions are actually made. When prescriptive analytics are at their best it will help predict not only what will happen, but also why it will happen providing recommendations regarding actions that will take advantage of the predictions. With this type of decision analytics, support business should feel comfortable with the actions that they need to take, either staying the course or pivoting to right the ship.

Which analytics does your business need? Does your business need descriptive, predictive and prescriptive data analytics? I believe in order to answer that question the business needs to know how advanced of a business intelligence solution it needs in order to be successful. In understanding how each descriptive, predictive and prescriptive and what questions they can answer for the business will drive the business to implement a simple or more complex business intelligence solution. One piece of advice that I would like to give here is start off with the simple solution and once that solution is providing the information you need, then enhance your business intelligence into a more and more complex solution. I believe taking this approach will give you a much higher success rate of implementing your business intelligence solution as well as a higher user adaption.

To quickly summarize the last three paragraphs, descriptive as we know answers the question of the how it looks at data in the past. We also reviewed predictive where we talked about how it will most likely answer questions on how something might happen. And lastly prescriptive will give you answers to questions on what actions can happen. Depending on your business goals and what answers you need from your data, the decision on if you need descriptive, predictive and prescriptive data analytics is very personal to you and the business.

I think it is important to show you the different levels of human input to draw conclusions from descriptive, predictive and prescriptive as well how each analytic area answers which questions. This will give you a good sense of employee time that will be needed depending on the way you will be looking at your data

SQL Right Joins

Posted on by Paul Kukk

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

Posted on by Paul Kukk

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

Posted on by Paul Kukk
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.
Starting

Getting ready to start a BI Solution?

Posted on by Paul Kukk

Before building out the plan for a bi solution project, I always like to think about what other projects that I have done or know about that are similar to the one I am going to embark on. I always ask myself what issues or obstacles did I encounter in those projects? There are so many reasons why projects get dragged out or just do not succeed, either from the start or they dwindle out if the project exceeds its expected delivery date. Lets just review some and see how many you came across in your professional career on why a project has failed.

 

  • No Executive sponsorship. There is no real buy in from the senior management team, which can lead to many different problems for the success of the project. You have to be persistent here and spend time presenting and socializing how important this project is to them and the company.

 

  • Battle between departments. Believe it or not I have run into this issue so many times when I have investigated why so many companies have failed in implementing a business intelligence solution. So what I mean here is that each department is acting like a ten year old child that does not want to share their data or their process because they believe it is their own secret sauce or they have something to hide.

 

  • No real project plan in place. This is were a company decides they would like to shoot from the hip on this project. They have no real kick off, no steps, no definition of what success is for the project, no anything. They fail to realize that this is a company wide project and the need for a project plan not only holds everyone accountable but ensures the right steps are completed before the next step are taken. It is this mentality that will kill any project almost from the start,

 

  • The IT department wants to build everything from scratch. This can be a problem in a lot of companies were the IT department wants to build everything custom. This can be not only very time extensive but also very costly. I always tell my clients to take the build, buy, or align approach when looking at the different areas of a project. There are several reasons when you should consider building a custom application over buying or aligning: Off the shelf products cannot meet every need, off the shelf products are to rigid, off the shelf products may not be compatible with your existing applications. Now let me give you several reason when you should be thinking of buying an off the shelf product: budget is limited, lack of time, lack of technical proficiency, and technology would not give you a competitive edge.

 

Above are just a few examples why your project could take longer than expected to be completed or just fail. I am sure that you can think of many more or even have experienced many more. I know in my long career as an IT professional I have come across reasons why projects have failed that would make people laugh and then some other reason that would make people cry. I will save those stories maybe for another blog. I think the main reason why I wanted to get you thinking about project failure is so that history doesn’t repeat itself and I am a firm believer that if you can learn from all failures, you will have a more successful future.

Looking at your data in different ways can help your business

Posted on by Paul Kukk

You can look at your data many different ways and it will help your business to look at that data in as many different ways as possible and not just one. We will cover some of the basics here and hopefully it will get your mind thinking about what data you currently have and how you can look at it. By looking at your data in different ways or different perspectives you can obtain different business values.

Grouping

You can group your data. What I mean by grouping is when you have rows and rows of data and you want to bring that data together then grouping logical can help you. Take for instance you have eight to ten sales reps making calls to physicians daily trying to sell them their pharmaceutical drugs. You can group all that data by day for a giving week to show what days the sales people are making the most successful sales calls.

Granular

Looking at your data granular is the processing of driving further down into the details of the data. Let’s take the above example of showing successful sales people calls for a giving week. Well we know that Bob on the sales team has be crushing it since he joined, but looking at the data for daily sales calls he and the other sells team members are placing about the same amount of calls Monday through Friday. Let’s get a little more granular and drill down into each of the days calls and see what time he is making his calls. Now the data is showing us that Bob is making all his calls either early in the morning or late afternoon and all the other sales people are consistently calling in between Bob’s hours. Deriving this types of answers and drilling into the data will give you more answers that will help you guide others in the business to be better.

Visualizing

One of the most important ways to look at your data is seeing it in a visual concept or chart. I feel this is one of the most important ways for your data to tell it’s story. One of the biggest reasons visualizing your data is so important is because people are so visual and seeing data in row or tabular form to understand the data can be difficult and sometimes mind numbing. Showing a data story through a picture (chart and/or graph) allows people to understand the meaning quicker. If there was a significant increase or decrease in products sales or customer service calls seeing that spike in a graph will be quick and easy. If there is bad data in your warehouse a picture will definitely tell that story and you will be able to visually see that very quickly. Data visualization tools have greatly changed how businesses have worked with their data as well how businesses have dug deeper into their data gaining valuable insight. So make sure you ask the question “Can this data be visually representing?” and if the answer is yes build out the data visualization and share it with the business. 

Data Relationship

Can my data be related? I think this question not only does not get asked enough, but it is one of the most powerful ways to look at your data. If your data can be related to other data how strong is that relationship? Even though asking about data relationships is one of the most basic questions in looking at your data it is the most overlooked and the most informative. Let’s look at a simple example: You have a subscription form on your website that allows potential customers to fill out and subscribe to your newsletter. You have two sets of data, one set being the customers information and the other being website. If you look at each data separately you can know who the customer is, from the form information. Looking now at just the website data you can see how an unknown person entered your web site and walked it (What I mean by walking, is what web pages did they visit on your web site). Now let’s join the data (the relationship), Once we do that we know who the person is, how they got to the web site, what they did on the web site, what page they came from before filling out the form, and then what they did after filling out the web form. Now you have a complete picture and you can make some pretty good assumptions about the user. Powerful right. Relating your data will take time and thought but trust me it is definitely worth the work.

These are just some of the ways that you can look at your data and get answers to questions that have been asked and possibly questions that have not been asked yet. Never stopping asking questions and never stop looking at data in different ways.