April 17, 2015 // By Stevo Smocilac
A good data warehouse is a core component of any organization’s BI infrastructure. It is used to create the cubes, reports and dashboards that deliver business value. For this reason, it is extremely important that any new data warehouse project is successfully executed, otherwise the rest of your BI infrastructure will be negatively affected.
In this blog I am going to highlight some tips which, from my experience, can substantially improve the likelihood that your data warehouse project will be successful.
Keep the End Goal in Mind from Day 1
When designing any new data warehouse, it is extremely important to plan with the big picture in mind. Focus not only on how your design will solve the current business problem, but also on how your solution will fit into a future organization-wide data warehouse. Investing extra time up front can prevent the need for major design changes to support new functionality down the line.
A really useful tool to use when working on this phase of your project is a bus matrix. This can help you visualize your solution, communicate it to the project stakeholders and prioritize development efforts.
Figure 1. Example of a Bus Matrix for the AdventureWorks sample database
Limit the Scope of Your Project Phases
Try to keep the scope of each phase of your data warehouse project small enough to deliver a working product every 3-6 months. This modular delivery model has a number of benefits including:
- Increasing the stakeholders’ confidence in the project
- Allowing business users to start using the new model to immediately benefit the organization
- Helping detect any design issues early on so they can be addressed in subsequent phases
- Spreading out the project costs
- Minimizing the organization’s project risk
- Allowing you to more easily react to changing environmental conditions
We have found the following project approach, based on standard SCRUM and Kimball Dimensional Modeling methodologies, fits in very well with this delivery model.
Figure 2. Magenic BI project approach
Don't Base a Design Solely on Existing BI Systems and Reports
When interviewing users for new system requirements, you may hear this familiar sentence: “Just make it look like my current reports.” If you dig a bit deeper, you may find that a number of the current reports are no longer even being used, or that even though they are currently consuming the data in a specific way, they would actually prefer a different type of solution. Often the current design may simply have been developed due to historic technical limitations. Taking some additional time during the envisioning and design phase will also ensure that you don't miss any new high value data which was not part of the old BI system or reports.
Lastly the capabilities of the Microsoft BI stack are rapidly evolving, and the new versions of the tools continue to open up many new design opportunities which should definitely be taken into consideration when designing a new system.
Keep it Simple
This is actually harder than it sounds, and I find that I need to keep telling myself this throughout the project lifecycle. As technologists, we tend to want to do things in interesting and exciting ways. Sometimes this causes us to forget that the systems we design will need to be maintained and used by people with varying levels of expertise. The simpler and more understandable we can keep our designs, the more likely it is that our systems will be successfully adopted.
One part of this mantra which is especially relevant to data warehouse design is to only include data which is really required. Avoid an "if I build it they will use it" mentality. If users are not currently using some data for analysis, it is highly unlikely they will start using it just because you added it to the new data warehouse. A much better approach is to build a highly efficient, well-structured data warehouse that they can easily use to gain additional insight into the data they already have. As their usage patterns evolve and become more sophisticated, they will hopefully reach out and request additional data that will add even more value to your warehouse. The pattern here is that the end users will always understand the data better than the data warehouse developers, and they are normally more suited to make decisions on what new data should be added.
Plan for Continuous Improvements
A static data warehouse becomes less and less useful as the business evolves over time. Your initial design should include some type of process to make future enhancements to your new system. Ideally, these enhancement requests should come from and be prioritized by the business users. In this way they have input into the future direction of the design, and they are aware of the resource constraints and implications of their requests.
Hopefully these tips give you some food for thought and help you on your path to a successful data warehouse implementation.