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

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

Within Business Intelligence, you may be familiar with the terms Data Warehouse and Data Mart. This is an often confused and debated topic. Let’s talk about the differences and when to use each. At the root, both are basically tables stored in a database. Where the differences begin are what those tables contain and how they are organized.

Define: Data Warehouse

Definition of purpose:

A data warehouse contains generalized data encompassing multiple subject areas with deep granularity designed to meet the needs of a general audience.

That is a lot to take in, so let’s break it down.

Generalized Data

Generalized data means a broad selection of data. In many cases, you may only have 80% of the data required, leaving behind highly specialized data. Specialized data is where the data marts come to play, which we’ll talk about later. Generalized data is generic — a minimal set of business logic has been applied. We want to keep this data as flexible as possible for the general audience.

Multiple Subject Areas

Warehouses contain data from multiple systems or subject areas. For example, if your company sells widgets, your warehouse may contain data around sales, purchase orders, product details, shipments, and inventory. All of these subject areas can provide specific data for the business as a whole.

Deep Granularity

Granularity defines the level of details available. For example: every sales transaction is one granularity, while sales by day by product is another. In the latter example, all individual sales transactions are aggregated to a daily level, therefore the detailed transactions are not available. When we say “Deep Granularity”, deep refers to the lowest level of granularity across the board. In other words, every single sales transaction, all shipments, all waybill numbers, etc.

Designed to meet the needs of a general audience

So what is the general audience? In most cases this means the entire company, division, business unit, etc. While designing a Data Warehouse, there are a lot of design decisions to be made. To support a general audience, you will minimize the amount of business logic, aggregation, mapping, etc. Let’s use a sales example to illustrate. For a general audience, we may just provide basic sales data, quantity sold, sale price, customer, and product. This type of data is robust enough to help the general business understand sales. But what about finance? They may want to know far more details around freight costs and COGS. For this scenario, finance might need their own data mart to assist with their analysis.

Define: Data Mart

So if a Data Warehouse is general purpose, then where do specifics reside? The Data Mart of course! Definition of purpose:

A data mart is comprised of specialized data for one subject area with appropriate granularity designed to meet the needs of the intended audience.

Specialized Data for one subject area

Specialized data is the specific data required for the audience. If we are building a sales data mart, there would not be salary data from HR systems. A data mart’s focus is to solve a data analysis problem for one subject area.

Appropriate Granularity

So a data warehouse has “deep” granularity, but a data mart has “appropriate?” Confusing, yes. As with everything, there is no defined rule, but a case by case basis to follow. When talking to end users, granularity is a key area to understand the requirements around. It is possible end users will want all details down to the number of nuts and bolts on widgets, or just how many widgets fit into a box, or how many boxes fit on a pallet.

Designed to meet the needs of the intended audience

The intended audience is who will use this data mart, whether it be finance, the CEO, or report monkeys in the basement. A data mart’s purpose is to meet the requirements of a specific audience that the generalized data warehouse cannot solve. Let’s pick on our friends in finance again. It could be that finance has really wacky requirements, complex business logic, and custom views of the data. We have to understand what they’re solving for in order to effectively design the data mart.

Data WareMart? YES!

I’ve heard variations on data warehouse/mart, which is just a cute way of calling a pig a pig… data barns, data sheds, data box, data universes, the list goes on. This is what happens when architects get fancy. From an internal marketing perspective, if you have to call the technical side of your Business Intelligence solution something, use Data Warehouse. It is widely known and won’t require explanation.

Okay, so I’ve led you on through this entire post to this point. Time for the “Simplified” point of view: forget what you call it or what it is “supposed” to be. The best solutions built are designed with one and only focus: the customer. If you need specialized data combined with deep granularity, then do it. In order for Business Intelligence to impact the business, it must meet the needs of decision makers while being easy to use. The technical design of the database directly impacts both significantly.

---

Are you lost or fundamentally disagree? Share your opinions in the comments.

Tune in next week for: Data Warehouse Design Overview

