Clear Information Management

IM This Week: Time for Business Intelligence

2009-03-06T15:45:46-06:00

Business Week published a Special Report entitled "The Ceo Guide to Business Intelligence". The report discusses how BI is helping to reduce costs at Brinker International and Welch's and reduce fraud and wasted time at IMV Projects and Best Buy. We also get a nice little commentary about how data is transformed into useful information citing health care as an example. Overall, a high level piece directed to those less familiar with BI, but it's good to see that BI is being looked to in troubling economic times.

Another example of how BI is being seen as a way out of the recession can be found at TechNewsWorld (republished from CRM Buyer). Some good points are made here about increasing volumes of data and how companies are struggling to turn it into useful insights. Yet, the answer is better business intelligence, getting better information to decision makers more quickly.

Following along the same line, here's an article on Supply and Demand Chain Executive discussing Performance Management applications and how CPM/BI is more important than ever.

Doug Henschen, editor of Intelligent Enterprise, talks about executive priorities and refers to a white paper recently published by IE entitled "Attitudes And Imperatives: Intelligent Enterprise Reader Priorities Survey 2009". This report presents a number of opportunities for information management but is also a warning that we need to continue to improve our methods and technologies. I have some additional thoughts around this that I will cover in a separate entry.

Among other news: Netezza announced strong results for Fiscal 2008 and 1Q2009. Vertica unveiled a virtualized appliance for VMWare. And from the WTF file...IBM adds data warehouse features to Informix?

History in the Data Warehouse

2009-03-05T14:50:30-06:00

This is the next post in a series of discussions about data warehousing best practices based on this blog entry from February 11. Today, we look at capturing history in a data warehouse. "The data warehouse is able to present a view of the business at a particular point in time and track Key Performance Indicators (KPI's) over time." There's not too much to argue here.

One of the primary functions of a data warehouse is to capture history and perform point-in-time reporting and analysis. There are a couple of ways to do this depending on the architecture and requirements. The most common method in dimensional modeling is the Type 2 Slowly Changing Dimension (SCD). Type 2 SCD's track tie a transactional or fact record to it's dimension attributes, as it was at the time it occurred. Variations on the type 2 SCD will allow us to query the transaction as it was at the time it occurred or as it appears now. If we don't care how it was in the past and only care how it looks now, we'll use a Type 1 SCD.

Let's look at an example using a hotel transaction. Suppose we are looking at a transaction that occurred on 12/20/08. The transaction includes the room rate plus taxes that the guest paid, the type of payment he used, the guest's name and rewards number, and the hotel brand name and location.

Let's say on 1/1/09, the hotel company changed the brand name of that property to Fairview Inn from Village Suites. Hotel companies, such as Hilton and Marriott, own a number of brands, and there are occassions that they may convert one brand into another.

On 2/1/09, we run a report showing sales by brand for each month in 2008. Do we include that hotel's 2008 transactions under Fairview or Village? That probably depends on the use. If we are tracking revenue in order to calculate bonuses for the Village Suites VP, then he would certainly want to us to include those transactions in his bucket (as-was). However, if we are using last year sales to project Fairview sales for 2009, then we want to include the changed properties sales with Fairview (as-is).

In this case, we need to be able to query it both "as-is" and "as-was". A flexibile architecture will allow us to do either one. Much of the time, the "as-was" serves no purpose. For example, if we have status codes, and we change the description of a particular code. Most of the time, we'll want to see all records associated with the new description.

Determing which method we use to track history is one of the most important decisions we make in the data warehouse design. It affects the flexibility of the warehouse, as well as performance. We have to balance each. However, it is also one way to derive the greatest value from the solution, since this historical information is rarely available in any consummable form anywhere else.

Granularity of a Data Warehouse

2009-02-27T15:29:14-06:00

We are continuing a series of discussions about data warehousing best practices based on my blog entry here. This time we take on choosing the right granularity in a data warehouse. The rule of thumb, and the point I made in the original blog entry was that you should always choose the lowest level of granularity. That makes sense in that you can always aggregate to a higher level, but you can't go back down to detail if the data is not stored.

I can give an example of one data warehouse that I am familiar with that has won awards and accolades for best practices. However, a closer look reveals several trade-offs that were made during design that have had far and long lasting effects. During the design process, the decision was made to drop a lot of detail data in order to save space. This data, which many would consider essential to any data warehouse, contained transactional details and customer information. The data warehouse was built for financial use, and the finance organization was unconcerned about these details. They were looking at metrics by products and the chart of accounts.

