If your company is planning to build a data warehouse or BI solution (see Why you need Business Intelligence), you need to be aware that BI projects have high failure rates. Gartner says between 70% to 80% of corporate business intelligence projects fail (see Poor communication to blame for business intelligence failure). And with “big data” adding more complexity you can expect even more failures. However, the major causes of these failures are well-known and can be avoided by implementing a set of best practices.

I have worked on dozens of end-to-end BI projects (which almost always include the building of a data warehouse) and have seen my share of successes and failures. Below I share a list of reasons BI projects fail, best practices, and lessons learned so your BI project will fall into the “successful” category:

Business user involvement is key. Get the key stakeholders involved early and often. They will feel like they are part of the solution and will be much more likely to praise your solution then if you ignored them. Get their input and get them excited about the project

Get inside the users head. Find out how they view their world. Start on the right viewing their reports and then go left to the sources

Poor communication between IT and the business is a killer. Make sure you are constantly communicating with the business on your progress, giving them status updates, showing them your solution as you build it, etc. There is a gap between business and IT working together that you need to close

A major mistake is the failure to ask the right questions or to think about the real needs of the business. Focus on the questions you’re trying to answer, not the data you have. You are trying to solve specific business problems and that unusually involves using KPIs

Business users can only focus on how they do work now, making it difficult to understand how to solve their problems and answer their questions

Use PowerPivot to prototype (visualization prototype). You mentor them along. This is a much better solution than meeting and just talking about their requirements. They don’t know what that don’t know: they need to see it to know what they want

Business is changing where IT is no longer creating the rules. You need IT and business to work together to build rules

Get people excited about BI thru training, which can help them in choosing the right tool. Cover all the various Microsoft reporting tools so the users have an idea of all the capabilities available

You need to make things simple for business users. Tell the truth, put a good face on it, present the right facts

Use an iterative process or agile methodologies for data warehouse development and the building of BI solutions. Establish an SDLC that fits your BI team

Inexperience and lack of technical knowledge by the people running the BI project will cause the solution to be delayed, poorly architected, and have performance problems. Hire perm employees or consultants who have done this before

Scope out all the subject areas that will be needed, but start with a small subject area and do one subject at a time. This will give you quick wins so you are not always hearing “Is it done yet?”. This will also allow you to create a repeatable process that you tweak with each subject area

Follow the Kimball methodology: The blueprint is all written for you in his book

Spend time creating an enterprise-wide architecture and design so you avoid having to go back and re-architect your solution as you add more sources

Team structure is important. Try to have 4-6 people who are building the solution with an expert in one or more areas: SSIS, SSRS, SSAS, PerformancePoint, database architecture, business analyst, program manager, SQL. Cross-train while on the project so each person can become an expert in another area

You need to keep the people building the solution busy: make sure they have plenty of tasks on their plate so they are operating at 80% efficiency. To accomplish this you need someone in a BI director role who is “running the show” and making sure the project is moving along

Minimize politics and try to have one person shield all the other team members from it. Be aware some may feel threatened by the outcome of warehousing projects and some people will claim they own certain data and no one else is going to use it

Make sure to find a champion of project, preferably a C-level person at the client. You need buy-in from top and who can cut through the red tape and push the project forward

Find Subject Matter Experts (SME) for each source of the data

Get a good business analyst. Someone who is good at pulling information from business users will be invaluable

Don’t skip QA. Reserve a lot of time to validate the numbers. The last thing you want is the first impression the user gets is seeing wrong data. They will never get over it

We all hate to do it, but you need documentation, and lots of it. The business users need it to better understand what they are looking at, new developers to the project will need it to ramp up quickly, and you will need it after you are not on the project for a few months and forgot things when you jump back in

Avoid using offshore developers. BI is not like most application development in that you can come up with functional requirements that won’t change and don’t leave room for misinterpretation, which is what is required for offshore developers. Have people onsite with good communication skills if you want a succesful BI project

If you will hire a consulting company to help build the solution, make sure the people they place on the project have a ton of experience doing similar projects and that they do a lot of knowledge transfer and mentoring so you are able to continue the project on your own when they leave

Block out a lot of time in the project plan to implement data quality (profiling and cleaning data). So many times have I heard a client say “our source systems have no data quality issues” only to find that they do (in every case). Your data will never be as clean as you think it is. You know the saying “garbage in, garbage out”

Master Data Management (MDM) if frequently not given any thought until after the data warehouse is built and a business user gets his first report and asks why Customer A is listed multiple times in the report. You realize this is because you have two source systems that you are pulling customers from, and this particular customer was in both. Then you hard-code in SSIS to combine the customers, and more duplicates are discovered and more hard-coding is done. Now you have a mess. Instead, build MDM into your solution early on to help with data cleanup and in addition, it will help with modeling, hierarchies, business rules, workflows, security, auditing, versioning, and integration

When looking at the total time to build an end-to-end BI solution, you will find that at least 75% of the time is in building the data warehouse

If you don’t have the in-house expertise, it is important that if you bring in consultants that they mentor your employees and perform knowledge transfer so your employees become familiar about BI/DW and can add features and fix problems with the solution and not have to rely on the consultants

You need to understand DW methodologies. Should I use a normalized or dimensional approach? What’s the difference between the Kimball and Inmon methodologies? A Data Warehouse isn’t just a copy of the data or a denormalized copy of the data. I have a recording that discusses this topic

Sometimes it’s better to take twice as long and spend twice the money because we get 50 times the value

Don’t spend a lot of time on business requirements. It is utterly pointless paying a Business Analyst to spend weeks asking users what they want from a BI project. Users don’t know – and will NEVER know – for absolute certain UNTIL they see something

Get the required skills (modeling especially). Just because you can build a database doesn’t mean you have the experience to model for a warehouse

Don’t focus too much on end-user tools. The end-user tools should be easy if the model is good, but do try to get the right tool to the right user

Don’t underestimate what it takes to build a proper data warehouse and BI solution. I have seen clients who took years to build a new transactional system and then want to add BI in a matter of months

Avoid building a complex cube as they are very difficult for an end-user to use. If the purpose of a cube is analysis, try not to have more than eight dimensions

Avoid building too small a cube, such as building a cube just for one report. This will discourage users from performing their own ad-hoc analysis. Instead, look at a number of reports and find common data, then build reports to address as many of these reports as possible (while avoiding oversized cubes). The idea is no to turn your BI reporting into a report-writing project but rather a end-user analytics project

Set aside time to performance-tune the cubes. The cube may be “fast” for SSRS reports (i.e. 5 seconds), but that would be too slow when slicing and dicing on a dashboard

Make sure to have a big enough budget and fund all aspects, include hardware, software licenses, and training (data cleanup will chew up more of the budget than you expect)

Push back on aggressive timelines and when you are given a deadline before the project is even started and told to work backwards from that date

Beware the executive who says “BI is easy”. Educate them or you are setting yourself up for failure

Your solution should use star schemas for the presentation of data to the users who will be creating their own reports

You need to create an SSIS framework that includes auditing and alerting

Model the data warehouse to reflect the business and not the source data

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.