Analysis Report Two - Systems Integration and Decision Support

Author

Jessica Garcia

Executive Summary

This report examines how systems integration can improve organizational decision making by connecting information across departments and business functions. Transaction Processing Systems collect day to day operational data, Management Information Systems transform that data into structured reports, and Enterprise Resource Planning systems integrate information into a shared organizational database. This integration can empower employees by providing faster access to complete information, but it can also create problems related to information overload, poor data quality, privacy, system dependence, and biased decision making. Using the Northwind database, this report demonstrates how SQL INNER JOIN statements can combine information from different areas of an organization. The first analysis connects employees with order processing information to examine shipping times, while the second combines product, category, and transaction data to examine discounting practices. The findings demonstrate that integrated data can provide valuable strategic insights, but organizations must ensure that the data is accurate, relevant, secure, and properly interpreted.

Introduction

Organizations rely on different information systems to support operations and decision making. Transaction Processing Systems, or TPS, capture the high volume of routine transactions that occur during daily operations. Examples include customer purchases, payments, inventory updates, order entries, and shipment records. Because these systems support core business activities, they must process information quickly, accurately, and reliably. Management Information Systems, or MIS, transform the detailed information collected by TPS into structured reports. Instead of requiring managers to review thousands of individual transactions, an MIS may provide weekly sales reports, inventory summaries, employee activity reports, or comparisons between actual and expected performance. These reports support tactical decisions by helping managers identify patterns, problems, and opportunities. Enterprise Resource Planning systems, or ERP systems, provide an even greater level of integration. ERP systems connect information from areas such as sales, inventory, accounting, logistics, manufacturing, and human resources through a shared database. This gives employees access to a more complete and current view of the organization. For example, a customer-service employee may be able to view a customer’s order, payment status, inventory availability, and shipping information without contacting several different departments. Systems integration can empower employees by reducing information barriers and allowing decisions to be made more quickly. However, integration can also create challenges. Inaccurate information can spread across multiple departments, employees may become overwhelmed by excessive data, and broader access can increase privacy and cybersecurity risks. Integrated systems can also increase organizational dependence on technology because a system failure may disrupt several business functions simultaneously. Therefore, integration must be supported by strong data governance, employee training, access controls, and reliable backup procedures.

Outside Research

The article “When It Comes to Data, Sometimes Less Is More” challenges the assumption that more information always leads to better decisions. Although additional data may sometimes improve analysis, it can also make predictions less accurate when decision makers give too much attention to weak, irrelevant, or biased variables. Research discussed in the article shows that simple rules based on a small number of meaningful variables can sometimes perform better than complex models using large amounts of information. This argument is especially relevant to integrated information systems. ERP systems can provide employees with access to information from nearly every part of an organization, but access to more data does not guarantee that employees will make better decisions. Reports and dashboards should emphasize the information that is most relevant to a specific business problem instead of displaying every variable simply because it is available. Otherwise, integration may create information overload and distract decision makers from the measures that matter most. The article “Why Most Companies Shouldn’t Have an AI Strategy” provides another perspective on organizational technology. They argue that many companies are not prepared to implement effective AI strategy because they have not completed the necessary foundation work. Common problems include poor data quality, outdated systems, technical debt, disconnected processes, weak governance, and limited employee knowledge of AI. AI depends on reliable and properly integrated information. If data from sales, inventory, finance, and operations is inaccurate or inconsistent, an AI system may automate and expand those problems instead of solving them. The article also argues that AI should not be separated from the organization’s broader technology and business strategies. Organizations should first improve their data, infrastructure, processes, employee capabilities, and responsible use safeguard. Together, the readings show that technology creates value only when organizations use accurate, relevant information and connect technology decisions to actual business needs.

Data Visualizations

The following analyses demonstrate how information stored in separate Northwind tables can be combined through SQL INNER JOIN statements. The first visualization uses a two-table join, while the second uses a separate three table join. Both analyses demonstrate how systems integration can create insights that would not be available from an isolated table.

:::

Visualization One - Two Table Join


SELECT Employees.LastName,
       julianday(Orders.ShippedDate) -
       julianday(Orders.OrderDate) AS ShippingDays
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate NOT NULL
ggplot(data = myquery1,
       aes(x = ShippingDays, y = LastName)) +
  geom_boxplot() +
  theme_minimal() + 
  labs(
    title = "Distribution of Shipping Times by Employee",
    subtitle = "Days between order placement and shipment",
    x = "Shipping Time in Days",
    y = "Employee",
    caption = "Source: Northwind SQLite Database"
  )

