The task of validating a new data warehouse is a necessary evil. It’s tedious and time-consuming, but if done properly it will result in a stable and robust data warehouse that is the basis for your business intelligence solution. Here are a few things to consider when taking on the task of validating a new data warehouse:

1. What data sources are driving the creation of your new data warehouse? You will likely have multiple source systems such as a point-of-sale system, a human resources/payroll system, an inventory system, or other industry-specific systems that will be included in your data warehouse. In many cases you will also have your current/old data warehouse and a data-staging database, or a reporting database. You may choose to pull history data from the latter sources if your main source systems clear data that is older than x number of days. This is what I experienced when working with a source system that lets data fall off after two months. The needed data was already archived in the current data warehouse, so those records were brought into the new data warehouse once and new records were added directly from the source system.

2. Do these source systems have data integrity issues or other areas of weakness? The opportunity to build a new data warehouse includes the opportunity to clean up dirty data and fix structural and design issues that didn’t quite correlate with your business processes previously. A data quality specialist will be able to cleanse the dirty data in preparation for your new data warehouse. When it is time to compare the data in your new data warehouse be sure to consider the fields and files/tables that you know have un-clean data. If you can identify these areas ahead of time then you will be prepared to give the new data warehouse special consideration when validating these areas.

3. Who is part of your validation team? Even if contractors developed your data warehouse for you, your in-house employees should always be the ones to validate the data. In my experience the in-house team was made up of the data team and a server administrator. The data team had experience with both the old data warehouse and the source systems and the server administrator was an expert in one of the source systems. The team also involved other employees when investigating information that was job-specific to that employee.

4. We’re a team, now what do we do? There are many different ways to document the validation progress, but I have found that a simple spreadsheet will do the trick. List all of the tables in your new data warehouse and assign a team member to a table based on prior knowledge and previous experience. You should place this document on a sharing site such as SharePoint, Google docs, or even a shared network folder to allow both your data validation team and the data warehouse developers to access it. As you find data integrity issues record them on the document in the corresponding table row. Your data warehouse developers will be able to view the issues and update the item as being fixed. This back and forth correspondence will continue until all of the tables have an “approved” status.

5. Now that you have a healthy level of confidence in your data warehouse, begin writing reports from the data. This will allow you to validate more complex and aggregated data that will challenge the integrity of your data even more. I suggest duplicating reports that you know are currently being used in your company and comparing the results.

Like I said, tedious, but necessary. When you have proven the data to be accurate your users will have confidence in the data which will improve user adoption and make your job much easier in the long run.

With business intelligence dashboards simplicity is key. We’ve all experienced carnival-like websites with side bars cluttered with links, advertisements, and other garbage that is just plain annoying. When we are searching the web we are seeking data and information. Clutter makes it harder for us to extract the information that we need. The same is true with dashboards.

When users view their custom dashboards, they don’t want to see data that is irrelevant to their positions. They also don’t want to see all of the data that is relevant to their positions. Steve Krug wrote a book titled “Don’t Make Me Think: A Common Sense Approach to Web Usability” that addresses the importance of simplicity when designing websites. I believe that the same concept of “Don’t make the user think” can be applied to the creation of an effective dashboard design.

When your business analysts, managers, and VPs log in to view their dashboards provide them a few broad categories that display current data that they might want to glance at to start their day. If you are like me, the first thing you do when you sit down at your desk is check your email. For dashboard users, the second action is probably to glance at their BI dashboards. The beauty of the BI dashboard design is that the users have the ability to drill-down and view details about summarized data, so there is no need to display complex aggregated data on the home page of the dashboard.

Lastly, I have to point out that you can’t guess what the users want to see on their dashboards. Don’t get overly enthused and skip the requirements gathering phase. Meet with your users before designing their dashboards to get a feel for what they would like to see. After they have had access to their dashboards for several months, follow-up with them; Are they using the data that is provided? What types of reports would they like to see? Make changes as needed to keep the dashboards up-to-date and relevant.

An extremely empowering type of software that you are likely to use with your business intelligence solution is an Online Analytical Processing (OLAP) tool. OLAP is used to access multidimensional data in your database and analyze the various dimensions of the data. This is a popular technique used for trend analysis.

One of the most empowering attributes about OLAP is that there are very little technical skills required to produce high quality, accurate, and aesthetic reports. In fact, many people likely use OLAP without even knowing it. Business intelligence solutions may offer this tool and present it as a “drag-and-drop” online reporting interface. In most cases the Business Intelligence Developer will organize a relevant subset of user-specific data (much like a data mart) and the user will be able to select measures and dimensions to build meaningful multi-dimensional reports.

