Would you recommend datamart or data warehouse for a first time BI project?

I agree with John to some degree, you have to know what the business wants before you can build anything. Start with the requirements, and go from there.

Where I disagree is the suggestion that the Data Mart is the place to start. It may LOOK like the easiest method to produce up-front, but it very quickly becomes the fastest way to tear apart any global or enterprise level effort in the short-term future.

Why? Because it forces you to re-engineer your solution. Every time you want to add new systems, new data, or even change the business rules that are loading the Data Mart - the Complexity of the system increases on an exponential scale. Eventually the complexity of Data Marts grows so large that the effort is not sustainable.

It is at this point that the "data warehousing effort" is seen as too costly, too difficult, and takes too long to change/update. It is at this very point that the business often shuts down/kills the project/labels EDW a failure.

I would NOT suggest going with a Data Mart, because once you've succeeded in a limited and managed prototype, the business demands more.

There is an alternative architecture called the Data Vault Model and Methodology (*note: I am the founder / inventor, and I am not trying to push the agenda - I am merely suggesting you look at this as a solution). There are over 350 world-wide corporations from US Government, to Edmonton Police Services, to ABN-AMRO Bank and ING Real Estate that are using the Data Vault.

Using the right architecture, you can construct in incremental fashion, the proper foundation. You can construct a Data Warehouse foundation that is scalable, flexible, and auditable as you grow.

JP Morgan-Chase used the Data Vault model to merge 3 companies systems & data in 90 days flat to provide reports all the way to the executive staff.

Starting small is key, but having the right architectural blue-print is also key. You wouldn't lay a foundation for a 2 story house if you knew that next year you wanted a 25 story sky scraper would you?

When you use the Data Vault model and methodology, complexity increases are mitigated through divide and conquer. They are also contained to downstream processes that CAN be (don't have to be) independant. You can find out more by typing "Data Vault Model" in to your favorite search engine, or by going to my web-site: http://danLinstedt.com

Hope this helps,
Dan Linstedt

I would recommend a data mart.

So for a first time BI project start by identifying the purpose of the project. Then examine the enterprise data model or create one to determine what data elements are needed from internal operating data. Then determine what external data is needed and whether it exists, must be collected or can be bought. Then create a project data model for the data mart. Extract, transform and load the data from the various sources in the data mart. Now create queries and reports that provide the desired business intelligence. Use the data mart for awhile, keep the data refreshed and assess the results of the data mart project.

I hope your project will be a great success and that you will need to migrate this database to a more integrated data warehouse.

Hi Tom, nice to see you again after meeting you at ING Card. I see you're into analogies so here's mine :)

If people are asking for transportation, sometimes a bike will do where a car would be nicer. And if the person still need to get their drivers license, a bike is actually the best solution - and I'm not even taking the need for practice into account, which is safer with a bike than with a car when you have no experience.

But I agree completely with your statements in the abstract. Data marts are fast and cheap only in the short term, and in the long run probably more expensive *if* you ever go beyond this one datamart. But what if you never do? What if there is no urgent need for more? What if you have never built a data mart or data warehouse? Trying to force management into implementing a DWH in that sort of circumstance is setting yourself up for failure. It also gives DWH implementations a bad name in general, because I see too many people implementing Rolls Royces where all the customer needed was a bike. Yes, there will be a time when they will need the Rolls and are happy for it. But not right now.

That having said: if you have an organisation that is, even if just barely, looking at the limits of a datamart and ready for a DWH: go for it. If you implement a Data Vault I find that just as Tom said, it is a flexible solution that is ready for growth and scales very well. But I disagree that you should do this *in every situation*.

In Decision Support Systems (journal) (49) 2010 Hugh Watson (!) and Thilini Ariyachandra wrote a nice paper about "Key Organizational factors in data warehouse architecture selection". It's an attempt to approach the question posed more scientificly. I highly recommend this paper to anyone. Variables like Information interdependency, Urgency, Task Routineness, Strategic view, Resource contraints, perceived ability of IT staff and sponsorship level are build in a research model that tries to explain DWH architectural style.