Was this necessary? Arguably, it was. Since IT projects were funded by the business organization, the finance organization had no incentive to pay for storage or CPU required to process more data than was absolutely required. They simply made the decision to design for their own users' requirements.

However, this decision also had several long term effects that may have not been taken into consideration. Since the warehouse did not contain details that were important to other departments, such as marketing, sales, and operation, the warehouse was rendered useless for a large segment of the company. These organizations, in turn, were then forced to build their own data warehouses from the same source data in order to satisfy their users' needs. The end result being, as a company, they spent more building and maintaining multiple data warehousing systems than by investing more in the first one.

Another effect that had considerable cost was the other organizations' difficulty reconciling their numbers to the finance organization. Since the detail source data was not maintained by finance, and their business rules were buried in thousands of lines of code, users in the other departments spent countless hours trying to resolve differences between the systems.

So, was the right choice to build one true enterprise data warehouse to serve the entire organization?

In theory, the enterprise data warehouse is the best solution. However, we recognize that the larger the organization, the more difficult this is to accomplish. Enterprise level designs in 50 billion dollar + companies become almost a fantasy. There have been a few documented successes, but the kinds of investment involved in these projects take years and millions of dollars.

It is important that there be a chief architect overseeing the development of all information systems. That person must have a handle on the current environment and a vision for integration and reduced costs. I have met a few of these guys who had the title and responsibility but lacked the vision. I have also met a few who were convinced that silos were simply more cost effective and maintainable. Every organization is different and what works for one might not work for another.

So back to the original consideration for this post. Should a data warehouse reflect the lowest level of granularity? In general, yes, it should, but there are a number of other factors that must be considered when making the decision. Ultimately, the answer is that the granularity of the data should reflect the lowest level that has the greatest use to the greatest number of users.

Conformity of a Data Warehouse

2009-02-19T16:32:05-06:00

In an earlier post, Data Warehouse Basics, I reprinted something that I written a while back about what makes a data warehouse. In a series of entries, I am exploring each point in further detail and see if the meaning still holds up. In this post, we take on "Data is conformed".

Conformity in a data warehouse occurs means essentially two things, conformed dimensions and conformed facts. Conformity allows data warehouses to be distributed across the enterprise and unanticipated new sources to be added. Conformity assures that definitions and uses are consistent throughout the warehouse.

Conformed dimensions assure that users will be able to drill across data sources and arrive at conclusions that were not possible without a data warehouse. This is simply assuring that customer id 1234 for John L Smith in one fact table is the same customer id 1234/John L Smith in another fact table. This example certainly seems obvious. However, determining conformed dimensions is not always easy.

Two departments may view the information very differently. This is often the case between finance and sales or marketing. Finance typically views things as they are tracked in the accounting system. While marketing or sales may view them entirely different. For example, when tracking sales by location hierarchy, finance may have a hierarchy that is based on legal entities. However, marketing may not make any distinction between these legal entities and may be more concerned with brand or geography. These cases may present challenges to modelers and to arriving at conformed dimensions.

Conformed facts ensure that terminology for measures in one fact table mean the same in another. Revenue always means revenue, right? Well, again finance's definition of revenue may be different than marketing's. Once again, we have to ensure that our terminology is consistent. Doing so, will allow us to derive meaningful results across fact tables.

The concepts of conformed dimensions and facts are the cornerstone concepts to dimensional methodology. Without them, the data warehouse becomes a database of siloed data marts.

I extend the concept of conformity to include data naming standards. Consistent naming schemes make the database understandable and usable . So perhaps, we should call this one conformity and consiststency of a data warehouse.

DataValidator for Informatica Introduced

2009-02-12T16:44:54-06:00

Last week, DVOSoftware announced the first release of DataValidator. The company's founder, Val Rayzman, is a former executive at Informatica.DataValidator that seems so obviously useful that it is curious that no one else has done of it before. It compares two data sources and finds differences based on the evaluation criteria you select.

For example, consider an ETL process that loads 5000 records from an operational system into a data warehouse. We want to know that all of the records successfully loaded and that none of the values were unexpectedly truncated or altered. In a few clicks, we can create and execute a test in DataValidator that will show any variances between the two tables. Of course, DataValidator can also handle more complex tests as well, including set tests (i.e. show all values that occur in one set but not the other), multiple tables, incremental values, lookups, etc.

DataValidator uses the Informatica engine to build and execute Mappings and Workflows. Technically, it compares any set of tables. The tables themselves do not have to have been loaded by an Informatica process.

