Showing posts with label Touch It Take It. Show all posts
Showing posts with label Touch It Take It. Show all posts

Monday, August 23, 2010

Business Keys

I'm engaged in a project that's actively using key concepts from Dan Linstedt's Data Vault methodology.  There are lots of very powerful benefits that we seem to be realizing with this methodology, but this series of blog posts won't be particularly about use of the Data Vault.  Still, I felt it was appropriate to credit the Data Vault for helping provide a structure for our own discovery.

This first post is about the struggle to identify keys for business entities.  We set forth some fundamental principles when we started out on our latest large scale project.  First and foremost, we would "throw nothing away".  What that's meant is that we want to design the foundation of our reporting database to be a reflection not just of one department's truth, but all of the truths that might exist across the enterprise.

As a result, the design of every major entity has run into the same challenge: "what is the business key for this entity?"  Well, from System A, it's the abc code.  From System D it's the efg identifier.  But if someone put in the xyz ID that the government assigns, then you can use System D to get to this industry file that we get updated every 3 months and link that back to an MS Access database that Ms. So-and-so maintains.  Ack!  Clearly we can't just say an apple is an apple.  And clearly there's a data governance issue at play in this scenario also.

In one case, some of these are legacy systems that simply are what they are and aren't worth investing additional time and energy into.

Our data modeling challenge is to determine what the one business key would be for all the different instances of this one logical entity.  When confronted with the challenge of having no clear business key, the project wanted to "just add a source system code and be done with it."  I pushed hard against this approach for a couple of weeks, insisting that the team keep going back and working harder to dig up what could be a true business key.  Eventually, I realized that I was both working contrary to one of the original goals I'd set forth and becoming the primary roadblock to progress.


Interesting side note:  One of the better tricks of good software design is to defer decisions to the last possible minute.  If you get away without writing some piece of code, then best to put it off until you have to write it.  There's obviously some nuance and art to understanding how to leverage that.  The Strategy Pattern is a good example, though.


What I realized I was doing was trying to put a huge and potentially very dynamic piece of logic out in front of our need to simply capture the information that was being created by source systems.  So, we instituted what felt like a completely counter-intuitive design standard:  every business key would include source system code as part of the compound key; and we would defer the need to consolidate and deduplicate instances of an entity until after all the underlying data had first been captured.

Deduplicating is the immediate next process, but this allows to be sure that we've captured all the raw information from the source system first before throwing away the fact that there are different versions of the truth in different source systems.

A very powerful lesson for us that felt very counter-intuitive; that we started considering for the wrong reasons; and finally decided to follow through on for all the right reasons!

Saturday, December 26, 2009

Where to Put Business Logic


Dave Rathburn has a great blog about SAP / Business Objects and posted in October about choices in where to put business calculations in the data warehouse / reporting stack.  I thought I'd share a couple of quick thoughts about some experiences I've had with project teams trying to decide where to put certain pieces of business logic, specifically in the ETL workflow.

For this example, the project involves the integrated reporting of accounts receivable history from about a dozen separate legacy systems.  The design question came up: how much business logic should be done by the legacy source systems who are writing the extracts using proprietary tools, and how much should be done by the ETL team who will be integrating and merging the data into the warehouse.

In the beginning of the discussion, we had two polarized and protectionist perspectives:

Argument 1: Since the source system teams are subject matter experts, they'll know quickest how to understand and translate from source system specific domain value and logic into data warehouse values.

Argument 2: It's the data warehouse team's job to do the integration work, so the logic should all go into the ETL.

The discussion degenerated for a while until I noticed that what we were really revolving around was a straightforward operational argument that is the most convincing that I've ever heard.  Here's how we summarized it at the end of our discussion.
 

General data warehouse design goal: put as little business logic as necessary in the source system extracts themselves.  Rationale...
This helps to reduce the number of places you have to look to troubleshoot when something goes wrong.  If most of the business logic is applied after the data is retrieved from the source system, then it's also most likely that any errors that occur happen after that initial extract step.  If there is significant business logic in both the source system extract as well as the integration ETL, then both places have a reasonable likelihood of introducing errors into the data.  Some business logic must exist in the integration ETL to be able to merge common dimensions and do the work of actually integrating the data. Therefore, having limited business logic in the source extract means that you don't have to focus on both the legacy code AND the integration ETL code when troubleshooting.

 In the end, we all agreed that we'd keep the logic in the legacy extracts as simple as possible, and put as much business logic as possible in the ETL.

Wednesday, December 2, 2009

The Agility of Touch-It / Take-It

A parable about the agility that "Touch It, Take It" adds to data warehousing; and the extra work that a misuse of "You Aren't Going to Need It" creates.

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 still believed that the angled stones might eventually come in handy, though.  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 paused and dropped the extra angled stones on 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, but leaving the extra angled rocks on his pile.  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 during 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 own 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.