Data warehouse design from top to bottom

When it comes to business intelligence, should you go for a "Big Bang" by choosing an enterprise data warehouse, or opt for the...

When it comes to business intelligence, should you go for a "Big Bang" by choosing an enterprise data warehouse, or opt for the "let many flowers bloom" multiple data mart approach?

The issue remains controversial. While IT departments might prefer the former on the basis of "let's take the trouble to get it right first time, and not leave anything out", the hectic pace of business life usually militates towards the sprouting of locally-owned, "quick win" data mart flowers, irrespective of whether any data mart can communicate or exchange with any other, or even an enterprise data warehouse built elsewhere.

The bottom up approach
Wolfgang Martin, former senior vice-president, application delivery strategies, at Meta Group, advises against big bang deployments. "Enterprise data warehouse is not big bang at all," says Martin. "You build it data mart by data mart, slice by slice, iteratively."

By building a series of data marts to an agreed architecture, the enterprise data warehouse can be assembled slice by slice, until it is complete enough to regard the data marts as subsets of the now much greater whole. Architecture is key to success, as the data marts must not be built in isolation. Users need therefore to design data marts in the knowledge that each will eventually form part of a larger enterprise data warehouse.

Such an approach can prove attractive to businesses. Each data mart can be implemented within six to nine months. Each can tackle an identifiable business problem making it possible to calculate returns on investment (ROI). The approach also offers a valuable learning curve for the build team, who can test out products and processes until they get it right.

But there are several disadvantages. Since they are so easy to implement, users may be tempted to save time by building a data mart that does not comply with the business' overall enterprise data warehouse strategy. This will make the offending data mart instantly obsolete.

Martin says the only time when a standalone data mart is justified, is if it solves immediate - and temporary - pain. "If the business benefit of the data mart comes from solving an urgent problem, that's okay - if you're prepared to throw it away afterwards," he adds.

Building a temporary data mart may also give a valuable insight into corporate business intelligence problems overall, and how to solve them. But, he warns, make sure users know if their data mart is to be temporary.

"Agree before you start that it will have to go," Martin says. "If you build it, and then switch it off without telling them, you'll be killed."

Even when a bottom-up strategy is built with the enterprise data warehouse in mind, it is more difficult for IT - and the data mart users and sponsors - to see the big picture affecting the whole enterprise when only modelling a part of it in a single data mart.

In addition, if several data marts are built simultaneously, albeit to the same enterprise data warehouse blueprint, there is a resource and project management implication of multiple teams and parallel build. Finally, a successful data mart can be a problem in itself - happy users will want more and more bells and whistles, not caring that other departments are still waiting for their first data mart.

Top down
The opposite of starting with individual business issues and expanding up the organisation hierarchy is to start at the top. A top down enterprise data warehouse and a subset data marts strategy is "the most elegant design approach", says Doug Hackney of business intelligence systems specialist, the Enterprise Group ( He says that such an approach would vastly ease maintenance, summarisation, metadata management and extraction, transformation and loading (ETL) of data.

But which of these opposite approaches works? In a nutshell, if the business pain the data warehouse will solve is felt at the highest, most enterprise critical levels, and the CEO wants it solved, but can afford to wait for an enterprise data warehouse, go for top down. If the pain is hitting lower down the organisation, time is of the essence, and quick ROI is key, go for bottom up.

Reasons for failing
Failure probably will not come for technical reasons, but political ones according to Hackney. "Enterprise data warehouse projects are inherently cross-functional. IT managers are not usually highly practised in the political skills needed to prosper in the sometimes bloodthirsty area of competing functional, geographic and organisational [fiefdoms]," he warns.

Another point to consider is that the enterprise data warehouse must map to the structure of the company. "If the business is organised into stove-piped functions that don't exchange data, [trying to build an enterprise data warehouse] will draw you deeper and deeper into the problem," warns Martin. "Business intelligence systems must be related to the business process - and to business performance management."

What this means is that any business department that is totally isolated from the rest of the business "might as well be outsourced", Martin argues.

Read more on Data centre hardware