Simplifying Business Intelligence

So after reading the last post, Data Warehouses vs Data Marts, let’s get one thing out of the way. For the purpose of this blog, we’re going to call our data storage layer a Data Warehouse, while following our design philosophy of solving for the customer — not the definition of what we’re calling it (See the Data WareMart section of the Data Warehouses vs Data Marts for details).

Now, onto our high level Data Warehouse Design overview. There are many different approaches to this topic, almost to the point of passionate debate of “Great Taste…Less Filling!” As with anything, your needs will depend on the specifics of the project. The design we’re talking about below has a number of advantages for the majority of projects.

Before we discuss the benefits of this design pattern, let’s understand the above diagram a bit.

• DB/XML/EDI/Flat File: Mix and match the various data sources that are required to meet your customer’s requirements.
• Level 1: Data source organized drop zone for data coming from the various data sources.
• Level 2: Subject area organized data store used as the launch point for downstream systems (which includes our Data Warehouse and more).
• DM/Cubes: DM = Dimensional Model. Dimensional models are a special database design which is tuned for reporting and analytical systems. Cubes are precalculated views of the dimensional model.
• User Interface: The access point to the Data Warehouse for our customers.

Layers of Isolation

A close friend shared the following quote with me a few years ago:

The only thing constant in life is change. -François de la Rochefoucauld

I cannot stress this point enough. The business climate today is under ever increasing pressure by a pace of life that is moving faster than ever before. The result is continuous churn in products, services, and processes for a competitive edge. For the lucky teams building systems with tight deadlines and budgets, our designs must be flexible and adaptable to whatever is coming next. For traditional data warehouse designs, change can be a real problem. Using Layers of Isolation can ease the pain.

Layers of Isolation is accomplished through the organization and purpose of the data at the various stages of the Data Warehouse. Going back to the Level 1 definition, we know the data is source organized. This allows us to deal with new data sources, logic changes, or new functionality from our source systems without disturbing the rest of the downstream Data Warehouse. Once Level 1 changes are implemented, Level 2 and Dimensional Model changes can be subsequently implemented. The development effort can be executed in parallel for speed or sequentially to minimize system downtime.

Organized, Simplified, Standardized (OSS) Data Store

Level 2 is our organized, simplified, standardized data store. Traditionally Data Warehouses serve as a tool for reporting, but our approach is to also provide a data store for the company. In larger companies it is not uncommon to have multiple systems that do the same or similar functions, with no cross communication. The business does not care about disconnected IT systems, just a unified view of data. There is also more than just multiple systems. Maybe your business sells through a Direct and Retail channel and the business logic in the order and shipment systems is radically different between the two. Our goal is to standardize so that regardless of business logic, data source, terminology, language, currency, time zone, etc. we have a unified view of the company’s data. Think about the benefits of having a data store for all of our business critical information in one place. There will be a number of projects beyond our data warehouse that will benefit tremendously. Clarity is important, so let’s define Organized, Simplified, Standardized:

• Organized: Bring together similar data into one place that is easily identifiable and joinable. Joinable is important if we want the ability to match Orders with Shipments for example.
• Simplified: The source systems may use complicated or arbitrary column names. Simplified uses an easily identifiable meaning from the name. If Attribute14 is actually Delivery Date, well then, call it Delivery Date. Another simplification that happens is denormalization. Most transactional systems normalize data. It could be that order information is spread across 10 tables in the source systems. Our data store simplifies this by appropriately consolidating data together into fewer tables (optimally 1 or 2 in our Orders example).
• Standardized: Different business processes and source systems may not use the same terminology. For example, one system might say Promised Delivery Date another may use Estimated Delivery Date. From a business process perspective, what both actually mean is the date we anticipate our customer receiving their order. We might just call this field Expected Delivery Date in Level 2 to standardize the meaning. There may also be cases when business logic will be applied to data. Level 2 is the place to do it once so data is standardized.

As a final benefit of OSS (Organized, Simplified, Standardized), we can do thorough quality assurance on a large chunk of the business logic. This helps us deliver consistent and tested data to downstream systems.

Development Speed

The layers of isolation naturally lead to a faster development cycle. Once the initial design of each layer is completed, developers can work in parallel to complete each section. In a traditional Data Warehouse design, there is typically only one level before the dimensional model, known as staging. In our design, you could say we have two staging environments. This is true and yes, there is additional development work required. We touched briefly on the Level 2 OSS benefits. If another project needs Orders, Level 2 is the place to go. No need to understand the 10 different orders systems in the company. This advantage significantly boosts development speed for downstream applications and more importantly, new Dimensional Models in our Data Warehouse.

Example Time

You have a company that sells widgets through retailers, along with phone, and online orders. Unfortunately, the IT systems used for each of these sales channels do not communicate with each other. A number of problems are created from this. There is no single view of customers, orders, shipments, revenue, marketing effectiveness and more. What is worse, the company is massively expanding internationally and setting up complete new IT systems in each country. The CEO wants a dashboard to understand the health of the business. In our Level 1 layer, we might have tables such as:

• canada_phone_order_header
• canada_phone_order_line
• canada_online_orders
• canada_online_customers
• canada_retail_order_header
• canada_retail_order_line
• canada_customer
• mexico_online_orders
• mexico_retail_order_header
• mexico_retail_order_line
• mexico_customers
• australia_phone_txn_orders
• australia_customer

The above represents the various IT systems and their deployments to different geographies. You’ll notice the inconsistencies across systems and even within countries. Once in Level 2, we’ll have:

• Orders
• Customers

Look good? Within Level 2 we have merged all of the order and customer information into OSS Orders and Customers tables. Next time a new country is brought online, we just add the appropriate Level 1 tables and logic to translate to our OSS Level 2 Tables. All of that without any changes required from Level 2 to our Dimensional Model.

While this is a highly oversimplified example, hopefully it wets your appetite for the deeper discussions on the design layers. At this point, you probably have more questions than answers. Over the next few posts we’ll cover the various layers in detail.

---

Do you see the value of the design approach? What would you do differently? Please join in and share your perspectives!

Tune in next time for: Level 1 Design Details