Analysis Report Two - Systems Integration and Decision Support

Executive Summary

More data can create just as many challenges as it does benefits. Without a strategy for integrating and managing information, organizations may struggle to make use of their data to support their goals. The Northwind visualizations showed how integrated data can help organizations better understand customer demand, shipping activity, and product pricing. The research also emphasized how Business Intelligence (BI) and Decision Support Systems (DSS) help organizations turn integrated data into useful information. For Northwind, this could include implementing a BI dashboard, making integrated information accessible to frontline employees and using predictive analytics to anticipate customer demand and spot trends in pricing or sales.

Introduction

Last week, the focus was on finding the right balance between offensive and defensive data strategies based on an organization’s industry, goals, and regulations. This week’s readings build on that idea by shifting the focus from managing data to managing information systems. As organizations continue adopting technologies such as cloud computing, AI, and blockchain, just having access to more data or the newest technology doesn’t guarantee better decision making. The reading When it Comes to Data, Sometimes Less Is More emphasizes this idea by explaining that more data isn’t always better if organizations don’t know how to use it strategically. Instead of gathering as much data as possible, the goal of information systems is to integrate data from different areas of an organization and turn it into meaningful information that helps employees make better decisions. Organizations do this through a variety of information systems, each designed to help employees at different levels of an organization. Information is first collected through Transaction Processing Systems (TPS) which are designed for frontline employees and record everyday business transactions. Management Information Systems (MIS) then use that information to create reports that help managers make sense of the data and make informed decisions. The highest level is Enterprise Resource Planning (ERP) which integrates information from across the organization into one centralized system. This gives senior managers and executives a complete view of the organization by bringing together information from every department.

Outside Research

In Information Systems for Retail Companies: Challenges in the Era of Digitization, Shütte explains how retail information systems are changing as organizations continue adopting new technologies. Instead of treating each value-added step of the business separately, Shütte explains that retail information systems should connect information and processes across all value-added steps, from production to the customer. By doing so, different areas of the organization can share information more effectively, helping managers make more informed decisions and support the organizations goals. As organizations continue adding new technologies and building new systems, making sure those systems work together becomes more difficult which can make it harder to share information across the organization (Schütte 2017).

Similarly, Alasiri and Salameh explain that the retail industry operates in a highly dynamic and competitive environment where organizations rely on increasing amounts of data to better understand and predict consumer preferences. In their exploratory study, the authors explain how Business Intelligence (BI) and Decision Support Systems (DSS) help retailers use that data more effectively to support better decision making. BI converts data from multiple sources into meaningful information, while DSS allows decision makers to evaluate different business scenarios using tools such as what-if analysis and goal-seeking analysis. By utilizing these tools, retailers make quicker decisions, better understand customer behavior, improve reporting speed, and monitor business performance in real time. Despite these benefits, the authors mention that challenges such as data management, limited funding, lack of expertise, training, and user acceptance can affect the effectiveness of BI and DSS if they are not properly implemented (M Alasiri and Salameh 2020).

Data Visualizations

The first visualization makes it easy to compare the number of customer orders across different countries using data from the Customers and Orders tables. The results show that the United States had the highest number of customer orders, followed by Germany, France, and then Brazil. This allows Northwind to quickly see which countries have the most orders and where the demand is highest. Adding the Shippers table also allows Northwind to compare which shipping companies are being used in each country, providing even more information that could be useful when comparing shipping activity.

Visualization One - Two Table Join

SELECT Customers.Country, Orders.OrderID, Shippers.CompanyName
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Shippers
ON Orders.ShipVia=Shippers.ShipperID

ggplot(data = myquery1,
       aes(y= Country,
           fill=CompanyName)) +
  geom_bar() +
  theme_minimal() + 
  labs(
    title = "Customer Orders by Country",
    subtitle= "Grouped by shipping company",
    x = "Number of Orders",
    y = "Country",
    fill="Shipping Company",
    caption = "Source: Northwind Sqlite"
  )

The second visualization compares the prices of products sold across each category using data from the Products, Categories, and Order Details tables. The graph makes it easy to tell the categories that have similar price ranges, other than the Meat/Poultry category which has a bigger range of prices. The graph also shows a few price outliers, specifically in the Beverages category, where one product is priced much higher than the rest. By using this visualization Northwind has an easy way to spot pricing patterns and the specific categories that may need a closer look.

Visualization Two - Three Table Join

SELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID
INNER JOIN "Order Details" AS od
ON Products.ProductID = od.ProductID

ggplot(data = myquery2,
       aes(x=UnitPrice,
           y=CategoryName)) +
  geom_boxplot() +
  theme_minimal() + 
  labs(
    title = "Prices of Products Sold by Category",
    x = "Unit Price",
    y = "Category",
    caption = "Source: Northwind Sqlite"
  )

Recommendations for Industry

Firstly, I would recommend that Northwind implement a BI dashboard that combines customer, order, shipping, and product information into one place. This way managers have a quick snapshot of customer demand, shipping activity, and product pricing, allowing them to make more informed decisions using integrated data. Northwind should also empower frontline employees by providing them with BI tools that summarize that data. Having this information at hand, would allow employees to more quickly assist customers and identify any changes in shipping activity or product pricing that should be brought to management’s attention. Lastly, as Northwind continues collecting more data on customer order history and product data, predictive analytics could be used to anticipate customer demand and monitor pricing trends.

References

M Alasiri, Mohanad, and Anas A Salameh. 2020. “The Impact of Business Intelligence (BI) and Decision Support Systems (DSS): Exploratory Study.” International Journal of Management 11 (5).

Schütte, Reinhard. 2017. “Information Systems for Retail Companies: Challenges in the Era of Digitization.” In International Conference on Advanced Information Systems Engineering, 13–25. Springer.