Purchasing a cappuccino from a coffee shop generates lots of data;

• The date and time the purchase occurred

• The type and size of the beverage

• The cost of the transaction and the VAT amount

• The coffee shop where the purchase took place

• Was the coffee for "takeaway"?

• Did the customer use a reward card?

• Was there a promotion (i.e. buy one coffee get a second half price)

• The payment type

• The barista who fulfilled the order

Several systems will have directly influenced this transaction;

• The cashiering system used by the barista to carry out the transaction

• If a credit or debit card was used, the card machine system used to withdraw the monies from the customer's account

• A separate system may have been used to capture details of the reward card

Also, several back-office systems may hold further information indirectly related to this sale;

• The payroll system and HR system containing salary and shift patterns for the barista who carried out the transaction

• The inventory system for the purchase of the coffee beans, milk and plastic cup used to create the beverage

• The asset system containing details of the coffee machine that made the cappuccino including details of the coffee shop where the customer purchased the drink

Multiple systems may be at play for something as simple as ordering a coffee.

From a transactional perspective;

• the cashiering system doesn't need to know the asset system exists

• you wouldn't expect the HR system to talk to the card machine system

• the asset system doesn't need to link to the inventory system

But from a reporting and analytics standpoint knowing;

• the exact details of the shop the purchase took place

• knowing if specific shift patterns are impacting the accuracy of manual entry of the transaction amount into the card machine

• which type of coffee machine is consuming the most beans

This information may be essential for management reporting.

So, in the data warehouse, how do we integrate data from all of these disparate systems?

There are several business concepts identifiable in the purchasing of a coffee, such as;

• The date and time of the purchase

• The customer

• The employee (barista)

• The coffee shop

• The payment method used

• The reward card

• The product (beverage)

• The asset (coffee machine)

• The promotion

• The ingredients that went into making the coffee

We can uniquely identify the records relating to the business concepts via their business keys. For example, the business key for;

• The credit card is the long card number

• The employee payroll number on the HR systems or their login ID on the cashiering system

• The coffee machines asset number

• The coffee shops postcode and shop number

We can integrate data from across the disparate systems via their business keys and map relationships between the business concepts to obtain a complete view of the transactions or business processes. For example;

Mr Hill (customer)

purchased one takeaway grande cappuccino (product)

from 23 Highstreet, Lower Town, PB12 1RS (coffee shop)

on 11 November 2019 (date) at 17:03 (time) for £4.25 (85p VAT).

He paid with credit card 1234-4567-1234-2222 (payment)

and received 130 reward points (reward).

There were no discounts (promotion) applied to this purchase.

He was served by Debby – 1234567 on afternoon shift 4, who is on pay grade 2 (employee).

She used African Fairtrade beans and semi-skimmed milk (ingredients)

with Mastrena High-Performance Espresso Machine inventory number 454351 (asset).