When I was learning to program in C and studying algorithms, the assert() assertion macro was one of my favorite debugging tools. Assert can be used to validate that something isn't going wrong that could send your program into left field during the execution of some procedure. For instance, a balanced binary search tree should never be more than log2(n) levels deep (or something similar to that based on the exact insertion algorithm), where n is the number of items in the tree. After a new item is inserted in the tree, you can assert(tree.depth() == log2(tree.count())). If that assertion fails, then you know the tree isn't staying balanced and the search performance guaranteed by a balanced tree isn't valid any more.

If that's too much computer science for you, hold on and see where this is going. There's relevance to this idea beyond low-level programming and computer science theory.

I've been in many conversations with data warehouse sponsors that focused on the question of "how are you sure that the data in the warehouse loads correctly every night?" One of the better ways I've found to approach this kind of data integrity assurance is to think about what kinds of assertions can be found throughout the batch ETL processes that I create.

For this example, suppose a somewhat traditional sort of ETL process that happens in the following steps:

1. Copy or extract raw data from source system
2. Detect changes from last pull
3. Lookup surrogate keys and other translations
4. Apply deletes (as soft-deletes with setting exp_date = current_date())
5. Apply inserts
6. Apply updates

For the rest of this post, see the original at Sharpening Stones

When I was a young child, we spent every Thanksgiving with my paternal grandparents in Denver. There are two particularly memorable things about those visits. First, even into the late 1980's, my grandparents didn't own their own telephone. They rented their phone from the telephone company. It was the same rotary dial phone they'd had for years, hanging in their kitchen, with an extra long handset cord attached so they could stretch across the dining room or kitchen while still talking on the phone. Second was the important lesson that I learned about doing dishes by hand.

Doing dishes by hand is ideally a three person job: one to wash, one to rinse, and one to dry. The lesson that my grandmother taught me about washing dishes was that the drier is the person accountable for making sure the dishes were clean when they went back into the cupboard.

As data warehousing professionals, we spend a fair amount of time and energy arguing that data quality is something that has to be fixed up stream, by applications. My grandmother would insist that sending the dishes back to the washer is not our only option.

If a dish comes to the drier not quite clean, there are three options:

• send the dish back to the washer to be cleaned again from the beginning with soap;
• send the dish back to the rinser to have the mess rinsed off with some hot water; or
• use a little extra effort and wipe off the mess with your dish cloth.

Ideally the dishes come to us clean and ready to dry. It's a lot less work to dry off some steaming droplets of water and put a nice clean warm dish away in the cupboard than it is to notice that little bit of bread from the stuffing that didn't quite get cleaned and have to use the tip of your fingernail through a dish cloth to get the crumb off.

What are the downsides of sending the dish back through to be rewashed from the beginning:

• the washer has to stop in middle of scrubbing that big pan to rewash the plate;
• the plate takes longer, overall, to be rewashed, rerinsed, and redried;
• both the washer and rinser have to redo work.

I'm not suggesting that any application is allowed to be intentionally lazy about data quality, or should not correct issues that are identified. Rather, I'm suggesting that we make sure we all continue to see data quality as our responsibility and not merely blame up stream systems when there is something that could be done at various points in the chain to ensure quality information is used for decision making.

(Reposted from: Sharpening Stones)

Once, there was a great chief called Yagni. Chief Yagni's village was very prosperous and had grown much during his rule. Eventually, Chief Yagni decided that it was time for the village to have a new gathering space as the old one had been well out grown. So, Chief Yagni recruited 2 strong men, Gwyn and Titi, to bring stones from the quarry to the village center so that a group of builders could stack them into the new gathering space.

Gwyn and Titi both arrived on Monday morning to receive their directions from Chief Yagni. Yagni reviewed the building plans and told Gwyn and Titi that he needed 20 large flat square stones for the base of the building. Gwyn and Titi took their push carts down to the quarry, gathered rocks, and returned to the village center. They emptied their rocks together in piles for Chief Yagni's review. Gwyn's pile had exactly 10 flat square stones. Titi's had 10 flat square stones and 3 smaller angled stones.

"Why are these here?" asked Yagni.

"I had to pick them up off of the flat stones in the quarry," replied Titi, "so I thought I would just bring them along in case there was a use for them."

"Get rid of them!" shouted Yagni angrily. "You've wasted time in gathering those worthless rocks I did not ask you to collect. We need 10 tall narrow stones for the doorway. Go back to the quarry and bring me those. Only those, Titi! When you see the other rocks, tell yourself that You Aren't Going to Need It."