The real value of data validator is in the reduced amount of time it takes to perform data validation. Data validation is a highly manual process in most organizations. Some have developed scripts which are then automated and run following each load cycle. However, many organizations, short on time and resources, never get this far. Instead, they end up in a reactionary mode, correcting data errors after they have been discovered by users. The irony here is that organizations purchase Informatica to speed up and automate load processes, but then are still required to manually write code for validation. DataValidator fills that void.

There are a few features that would make DataValidator a more complete tool, such as scheduling. Val assures that these additional features are in the plan for future releases. The idea is to get the product into the market and learn from its users which features are needed most. That said, I believe that DataValidator offers a lot of value as it exists now. I don't think it is difficult to make an ROI case for something like this. But the challenges are the same as the data quality and profiling face, convincing organizations that data quality has a financial impact on the bottom line.

I am hoping to work with DataValidator on a live project soon and evaluate it more completely. I will be certain to share my experiences here.

Comprehensiveness of a Data Warehouse

2009-02-11T11:08:18-06:00

In my previous post, Data Warehouse Basics, I reprinted something that I written a while back about what makes a data warehouse. I want to explore each point in further detail and see if the meaning still holds up. In this post, we take on "Data is comprehensive".

The description says that data must consolidated from multiple systems. True, but does that in itself make a data warehouse comprehensive?

Not all data warehouses should include all subjects. It is only realistic that some data warehouses are directed towards finance, while others are focused on marketing.

Perhaps, it would be more accurate to say that the data is comprehensive around a particular subject. Our data warehouse should reflect all known details about a particular entity so that analysis produced from it is accurate.

For example, we might be performing customer valuation. However, if we have consolidated data from our only 3 of our 4 customer facing systems, how can be sure that our results are correct?

This happens often in the hospitality industry where the parent company operates several brands and many offer reward programs, in which the customer earns points for stays at any of the chains' brands. In order to evaluate the true value of a customer, we must be able to consolidate information from each of the company's brands. Often, these brands operate on entirely different point-of-sale or property-management-systems. The data warehouse must capture customer transactions from every system in order to place a true valuation on customers.

So from that respect, data must be comprehensive. However, there is another point of comprehensiveness that we should consider. Yes, we should capture every transaction for a customer from every system, but should we store every attribute from the transaction if we are not required to do so?

Experienced data warehouse designers know this can be a fine line to walk. On the one hand, we want to maintain the usability of the warehouse by not over complicating it with clutter that has no business relevance or use. However, when we omit certain data elements, it usually isn't long before someone comes along and requests that the data be added to the warehouse.

The rule of thumb is to include all business information available during the initial design. The cost of additional storage usually pales in comparison to the cost to modify, load, and test additions at a later time.

So to wrap up, yes a data warehouse should be comprehensive around a subject. However, that does not mean that it must include every subject relevant to the company.

Data Warehouse Basics

2009-02-11T10:17:39-06:00

A year and a half ago, I posted ITtoolbox about the basics of data warehouse. That post got a few interesting comments even one as recent as last week. I thought I would re-post it here then further explore the topic in subsequent posts.
...

A friend of mine recently asked me to help her develop some ideas for explaining data warehousing to a group of business users. It seems there was some confusion in her company regarding why their so-called data warehouse was really just a data dumping ground and not a true best practices data warehouse. The list below details what I think separates a data warehouse from an archive database, data mart, ODS, etc.

The data warehouse data model should be designed for the following:

1) Data is comprehensive - Data is captured and consolidated from multiple systems.

2) Data is conformed - This is the famous line "single version of the truth". Data elements are conformed so that the definitions of "customer" or "revenue" mean the same thing no matter which system it originated. Tables are conformed when they can be queried across dimensions and facts without changing the meaning of the results. This is what is needed to truly integrate data in the warehouse.

3) Data is granular - Ideally, we capture and store data at its lowest level of granularity. You can always aggregate up, but you can't drill down if the data isn't stored that way.

4) Data is historical - The data warehouse is able to present a view of the business at a particular point in time and track Key Performance Indicators (KPI's) over time.

5) Data is shared - A data warehouse that cannot be queried or otherwise accessed has little value.

Vendor Stability and the Challenge for Start Ups

2009-01-30T13:29:17-06:00

Seth Grimes has a great post on Intelligent Enterprise about vendor stability. He makes the point that while technology is important, the stability of the vendor is too and should be considered. Seth presents two companies that have compelling technology but could face uncertain futures for other reasons.

