Tod means Fox

Today’s session was packed with practical design and implementation advice and best practices for the technical architecture design, product selection, and physical design of the data warehouse. Warren has a great wit and produced a lively session out of material that most normal humans would fall asleep to. We all managed to stay awake though — even past our 5 O’Clock end time.

So far, out of the three days, I learned the most today.

Technical Architecture

“Most data warehouse projects don’t take the time up-front to create a viable technical architecture.” That was the bullet on the Power Point slide. Fortunately, I’ve been on projects that did. In one group we had a CORD (Concept of Operations & Requirements Document) together with several other documents detailing the ETL architecture choices and standards. In another, we had a full LPAD (Logical and Physical Architecture Document) which clearly laid out the hardware, metadata, and flow of the expected system.

Designing such a document is unchartered territory for me, even though I’ve penned several (more than I care to recount) technical documents. The framework and requirements of the final data warehouse technical architecture document is quite demanding and just plain hard to do. You must include business requirements, standards, implementation details for the business requirements, ETL service details, Subsystem strategies, data access strategies, metadata maintenance plans, data storage, backup and recovery, deployment, interview summaries, and many more categories.

To create this plan, Warren detailed an 8 step process:

1. Prepare
2. Gather architecture-related requirements
3. Draft document
4. Develop architectural models
5. Determine implementation phases
6. Spec the Subsystems
7. Develop the final document
8. Review and finalize the plan

As you can see, preparing this document is not trivial but an integral part of the design process. Most of what is done at this level can be implemented across the entire data warehousing “Program” (not just the “project”). Individual projects might have some specific requirements, so during each iteration of the Lifecycle, it might be necessary to refine or update the document.

Metadata

We talked at length throughout the sessions today about metadata. In our environment, there are really three types we truly care about: business, technical, and process metadata. The following sections will give you an idea of what is expected for each category.

Business Metadata: Describes the contents of the data warehouse in more user-acceptable terms:

• Data quality
• Data dictionaries
• Logical data mappings
• Business rules

Technical Metadata: Defines the objects and processes of the warehouse:

• System inventory
• Source descriptions
• ETL Job logic
• Backup and security

Process Metadata: Describes the results of various warehouse operations:

• ETL operations statistics
• Audit results
• Quality results

Metadata management is really about following the 80/20 Principle. Warren even talked about striving for a realistic 10% metadata coverage instead of shooting for the holy grail, entirely integrated metadata repository. There are a lot of reasons for holding back efforts. Perhaps the biggest reason is that it is a maintenance headache with very little or spotty business value. Warren stressed that vendor tools should provide metadata and it’s about time we started demanding better of them.

Presentation Server

The presentation server(s) contains the atomic business process dimensional models. The server(s) acts as the access point for all BI applications and is fed by the ETL system. The dimensional models are complete with atomic-level data, aggregates, and conformed dimensions and facts. The server’s configuration and design is based on the requirements established during planning. The dimensions will contain combinations of SCD types, multi-valued hierarchies, and surrogate keys. Fact tables will be at transaction, accumulating snapshot, and periodic snapshot grains.

All of these things are planned for and conceived before the ETL and DBA groups get their hands on the documentation.

Real Time Data Warehousing

Real-time is defined (at least for this discussion) as any processing which requires access to data in the presentation servers less than 24 hours after the event occurs. Although the buzz of real-time has subsided — in some cases the term “right-time” has taken over — there is still a need to process data quickly. Warren went over a few different solutions, including the design of a “Real Time Layer”, which is a parallel structure to the data warehouse. This structure is in a dimensional form that matches (conforms, I guess you could say) to the dimensional models in the presentation servers.

The real-time layer would contain facts and their related dimensions only for the business events that have occurred since the last reconciliation with the data warehouse. Sounds simple? Not so fast. There are various issues with Type 1 and Type 2 changes, as well as the added complexity in maintaining the layer. But, as Warren stresses, if there is a compelling business need to have real-time access to dimensional data, then this might be a good way to go. Other options include building operational data stores, or building some sort of federated reporting system.

Finally…

We talked a lot about different vendor products. I may talk about these in some future post, but for now I don’t really see much value in it. There have been so many mergers and acquisitions, that it is very difficult to keep track of who is who. Warren even admits that he can’t keep up with some of these marketing departments as they re-brand and re-package many of the same products.

…

Tomorrow is the last day. I feel pretty good about what I’ve learned so far. More importantly, I feel that the work I have been doing to this point is valid. It’s nice to get reassurance once in a while!

I am thoroughly exhausted! Margy followed up day one with another day packed with information.

We went through the various types of Slowly Changing Dimensions (SCD) and even talked about Type 6 SCDs, which I’ll get to in a bit. We then rolled right into mini dimensions and other related concepts. After that, we talked about the modeling process, different types of fact tables, and then finally how to deal with a mature data warehouse.

Slowly Changing Dimensions

Although I’ve posted a few entries (1 2) on the SCD Types, I’ll go over them once again here, but hopefully in a slightly new light. We talked specifically about:

Type 1: Overwrite the Attribute