Gwyn smiled at Titi's scolding, feeling proud that he'd followed the chief's directions so precisely. Titi believed that the angled stones might eventually come in handy. Gwyn and Titi began pushing their carts back to the quarry. Gwyn's light and empty. Titi's partly full of unwanted rocks.

Frustrated, not wanting to push the angled rocks all the way back to the quarry, Titi dumped the extra rocks in a small pile just outside of chief Yagni's sight.

Gwyn and Titi gathered the tall narrow stones the chief asked for. Titi, again, had to clear angled rocks from on top of the narrow stones, and added them to his cart. This time, Titi added the extra angled stones to his pile just outside of Chief Yagni's sight.

Gwyn and Titi returned to the chief with their carts full of only tall narrow stones and the chief was pleased with them both. This continued for several more trips until the new meeting place was nearly complete. Gwyn following directions exactly and Titi always bring back more than asked. By this time, Titi has accumulated a large enough pile of angled stones to fill his entire cart.

On their last delivery to Chief Yagni, the chief looked over the plans again and stroked his chin in thought. "Gwyn, Titi," he said. "I need you to bring some angled stones for the roof. Like those that you brought back on the first trip, Titi. Go back to the quarry and bring me two carts full of those."

Gwyn and Titi hurried back toward the quarry. Gwyn went to the quarry and began collecting his cart full of angled rocks, but Titi had the large pile he had been accumulating throughout his other trips. He stopped just outside of the chief's sight, collected all of his angled rocks into his cart, and returned to the chief well before Gwyn had even loaded half of his rocks.

"Titi. How did you gather these rocks so quickly, when Gwyn hasn't returned yet?"

Titi explained to his chief that when he had to pick up a stone anyway, he decided that if he had to Touch It, then he should Take It.

The chief was please with Titi's foresight and promoted him to lead rock collector.

(Repost from: Sharpening Stones)

If the sound of a falling tree occurs in the woods and no one is there, did the tree fall?

The data warehouse is fundamentally a communication tool. The information that it conveys through its data and the representation it provides of business activities can be incredibly valuable. All the work that goes into collecting, correlating, and conveying information in the data warehouse is wasted if the right decision makers are not consuming the information. Consumption is not only about the technical ability for some abstract decision maker to use the data warehouse for analysis. Rather, it is about ensuing that each enhancement to the data warehouse helps to drive adoption and increase both strategic and operational use of the system. As users have a positive experience that aligns to their immediate business need, they're more likely to come back to the data warehouse with their next question, rather than striking off on a journey to collect information from various systems, correlate that data together, and then convey that information to decision makers and senior leaders.

(Repost from: Sharpening Stones)

One of the primary responsibilities of the data warehouse is to take information that would otherwise not be available and deliver it to decision makers and analysts who can do something valuable with the information and make the world a better place. Perhaps you might think that's a bit of a stretch, or perhaps I'm being overly optimistic. However, my position is that we are more likely to make decisions that move society forward if we have more knowledge about the context in which we're making those decisions. So, even if we don't always use better information to make better decisions, we couldn't even have the opportunity to try to make decisions if not for tools like the data warehouse.

Effectively conveying information is a factor of the format of the information, performance of the system, the accuracy of the content, and the ease of use with tools that will be used to access the information. Most importantly, the data warehouse has to be able convey to users, in a natural way, how to find the information they need and what aspects of the physical implementation impact how the system can and can't be used.

(Repost from: Sharpening Stones)

Other correlation activities are ones that can be supported directly within the data warehouse environment:

* Code set translations ensure that a user can reference any data source using the approved corporate standard code set for some particular attribute.
* Cross-reference translates that allow two different identifiers for the same particular entity to be combined together.
* Transformation of one representation of a particular identifier into a matching representation somewhere else. For instance, if one system uses a 15 digit cost center that includes accounting unit, account, and sub account, the data warehouse should take that 15 digit string, break it into components, and match those against the information from the finance system.

Having a data warehouse to preexecute these kinds of correlation activities once and store the results for convenient use in tens of thousands of queries every month or even every day is a huge benefit to analysts through clear communication and improved performance.

(Repost from: Sharpening Stones)