I agree with him that vendor stability does matter and have made similar points before when talking about open source technology. Newer entrants into the market face steep uphill battles to get their products into the mainstream and become profitable. Every now and then, one breaks through and becomes a star, but most will either be acquired (best case) or die a slow death (worst). Either way, customers face risks if the technology is critical to their business.

A number of analysts in the industry are quick to get fired up by new technology and vendors as they enter the market. Typically, these companies are very accessible and offer compelling stories. You will see press releases, blog entries, white papers, etc. touting the benefits of the new technology. All the publicity is great, but it doesn't necessarily translate into genuine growth.

I would not suggest that these vendors be ignored by the industry, nor should they be denied consideration by businesses looking for solutions. In fact, I would urge companies to look at all the options and measure them according to their requirements. Many offer technical breakthroughs and exceptional value.

The challenge for start ups is that the large majority of IT decisions are made for reasons that go beyond simply cost and performance. Personal relationships, experience, and existing skill sets factor into decisions as well. While names of established vendors like Oracle and IBM aren't as sexy and may not be as fun to talk about in the blogosphere, the fact is that these companies will continue to dominate the mass market for information management software. My point is not to dismiss the smaller guys but to point out that stability is a reasonable factor for choosing an established vendor.

Latest Gartner Reports

2009-01-22T17:27:10-06:00

The latest Gartner Magic Quadrants for Data Warehouse Database Management Systems, Business Intelligence Platforms, CPM Suites have been released. You can find them respectively, here, here, and here. I don't have too much to say about these. Curt Monash has some interesting comments here and here. For the most part, I agree with Curt. These reports are interesting but not always reflective of what is happening in real world implementations.

For example, Sybase is ranked only slighly above HP in ability to execute, yet Sybase has about 1500 Sybase IQ customers and HP might have as many as 30 Neoview customers. And how many of those are in production? Maybe, none of them are.

Yes, Sybase IQ and HP Neoview are two different products with different strategies and target markets. That is exacltly the point. If you only looked at the MQ, then you would think these two products are close, but there would be nothing further from the truth.

A Brief Introduction to Columnar Databases Part 2

2009-01-22T16:11:15-06:00

In the last post, I discussed the basics of column-store databases, and how they differ from traditional row oriented databases. Here, we'll discuss the benefits that column orientation provides.

Column stores provide two primary advantages for analytical queries over row stores, compression ability and query performance.

Compression

Column orientation allows compression algorithms that are not possible or have limited value in a row store. For example, run-length encoding replaces a sequence of repeated values with a number and a count. The sequence, (10, 10, 12, 12, 12, 12) could be replaced with (10*2, 12*4). This not only saves space but allows operations directly against the compressed values, saving CPU time that would normally be spent on decompression. In addition, query operations are being performed on multiple values at once saving even more time. Other compression schemes, such as null suppression, bit-vector, and dictionary encoding may also be used to some extent, depending on the package.

Column store compression may vary between 3 times and 20 times the size of the raw data. It all depends on the profile of the data and the type of compression used. In one case study, Par Accel customer Merkle, claims 6 times compression from the raw data and 100X query performance over Oracle (12 minutes --> 7 seconds).

Query Performance

I touched on the query performance advantages in the last post. Because analytical queries tend to select a few columns over a number of records, a column store can retrieve records more quickly because it doesn't have to scan as much of the disk as a row store. Most row stores process a row at a time, meaning the query executor reads the entire row and then extracts the needed attributes. The column store can treat the entire column as an array, iterating through the column very quickly.

What's the Catch?

You might be thinking, "that sounds great. Why haven't column stores taken over data warehousing?" Well, the trade-off for column stores is that single row selects and updates are not very quick. For that matter, neither are trickle feeds found in near real-time data warehouses. Column stores operate best in batch loads using their own optimized load utilities. For many organizations that tend to standardize on one of the big three database vendors, the need for flexibility outweighs the need for performance.

Most data warehouses serve multiple purposes. Yes, they are used for analytic queries, but they are also used for research, deconstructing history, fraud detection, and many other applications that may not be optimal for a column store.

Recently, we have begun seeing a shift in the market toward creating separate analytic data marts for intense, ad-hoc queries. Data warehouse appliances have seemingly opened the door, as companies are looking for low cost alternatives to beefing up their existing data warehousing hardware. Several column store vendors have come to market in the past 2 or 3 years. It is still early in their evolution, but it will be quite interesting to see if these products begin to reach mainstream status.