In short, when an attribute’s value changes, we overwrite the old value with the new value. This technique does not preserve history. While it might be easy to implement, its use must be fully justified! In most every case, we will want to keep some sort of history in our dimensions.

Type 2: Preserve History / Add a New Record

The most common type, this technique preserves the old value by inserting a copy of the current row with the updated attribute values into the Dimension. The old row is marked as archived (typically by using active and inactive dates and a status indicator).

Type 3: Alternate Reality / Add a New Attribute

New attributes are added to the dimension to represent the old and new value. In some cases this is a handy technique because you can preserve the old value along side the new value. This technique is best used when there is a massive, dimension-wide change that affects many rows. For example, if a voter’s representative district changes during redistricting, we might want to see what election results would have been if the old districts were used in the analysis.

Type 6: Advanced Hybrid

This is a hybrid approach that combines the other 3 types (1 + 2 + 3 = 6). According to Margy: “We’re creating new rows to capture change (Type 2), adding attributes to reflect an alternative view of the world (Type 3), which are overwritten for all earlier dimension rows for a given product (Type 1).” Read more about this here and be sure to check out this article by Margy and Ralph for more information. I hope to have some examples posted in the coming weeks as well.

We didn’t talk about inferred dimensions (although Margy did mention the late arriving kind). I suppose the idea of an inferred dimension (or Type 1.5) might be discussed with Warren in the coming days.

The Mini

After all that SCD talk, we talked about dealing with very large dimensions. Especially those that have Type 2 attributes. Margy introduced the concept of the Mini-Dimension, and stated that for extremely large dimensions, using Type 2 may not be a good idea for attributes that might change often. For example, suppose you wanted to store an airline passenger’s ‘age’ in your Passenger dimension. If the passenger flies 7 times over an 8 year period, you could have as many as 7 rows reflecting that change.

Instead, create a mini-dimension with attributes that change frequently and link them to the Fact table using a separate foreign key. It is hard to explain this in any detail without a picture. I promise once I get back from Amsterdam I will prepare a better example for this (with pictures!).

The Modeling Process

I was happy to see a segment dedicated to the overall modeling process. When it first came up, I felt it was out of place (perhaps it could have been discussed before we dove into sketching our case study models?), but in the end, it seemed to fit the flow.

The modeling process includes the following steps: Preparation, a high level design session, detailed model development, Dimensional model review and validation, and final documentation to be handed to the ETL team. It is important to note that this process is iterative.

Next we went over the various participants that need to be involved: the data modeler, business analyst, power users, BI application developer, data steward, source experts, and the ETL team.

During this process, we’ll also start to conduct our first rounds of data profiling. I find this part of the process to be most crucial because it is at this level where you can start to determine the feasibility of implementing a particular business process.

Types of Fact Tables

After a brief case study, Margy took a little more time to go over the different types of Fact table grains. These include the transaction, periodic snapshot, and accumulating snapshot. For more information on the different types, please read my post “Fact Tables“.

Some additional notes on Accumulating Snapshots: They generally represent some sequential, short lived, and predictable process that needs to be expressed by various milestones. These milestones are most often dates. These fact tables often span multiple business processes as well. For example, an order is placed, it is released, shipped, and delivered. These are separate milestones that have some serious significance to the business for analysis.

Lastly…

We finished the day discussing mature BI projects and how to handle existing warehouses that might not be in great shape. Margy went over several important common disorders (ranging from lack of business sponsor to infrastructure issues), some symptoms, and finally she proposed a treatment plan. Every slide in this section was packed with good, solid advice. When I can get my notes together on this, I plan to write about some of my own experiences with these disorders.

…

Tomorrow Warren will take over. I trust he’ll be as good as Margy!

As you may know, I am currently in Amsterdam attending the Kimball University (organized by Quest for Knowledge / Q4K) training on the Data Warehouse Lifecycle. Margy Ross and Warren Thornthwaite are here and so far so good. Margy is teaching for the first two days, while Warren will take the final two days. I am not disappointed thus far.

Admittedly, much of the material in this course is not new to me. I’ve been exposed to dimensional modeling for some time now, and with a few projects under my belt, I’m starting to feel like I know what I’m talking about (which could be dangerous, I know). I’ve read the books, the articles, the tips, and have even written my own articles and blogged on many subjects. Attending these four day sessions will be primarily to validate and shore-up what I already know and hopefully correct some of my misconceptions and mistakes. With that said, I am getting a lot out of each session so far.

Project and Program Planning

Margy started out with a brief introduction, detailing the roots of the Kimball Group. Its pretty interesting to hear how they started out as 3 2-person companies and essentially merged into one: The Kimball Group. These six people have fundamentally changed the way data modelers and architects think about and approach data warehousing. What sets them apart, I believe, is that their ideas and theories stem from needs of the user.

Tremendous emphasis is placed on getting the requirements right. Margy pointed out that involving the users early and often is a key to success. Oftentimes, she said, users are involved early on and then forgotten once development begins. Three or Four months later, what is delivered might not be exactly what the end user had in mind. As the development team works, they uncover more and more hidden issues that must be resolved. The best person to help resolve many of these issues is the business user — not the data modeler or ETL architect.

