BI: The Project that Never Ends

If you are like me, you probably started your business intelligence project with a time line that included a hypothetical “beginning” and “end” date. About a day after the initial launch of our BI solution I quickly realized that there really is no “end” to this project. Many typical IT projects will end with the team members all breathing a sigh of relief, but for your BI project…don’t hold your breath waiting on this “post-project break”.

Your business intelligence solution is only a jumpstart to foster an environment of continual process improvement, with continual being the key word here. A successful BI implementation will merge multiple source systems and provide your analysts with mountains of new data. With merged records the analysts will be able to view the data in new ways and make inferences that they were previously not able to make. These new insights will define areas in your business where processes can be improved which will translate into changing business processes.

When new business processes emerge a chain link reaction is triggered. New processes will ultimately result in new types of data being generated. This may come from new data fields being populated in existing systems, different measures and aggregations that are used to define business terms, or even in the form of a completely new source system. Each new piece of data will now need to be extracted from the source system, transformed into meaningful dimensions and measures, and loaded into the data warehouse. The data will then need to be linked to your BI software so that users can access the data and use it to generate meaningful reports. This cycle is never-ending, and encouraging this type of continuous process improvement will greatly increase the likelihood that you will get your expected ROI from your business intelligence solution.

Spreadmarts: The Spreadsheet Debate

The term spreadmarts was coined by Wayne Eckerson in 2002. The term refers to the partial sets of data that accumulate when users export data from their BI solution into a spreadsheet.

How do spreadsheets fit in with your BI solution? This is a topic that is highly debatable. People in organizations have become so accustomed to putting data into a spreadsheet and manipulating the data before sending it off to VPs and executives to make important company decisions. The biggest danger here is that the data in spreadsheets is not linked to current data. The figures may be outdated before they even reach the desks of decision makers, causing errors in decision making. Each individual that generates a spreadsheet usually has different rules and formulas that are used to aggregate data, so when errors arise deciphering the meaning of the data can become costly and time consuming.

What’s the solution? Most BI solutions provide users with the capability of rendering data in a format that is user friendly while still being connected to the data warehouse. Users will typically shy away from using the BI solution because they are more familiar with Excel spreadsheets and they feel comfortable with the tool. In order to encourage user adoption you will first need to understand the concerns that the users have by questioning them using non-threating terminology. You will be able to slowly increase user adoption rates by explaining and demonstrating some of the benefits that a BI solution has over Excel spreadsheets. In short, show the users how the BI solution can make their jobs easier. Something as simple as report auto-generation and scheduling will save the user time. Demonstrate that BI reports have the same, and more capabilities as spreadsheets. Another strong point to reference is that “homemade” spreadsheets can’t be supported by IT and they usually die when the creator of the report leaves the company. Reports generated through a BI solution are supported by IT and they can easily be transferred to different users.

 

 

Validating Your New Data Warehouse

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.

Why Simple BI Dashboards Provide the Most Insight

http://commons.wikimedia.org/wiki/File:Scudo_dashboard.JPGWith 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.

What is OLAP?

http://commons.wikimedia.org/wiki/File:OLAP_Cube.pngAn 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 with OLAP

http://commons.wikimedia.org/wiki/User:RRZEiconsMultidimensional 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

Single Dimension AnalysisThis type of report gives us basic data for quarterly sales of show tickets.

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

Single Dimension AnalysisThis 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

Two Dimensional AnalysisWhen 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

Multidimensional Analysis

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.

We Won!…I Think – How to Define Success Criteria for your BI Implementation

VictoryGuiding 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.