Business Intelligence (BI) is a hot topic. But what does it really mean? This maiden post is the origin on our journey together to find answers. To start off right, let’s begin with my definition of Business Intelligence, which is the foundation of this blog:

Business Intelligence is the convergence of technology and analysis to enable effective decision making.

In the traditional sense, Business Intelligence describes technology. We must take the definition further… technology alone cannot solve the one and only purpose of Business Intelligence: enabling effective decision making. Let’s discuss the definition piece by piece.

Convergence

A computer can be programmed to make decisions based on a set of rules. What happens when a scenario comes along where no rules are written? This is where intelligence comes to play, particularly human intelligence. The human brain is an amazing device which has the ability to process numerous different inputs, resulting in an outcome.

Unfortunately, the brain has limitations. For example, if I had 1,000,000 visitors to this blog every day and wanted to understand what content is most popular, my brain could not digest that much data at once. Technology to the rescue! A database can process massive amounts of data with ease.

Ultimately, convergence is required to solve the limitations of technology and our brains. Together, the goal of Business Intelligence is possible.

Technology

The technology side of Business Intelligence is often comprised of multiple layers. Depending on what your BI goals are, you may add or remove layers. In future posts, I’ll deep dive into different layers. But let us start with the basics.

Data Sources are typically the transactional systems used to run your business. This can include ERP systems, web server logs, manufacturing systems, etc. In some cases, it could be data collected manually on a piece of paper. Keep in mind however, at some point all data must reside in a system where it can be accessed by the Data Movement layer.

Data Movement is the beginning of a robust technical solution. By moving data out of your data sources, you are isolating the performance impact on the critical systems used to run your business. There is another key: you’re not only moving data, but also reorganizing it in a fashion that makes retrieving data for reporting much easier and faster. Data movement is also referred to as ETL (Extract Transform Load).

Data Storage . You have to store the data you moved out of your source system somewhere right? Of course you do! Besides minimizing the impact on your transactional systems, moving data to a new storage location provides the opportunity for further performance tuning for your data queries. A database configured for a transactional system is radically different than a database configured for BI. In a transactional system, the goal is to retrieve 1 record as fast as possible. Meanwhile, a BI database will acquire millions of records, summarize, then return a concise result. I’m a fan of analogies, so here you go: Imagine if UPS delivered packages with a Corvette. They could deliver 1 or 2 packages really fast, but they would have to drive the Corvette back and forth to the distribution center so many times, it would not be efficient for the hundreds of packages delivered in a given region. So, a slower, yet big truck can hold many packages is more efficient (although not nearly as fun for the delivery driver! ).

Data Presentation is the user interface to your data, which provides the capability to retrieve data from data storage and process it into the output the user is looking for. The output comes in many forms, which includes reports, dashboards, RSS feeds, desktop widgets, mobile apps, etc.

Analysis

All too often reports are simply “data dumps” and not effective information to make decisions with. That is the role of analysis. Data is not useful on its own, so it is important for human intelligence to play a role in transforming data into decision enabling information.

Business Analysis is the process of taking data and applying human intelligence to create information. You might wonder the difference between data and information. Data is like raw ingredients, egg, flour, sugar. Information is all of the raw ingredients mixed and baked into a yummy birthday cake. While you could eat the raw ingredients, the birthday cake tastes much better! The same is true for data and information. Data can be used on its own, but information is far more useful. Information is raw data combined with knowledge of the business climate and processes to produce actionable information and recommendations.

Enable Effective Decision Making

One of the most important aspects of business are the decisions which drive execution. Business Intelligence is nothing more than an enabling function. It does not make decisions, people do. But, by utilizing the outputs of BI, decision makers can absorb key metrics, apply tribal knowledge and gut feel to result in effective decisions to drive their business forward.

---

So there you have it… Business Intelligence in a nutshell. How would you define Business Intelligence? Please join in and share your perspectives!

Tune in next week for: Data Warehouses vs. Data Marts