In short, planning for a data warehouse must involve the business community. You need a strong and charismatic sponsor who has vision and influence, a data steward who cares about convention and quality, and a business analyst who can straddle the fence between the green grassy fields of IT and the barren wasteland of the business (kidding…).

Margy also developed a list of warning signs that ranged from failing to fill the various shoes I mentioned in the previous paragraph to acknowledging “BI/DW success is tied to user acceptance”. One of her points, not to underestimate the data cleansing workload, is finally starting to become less common. But it still remains one of the main reasons why data warehousing projects fail.

The Bus Architecture

Here’s something you don’t get by reading a book: Margy and the group didn’t quite like Ralph’s name for what we know as the Bus Architecture. I don’t have an electrical engineering background, but Ralph does. I suppose he felt that the way business processes and conformed dimensions related together reminded him of how a computer’s bus architecture worked. Essentially, being able to swap in and out components (If you have an electrical engineering background, or are just plain smarter than me on this, then please correct me if I’m off-base!).

I asked Margy what she would have called it, and she didn’t really know. I guess that’s why Ralph won-out. He was the only one with an idea!

For our purposes, the Bus Architecture “provides a standardized master set of conformed dimensions and conformed facts used throughout the data warehouse.” Click the image for a larger picture. This image comes from a SQL Server Mag article “Dimensional Modeling Basics” by Joy Mundy and Warren Thornthwaite.

Dimensional Modeling and Conformed Dimensions

I’ve talked at length in my articles and posts about dimensional modeling. Margy went over the basics today, starting out with a discussion on the differences between the Kimball approach and the Corporate Information Factory (CIF) approach pushed by Inmon. Although the dust between the two camps has settled in recent years, Margy was quite adamant about the benefits of dimensional modeling over the enterprise data warehouse concepts. Margy detailed these, but until I get my notes together, I’ll leave it at that.

She also stressed the reliance on the star schema, and warned of snowflaking designs. Snowflaking occurs mainly when you attempt to normalize a dimension, usually by breaking the dimension up by its hierarchal data. For example, you might be tempted to break the “Store” dimension, which contains city, district, region, and state into separate City, District, Region, and State dimensions. Of course, doing so creates a snowflake and makes the model much more complex than it needs to be.

Conformed dimensions and facts are really the key to a successful dimensional modeling — and by extension data warehousing — project or program. They allow you to conduct drill-across queries, where you query data across business processes, and ultimately conformed dimensions and facts give your end users a consistent version of the data no matter what process they are looking at. Using the Bus Architecture, dimensions that are used across business processes are easy to spot.

Comments and Questions

I am also paying close attention to many of the questions and comments being made during the Q&A moments. The room is roughly composed of about 75% of people who are somewhat new to data warehousing and business intelligence. So it is a great opportunity to hear some of the questions the group has had for Margy.

Some questions: a.) How do you link a promotion to a product to show all the products that did not sell during a promotion? b.) Can you add the time to the date dimension? c.) Why should I conduct interviews during requirement gathering when I know the business well enough myself? d.) When determining the feasibility of implementing a business process, are there other important factors outside of data availability to consider?

I’ll elaborate more on all of these answers (and more) in future postings. In short, the answer key is simply: a.) Use a factless fact between product and promotion; b.) No, use either a Time dimension or store a timestamp in the Fact table; c.) You don’t know as much as you think you know and interviews will form the basis for future documentation; d.) available resources and technology issues can be overcome, perhaps the next biggest factor is dependencies between business processes.

And Lastly…

Almost everyone in attendance is a consultant. I am still getting used to this. I’ve seen and met more consultants in my short time here in Europe (about 5 months so far) than I have my entire life. Margy was also surprised. She did make a very interesting observation though. Consultants, who usually pay their own way through courses and seminars, typically stay away when times are tough. When the economy is good and/or they have plenty of work, consultants attend more conferences. Interesting. I suppose this could mean that the consultant business is doing quite well in this part of Europe.

…

I couldn’t get everything into this single post. I’ll be sure to post more notes as time goes on. For now, it’s on to tomorrow!

I’m starting to get excited about next week. I’m leaving Brussels for Amsterdam at about 5pm on Monday to attend a four day course at Kimball University. Subject: The Data Warehouse Lifecycle in Depth. Instructors: Margy Ross & Warren Thornthwaite. I’m looking forward to diving into the DW Lifecycle with industry experts: those who literally wrote the book. Ralph Kimball won’t be there but I trust that Margy and Warren will give us a great week of sessions.

What’s really, really cool is that I’ll be getting the new edition of “The Data Warehouse Lifecycle Toolkit“:

What better way to start 2008 than with a major new data warehousing book! Joy, Warren, Margy, Bob and I have been working on the second edition of The Data Warehouse Lifecycle Toolkit for most of the last year. Virtually every paragraph of the first edition was changed, and more than 60% of the book is new material. The happy looks on our faces are both pride in our new baby, as well as relief from a long writing and editing stint.

I plan on blogging from the event, so if anyone is interested in getting some insight on any of the sessions, please let me know. I’ll be sure to take especially good notes!