It is nice to see the discussion around Datamart Vs Datawarehouse. As suggested by John and Dan, Datamart is a good start for the first time BI implementation and demonstrate the analysis capabilities in BI Reports. After you gain the trust of Business stakeholders, prepare a BI Road map by considering your stakeholders needs, interest and expected IT budget over the next 3-5 years. During this phase, you can make a decision whether your organization should plan for Datamarts or Datawarehouse. Usually, every organization start with Datamarts with a vision of building Datawarehouse for their future needs. Due to several budget, resource constraints and other factors, building a one-source of truth (Datawarehouse) remains a challenge or dream. However, with a solid vision, BI Road map, Budget, Time and Resources, Datawarehouse can be built. Before building Datawarehouse, ensure business makes ROI with the Reports generated from datamart.

Folks, I don't like to rain on anyone's parade here, but building a Data Mart first is simply asking for trouble, especially if the data mart is "happy", and the initial customers are happy.

Building a data mart first inevitably leads to descension among the ranks, silo built solutions, and grumblings about why the IT team is no longer agile, or cost effective. It's a sure fire way to "dig a hole and jump in to it" - regardless of whether or not you have a data warehouse or other marts or not.

I've seen it time and time again, where companies believe this is the right way to a quick solution, only to end up wanting the project to scale (and it doesn't), wanting real-time (and it won't), wanting enterprise data (and it can't)....

Now the arguments for a data mart against a data warehouse (about time to deliver, time to build, getting used to tooling) are all mis-guided in my opinion. With the right methodology, and the right scope controlled project, you CAN build an EDW behind a data mart consisting of several tables (4 or 5 even), and you CAN then feed a single star or single data mart from these structures, and you CAN build it in 90 days or less.

The end result is you have a back-end system that CAN be scaled, IS capable of being flexible, and CAN be fed in real-time and batch at the same time.

I've spent 20+ years or so in the industry, much of that as an implementation consultant in big projects, and some small ones for huge organizations, and I can honestly say folks: it CAN be done this way.

You DON'T need to know the entire business, you DON'T need to go on months long expeditions just to build a data warehouse. What you need is the right architecture that allows you to scope control, build, and be flexible - setting up a foundation that you can work from going forward.

The real question shouldn't be "mart or data warehouse" but rather: how do you build a sustainable BI architecture that's flexible, scalable, and agile right from the start...

By the way, I've seen some Data mart projects that also go on until Business Users can't take it anymore and shut them down... Business users outsource, or do it themselves, because they can't get IT to be agile and the model isn't flexible, and the complexity costs too much to try to "stuff" an enterprise vision into a single siloed data mart.

I hope this helps,
Dan Linstedt
PS: Really, I kid you not. If you are not familiar with the Data Vault model & methodology, you owe it to yourself to check out the free videos at: http://learnDataVault.com

It is difficult to answer this question shortly. But I will do it anyway.

The quick answer is datamarts, because of the short time needed in delivering. The business asks for a solution and wants it to be delivered today.

But, doing BI isn't a technical effort. It should have added value for the business. BI is different to the applications end users know. BI is positioned centrally in the organization, relating to processes and administrations.
By BI end users will get feedback and are trying to understand. It results in more questions than will be answered on a short term. Therefore BI needs a profound "information" infrastructure. My experience is that of all work to be done, 80% is below the surface. End users don't understand the why.

In practice I would say the truth lies in the middle. Other key factors like experience, budget, scope, impact (business case) and last but not least culture have to be taken into account.
That makes it difficult to decide between datawarehouse and datamart.

Marcel de Wit

Heh. This discussion reminds me of one I had a few years ago. I decided there that building a DWH was the way to go - and I was wrong. Currently I'm at another customer, we're again building a DWH first, before doing any datamarts. And this time, we're right.

Both DWH's use Data Vault as architecture for the storage layer. Both DWH have basically the same type of ETL.

What's different? The BI Maturity of the underlying organisation (see last line in my posting for the poster). If you enter an organisation that has no BI experience, management that does not look beyond the next spreadsheet and no experienced IT workers that can support a DWH solution, you are asking for trouble when you deliver a complex beast like a DWH to such an organisation. The reasons for that are many, but the most important ones are: the management does not feel the need for a DWH - they do not yet feel the pain of trying to integrate datamarts. And two: they *will* feel a lot of pain once it turns out they can't keep your beautiful DWH up and running and need to pay you every month, keeping them hostage to your DWH. So in a type 1 and 2 organisation, I'd say: build that datamart, and try to get people used to supporting IT processes aimed at reporting. Build awareness. Build trust.

