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
• Make sure the data warehouse is on hardware that can handle the size and performance needed.  Check out Microsoft’s Data Warehouse offerings
• 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

More info:

Best Practices for Implementing Enterprise BI Solution at SQL Saturday 2013 by Teo Lachev

Why BI Projects Fail (And What You Can Do About It) by Craig Utley

Best Practices for BI: What We Have Learned in 20 years

Business intelligence (BI) How to build successful BI strategy

Right-Sizing Business Intelligence

Data Warehousing: Lessons We Have Failed to Learn

My 30 tips for building a Microsoft Business Intelligence solution, Part I: Tips 1-5, Tips 6-10, Tips 11-15, Tips 16-20, Tips 21-25, Tips 26-30

Why data warehouse projects fail – purpose and black ops behaviour, communication and complexity, blind faith, data, resources

Design Tip #157 Involve Business Representatives in Dimensional Modeling

Data Warehousing Fundamentals Recorded Webinar by Todd Saunders

Business Intelligence: Why Does It Take So Long?

Five most common mistakes in a Business Intelligence project

Seven Steps for Successful Data Warehouse Projects