The first job of a data warehouse is to bring together, in one business tool, the information that an analyst, knowledge worker, or decision maker needs to understand business operations. Whether that collection of information is a literal copy between data stores, a federation of databases, or a more complicated transformation of transactional data into a dimensional model is merely an implementation detail. Some of those choices are more or less practical depending on the underlying systems and data (which we'll discuss later), but to be of any value, the data warehouse has to do th collecting of information together so that users don't have to. One of the key benefits of data warehousing is the ability of an analyst to have a one-stop-shop for most of the information they need to do their analysis. In analytically immature organizations, analyst will typically spend 80% of their time collecting data and putting it into some kind of local data store (which might anything from flat files to MS Access to small databases) and only 20% of their time doing analysis on that data. One of the goals of the data warehouse is to flip that ratio so that knowledge workers are able to spend 80% of their time analyzing business operations and only 20% of their time retrieving data from as few different sources as necessary.

When various analysts from different departments (marketing, strategic planning, sales, finance, etc) all ask the same people for the same data on a continual basis, it also prevents the application teams from having time to make improvements or plan upgrades to the applications themselves. There are still organizations that have multiple staff members dedicated to the work of fulfilling data extract requests to support internal analytical needs. A data warehouse that collects the information together once, into a common enterprise model of business activities, satisfies all of those individual departments with one extract from each source system and a consolidated environment in which to do their analysis.

(Repost from: Sharpening Stones)

So, what are the 4 C's of data warehouse success:

* Collect
* Correlate
* Convey
* Consume

These are the 4 things that a data warehouse has to be most effective at to achieve success. Over the next four posts, I'll describe what each of these represents and ways that you can measure the quality of your own data warehouse implementation against these.

(Repost from: Sharpening Stones)

During both that conversation with the storage team and another casual one I was having with our Unix server team, I mentioned the idea that our BI/DW team is starting to have discussions with some data warehousing specialists. I'm paraphrasing, but "ugh!" was the universal response. "We don't like proprietary solutions here. We've standardized on X storage vendor and Y servers. We don't want to support that."

We're only in preliminary conversations about bringing in a specialty data warehouse solution, but I certainly want to maintain a good relationship with our server and storage teams. Any advice on how to help them see the logic and design behind specialty data warehouses?

One of the key operations in the Triadic Continuum data structure is "setting context," and I would argue that this key operation might be prohibitive in anything but the simplest examples. I haven't fully studied and analyzed real-world scenarios, but here's my argument:

First, remember that in the Triadic Continuum, data itself is never duplicated. Every "sub component node" in the tree structure contains not any observed data, but rather a pointer to "sensor nodes" where data is actually stored. Like a columnar database, this is great in reducing the amount of data being stored.

Second, think about the ideas of context, constraint, and focus. These are three key concepts in getting information from the Triadic Continuum. Context is the idea that you have to provide some level of initial boundary to the question being asked: "I can about sales" for example. I can imagine this initial definition of context being brutally difficult. Imagine if you had a single index in a database that represented each column value that appeared anywhere in any column of any table. Here are a couple of concerns:

• That gets to be a pretty big single tree or hash table or anything to search through at the beginning of each query.
  
• Tracing from the sensor node back to the associated subcomponent nodes may result in a very large list of nodes to use in the context. Potentially millions, hundreds of millions depending on how broad the context is.
  

If anyone wants to pay me something equivalent to my current salary to go back to school and study this more thoroughly, let me know!

This particular article is about something called the triadic continuum - yes, sounds like something out of Star Trek, doesn't it? Still, it is interesting - especially if you have a PhD in cognitive science. There's a book about this invention that goes into more detail for those of you who are curious.

After a couple of back and forth comments in which I tried to look smarter than I am (and considering pitching the idea that if my boss wanted to send me back to school for my PhD that I'd be happy to go), this VP ended with: "But they said simple, scalable, universal." Yeah, right there on the package! ;)

My snide remark (being in a healthcare field) was going to be something like:

Right, and so is DNA!
Simple - only 5 compounds in the whole thing!!
Scalable - from a flee to a whale!!
Universal - it is a defining characteristic of life!!

I didn't send that response, yet.
DNA is simple, universal, and scalable, too.

You'll find Dan Linstedt blogging about the Triadic Continuum in the future, I'm sure. I'm still busy reading the book, but my gut tells me there are some very interesting things to be learned from this data structure. I don't think it's the solution to moving along Ackhoff's data-information-knowledge-wisdom curve, but I've already starting thinking about how to leverage some of the algorithms from the book in relational data models (even though that's what the inventors would discourage us from).

You'll see more from me in future posts as I try to decompose the ideas in the book and relate them back to the day-to-day information management challenges we all face.