Buuut... once the organisation has a competent IT department, has managers with experience in reporting solutions, that actually see the need for reports that transcend their own part of the organisation (or when the CEO sees the need for that and actively supports a project for it), then the case becomes different. Make sure that the organisation IS actually ready for it, and not just pulling the wool over your eyes (happened to me in project 1: organisation claimed they were doing great with DWH, when basically they were still living in Spreadsheet Hell).

To summarize: both approaches are valid. If you have the chance, go for the DWH first. However, realize that this requires an organisation with a pressing need for one, and experience in this area. If you don't have that, building a datamart is less risky (and less costly), but realize that if it gets beyond a single system, you will probably have to rebuild it from scratch and at some point, build a real DWH.

Ofcourse, this is just my opinion. But do check out the poster with the model at TDWI: http://tdwi.org/tdwi/tdwi/pages/posters/business-intelligence-maturity-model....

Basically my advice would be: assess your organisation (can be done in 30 minutes) and then build for the next stage. But don't skip stages, it will cause trouble.

The business wants an answer at the lowest possible cost, and more often than not they couldn't care less about the solution. It is up to us BI professionals to come up with a reasonable and sustainable solution. Omitting the data warehouse, in favor of a "fast" and "cheap" data mart ignores the wider information ecosystem we are all part of. Even if you deny it, it's still there. Which is why an agile solution (like a Data Vault) that allows for growth and scalability is what the business NEEDS.

Saying a data mart will get you there sooner is like recommending shoplifting to get quick access to precious goods. Or getting rid of your used engine oil by spilling it by the roadside. It may work, and sometimes you'll even get away with it...

In my opinion the whole question is not that relevant anymore, because why should you make the choice?
I agree with the arguements of Dan, that creating a Mart to start with, soon will become an obstacle.
But the reason for people taking that approach is avoiding the overhead that a DW brings and be able to deliver faster.
Well, that is out of date.
Today, there are software products that automates the creation of DW (even in a Data Vault format) and if you pick the right product, immediately creates Marts as well - including the ETL to populate!
So not only the optimized architecture will be in place from the beginning, but the results are achieved much faster and more flexible. A side effect of these tools is the capability of prototyping in a high incremental frequency as well.

I still think for companies without data marts or data warehouses, primarily medium-sized companies, the best place to start is a data mart. Companies with legacy marts/warehouses may be able to reengineer with an enterprise data warehouse. Figure out your purpose, check your resources.

Before we arrive at the best approach between datamart and data warehouse, we need to understand,
- What is the business model of the company
- What is the business vision, goals and objectives for next 3 to 5 yrs
- What are the current busines and IT landscapes?
- What kind of IT systems will be added based on the business/market dynamisim for next 3-5 yrs?
- What is the current and next 3 years data volumes?
- Scalability of the solution - business users, functional users, analysts, locations , business dimensions etc.
- What are the BI objectives - is it a short term focus or a long term initiative : If it is a start-up/sme business then a data mart approach makes sense, but if it is for an established large enterprise then a two-pronged approach may be required to derive business benefits/roi
- What is the scope - specific business functions or enterprise-wide
- What are the budgets/commercials that are apportioned to accomplish for this BI initiative - a DW involves a higher TCO, which a datamart needs a relatively less budget
- What are the timelines are we looking at - a datamart is relatively quick time to market
- What are the change management implications?
- Are the business functions, processes and systems are matured - for next 3 years?
- Consider people driving the projects - often attrition leads to lack of ownership to these projects.

In practical scenario, any project without an ROI within 6 months - 1year is very hard to justify.

We may need to consider a phase-wise approach for buidling BI infrastructre instead of a big-bang. Because typically, these days businesses / models are so dynamic that they change in 6 - 12 months. So any BI solution approach that does not accomodate business changes is not scalable.

My answer to whether we need a Data mart or a Data warehouse approach will depend after considering all of the above points.

If possible, a datamart. Reason is simple. You have a discrete deliverable that can be delivered in a shorter time span. This does a number of things.

1) Get everyone familiar with the technologies at a working level
2) Refine process and procedures
3) Win converts with an identifiable ROI.
4) Faster time frame.

I have seen Data Warehouse projects that go on forever. Business users never get what they want because of the complexity. Everyone grows weary and drops resources. If you can wade in with a Data Mart it is an easier sell initially and going forward