Logic behind Top Down and Bottom Up approach

[Jeff Smith]

I know that "Top Down" is Inmon/Imhoff and "Bottom Up" is Kimball. I understand that Inmon/Imhoff advocate for a seperate "Data Warehouse" built in 3NF that feeds Data Marts while Kimball advocates for a Star Schema and his definition of the Data Warehouse is all of the data marts (or even one database with all of the data) as long as they use conformed dimensions.

My question, is why is Inmon/Imhoff's approach referred to as "Top Down" and Kimball's concepts called "Bottom Up"?

[ngalemmo]

Inmon recommends that a full analysis of the enterprise data occurs prior to designing the data warehouse... i.e. create (or have) an enterprise data model. That is considered 'top down'.

Kimball recommends identification of conformed dimensions, which could be limited to the scope of what is intended to be implemented in the first round. However, even identifying conformed dimensions across an enterprise is not a particularly difficult or time consuming task. And, all you are doing is identifying them, not specifying content (except at a very high level, such as 'contains information about a customer') or STT mappings. The collection of dimensions and facts are built incrementally as the need arises. This is considered 'bottom up'.

[Jeff Smith]

There seems to be 3 areas of difference between Inmon and Kimball.

1) Approach - Top Down vs Bottom up
2) Architecture - DW feeding marts VS the Marts are the DW
3) Table Structure - 3NF VS Star

Does Inmon's Top Down approach necessitate a central DW built in 3NF feeding different Marts and Kimballs Bottom Up approach lend itself better to Conformed Dimensions .......

Or can the 3 areas be mixed and matched? Can a Top Down approach be used to create Marts with Conformed Dimensions? Before I break rules or recommend or negotiate, I want to understand if a hybrid approach is the path to ruin.

[ngalemmo]

Frankly, I don't understand what a 'hybrid' approach is supposed to be. Some say, because they publish marts using star schema, they have implemented a hybrid approach. But, realistically, it is expected that you would publish marts using dimensional models as some point when you are using the Inmon approach.

The key differences in the architecture, one where you store the data centrally and publish for end user use, and the other where you store the data for direct end user use are distinct. You do one or the other... there is no such thing as a hybrid.

[Jeff Smith]

Ngalemmo - Inmon says the central datawarehouse should be 3NF. If you have a central DW that was in a Star and still pushed data to marts, would that be a hybrid? I personally don't see the value in a central DW in a Dimensional model that isn't accessed by end users.

In any event, your comment, "You do one or the other" should really be written as "you should do one or the other". But some of us live in a world where we have decision makers making decisions about thinks they really don't understand and don't like to reverse themselves. That means the worker bees can either build things incorrectly or try to built it the correctly in a way to appears to be what the decision maker called for. He has essentially said we are doing Inmon but the Central DW will be a star.

[ngalemmo]

No.  If the central warehouse is a star schema, you are doing Kimball.  The fact that you may push a subset of data to a localized mart doesn't change that.  There is nothing in the architecture that prevents that, and there can be valid business reasons to do so.  But, the notion that a central dimensional warehouse is never accessed by end users is a corporate political thing, not a part of the architecture.  I wouldn't call it a hybrid, just a poor implementation choice.

The only reason Inmon does not recommend direct access to the central data store by end users is because it is in 3NF... the model is significantly more complex and difficult to use effectively.

[BoxesAndLines]

...But some of us live in a world where we have decision makers making decisions about thinks they really don't understand and don't like to reverse themselves.  That means the worker bees can either build things incorrectly or try to built it the correctly in a way to appears to be what the decision maker called for...

Jeff,
We all live in that world. Here's the Inmon vs. Kimball for Dummies explanation. Inmon - Build the enterprise data warehouse in 3NF. Spin off dimensional marts for reporting as needed. Kimball - Skip the 3NF and go directly to dimensional models. Use conformed dimensions to tie it altogether.

Only the largest of organizations have the pocketbook to do Inmon. Plus it takes twice as long. That's why I recommend Kimball.

[Jeff Smith]

I pretty much knew the architechture differences. What was throwing me were the terms "Top Down" vs "Bottom Up" and whether the approach dictacted the architecture. If Top Down necessitated a central DW in 3NF or a Kimball's architecture allowed for a bottom up approach.

[BoxesAndLines]

If you start with a business process, as prescribed by Kimball, that strikes me as a top down process. I would say it is an iterative top down process since I'm not looking at all business processes before going to the next stage in the SDLC.

[ngalemmo]

