Simplifying Business Intelligence

Level 1 is the entry point for data and the simplest area in the Data Warehouse. The concept of Level 1 will largely be explained through examples, however there are a few concepts to keep in mind before we start:

• Level 1 is for new, incoming data only. No history is maintained in Level 1. Once the data is pulled into the Level 2 area, the data in Level 1 is truncated. This aligns to the purpose of Level 1: Get data from the source systems as quickly and easily as possible.
• The tables are data source organized. By keeping the structure of Level 1 organized similarly to the source systems, it simplifies adding new sources in addition to troubleshooting and management.
• To increase the maintainability, the naming convention within Level 1 should be close to the source. The exception: it is a good practice to prefix the tables within Level 1 to identify the data source. You will see this concept in our examples below.

Example Introduction

The example used here will follow through future posts for Level 2 and the Dimensional Model. This is the foundation for those future posts. For this example the premise is fairly simple:

Our task is to build a Data Warehouse for Thingamajig Inc. which will provide analysis capabilities on orders and shipments. Thingamajig Inc. builds widgets and sells them through the web, sales team, phone, and resellers. All orders except resellers are shipped directly from our outsourced logistics partner. Resellers ship their own inventory. All of the order entry systems are separate systems. The Data Warehouse is the aggregation point for all order data in the company. Shipment data will come from our logistics partner and resellers.

There are a number of challenges we will have to address in our design. However, the majority of challenges will be addressed in Level 2.

Orders Design

We are going to keep the example fairly simple for each order entry system. We’ll only include a customer and product information. Obviously most order entry systems are far more complex, but we don’t want to get caught up in those details.

Web Orders Design

The Web Orders system is the newest system at Thingamajig Inc. There are tables for order header, order line, product, and customer. The orders contained in the Web system are our end customer orders.

Phone Orders Design

The Phone Orders system is the oldest in the company. This one includes tables for order header, order line, item, customer, and address. The orders contained in the Phone system are our end customer orders either taken over the phone or entered from the field sales team.

Reseller Orders Design

Reseller Order data is a standardized text file that we receive from all of our partners. Luckily Thingamajig Inc. was smart by forcing all resellers to use the same file format. The orders we receive from resellers are their orders for product, not the end customer order.

Orders Design Notes

• All of our sources for orders are structured differently
• The terminology used in each is different. For example, web calls our product a product, while the phone system refers to it as an item.
• The Reseller order information is minimal. It does not contain the ship to address. That information is stored in a spreadsheet on the Reseller Manager’s computer.

Shipments Design

Shipments are less complex in that we only have two sources for data: Our resellers and our manufacturing partner.

Reseller Shipments Design

Just like our Reseller Orders, the data we receive is in a standardized flat file format. Thingamajig, Inc. uses shipment information to recognize revenue, so it is critical that this data is accurate. The data we receive from resellers is fairly basic as the resellers manage tracking deliveries to customers, etc.

Partner Shipments Design

Our Partner shipments are provided by an electronic transfer directly to a database. There is more granularity in the data because we follow the tracking numbers to make sure our shipments are delivered to customers.

So there you have it. Designed for speed and maintainability, Level 1 is the most simple part of the Data Warehouse architecture. In our next post which will discuss the Level 2 design, the ideas will come together… so stay tuned!

---

What do you think? Do you have specific scenarios to discuss? Please join in and share your perspectives!

Tune in next time for: Level 2 Design