Drill-Down Reporting

This type of reporting allows the user to create a drill-down report which will consist of the main report hyperlinked in a way that users can “drill-down” into the details. For example, if a supply company has a list of clients who placed orders in a given week, a drill-down report would allow the user to click on the hyper-linked client name to display order details, order history, shipping details, or any other relevant detail data.

Aggregated Reporting

This type of reporting allows the user to “roll-up” data (rather than “drill-down”) that can be summed up from a particular dimension. For example, the results of a multi-division marketing campaign can be aggregated to determine the success of the campaign as a whole.

Selecting a Perspective

Reports that are created through OLAP are capable of displaying the data from multiple perspectives. Users are able to remove select sections of the reported data and view the report from a different perspective. This is highly effective when analysts are considering “what-if” scenarios.

Many people who have been in the IT industry for years might still stumble when explaining OLAP to another person. It is complicated only because of the vast possibilities that is presents, but the foundation of the concept is relatively simple: It is a browser-based reporting tool that allows for complex reporting through multidimensional analysis.

Multidimensional analysis uses OLAP (OnLine Analytical Processing) to organize numeric data in a clear and easy to understand format. Multidimensional reports empower decision makers by providing a more complete picture of the data and the transactions that are taking place within the company.

The images below demonstrate how a report can become exponentially more valuable by adding meaningful dimensions. For this example we’ll use the annual entertainment revenue for ticket sales at a local venue:

Traditional Reporting: A single dimension

This type of report gives us basic data for quarterly sales of show tickets.

This one-dimensional report provides insight into the audiences and how much revenue each segment generates.

This reports segments the shows into show types, which provides a bit more insight into which shows are bringing in the highest revenue.

While all three of these reports do have some value, they only provide the information in small portions. This is not the ideal presentation of data that will be analyzed to make marketing decisions. A better option is to use two dimensional reports:

Hybrid Reporting: Two dimensions

When we combine the data from tables one, two, and three we create a sort of hybrid table that can provide a more complete picture of the data.

The 3-D View: Multiple dimensions

This report highlights the capabilities of multidimensional analysis. It presents multipledimensions of data in a clean and easy to read format. An analysis can easily be made to determine which show types and which audiences provide the most revenue in each quarter.

When reports are designed with OLAP using multidimensional techniques your analysts become empowered and their understanding of your company data will present more of a 360 view of activities within your organization.

Guiding your business intelligence project to a successful completion requires the project to have clearly defined goals and success criteria. It may be easier to first create a simplified list of what you wish to accomplish with your business intelligence solution. List questions that you might ask after the project completion. You can then expand on these questions to create a check list type format that will put you on the road to completing a successful business intelligence implementation. Here are a few examples of questions you might ask:

Are the users empowered? / Are the users happy?

These two questions generally go hand-in-hand. When the users feel empowered, they will be happy. Hopefully you have involved the users throughout the BI project to ensure that you are including the data that they will need to excel at their jobs. Towards the end of the project the users should be trained in the areas of the BI software that they will need to use. After the project goes live, be sure to offer plenty of user support. Their first impressions of the BI tools will be lasting, and a good impression increases the chances of user adoption throughout the organization.

Is our data cleaner and easier to analyze?

If your business intelligence project did not involve migrating new data or merging multiple sources, the least you should expect is to see cleaner data that is easier to accurately query. Reports that were previously bulky and convoluted should now appear clean and easy to dissect.

Are the executives able to see value in the new BI software?

In most cases, the value that the executives perceive will be based on the dash boards that are designed for them. This is where they will see the “bottom-line” and where they will infer how much money the BI solution will save the company. In fact, the ROI produced by your business intelligence solution may be the single question that executives ask when determining if the project was a success, so it should definitely be included in your list of success criteria.

Can we better understand our customers?

In a previous post I discussed the 360 customer view. Once your data warehouse is complete your business analysts should be able to mine the data to answer behavioral questions about your customers that they could not previously answer. This especially true if your data warehouse design integrated several source systems that had not been combined in the past.

These are only a few questions that could be asked to determine the success of your business intelligence implementation. List as many as you can and then rank them in order of importance. It will be highly beneficial to consider not only what you consider a success, but the criteria that others in your organization will use when evaluating the success of your project.