I has to do with the analysis/design involved, not the architecture. Inmon recommends a big picture analysis, working down to specific implementations while Kimball recommends specific implementations that evolve into a big picture.

[Jeff Smith]

So Kimball and Inmon have 2 areas of differences? One is the architecture and the other has to do with how the DW gets built?

Can an org follow Inmon's recommendation of a big picture analysis and implement a data warehouse that looks like Kimball? I think the answer is yes and I would think Kimball would say that a high level big picture analysis is exactly what should be done if at all possible.

As far as I can tell, the difference between the 2 architectures is Inmon says build a DW in 3NF that supports Marts, and Kimball says use a staging area to build marts that become the DW and the staging area may or may not be 3NF (it can have some things that are 3nf and somethings that aren't). But in either case, reports use cubes and/or Star (with some snowflaking, thank you very much Laura Reeves).

The only fly in the ointment are databases that are used by the SAS guys who want data in a specific structure.

[ngalemmo]

Inmon is talking about building from an enterprise data model, and if you don't have one, you need to do it first.  I have not heard Ralph say or write anything close to that.  I have seen enterprise data models... they are usually in 3 or more large binders and the paper has begun to turn brown from sitting on a shelf for the last millennia (or seems like that).

Ralph recommends identifying conforming dimensions as part of establishing the bus architecture to drive design and development plans.  It's like night and day.

The thing is, by the time a group has completed an enterprise data model, the business has changed and it is no longer valid. The bottom up approach is much more agile... you address the particulars of the immediate project with an overall blueprint (the bus matrix) to guide integration of future efforts. It takes someone a few days of interviews with business people and a couple of hours in Excel to put together the matrix. Then a few minutes every now and then to keep it up to date.

[Nancy Rinn]

It may help the discussion if you read this article, The Bottom-Up Misnomer, on our website: www.kimballgroup.com/2003/09/17/the-bottom-up-misnomer/.

[Jeff Smith]

Thanks for the link. You might want to tell Laura Reeves to remove "Bottom Up Data Architecture" from the book she uses in the TDWI course "Modeling beyond the basics".

[pgali]

Using the Top Down approach, is it necessary to have Conformed Dimensions in the Data marts? Can the Customer Dimension from Sales Data Mart be different from the Customer Dimension in HR Data Mart?

[Jeff Smith]

You should use Conformed Dimensions in any data warehouse environment.

[ngalemmo]

Define "Customer".

I would think a customer to sales is much different than a customer to HR. One of the challenges of any DW project is getting some agreement on business terminology. I've never heard of an HR department that deals with 'Customers' in the traditional sense. They may like to call them 'customers' to give them a sense of responsibility or purpose to their jobs, but normally they are referred to as 'employees', 'applicants', or 'personnel'.

From what I can assume here, these are different entities and should be treated as different dimensions. Just give them different names to avoid confusion. If the HR department insists on 'Customer', the call them 'HR Customers'.

On the other hand, if what you are talking about is some sort of tracking of interactions of employees with customers, such as performance feedback relating to customer service or other types of interactions, then customer, in this case, is the same entity as a sales customer and should be the same dimension.

[pgali]

There is an Employee Dim in our case but I made up that example as I was not able to picture the clear difference in Data Marts in the two methods.

I understand that an EDW needs to be created first and then data marts will be created as needed in the top down approach. Excuse me if I'm being too naive but what I'm trying to understand is how the Data Marts created in Top Down approach are different from the Data Marts created using Bottom Up approach.

1) If Data Marts created in Top Down approach have Conformed Dimensions and follow bus architecture, are they not essentially same as Kimball Data Marts?
2) Does this mean that whatever Kimball says must be followed while creating Data Marts even while using Top Down approach?
3) If a group of Data Marts created in Bottom Up approach is considered a Data Warehouse, then why the group of Data Marts created in Top Down approach is not considered a Data Warehouse?

[ngalemmo]

Inmon's approach, a centralized 3NF data warehouse with publication to analytic models, takes the view that publication be in whatever form is appropriate for the application. It could be a flat table, a star schema, a MDDB (aka a cube), or whatever. Practitioners of this approach, when creating a star schema, will often create something to address a specific requirement, for better or worse. This thinking is completely different than creating a dimensional data warehouse.

In a dimensional data warehouse, you are designing structures to retain data at it lowest level of detail (atomic facts) and integrated across conformed dimensions. In an Inmon architecture, that is the role of the 3NF repository.

[Sponsored content]