Best Practices to Deliver BI Solutions

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