So the CEO is glaring at his white-board, or rather, white-wall ever since he had an entire wall converted to white-board surface - and its border-to-border scribblings, ad-hoc graphics and spider-web of interconnected projects. He wonders when these things will finally fall "under control".
He chats at board meetings, his various coffee-klatches and among his business acquaintances, peers and lieutenants. He has dreams. Big ones. Who doesn't? But we know that a corporate dreamer is no different than the several kids lying in the grass staring at the clouds or stars, their craniums barely touching one another as their bodies are configured in a pinwheel. They dream of what those clouds look like, or what it might feel to fly among the galaxies in a starship. If only they had the power to fly. The power to reach lightspeed.
But in a lot of ways, they already can. Research has shown, in various ways, that the "speed of thought" in the human brain requires countless millions of signals in any given fraction of a second. The permutations of thought-pathways are themselves mind-boggling, and yet all of this takes place rather seamlessly and instantaneously. People think just as quickly at the supermarket as they do in their homes or when driving. Martial artists don't particularly think faster, but have trained their reflexes to second-guess their brain's immediate intentions. What does all this mean?
One could assert that the human brain is already processing data far faster than mere electronics could possibly accomplish, meaning that the human brain actually thinks, on the aggregate, faster and with more directed power than the speed of light itself. Okay, that's pretty heavy, but think about those kids wondering if they could travel at the speed of light or beyond, when the mere act of dreaming has already taken them there?
How to capture this speed-of-thought capacity? When analysts stand up their uber-powered environment, they want to query their data, receive a response, formulate a ripost and fire it back again, as fast as they are able to think. Well, what machine could adequately keep up with the human brain? Not that it's directly bound to the brain or anything, but we know that if an analyst has to wait for ten minutes, or an hour, for a return-on-query, that their thought processes are not willing to keep themselves on hold for that long. The human brain moves on, and has formulated many more questions even before the machine returns the first one. Underpowered technology like this cannot serve analytics in any significant capacity.
Now let's go back in time to NASD (securities regulatory agency for NASDAQ), which in 2006 had a processing day that exceeded 28 hours. That's right, they would start a new processing day before the prior one finished, and they would catch up on the weekends. No matter how the CEO rearranged his thoughts or white-board priorities, it would not change the primary problem: a lack of capacity. They installed a Netezza machine and recharacterized their workload on it, effectively reducing their processing day to less than 2 hours. Simpler processes. More accurate answers - and some answers that had been unavailable before. Capacity in hand, the business forces saw an opportunity arise that would have been impossible without this capacity, and so NASD merged with NYSE's counterpart to form FINRA. This is not to say that Netezza was solely and completely responsible for the merger, but definitely played into the ability (or lack thereof) to make decisions toward this goal. Face it, without capacity, FINRA would never have seen the light of day.
The CEO doesn't really care about the length of a batch window or SLA compliance. He/She cares about capacity. This would true of any CEO on the planet. Whether it's a hospital, retail chain or head of a consulting firm, they want to capture more business and maintain good service with their current base. This requires capacity and "bandwidth" to do so. That CEO might have the best-laid plans on the planet, but without capacity, he's just a dreamer. Just another dreamer.
Is this what they thought of the CEO of NASD, or any other corporate exec who knows what the answer is, the path to sucess, favor and recognition in the marketplace? Knowing what the answer is, and having no power to fulfill it, well, looks a lot like just-another dreamer. What enabled this dream? Why dream at all if there's no capacity to make it happen?
So an architect gets the PureData/Netezza machine in-house, and handily squares-away that first project. Then another, and another. Unbeknownst to folks at her level, the CEO is happy to erase that first project from the white-board, and the next, and so forth. In reasonably short order, the project work has been assimilated by the time the CEO erases the final piece of the puzzle and steps away from the white board, now an empty canvas.
Then something interesting starts to happen. The "drag" on the CEO's mental cycles has abated, the engines are still firing on all cylinders but there's no overhead - all those outstanding projects - to distract them. Thoughts accelerate into the light-speed zone. A hand reaches for the EXPO dry-erase marker, and like the light-saber jumps into the Jedi's hand on-demand, the marker meets his hand in the air and the ideas start to flow.
He starts to dream.
And now we have a very powerful situation - a dreamer who is actively dreaming, with the power to make the dreams come true. Which is more valuable? The visionary with nothing more than gossamer-thin hopes for a brighter tomorrow? Or the dreamer with the capacity to make dreams come true?
Is a Puredata/Netezza machine a dream machine? Is it what dreams are made of? This might sound a bit surreal, but what other machine has the capacity to respond at the speed-of-thought, the ability to assimiliate - with a little assistance - the dreamer's thought processes? And so like Jackie Chan-in-a-box, it is able to reflexively anticipate the competitors next move. Whether that's the corporate adversary, the competitive company, the market forces or the various risks in every new endeavor - we need a machine that can defend us like a blackbelt, can anticipate like a reflex, and can convert mere analytics into a jump to lightspeed, leaving our competitors in the proverbial dust, stardust tho it may be. How's that for thinking big?
DavidBirmingham 2700043KNU Tags:  elt analytics configuration etl netezza puredata development simd 1,591 Visits
As with all analysis of implementations, please accept the following as a composite commentary (much like the Case Studies in Netezza Transformation). The names have been changed largely to protect the guilty. The innocent have already been punished.
So for those of you who may recognize shadows of your own environments in the discussion below, you now have plenty of time to get them cleaned up before anyone finds out about it! But honestly, don't admit to anyone that you are "doing it this way". Just fix it. What happens in the underground, stays in the underground!
I cannot (today) count how many on-site assessments I have executed or the variety of their outcomes. I have to say that on balance, most technology folks are pretty sharp and have things on track. I can usually advise them on how to make things better. This is of course exactly what they are hoping for. What manager wants to hear that they've done most it of it wrong? Or that their investment in the technology and the people, are a bust? No managers I know take their responsibilities so lightly. Some, however, inherit a mess from their predecessor and are flummoxed as to how to unravel it. They don't want me to "put lipstick on the pig", so to speak, but to provide a roadmap on how to dig out of the ditch (or hole, or rat's nest) and move things forward in a healthy direction.
Working with a 10400 Mustang, pre-TwinFin Era, one of our recently arrived data warehouse aficionados took our leadership aside and said, "What they are describing is a reporting system, like a data mart. But we aren't using any technologies to help them with this. We need to have a talk with them about standing up Microsoft SQLServer so we can put a data mart on it and..."
Stop right there. Yikes. He was so full of passion! It was really, really hard to talk him back from the ledge. So I finally said, "If you mention this plan to the client, even once, we will have to remove you from the project." And his eyes went wide like he'd been hit with a two-by-four across the forehead. "Why?" was his impassioned plea. Time to educate him on what Netezza does, right?
Netezza is a data warehouse appliance. It circumscribes and simplifies the data warehouse disciplines. It also makes some strong assumptions about the potential users of the appliance, not the least of which is what-problems-it-solves-well and what-problems-it-does-not-solve-at-all. (World Peace, Global Warming, Time Travel, Cloning of IT Staff Members, and getting the Dallas Cowboys to the Superbowl, to name a few).
Example: What if you were going about doing some-regular-task manually-and-tediously, and someone then showed you a device that would automate it? You might count your blessings and move forward with a skip in your step. But when you share the device's features with someone unfamiliar with the manual, tedious nature of an existence without it, they scratch their heads and say "I don't get it."
I am reminded of a joke where a lumberjack is in the market for a new saw. A powered-chain-saw salesman asks him how many trees he cuts down in a day with his manual saws, and the man says "30". Ahh, says the salesman, with one of these you could cut 100 or more in a single day. The lumberjack doesn't believe him, so the salesman tells him, Look, take this one for a test drive. Use it all day tomorrow and if it doesn't at least double your output, bring it back, no harm done, no questions asked. The lumberjack agrees but returns two days later, clearly disgruntled about the chain-saw's performance. "I was only able to cut down 10 trees with this lousy thing." To which the salesman balked, and wondered if it might not be defective. So he beckoned the lumberjack to follow him outside to their testing area, where he threw a log across two sawhorses and pulled the starter cord on the chain saw. When it roared to life, the lumberjack took a step back and shouted over the sound of the motor - "WHAT'S THAT NOISE?"
Clearly a little product-orientation was in order, no?
A CTO once lamented to me, "Well, we did the best we could with what we had." - Well sure. Don't we all? I don't know of anyone who borrows or rents help to do it poorly. Nor do they take their best people to deliberately make something sub-standard. The problem is, without a baseline knowledge of what the machine can do, how it is typically deployed, and what to embrace or avoid about it, then it's really no different than the lumberjack's problem. He did the best with what he had, didn't he?
What were the outcomes? Poor perception of the product by the user. An objective lack of productivity. General grousing about something that is not well-understood. Where have we seen this before? Give a call to practically any help desk of any product, especially a technology product, and they will bend your ear with "howling" examples of users who mis-applied the product - and some would say - were just plain stupid about it.
Underwriters Laboratory (UL) has a standard policy of quickly adjudicating claims against them no matter how frivolous. Seems that just having the "UL" on the product makes them a lightning rod for litigation. One man took his name-brand lawn mower, which also sported the UL sticker, picked it up while it was still running, and attempted to use it as a hedge-trimmer. He slipped, the lawnmower fell on him, and he sued the maker of the lawnmower and UL. Three boys found a giant bullfrog and decided to kill it by setting it on fire. They grabbed a gas can from the shed, doused the bullfrog with it and tossed a lighted match on the hapless creature. Someone should have told the lads about the volatility of gasoline fumes, because the flames climbed the can's fume-trail directly into its mouth and detonated the can's contents, seriously wounding and severely burning all three boys. They sued the makers of the gas can and UL, which also had a label on the can. Perhaps someone should send this one in to A Thousand Ways to Die. For bullfrogs.
But this is not a dissertation on A Thousand Ways to Fail with Netezza because frankly, it's really hard to fail with a machine this powerful. This is why I say that when we encounter howlers like the guy with the lawnmower or frog-immolation, we're clearly off the beaten path. Why is it then, that the "beaten path" pops up more often than it should? Or for that matter, pops up at all? Aren't data warehousing folks a little smarter than that?
Of course they are. In fact, I don't recall encountering any experienced data warehousing folks who have had a bad experience - quite the opposite. However, as for the folks who have never built a data warehouse but have a lot of experience in "applications" - well in this zone it can get a little choppy.
The point is, across the fruited plain we have exceptions to every rule. My sincere hope is that your project is not inadvertently caught in the "crosshairs of fate".
Rat's Nest Number One.
Upon arrival on site I knew something was wrong. People were squeezed into their cubes, boxes were stacked against walls in every room. The whole office just felt so crowded. And then they introduce me to "the machine". In this case, their production machine was the lowest-powered machine that Netezza had to offer, just short of a Skimmer. The admin at the desk barks at me for parking in the street and not in the garage underground. It's my first day, and nobody said anything about parking. The difference in cost was exactly $1, and if you're like I am and travel a lot, this kind of difference is not worth discussing. Except for here. It's tongue-lashing time. All of these things added up to some significant red flags, moving in a direction of a road lined with red flags.
Case in point, this is not a company doing things expediently or frugally. They are cheap. They will do things according to the lowest denominator of cost and skill, not because they have balanced priorities. They would rather save a few dollars on training or even a rent-an-architect, and allow the least-of-their-staff to painfully slog through the nuances of data warehousing on an immutable deadline. It's the immutable deadline I can't fathom. Here's why:
In a solution implementation, we have cost, duration and quality. Pick two. Whichever two you pick will shortchange the third. Every time it's tried. Well, these folks were shortchanging all three in the blind naivete that it was valid and workable. Without time or resources, quality is always the first, most expedient of the three to fall on its face. Doing it on-the-cheap? Well, what does this say of their readiness for prime-time? Data warehouses have an ongoing cost-of-ownership. It's not trivial. Those who want to play cheap should find another profession, one that does not cherish quality.
I was told by the client that their current back data processing environment used Netezza stored procedures. Another big red flag. Stored procs invite black-boxed code and we cannot capture data lineage through them. Netezza stored procs are ideal for the front-end. Never for the back-end. They are hand-crafted and rather ugly to maintain (this would be true on any platform).
On this particular platform, they had decided not to use monolithic stored procs (a proc with a lot of serialized operations in it) but use modular ones. So modular in fact, that each inbound data stream had its own dedicated "receivor" stored proc, followed by three more role-based stored procs plus another two - one to validate and one to push the data into the final target table. All 150 incoming record descriptions/filestreams had these 6 stored procedures assigned to them. With one catch - they were the same "role" of stored proc, but all of them were different. That's right, to intake 150 tables we saw 6 stored procs each, for a grand total of 900 stored procedures, and this was for just one of several data sources!
Many of you OO aficionados see something screaming out at you, that this should have been one general-purpose loader with six phases of operation, serving all 150 streams. Adding another source and another 100 streams, no problem, they go through the same loader and phases. Need more phases of operation? No problem, just add them to the loader and everyone benefits. Forever. It's a beautiful thing.
Of course, this means a deliberate instantiation of some reusable infrastructure. Many app-developer folks are not familiar with how to do this. After all, with 150 tables incoming,we could expect those definitions to remain pretty stable. But if the same stored procedures are facing the internal data model(s) (and they must), then we have worse than a cut-and-paste rat's nest, we have a hand-crafted rat's nest. If the data model must change, we may effectively invalidate most if not all of the stored procedures. Can you even imagine having to review - and re-review 900 stored procedures so -- oh never mind.
It therefore did not surprise me to learn that they had "frozen" the data model so that the stored procedures could have higher durability. We know this isn't realistic either, because the business will start to drive more requirements into the solution and the model must change to accommodate it, even if it's just attribution of existing tables. How do we keep these things from impacting the existing code? We have no choice but to freeze the data model. But this isn't really a choice, it's more like a un-necessary evil. Their stored procedure implementation only guarantees one thing: their functional code base will be in flux and unstable for the duration of the solution's lifetime.
I made a valiant attempt to explain the rather problematic issues concerning their implementation. (Problematic here, is a polite and professional term for rat's nest without having to say so). I have to admit however, that "rat's nest" may have done disservice to the rats. They also wanted me to "jot down" a list of "enhancements" that would make their solution better, stronger, faster - all that stuff. I could not think of a profesisional term for "burn it to the ground and start over".
Perhaps I could have told them the bullfrog story.
Rat's Nest Number Two
In keeping on our theme with stored procedures - recall - stored procedures in Netezza were originally concieved for supporting the front end BI tools. Not back-end data processing. In fact, pushing the back-end data processing under higher programmatic control is something new - even to ETL tools. That Netezza supports it very well is a bonus. Actually, Netezza does it better than any of the other databases, because the ease of manufacturing an intermediate table, using it and tossing it, is amazingly simple and easy to manage. Other machines, not so much.
But when I say "ELT" or back-end processing, it's still a SQL statement. We have options, like hand-crafting the SQL in script. Been there, done that. Or hand-crafting a stored procedure. Not really interested in doing that again. And then we have generated-SQL from a template or metadata-driven framework.
ETL tools have pushdown, but it's still pretty weak. At least, too weak for power-users like moi. I have no doubt that they will step up, eventually.
In this example, we have the opposite problem from the first. Just as much of a rat's nest, it is a monolithic stored procedure rather than a gaggle of modular ones. The monolithic stored procedure often runs for an hour or more, executes hundreds of SQL statements along the way, and has a lot of detailed steering logic embedded amongst them. It is a veritable nightmare to code and debug, and even worse to troubleshoot. I hear that some developers have been invited to padded cells afterwards, but I think those are just exaggerations. It can't be that bad, can it?
Given choice between only the two, I would choose the gaggle-of-modular over the monolithic. I mean, if you were implementing it and not me. I always have the choice to say no. I don't work for your company, after all. You may not have a good choice. Your uber-architects and their hired guns have told you it's stored-procedures-or-nothing. So it's time to pick a poison I suppose. I'll take hemlock for $400, Alex.
As these stored-proc programmers stared back at me with hollow eyes, I thought I had entered some macabre Tim Burton flick and all we needed was some spooky music, fog-machines and strange howling in the distance to make it complete. They spoke in muted, muffled tones and their questions seemed to drift. Had they slept in like, the last 48 hours? They all looked sooo tired. This is what a monolithic stored procedured does to your staff. Now watch it drain the lifeblood from your operations staff. It is the virtual/technical equivalent of leeches, and you thought we'd left those behind in the Middle Ages (for technology, that would be the 1990's)
Stored procedures don't have single-step capability. When we add another function to it, we have to test all of the functions at once, because it has to run end-to-end. We can creatively work around this in the beginning, but eventually we have to integrate it. When one test takes over an hour, or two, and the answer is buried in the mountain of carefully crafted NZPL-SQL code, at some point we have to wonder what we signed up for. (that would be, we signed up to do it wrong). Ouch.
Stored procedures cannot be parallelized (unlike their more modular counterparts) and as such is a glaringly missed opportunity. They are doomed to be serialized forever.
Now, our framework (that we consult and use as a problem-solving platform for Netezza - nzDIF) handled 100 percent of all data lineage no matter how many intermediate tables, databases or machines are involved in the overall flows and handoff of work. You won't get this with any other product, nor with anythng a stored procedure has to offer. This would be true of any stored procedure on any platform.
This is because on a transactional platform, procs are meant to handle multiple operations on singleton entities so data lineage simply is not an issue. On a Netezza platform, procs are meant to serve the BI platform, not the back end, so likewise data lineage is not much of an issue. Stored procs for the front end are largely summary/filters for pre-existing datasets. We want the lineage on those datasets, not the on-demand operations that consume them. "Could" we expect data lineage from stored procedures in Netezza? Why? The only reason would be to support back-end processing, and stored procedures are not for back-end processing. It's sort of a Catch-22.
Rule #10 in play here
And let's not forget Rule #10, shall we? Recently emblazoned in glowing letters on the catacomb walls of the Underground, Rule #10 is very simple: Never do bulk data processing in a general-purpose RDBMS engine running on a general-purpose platform.
Now, I just had to get Rule #10 into the forefront because this underscores the primary reason why stored procedures are bad for back end data processing. If we have a rule in place against using SQL for bulk processing on a general-purpose platform/engine, then any experience we may have with bulk processing through stored procedures on such a platform is itself a violation and not a marketable skill in the Enzee Universe. More importantly, it institutionalizes the violation and makes it so much worse. We could "maybe" dig ourselves from a ditch if we're using hand-crafted out-in-the-open SQL (also not recommended) but when ensconced behind the fortress of stored procedures, we have to first storm the fortress before we can loot it. Easier said that done.
All that said, folks who have instantiated stored-procedure-based data processing on general purpose platforms have already been doing it the wrong way, so why bring those practices into the Netezza machine? Just sayin'
Rat's Nest Number Three
Ahh, you thought we were done and coming into the home stretch, eh? Well, we're almost there.
This particular rat's nest only appears in places where people have churned a lot of contractors, consultants and other aficionados and hired guns through the company's various revolving doors. And as the person who inherits it rightly recognizes if as a rat's nest, or a hairball, something comes to mind that rushes through their brains like a river of water "Wow, and I deliberately signed up for this. What could I possibly have been thinking?"
Ahh, not to worry, this syndrome is rare, and shall pass. Breathing deeply will override the spooky breathing cadence of the Dark Lord of Expectations, and shall give you extraordinary confidence on how to resolve this problem.
This condition is entirely severable from the technology itself. Any shop that allows the contractors to establish their own standards without oversight is just signin' up for a world-of-hurt somewhere down the line. Fortunately for us, the Netezza machine is like a monster truck with gumbo-mudder tires. No matter how mired-in-clay it may be, we need only fire the engines and punch the accelerator to regain control and be underway in no time.
The first step, like any 12 step program, is to recognize that a problem exists. Bandaging a hemmorraging wound will not heal it. This will only forestall the inevitability of bleeding out. If we are to be proper stewards, bandaging has its benefits while we treat the larger wounds.
First and foremost, commit to some form of data management logistics. And this is not by purchasing a data backup tool. This is a committment to flow-based, insert-only architecture as the rule, with updates and deletes as the exceptions. After all, if we were using an ETL tool, we wouldn't be able to update or delete a flow of work. We can only integrate and filter the data while it's on its way elsewhere, but that elsewhere will always be an insert-only target - because it's a file set and and not a database. Only when we reach the book-end of the database can we perform updates and deletes, and these are largely to support things like constraints and slowly-changing dimensions. We just need to avoid invoking an insert/delete/update protocol for all tables at all times. Center on a theme and accommodate the exceptions. We must have rules, and this is one of them.
Commit to some form of rules-driven architecture. That is, when we encounter a new condition or potential fork in the logic, consider shaping it with a rule (one that we can switch on/off or modify from afar) rather than hard-wired SQL or hard-coded solutions. Is this easy to do? Of course not. Nothing is easy about data warehousing or large-scale flow mechanics, silly rabbit.
Netezza has simplified the harder, tedious and repeatable parts so that we can actually address the issues we never had time for before. The "next level" was never in view, or even on the radar because we were always immersed in the operational weeds of the implementation. With a Netezza machine, lots of that is behind us, but before us stands the new challenge. It goes something like this:
If I were to give you a 400-slice toaster, your problem is no longer toasting bread, but bread management. Keeping the toaster busy has now become a daunting problem of bread logistics, not machine capacity. The problem domain has shifted into a zone that lots of folks don't have any experience with. Time to step up.
DavidBirmingham 2700043KNU Tags:  poc mpp jag analytics scalability scale puredata netezza fiat 1,126 Visits
I recently bumped up against a Proof-Of-Concept where "Two MPP Powerhouses went Toe-To-Toe" - and I was fairly excited to see that there might be a contender in the ring, stalking the PureData Analytics/Netezza machine. These POC's are always fun to watch. I am sure not quite as gratuitous as gladiators in the Colosseum, but engaging nonetheless.
In this corner...
The contender, dressed in white, was an MPP, er - clustered servers posing as an MPP. Now let's level-set on what an MPP is, and what it is not.
As we can see with the above high intensity graphics - 6 - 2 Cylinder Fiats versus a 12-Cylinger Jag. Now here's the trick question so don't be shy: Which one really accelerates to close the distance faster? Take your time, I'll be right here.
It's no mystery that orchestrated, optimized and purpose-built hardware beats general-purpose commodity hardware every time it's tried. The contender was a cluster of commodity servers posing as an MPP. When we tried to launch scanning analytic queries on it, we could practically hear the whirrrrr-click of the machines as they quietly, well, went silent. For a very long time. I wondered if they would ever offer up the answer. Unlike the Hitchhiker's Guide where they had to wait a million years to get the answer to the ultimate question, we decided to kick off the same query on the PureData/Netezza machine.
I hit the "enter' button while I was standing at the keyboard, then recalled that I needed to check on something else and lowered myself into the chair, but before I could sit - Netezza had the answer. No, it wasn't "42" but something a bit more actionable.
We left the bulding that day satisfied that the Jaguar had in fact smoked the competition. I probably should mention that even as we left the building, the "other guys" still had not come back with an answer. Sad indeed.
Scalability for scanning/bulk operations is a result of strong architecture. It cannot be cobbled together with general-purpose parts. The cluster of servers posing as an MPP had failed. Cluster Failure. Send it back.
DavidBirmingham 2700043KNU Tags:  performance base zone analytics map cluster puredata netezza materialized view 649 Visits
In this case, a tortoise-brained hare is an animal that is capable of going fast but wonders why he can't.
Over the last six months or so I have seen a "trending" situation with those who use the Organize-On. This is some pretty cool functionality so it's important that we get it right. Generally speaking, folks who understand how zone-maps work will have a splendid time with Organize. Others, not so much. The machine is supposed to be fast, so why are all my queries so slow?
It is clear that our outlying cast of folks who have never been introduced to zone maps think that the Organize is either a clustered-index or some other kind of indexing scheme. Fair enough, is this because of the deceptive naming? Like the Materialized View? (okay, don't get me started). The Organize keys are not keys in the same sense as indexes and are certainly not used as join keys.
They are filter-attributes. By this we mean that we fully intend to use these columns with constant values, lists or selected-lists as constraints.
Zone Map Primer
Here I will use my favorite example because it is time-tested and describes the capability. If I pay a visit to Wal-Mart and I'm looking for batteries, I might visit the customer kiosk and the lady tells me that the batteries are "on the end-cap, Aisle Three". This is an indexing model because she told me exactly where to look. This does not scale because when we get to billions of rows we spend more time searching the index than retrieving records.
In a Netezza model however, she would say "It's on an end-cap but I'm not sure which one." So I go to Aisle 1, then 2, then 3 and jackpot, I buy the batteries and go home. The most important part of her answer however, was in what she "did not say". She did not say that the batteries were in Men's or Women's clothing, Automotive or Electronics. In constraining the search to a particular location, she also told me "where not to look". This aspect of "where not to look" is critical to understanding zone maps. It is also critical to stratospheric scalability. Clearly if this Wal-Mart were to quintuple in size, my battery-buying-duration experience would not change in the least.
Now for the geeky part:
Each Netezza disk has 120K physical extents. Each extent has 3MB of space, divided across 24 pages(blocks) of 128k each. If we are running prior to OS 7.x, we will be zone-mapping at the extent-level. If we are 7.x or higher, we will use the page-level. Run don't walk to 7.x and page-level zone maps! They will radically reduce I/O on the extent and this is critically important. A set of records found only on a single page can return in 1/24th of the time than if it has to scan the full extent (96 percent faster). In a real-world experiment, the zone maps on a Twinfin-18 were changed from page-level to extent-level and the same battery of queries executed against it. It performed 5x to 10x slower than with the page-level turned on. Do not underestimating the influence of disk I/O on query turnaround. Netezza is a very physical machine.
Here's another internal example: Compression in Netezza is a nominal 4x. I have seen it much higher. Let's say we have an uncompressed record that is 10,000 bytes in size. When we read it, we will read all 10,000 bytes. If the data is compressed however, we will read only 2500 bytes, a 75% reduction in disk I/O. Netezza is the only platform where compression boosts the power in both reading and writing data because it reduces disk I/O tremendously.
Zone maps are a table that Netezza holds in memory for supporting a table. Each table has its own set, describing the contents of the extents/pages allocated to the table on a given dataslice. The contents of the zone maps are built automatically when we load up our table. It will collect the information from the records stored on the given extent/page for all integers, dates and timestamps. It will store the high and low value of each, representing the table's record "ranges" for that extent.
Thus when we execute a query using one of these columns as a filter-attribute, it will go to the zone maps first and cherry-pick only those extents containing the data we want, excluding all others. this means that the data on those other zone maps won't even see the light of day for the query-in-play. If we use an Organize, we can use a wider range of data types and be more deliberate with zone-map management.
Let's say we want to search-on the transaction-date on our fact table. If we have not physically organized the data, the same transaction-date value may appear across many hundreds or thousands of extents, affecting the high/low range of many zone maps. If however, we were to physically co-locate the records with common dates into tighter physical groups, the records will physically appear inside fewer zone maps. These are the extents/pages that the machine will cherry-pick for scanning and will completely scan each one. We want it to scan as few of these as possible.
In times past, we had three primary ways of doing this.
Let's say that our table's data takes up 400 extents on each data slice. If our transaction-date appears in 300 of these (it is poorly organized) then when a query runs like so:
Select count(*) from mytable where transaction_date = '2014-01-01';
All 300 extents will be searched for this information. However if we Organize on this transaction-date and then groom - the data will be physically shuffled around to co-locate the records with the same transaction-date on as few extents as possible. Let's further say that once this happens, the given date appears in only one extent. What we have just done is optimized the table 300x. We have eliminated 299 other locations to look for data. This is important because scanning a 3MB extent is a lot of work. If we are scanning 299 additional extents for each dataslice, we're really doing a lot of extra work for nothing. If we translate this to a page-level problem, we may have 24x300 pages originally containing the keys. If with Organize we reduce this to a single page, we have further reduced our scanning load by another 96 percent of the extent containing the page.
The important factor in the example is the "out-in-the-open" value of "2014-01-01". It is a "filter attribute". It is not being joined to a table with this value, Doing it this way means that the FPGA/CPU will discover which zone maps have a high/low boundary that contains this value and will retrieve a candidate list of them, If there is only one as opposed to 300, we have radically reduced out workload. Netezza will literally exclude those extents from being examined at all. We have told the machine where-not-to-look. If we join this value however, such as using a time dimension, applying the date value to time dimensions and joining the time dimension to our fact table, we will require the system to fetch the record in order to examine it, at which time it will determine whether to keep it or toss it. this sort of thing can initiate a full table scan, nullifying the zone map entirely.
We don't want this to happen. We want the data to stay on disk and never see the light of day if it's not participating in the query.
To show how dramatic this can be, we were at one site hosting over 100 billion rows in the primary fact table. A full scan of this table took 8 minutes (which is not too shabby in itself, just sayin' ). The reporting users knew that if a query ever exceeded a few minutes in duration, it was probably ignoring the zone maps. This is because once-Organized, this table would return the average query in sub-second response. Think about that,100 billion rows in sub-second response.
This is why paying attention to zone maps is such a big deal. Optimizing distribution can get us boost in the single-digits (2x, 3x etc) on a given query. Optimizing zone maps can get us 1000x boost and higher.
The Organize-On accepts one or more keys which will be applied to physically co-locate records of like-valued keys, then it will update the zone maps. Here is a test to see if we understand the application:
Take one of your largest tables. CTAS the table to another database, order by the distribution key, or by the hidden "rowid" column to make sure that the given filter key is not ordered. This could take a bit of time, of course. Then perform a query using one of your date parameters as in the example above, and time the query. Now perform an
Alter Table tabname organize on (date column name here).
Then perform groom. Once completed, execute the same query and get a timing. We can see that a several-minute query can go sub-second very easily.
What's more the additional keys in the Organize are (more or less) independently organized. They will all enjoy a much faster turnaround than not using the Organize. If records arrive on the table out-of-order, no worries. Run the groom again. Subsequent runs of groom will always be shorter in duration than the first one.
Clearly however, if the zone-map is intended to apply filter-attributes to guarantee the exclusion of extents/pages completely, we cannot use a join-key. Or at least, not a join-only key. This also means that the distribution key is out (unless we plan to call-up an individual record based on the distribution key). Also, generally do not mix high cardinality keys with low cardinality keys. Netezza finds its strength somewhere in the middle. We will find that it disfavors the low-cardinality keys when high-cardinality ones are in-the-mix.
A good way to tell which of the filter attributes for a table are "high-traffic" is to turn on query history and then examine the table/view associated with "column access statistics - $vhist_column_access_stats. This will provide the number of times the column participated in a query and with which table(s) the base table interacted with. Perform a descending sort on the NUM_WHERE column and this will reveal all. In this short list we will see filter-attributes that are most useful. Don't use any of the join-only columns or the distribution key. These may adversely affect the multi-key algorithm's output and might not optimize any zone maps for this table.
At one site, we noted several inappropriate keys in the Organize, and simply by removing them and "grooming" again, the table experienced a 100x boost. The inappropriate keys were washing out the effectiveness of the other keys.
Many of us have seen this skyline, with buildings of various heights stabbing toward the sky. Compare this to the distribution graph that is part of the Netezza Administration GUI application. Normally this should be very flat (but a jagged-edge is usually okay). Clearly a Manhattan skyline is forbidden.
Or is it? if we have a table that is very skewed (like a Manhattan skyline) but the data can be easily "horizontally" sliced with zone maps, our round-trip time for a "tall" data-slice is no different than a "short" one. All we need to look out for is process-skew (too many horizontal slices on one dataslice)
Measuring the madness
Okay, David this is all very interesting but how can I know which extents or pages or whatever is being used by the keys? Well, there are a couple of handy hidden columns on each row that can help tell-the-tale. One is the _PAGEID and one is the _EXTENTID.
select count(*) , datasliceid dsid, _pageid pid from fact_customer group by datasliceid, _pageid
will tell us how many distinct pages are being used for each data slice.
select count(*), datasliceid, _pageid from fact_customer where transaction_date = '2013-01-01' group by datasliceid, _pageid order by datasliceid, _pageid;
In the above, the "count" should be reasonably even for each dataslice.
If the total count is radically more than "1", then let's organize on transaction_id and then groom. Now try the metrics again to see if it did not reduce the total pages.
I am sure with these two columns in-hand you can think of a variety of creative ways to use them. The conclusion of it all is to get the records with common key values packed as closely as we can so they take up as few extents / pages as possible.
It's a wrap
So now that the Organize seems a little better, you know, organized, maybe this will provide a bit of guidance on how to set up your own Organize and zone maps.
And don't forget to groom when changing the Organize keys.
We won't need to groom every time we do an operation. I would suggest a groom on both a schedule and a threshold. Pick a threshold ( a lot of folks like five percent). When the total deleted rows gets above five percent of the total non-deleted rows, or the total pages per unique data point gets above an unacceptable threshold, it's time to groom. But grooming on every operation is expensive, has marginal value and actually may throw away records we wanted to keep (in case of emergency rollback).
"We're charter members," claimed Bjorn, a tech from across-the-pond, "been working with the stuff for ages."
"Same here," asserted Jack, a DBA from Kansas, "You'd be surprised how fast this thing scoots around in first gear."
"Exactly," said Bjorn, "Those who master the roads in first gear are actually better off. You run at a speed that gets you around but not fast enough that you accidentally run into things."
"So it's a safe speed?" asked the interviewer.
"Indeed," said the pair, almost in unison.
"So what happens when you take it for a spin on the highway?"
The two glanced at each other, then to the interviewer, looked down or elsewhere and then one at a time eventually answered.
"Highway driving is problematic," said Jack. "We'd rather stay on the city roads."
"Yes, city roads," Bjorn agreed, "No need for the highway."
The Director of Integration watched the interview on video and made a smacking sound against his teeth, "Pathetic. We bought the car to drive on the highway most of the time. These guys are acting like a couple of children who are scared of the Big Bad Freeway."
"Well, it has too many lanes in it," smirked his assistant, "If they go on the highway, they might actually get somewhere."
"What's that logo on their shirts?" the Director noted, "Zoom in on that -"
"It's FGA," said the assistant.
"Is that a misspelling? I thought the machine used an FPGA?"
"Oh no, that's correct. It's the First Gear Association. But now they call themselves the First Gear Society. It's way past a club. Now it's a philosophy. Or maybe culture is a better word."
"You mean they actually run around justifying why they stay in first gear?"
"It's almost like a discipline sir," grinned the assistant, "They have rules and protocols for their followers. It's cultish if you ask me."
"I didn't ask you."
"So what are some of these so-called protocols?"
"Well for starters, they can't stand deep-metal. It's like garlic to a vampire."
"But deep-metal is - " he sighed, "Never mind, just keep going."
"If they find themselves going too slow, they try to get the machine to go faster in first gear. If they can't they blame the machine. They think that having a powerful machine is good enough. After all, a couple-hundred horsepower under the hood looks good no matter what gear you're in. And to the other chap's point. If you put it into a higher gear, it'll go so fast that it's hard to steer. They aren't very good at steering, so - "
"So it's a fear thing?"
"Not so much. They like rollercoaster rides, judging from some of their project schedules and deliverables anyhow. They like the whole livin-on-the-edge brinksmanship. As long as someone else is driving."
"Ahh, so just afraid to punch the accelerator themselves, eh?"
"They'll punch it, just in first gear. It's almost like any gears higher than that are a fearful thing."
"How do we get them into the deep-metal? It can't be that hard."
"Oh they go into the deep-metal on expeditions and stuff. They like popping the hood and exploring the architecture. But when they get behind the wheel, it's a first-gear-only driving experience.""Perfect."
"So tell me what to look for in a First-Gear-Society member. What can I expect?"
"Here's a short list," the assistant accessed his handheld and pulled an image onto its screen, showing it to the Director.
"Let me see if I understand this -"
Common symptoms are:
"We were doing just fine until a week or so ago, then everything started to slow down. Jobs are running a lot slower, we're missing all of our deadlines. Has the hardware worn out?"
"We just added some functionality to the solution and two days later the whole machine started to drag. We backed out the solution but the machine is still dragging. I think we broke something. There's a problem with the hardware."
"The machine is having a hardware problem. We've been running these applications for years and then one day everything slowed down. We upgraded to a bigger machine and we didn't get any lift, but a few days later things got worse. A lot worse. This is clearly a hardware problem."
"Wow, they think it's the hardware when they haven't even taken it out of first gear. Amazing."
"Here's the punch line - they honestly believe that they can get more power from the machine if they learn to steer it better."
"No kidding. Take a look at these quotes:"
"We reworked the logic in the queries to tighten things down. Every time we change the query, we get a few percentage points difference in performance, but we need a multiplied boost, like 10x or better."
"We put in summary tables to make things go faster but it didn't help. We have query engineers tightening down the queries so that the join logic is efficient."
"All of our query-tuning has failed. We are at a standstill."
"See all that? They really believe that they can make it go faster from the steering wheel. Like the machine they are sitting in is not the source of power."
"How do we convince them to take it out of first gear?"
"Not sure that there is a way, sir. They are so accustomed to believing that the steering wheel is the place to affect performance that they haven't bothered to examine where the power-plant actually resides."
"Under the hood."
"That's right, the power is in the hardware. The steering wheel can only direct the hardware to the right location, but cannot affect power."
"Well, they can certainly steer it poorly."
"Oh sure, like driving it through mud and all that. But that brings up another issue. Some of them like an extreme challenge, so rather than taking it out of first gear, they try to make the machine go faster by making the gears grind."
"Sure, a grinding gear just seems like it's working hard."
"But they don't see that the friction of the grinding is slowing them down?"
"Not in the least. Look here:"
"We built our tables to provide fast back-end data processing, but the distribution we use for back-end processing doesn't work so well for reporting. Every time they issue a query, it's terribly slow. If we copy the larger tables to another distribution, it makes back-end processing slow but the reporting fast. We can't seem to win here. So we're going to keep the distribution for the back-end and then find a way to build something else for the front end. They want us to make two versions of the big tables, each on different distributions. The experts tell us that this is common and the best thing to do, but it sounds crazy to us."
"See that? They don't even listen to the experts. They would rather grind the gears."
"But all they are saving is disk space. I mean, seriously?"
"Old ways die hard. They would rather preserve disk space and incur the wrath of the users."
"Or preserve disk space and then build-out a convoluted set of summary tables that is twice as hard to maintain."
"Yep, the simple, dumb photocopy of data into another distribution is just too simple. They have a need to engineer."
"Engineering in first gear is not really engineering. It's more like polishing the stick-shift."
"Here's another quote:"
"We have a lot of complex views, and those views join to other views. I've seen EXPLAIN plans for this hit fifty to one-hundred snippets deep. Netezza returns this stuff fast all the time. Only now it doesn't anymore.
"Ahh, so their underpinning tables finally reached a tipping point."
"Yeah, this is funny. They add a ten pound weight to the trunk of the car every day and three hundred days later, and 3000 pounds heavier, the car is starting to run slow. Imagine that."
"Running slow because they have it in first gear."
"Well of course, if they were to use a higher gear, all that weight would be practically weightless."
"Funny how the density of deep metal is a lot like anti-gravity."
"Well, the competition sees these things happening and they say, you know, with that particular vehicle, the more weight you add, the slower it gets. It's just the nature of the machine."
"But that's not true on deep-metal."
"Not at all. We know folks who have tons and tons of weight on the machine but it's light as a feather."
"They're using higher gears and the deep metal together."
"Absolutely. Nobody from the First Gear Society is allowed in their shop. They know better."
"Well that's a problem with nested views. The master query attempts to provide the filter attributes but if the nested view doesn't pass them along, the underpinning tables end up table-scanning."
"And as you know, Netezza is the best table-scanner in the business. It can scan tables, really big tables, in no time flat."
"But it's not supposed to be scanning the tables. It's supposed to be using the zone maps and filter attributes so that it doesn't have to scan the tables."
"Well, exactly, but the people in first gear don't know this. They can't really tell that their queries are getting fractionally slower with each passing day. Then one day it reaches a point of no return. Sort of like how rust slowly clogs up a pipe until one day the pipe just closes over. It doesn't happen in a single query or just because we added a new table or two. It's pervasive and pandemic across the entire implementation."
"I bet when they hear that, they go insane."
"Yeah it's because with any other machine, remediating something like this would be very hard and tedious. But with Netezza we can remediate it incrementally and get more lift with each change. It's just not hard to recover the capacity if you know what you're looking for."
"I think the bottom line is that a really powerful machine tends to hide inefficient work."
"Or like one aficionado put it: Netezza can make a really ugly data model look like a super-model, and can make really bad queries look great. They just don't realize that the ugly models and bad queries are sapping machine's strength like a giant parasite."
"Ugh, now there's a visual I can live without."
About a year ago I engaged to assess a Netezza-centric data processing environment. They had used stored procedures to build-out their business processing inside the machine using SQL-transforms. As you know, I'm a big fan of the SQL-transforms approach, but I'm not a big fan of how they implemented it. Stored procedure for back-end processing are a bad idea on any platform. But even if they had done it without stored procs, the implementation was a "total miss". I mean, it could not have been more "off" if they'd done it outside the machine entirely.
I received word some months ago that while their shop remains a strong Netezza environment, for this particular application they intended to go in a different direction, with a different technology. This was unfortunate since I had told them exactly what the problem was - not the hardware but the way it had been deployed. But they were in denial! Forklifting their application onto the new machine, they attempted to tweak and tune it. They actually received marginally more lift at the outset, but then it rapidly degraded when more data started arriving. Now I'm in dialog with them to discuss "what went wrong".
What went wrong began, quite literally, many years prior.
It's like this: If rust starts to build in a water pipe, we won't know it until the water pressure starts to slowly degrade. Eventually it becomes a drip and then one day it's closed off altogether. We could attempt tracing it to a single cause, but it would only be another straw on the proverbial camel's back. What "really" happened was that we treated the machine in a sloppy manner. Or rather, we saw that it had incredible power but we weren't particularly good stewards of it. Netezza makes a an ugly model look great, a good model look stellar, a marginal model look like a superstar, and can make the sloppiest query look like the most eligible bachelor in town. Power tends to make people starry-eyed.
Time and again we coach people on a migration. They say "Wow, we just went from a five-hour query on that Oracle machine to a five-minute query on the Netezza machine. Sold!" and they move everything over "as-is" from the other machine. Never mind that those old data structures were optimized for a different technology entirely and never mind that the data processes running against them were likewise optimized with the older structures in mind. They were both optimized in context of a machine that could not handle the load to begin with. They just didn't know it yet. Now standing in the Netezza machine's shadow, it's painfully obvious what shortcomings the old machine had. Not the least of which being a load-balancing transactional engine, which is always the wrong technology for anything using a SQL-transform.
The bottom line: what if just a little tuning of that five-minute query could make it a five-second query? What if we received a 10x boost moving data "as is" from the old machine, but if we had engaged a little data-tuning we could have received 100x? In short, how many "x" have we left on the cutting-room floor? Enzees have learned (some the hard way!) that performance in a Netezza machine is found in the hardware. This hardware has to actually arrive in massively parallel form, not marginally parallel form. So we know that that expecting production performance from the Emulator or the TwinFin-3 is a quixotic existence. This ultimately leads to two universal maxims:
This "additional boost" or "leftover power" is an important question, especially for the aforementioned Netezza-centric application. Even if we had kept the entire application in stored procedures, their implementation could not have been more wrong. They had of course, outsourced the whole development project to a firm in a distant country, who had given them a marginal development team at best. This team proceeded to treat the Netezza machine like "any other database" and completely missed the performance optimizations that make one of these machines a source of legend.
What that team did, was pull two hundred million records from a master store and use this body as a working data set even though only twenty columns were being processed at any given point. Dragging over two-hundred columns (90 percent dead weight) through every processing query (many dozens of them), and without regarding distribution to manage co-located reads and co-located writes, turned a twenty-minute operation into a fifty-five hour operation. We showed them with a simple prototype how a given fifteen hour process could be reduced to four minutes. The point is, they were ridiculously inefficient in the use of the machine. Nobody in the leadership of the company would accept that they were as much as 20x inefficient.
A major "miss" is in believing that Netezza is a traditional database. It is not. It is an Asymmetric Massively Parallel Processor (AMPP) with a database "façade" on the front end. Anything resembling a "database" is purely for purposes of interacting with the outside world, "adapting" the MPP layer for common utility. This is why it is firmly positioned as an "appliance". If the internal workins' of the machine were directly exposed, it would cause more trouble than not. Interfacing to the MPP "as a database" is where the resemblance ends. This is the first mistake made by so many new users. They plug-in their favorite tools and such, all of which interface (superficially) just fine. Then they wonder why the machine doesn't do what they wanted. Or that they are experiencing the legendary performance.
When an inefficient model is first deployed, we could imagine that even in taking up 10x more machine resources than necessary, it still runs with extraordinary speed. But let's say we have 100 points of power available to us. The application requires at least five points of power but we are running at 50 points of power (10x inefficient). We still haven't breached any limits. As data grows and as we add functionality, this five points of power rises to 8 points, where we are now using 80 points of power in the machine. Wow, that 100 points of power is getting eaten up fast. We're not all that far away from breaching the max. But data always grows and functionality always rises, and one day we breach the 10th point of power. And at 10x inefficiency, we have finally hit the ceiling of the machine. It spontaneously starts running slow. All the time. Nobody can explain it.
The odd thing, is that the Netezza machine is a purpose-built appliance. Why then did we allow our people to migrate a schema optimized for a general-purpose machine into a purpose-built machine? Moreover, why did we continue to maintain that the so-called purpose-built model in the old machine was really a general-purpose model in disguise? Did we use general-purpose techniques? Why?
Did we load data into one set of structures expecting them to be the one-stop-shop for all consumers? A common-mistake in Netezza-centric implementations is that one data structure can serve many disparate constituents. The larger the data structure, the more we will need to configure the structure for a constituent's utility, and may need redundant data stores to serve disparate needs. Is this reprehensible? Which is better? Just declare another identical table with a different distribution/organize and then load the two tables simultaneously? If we go the summary-table route, the cost is in maintaining the special rules to build them along with the latency penalty for their construction. It seems counter-intuitive to just re-manufacture a table, but the only cost is disk space. On these larger platforms, preserving disk space while the users are at-the-gates with spears and torches, doesn't seem to be a good tradeoff.
The point: Don't waste an opportunity to build exactly the data model you need to support the user base. Don't settle for a contrived, purist, general-purpose model. If the modelers say "we don't do that", this is a sure sign that we're leaving something very special on the cutting-room-floor. It's a purpose-built machine, so create a purpose-built model, and like the Enzees say, "give the machine some love."
When capacity seems to be topping-off, with a few, simply-applied techniques we can easily recover that capacity. It's just very annoying that it's caught up with us and nobody can seem to explain why. It's because they are looking in the wrong place. If we had concerned ourselves with the mechanics of the machine and its primary power-brokers, the distribution and the zone-maps, and avoided the most significant sources of drain, like nested views and back-end stored procedures, we might be closer to resolution. If not, it may be that resolution would require rework or retrofit. In the above 50+ hour operation, the only answer would be to overhaul the working queries end-to-end. We wouldn't need to do much to the functional mechanics, just streamline the way the queries perform them.
What does this streamlining look like? Well, if we already knew that, we wouldn't be having any problems. We would have been streamlining all along and most of our capacity would still be well-preserved. People do it all the time.
Symptoms of a PDA system under stress:
It's sort of funny how deterministic the machine is. "We've been doing the same thing all along" and now it's not working right? Perhaps we weren't doing the right thing all along, regardless of how consistent our "wrongness" was? This is how we know it's not a hardware problem. In fact, if our folks are blaming the hardware first, it's the first sign of denial that the implementation itself is flawed. If our people build contrived structures like summaries, it's a sure sign that the data model is flawed. It's also a sure sign that we're trying to implement a general-purpose schema rather than a purpose-built one. If our people spend a lot of time swarming around query-tuning, it's a sure sign that our data structures aren't ready for consumption. Nested views never have a good ending in Netezza.
At one site, the users had to link together eight or nine different tables to get a very common and repeatable result. If the users must have the deep-knowledge of these tables and their results are repeatable, we need to take their work and manufacture a set of core tables that require less joining and are more consumption-ready. Consolidation, denormalization and shrinking the total tables in the model are actually performance boosters. Why is that? The more tables are involved in the mix, the more we have to deliberately touch the disk through joining. If we have fewer tables and more data on larger tables, zone maps allow us to exclude whole swaths of the tables altogether. We stay off the disks because the zone maps are optimized for it.
It sure "seems" right to put everything into a third-normal-form and make the model "look like the business", but nobody is reporting on the business. They're analyzing, not organizing. We should be the ones organizing the data for analysis, not requiring the analysts to organize the data "on demand" through piling tables together in their queries.