Data and SQL Explanation

This analysis combines information from the Employees and Orders tables. The Employees table provides the last name of the employee associated with each order, while the Orders table provides the order date and shipped date. The two tables are connected through the shared EmployeeID field. The INNER JOIN returns records where the employee ID appears in both tables. The julianday() function converts the order and shipment dates into values that can be subtracted. The calculated field ShippingDays represents the number of days between the date an order was placed and the date it was shipped. The WHERE clause removes orders that do not have a recorded shipment date because their shipping time cannot yet be calculated. The results are stored as myquery1, allowing the SQL output to be used in the R visualization.

Visualization Explanation

The boxplot compares the distribution of shipping times associated with each employee. The line inside each box represents the median shipping time, while the box represents the middle 50% of observations. The lines extending from the box show the broader range, and individual points may represent unusually long or short shipping times. This visualization can help managers identify differences in order processing patterns and determine whether certain groups of orders experience more variable shipping times. the results should not automatically be interpreted as a direct measure of employee performance. Shipping time may also be affected by inventory availability, order complexity, product type, carrier performance, and other operational factors. This example is relevant to modern organizations because a manager could not conduct this analysis using the Employees table alone. The employee information becomes useful only after it is integrated with operational order data.

Visualization Two - Three Table Join

SELECT Categories.CategoryName,
       Products.ProductName,
       od.Discount * 100 AS DiscountPercent
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN "Order Details" AS od
ON Products.ProductID = od.ProductID
WHERE od.Discount > 0
ggplot(
  data = myquery2,
  aes(x = DiscountPercent, y = CategoryName)) +
  geom_boxplot() +
  theme_minimal() +
  labs(
    title = "Distribution of Discounts by Product Category",
    subtitle = "Transactions that received a discount",
    x = "Discount Percentage",
    y = "Product Category",
    caption = "Source: Northwind SQLite Database" )

```

Recommendations for Industry

Organizations should establish shared data definitions and quality standards before integrating information across departments. When information is inaccurate or inconsistent, integration can spread the problem throughout the organization and cause multiple employees or departments to make decisions using the same incorrect data. Organizations should also use role based access controls. Employees should be able to access the information required for their responsibilities without receiving unrestricted access to sensitive or irrelevant organizational data. This approach can empower employees while reducing privacy, cybersecurity, and information overload risks. Managers should design MIS and ERP dashboards around a limited number of decision relevant measures. More information should not automatically be considered better. Reports should be reviewed regularly, and measures that do not contribute to decisions should be removed or placed in more detailed secondary reports. Organizations should monitor operational measures such as shipping times and discounting practices, but those measures should be interpreted within their full business context. Shipping delays should be examined alongside product availability, carriers, customer location, and order complexity. Discounts should be evaluated alongside revenue, order quantities, product costs, and profit margins. Employees should also receive training in data interpretation. They should understand that a relationship shown in a graph does not necessarily prove that one factor caused another. This is especially important when integrated systems make it easy to compare employees, departments, products, or customers. Finally, organizations should improve data quality, modernize older systems, reduce technical debt, and establish responsible use safeguards before making large investments in AI. AI should support clearly identified organizational objectives rather than being treated as the automatic solution to every business problem.

References

Getting your citations out of Google Scholar and into your references.bib file takes just a few clicks.

  1. Go to Google Scholar and search for the paper you want to cite (for example: “What’s your data strategy”).

  2. Look directly underneath the search result for the Cite button (it looks like a double quotation mark ”). Click it.

  3. A pop-up window will appear showing standard citation styles (APA, MLA, etc.). At the very bottom of that pop-up, click the link that says BibTeX.

  4. A new page or plain text block will open showing some bibtex code. Paste that block of text into your [references.bib] file.

If you have multiple sources, just keep pasting them one after another down the file. You don’t need commas between the different articles; just separate them with a blank line to keep it clean. You can then cite them like this (DalleMule and Davenport 2017) and they will automatically appear in the references section.

DalleMule, Leandro, and Thomas H Davenport. 2017. “What’s Your Data Strategy.” Harvard Business Review 95 (3): 112–21.