Front Room Operations

When a new DW/BI system is deployed, business users focus on the front room: the interface they see and use every day. In fact, user communities sometimes refer to the data warehouse by the BI tool vendor's name or the “reporting portal.” Little do they realize that building and deploying reports is only a fraction, typically less than 10 percent, of the effort required to create a DW/BI system. But people focus on what they can see. If the reporting portal is ugly, uninformative, or slow, the entire warehouse/BI system looks bad.

When launching the BI environment, you'll publish an initial set of reports, charts, and analyses that meet the requirements identified through business user interviews at the beginning of your project. Let's assume these deliverables are meaningful, attractive, parameterized as appropriate, and allow drill-down where useful. You must train the business users on the system you've built: not just where to click, but also how the analyses should be used and why. Here are some typical user questions you must be prepared to answer:

• How do I access the BI system?
• How do I request broader system access?
• How do I find the report I need?
• When was the data in the system last refreshed?
• Where can I get help?
• How do I customize a report?
• How do I build a completely new report?
• How do I add my report to the system so others in the organization can use it?
• How do I customize my portal view?
• How do I build a more complex analysis?

You might be able to build such an intuitive reporting environment that the answers to some of these questions are obvious, but you're fooling yourself if you think you can avoid documenting the system and providing rollout training.

You must establish (and maintain) a web site with fresh information about how to use the system, data currency, and where to get help. If your reporting tool has a web-based launch page, think about customizing that page to add this important information. Otherwise, train users to go to your page first and link from there to the reports and/or tool page.

You must also develop plans for training after the system is in production. New hires will need training and a surprisingly large percentage of the initial users will benefit from more instruction. Perhaps you'll need to offer new types of training, for superpower users or very occasional users. Online instruction materials are useful, but there are significant benefits to getting business users in a room where you can talk to them, and they can talk to each other, face to face.

Your front room rollout and operations plan should include a plan for the help desk. If your organization has a centralized IT help desk, use the same infrastructure for first-tier support. Even though connectivity is a lot simpler than it was a decade ago, the majority of user issues are still about how to connect. A centralized help desk can help on this front, but plan for a large percentage of questions to get escalated to specialists, probably someone on the data warehouse team.

Many query and reporting tools let business users customize their system views, often by creating “My Reports” folders on the portal. This functionality is great, but you should develop policies and procedures for personal reports warranting higher visibility. It often makes sense for central BI team members to put reports through quality assurance and tweak them before publishing to a broad audience. It's not only a matter of ensuring that new or revised reports are accurate; you must also ensure that they perform well and that modifications won't adversely affect existing users.

Back Room Operations

The second set of considerations focuses on technical systems management. Long before you go into production, you must think about a host of operational concerns because your decisions will affect the system configuration and design. If you don't create a sound operations plan before you deploy, the data warehouse team will end up patching things together in crisis mode, which inevitably leads to crises of confidence within the user community.

The back room operations plan should address at least the following questions:

• How will you monitor resource usage?
• How will you report on usage?
• How will you automate your operations, especially the extract, transformation, and load (ETL) processing?
• How will you know when your ETL processing has encountered a problem?
• How will you notify users of data problems?
• How will you monitor system performance?
• How will you kill relational database and OLAP system queries?
• How will you identify and solve performance bottlenecks?
• How can you tune the system to prevent bottlenecks?
• How will you ensure your system never runs out of disk space?
• Do you need to modify your ongoing ETL process to accommodate data partitioning?

Most important, you need to plan, implement, and test your backup and recovery strategy. A backup and recovery plan that's not tested regularly isn't a plan. It's a wish, and it's unrealistic of you to expect that wish to come true.

There are no easy answers to these questions. The best solutions are intertwined with your ETL system design. You can prevent a lot of problems by designing your ETL system to proactively check for conditions, such as the successful completion of an operational process, before starting work. As described in article 11.2, The 34 Subsystems of ETL, a robust ETL system checks for exceptions and whether the data volume and contents are reasonable. Keep track of how much disk space a daily load requires and verify that plenty of disk space is available and allocated.

Monitor Operations

Plan to monitor the operations of all the software components in your DW/BI environment: operating systems, relational databases, OLAP and data mining systems, query and reporting software, and any web portals. Developing a robust monitoring system usually requires combining features of your operating systems with features of your database engines and front end tools. Even if you use a single vendor for your BI technology, you should expect to combine several kinds of monitoring. Plan to create a relational database to store the monitoring performance data. You may populate the results of monitoring traces directly into the relational database, or it may make more sense to trace into files and then load those files periodically.

Monitor the systems at all times. Set up the “always on” monitoring to include important events such as user logons and ETL process completions, in addition to all error events. This “always on” monitoring should periodically record statuses such as memory usage, perhaps at 15-minute intervals. Because this basic monitoring is continuous, you should justify the inclusion of every element you're tracing.

Periodically, perhaps once a month, ratchet up the monitoring to capture more events and statuses. This monthly detailed baseline can be invaluable in diagnosing performance problems, and you can often catch these problems before they're perceptible to business users. You may trace statuses at five- or 15-second intervals and keep detailed track of the processes occurring. Detailed monitoring usually affects system performance, so avoid doing it during system usage peak times. On the other hand, if you monitor the reporting system in the middle of the night, you probably won't learn anything.

These operational issues aren't sexy. You're not going to fire up the imagination of business users by talking about them. But if you don't think about these problems early on and build an operations plan into your procedures, your DW/BI system will fail. Business users won't know how to use the system and they won't know where to go to get information and assistance. Their confidence in the system, the efforts will be shaken. All they'll see is the bad, and the 98 percent of the system that's good will go to waste.

Product

As far as the business community is concerned, the DW/BI products are the information needed for decision making and the BI applications and portal through which the information is delivered. Our products must excel in the following five areas:

• Value. Meet the business needs identified in the requirements process.
• Functionality. Product must work well.
• Quality. Data and calculations have to be right.
• Interface. Be as easy as possible to use and look good.
• Performance. Deliver results in a reasonable time frame as defined by the users.

Price

Most users don't pay for the DW/BI system directly. The price they pay is the effort it takes to get the information from the DW/BI system compared to other alternatives. There is an upfront cost of learning how to use the BI tool or applications, and an ongoing cost of finding the right report or building the right query for a particular information need. You must lower the price as much as possible by first creating excellent products that are as easy to use as possible. Then offer a full set of training, support, and documentation, including directly accessible business metadata, on an ongoing basis.

Placement

In consumer goods, placement is obvious: The product has to be on the store shelf or the customer can't buy it. For the DW/BI system, placement means our customers are able to find the information they need when they need it. In other words, you must build a navigation structure for the BI applications that makes sense to the business folks. Additionally, tools like search, report metadata descriptions and categories, and personalization capabilities can be extremely helpful. For additional information, take a look at article 13.11, The BI Portal.

Promotion

Every customer contact you have is a marketing opportunity. TV ads are not an option, not counting YouTube, but you do have several promotion channels for the DW/BI system:

• BI applications. These are what people use the most. Names are important; having a good acronym for the DW/BI system can leave a good impression. Every report and application should have a footer indicating that it came from the DW/BI system and a logo in one of the upper corners. Ultimately, if you create a good product, the name and logo of your DW/BI brand will become marks of quality.
• BI portal. This is the main entry point for the DW/BI system. It has to meet the same requirements as the BI applications.
• Regular communications. Know who your stakeholders are and which communications vehicle works best for each. Your ongoing communications plan will include status reports, executive briefings, and user newsletters. Consider webcasts on specific topics if that's an option in your organization.
• Meetings, events, and training. Any public meeting where you can get a few minutes on the agenda is a good thing. Briefly mention a recent successful business use, remind people of the nature and purpose of the DW/BI system, and tell them about any upcoming plans or events. Host your own events, like user forum meetings, every six to nine months or so.

Ongoing marketing is a key element of every successful DW/BI system. The more you keep people informed about the value you provide them, they more they will support your efforts.

Recognize the Target

Let's start at the end state goal. Your target enterprise information architecture consists of multiple interconnecting business process dimensional models sharing conformed dimensions as advocated by the enterprise data warehouse bus architecture approach. Using shared conformed dimensions has several important implications. Durable keys in the dimensions must be identical. How else can you join across subject areas? Additionally, you must have corporate agreement about which attributes are tracked through history and which are updated in place. How else can dimension keys be identical?

Case Study Scenario

Let's imagine you launched your enterprise information infrastructure with a successful retail sales dimensional model. The retail sales information is used daily by store management, as well as by many corporate users in marketing and finance. It captures data at the correct transactional grain, its daily update process is smooth, and system downtime is well within the service level agreement (SLA). The retail sales schema is a thing of grace and beauty.

The success of the retail sales project has emboldened management to extend the enterprise data warehouse to support additional CRM requirements, namely customers' calls into the call center. In theory, this decision is no problem: You'll reuse the date and customer dimensions from the first project, add some new dimensions and new facts, and you're done, right? In practice, there will be a few wrinkles.

Not So Fast

Obviously, you'll need to figure out how to modify the ETL process to also populate the new tables in the call center schema. But there may be some more complex implications.

Let's think a bit more about the customer dimension in the original retail sales schema. First, the definition of a sales customer is actually a subset of all customers; the only customers you know anything about are those with loyalty cards and corresponding account numbers. When you add the call center information, you can expect to get information about a whole group of customers you've never seen before. The customer dimension ETL process is going to have to change to accommodate the new source, as I'll discuss later. Adding new customers to the customer dimension shouldn't affect the existing dimensional model. The new customer rows won't join to any existing retail sales fact rows because there's no way to link a “nonloyalty” caller to a sales transaction. The existing sales schema should behave the same, or almost the same, as it always has.

What if the business users want to track more information for call center customers than was necessary or available for retail sales? For example, what if the caller's phone number is important? You need to add that new attribute to the customer dimension, again modifying the customer dimension ETL process. As was the case with the new customers, adding the phone number shouldn't disrupt the original dimensional model; existing reports and analyses will continue to work as before.

Finally, let's tackle a harder problem. What if the business users of the call center data have a compelling need to track history for the customer's address? In other words, they want to be able to know that when a customer called last year she lived in Montana, but now lives in Hawaii. Dimensional modeling can handle this requirement with the type 2 slowly changing dimension (SCD) technique. The issue is that you're changing a fundamental characteristic of the customer dimension in a way that could affect the existing retail sales dimensional model. I'll return to this dilemma in a bit.

Plan Before You Build

Not surprisingly, the first thing you need to do to deploy the call center data is develop a plan. I hope you already have policies for managing the development cycle: checking in code to source control; using separate development, test, and production environments; having specifications and test plans; deploying SLAs for system availability, and so on. If not, now is a good opportunity to put that infrastructure in place.

The call center schema design specifies the new tables and modifications to the existing tables, such as customer. The designers should also provide a reasonable specification for the ETL process. You need to drill down on those specifications to a finer detail, discussing what the designs mean within the context of modifications to a system in production. In other words, you need to think through, at a high level, how you're going to modify the existing ETL system to incorporate the new workload. You also need to consider whether you're going to alter the existing customer table or rebuild it. In either case, you must think about any implications on the SLA.

One of the most important issues to resolve during the planning phase is how to handle the modification of the customer dimension to track history. By far the easiest approach is to start tracking history now. All existing historical data gets associated with the customer's attributes as they are today. New facts get correctly associated with future changes in customer attributes. Often it's the only feasible approach because the history of customer attributes, address in our example, is simply unavailable. Note that even with this simple approach, you might need to modify the ETL process for retail sales so that you're propagating the correct customer key into the fact table. In practice, the retail sales ETL process likely picks up the most current customer key, but you'd be foolish to exclude verification of this point from your test plan.

A more challenging approach is to re-create history for a customer. You may be able to build a version of the customer dimension that includes the history of customer address changes. This is seldom easy, but may be possible. If it's possible, it's the job of the data warehouse team leader to present management with a cost benefit analysis of the effort. If you decide to take on this challenge, you have a second decision: whether or not to re-create history for the retail sales schema. The process of re-creating history in the fact table is conceptually simple because you know the sales transaction dates and the dates for which a customer's address is valid. However, performing this simple action requires updates on a significant number of fact table rows, which is an expensive operation. A second cost benefit analysis is required. And don't forget to develop a test plan to ensure you didn't mess things up!

Finally, your plan should discuss how to handle the proliferation of these changes to aggregate tables and OLAP cubes. If these structures are affected at all, they probably need to be rebuilt from scratch. Adjust your schedule accordingly, and don't forget your test plan.

Develop, Test, Deploy

The development of the new database elements and ETL processes is typically done against a small subset of data. This approach is only sensible. But developers need to remain constantly aware that they're adding new elements to an existing production system with potentially significant data volumes. A technique such as dropping and re-creating a table might work just fine in the development environment, but could be a complete disaster in the real world.

The test environment is the place to verify that the database can be upgraded, new data sources added, and new transformations processed without adversely affecting the existing operations. The first thing you need to do in the test environment is back up the entire system: data structures, data, and ETL processes. You need to test these main things: the new database structures, the ETL processes, and the process of migrating to the new structures and processes. Each test iteration should start from a clean system that hasn't had any of the migration changes applied to it (in other words, a system just restored from backup). Although early testing can be performed against a subset of data, your final set of tests should operate against a comprehensive environment, ideally an exact copy of the production system that you'll be modifying.

It's difficult to determine how to test the migration process. People often use this time as an opportunity to migrate the data warehouse to new hardware, which they've been testing as a shadow system. If you're running your data warehouse on commodity hardware, it's not terribly difficult to justify this approach. But if you have huge data volumes in production, you probably have no choice but to invest some serious planning and testing time to “doing it right.”

Some Final Thoughts

Much of the pain discussed in this article can be avoided if the initial design for retail sales thinks a few steps ahead to anticipate the need for address change tracking. One of the elements of art in data warehouse design is to think just broadly enough without being sucked into getting all requirements from all users. Realistically, it's a characteristic of a successful data warehouse to change over time, and the data warehouse team should be prepared to accommodate those changes.

In general, my recommendations are: Plan, plan, plan. Communicate. Develop. Test, test, test.

Business Sponsor Disorder

One of the most common, yet potentially fatal disorders involves the sponsorship of the DW/BI environment. A business sponsor disorder is often the key contributor to data warehouse stagnation.

Data Disorder

Accessing good data is one of the two pillars of the data warehouse. (The other is addressing the right business problems.) The most serious and common data disorders are poor quality data, incomplete data, and late data.

Business Acceptance Disorder

Here's another critical disorder affecting data warehouse mortality rates. If the business community doesn't accept the DW/BI environment to support decision making processes, then you've failed. Sorry to be so blunt, but it's a harsh reality. The business must be engaged if you're to stand any chance of DW/BI acceptance. Unfortunately, business engagement is often outside our comfort zones; we may be unsure about techniques for ensuring their engagement, plus there are typically no incentives in place for mastering this domain.

Infrastructure Disorder

Contrary to popular opinion, infrastructure disorders are seldom fatal. There's often room for improvement, but it's usually an elective procedure. Despite our personal interests in this disorder, it usually doesn't warrant the attention due the others.

Cultural/Political Disorder

Unfortunately, DW/BI environments aren't immune to cultural or political disorders, and there's no vaccine in development on the research horizon.

Early Detection

With many maladies, early detection is essential to mounting a strong defense. Similarly, proactively monitoring your data warehouse and business intelligence environment is the best method of ensuring its long term health. It's tough to prescribe a remedy if you don't know what you're suffering from. As a student commented recently, thinking about common disorders and alternative treatment plans is a “shot in the arm” for anyone who's trying to rescue a failing data warehousing project. The metaphor lives on.

Finally, remember there's nothing wrong with having a checkup and learning that you're in perfect health. In fact, that's the optimal outcome!

DW/BI Business Realignment

Viewing the DW/BI project through the business users' eyes is an effective method to realign with the user community. In cooperation with the business sponsor, DW/BI team representatives should talk with the business community about the ability of the environment to effectively support their needs. Results of this process are then analyzed and presented back to the business with appropriate recommendations.

The most important aspect of the realignment process is meeting with the business community to solicit their feedback. We talk to them about what they do, why they do it, how they make decisions, and how they hope to make decisions in the future. Along the way, we also need to understand how the current DW/BI efforts support this process as well as any issues and concerns regarding the DW/BI environment. Like organizational therapy, we're trying to detect the issues and opportunities.

Choose the Forum

Before meeting with the business community, determine the most appropriate forum for a productive session. There are two primary techniques for gathering feedback: interviews and facilitated sessions. For a realignment project, interviews are preferable to facilitated sessions. Because lack of business acceptance is suspected, it's reasonable to expect some negative reactions to the existing environment. You should avoid facilitated group sessions, which may disintegrate into finger pointing, blame casting complaint sessions counterproductive to your mission of reinvigorating business acceptance. Besides, interviews encourage a lot of individual participation and are easier to schedule.

Surveys aren't a reasonable tool for gathering realignment feedback. Business users are unlikely to feel their issues have really been heard through a survey. Most won't bother to respond. Surveys are flat and two dimensional; those who do respond will only answer the questions you've asked in advance. There's no option to probe more deeply like you can when you're face to face. A key outcome of the realignment process is to create a bond between users and the DW/BI initiative. This outcome just doesn't happen with surveys.

Identify and Prepare the Interview Team

It's important to identify and prepare the involved project team members, especially as some of the interviews could become contentious. The lead interviewer must ask great open-ended questions, but also needs to be calm and mature, capable of receiving negative feedback without taking it personally and becoming defensive or combative. The interview scribe needs to take copious notes, pages of them from each session. A tape recorder isn't appropriate for the realignment effort because it may cause interviewees to hold back on key organizational issues. Although we often suggest inviting one or two additional project members as observers in an initial requirements gathering effort, this practice is less desirable in a realignment project because you'll want the interviewees to be as open and honest as possible.

Before sitting down with the participants, make sure you're approaching the sessions with the right mindset. Don't presume that you already know it all; if done correctly, you'll definitely learn during these interviews. Prepare yourself to listen effectively without becoming defensive. These sessions are the business community's opportunity to share their perspective of the DW/BI project. They aren't intended for you to try to explain how or why the situation developed.

Select, Schedule, and Prepare Business Representatives

The businesspeople involved should represent the horizontal breadth of the organization. Obviously, you'll schedule individuals from groups currently served (or meant to be served) by the DW/BI environment. In addition, you should include groups that are potential DW/BI candidates. You want to uncover any issues that may be constraining the DW/BI project's ability to serve these needs.

You also want to be sure to cover the organization vertically. Project teams naturally gravitate toward the superpower business analysts who are the most frequent and capable DW/BI users. While their insight is obviously valuable and important, don't ignore senior executives and middle management. Otherwise, you're vulnerable to being overly focused on the tactical here and now but losing sight of the real reasons for flagging business acceptance.

Scheduling the business representatives can be an onerous task. Be especially nice to any assistants as calendars are juggled. We prefer to meet with executives on their own, whereas we can meet with a homogeneous group of two to three people for those lower on the organization chart. We allow one hour for individual meetings and one and a half hours for the small groups. The scheduler needs to allow a half hour between meetings for debriefing and other necessities. Interviewing is extremely taxing because you must be completely focused for the session's duration. Consequently, we only schedule three to four sessions in a day because our brains turn mushy after that.

When it comes to preparing the interviewees, the optimal approach is to conduct a launch meeting with the participants. Business sponsors play critical roles, stressing their commitment and the importance of everyone's participation. The launch meeting disseminates a consistent message about the realignment effort. It also generates a sense of the business's project ownership. Alternately, if the launch meeting is a logistical nightmare, sponsors should communicate the same messages via launch memos.

Conduct the Interviews

It's time to sit down face to face to gather feedback. Responsibility for introducing the interview should be established prior to gathering in a conference room. The designated kickoff person should script the primary points to be conveyed in the first couple of minutes when the meeting tone is established. This introduction should convey a crisp, business-centric message focused on the realignment project and interview objectives. Don't ramble on about the hardware, software, and other technical jargon.

The goal of the interview is to get business users to talk about what they do and why they do it. Although in the long run you want to understand how well the existing DW/BI environment aligns with decision making processes, you don't want to be singularly focused on the DW/BI project's current state too early in the interview. You're most interested in how the organization uses information to make decisions so that you can align your efforts.

A simple, nonthreatening place to begin is to ask about job responsibilities and organizational fit. This is a lob ball that interviewees respond to easily. From there, we typically ask about their key performance metrics and how they use information in support of decision making. Ultimately, we ask about their experience with the DW/BI project and its ability to support their requirements. If the interviewee is more analytic and hands on, we ask about the types of analyses currently performed, how easily they're developed, and how well they deliver. When meeting with business executives, ask them about their vision for better leveraging information in the organization. You're seeking opportunities to align future data warehouse deliverables with business demand.

As the interview comes to a conclusion, we ask interviewees about their success criteria for the DW/BI environment. Of course, each criterion should be measurable. Easy to use and fast mean something different to everyone, so you need to get the interviewees to articulate specifics. At this point in the interview, we make a broad disclaimer. The interviewees must understand that just because an opportunity was discussed doesn't guarantee that it will be resolved immediately. You need to take advantage of this opportunity to manage expectations. Finally, thank interviewees for their insights and let them know what's happening next.

Document, Prioritize, and Reach Consensus

Now it's time to write down what you heard. While documentation is everyone's least favorite activity, it's critical for both user validation and project team reference materials.

Two levels of documentation typically result from the interview process. The first is to write up each individual interview. This activity can be quite time consuming because the write-up shouldn't be merely a stream of consciousness transcript, but should make sense to someone who wasn't in the session. The second level is a consolidated findings document. We typically begin with an executive summary, followed by a project overview that discusses the process used and participants involved. The bulk of the report centers on the findings, including specific opportunities for improving, enhancing, and expanding the existing DW/BI environment to better address business needs and expectations.

The realignment findings document serves as the basis for presentations back to senior management and other business representatives who participated. Inevitably, you've uncovered more opportunities than can be tackled immediately, so you need to develop consensus regarding priorities. Prioritization of efforts is an important step in leveraging and fostering an improved partnership to ensure the DW/BI effort aligns with the business. A highly effective tool for reaching agreement on a DW/BI roadmap and action plan is the prioritization quadrant, discussed in article 4.10, The Bottom-Up Misnomer.

At this point, the DW/BI team has a solid understanding of what needs to be accomplished to better support the business community's requirements and expectations. Delivering on the opportunities identified during the realignment process will establish an improved DW/BI environment that's embraced and accepted across the organization.

Gathering Evidence

While usage statistics are interesting, they show only activity, not business value. Simple query counts tell you nothing about the content or business impact of those queries. Unfortunately, there's no automated way of capturing the value of each analysis from the DW/BI system. You still have to get this information the old fashioned way, by talking to people. Someone on the DW/BI system team has to go out into the user community on a regular basis and ask people to describe what they are doing, assess the business impact it has had, and document it.

Most of the time, the impact of any given analysis isn't all that stunning. People do useful things that make a big difference in their work, but it's not a multi-million dollar hit. Every so often, you will find an analysis or operational BI application that has had a significant impact. For example, the analyst may have identified a pattern of calls in the customer care data that led to a simple change in the documentation and reduced the call volume by 13 percent (at $6 per call, that's over $140,000 per year for a company that takes 500 calls per day). Or they may have analyzed the donor database in a small nonprofit organization and identified donors who had dropped out. This led to a special program to reconnect with these people that yielded a 22 percent response rate and close to $200,000. Or the operational BI application may offer ring tone recommendations on a web site based on customer purchase history. Each ring tone may fetch only $1, but a 30 percent increase in ring tone downloads could add up to real money. You get the idea.

Educating the Business: The User Forum

Finding high impact examples requires a bit of work. One effective technique the Kimball Group has used to identify and leverage qualitative examples of value is called a user forum. The user forum is a DW/BI event designed for the business community. Your main business sponsor should kick off this 90 minute meeting with a short speech about how important the DW/BI system is to the organization's success. The first agenda item is a brief presentation from the team about the recent accomplishments, current state, and short term plans of the DW/BI system. The bulk of the meeting is dedicated to two presentations from business analysts who used the BI system to generate significant value for the organization. They talk about what they did, how they did it, and what kind of impact it had.

Senior managers like these events because they see the impact. Often the head of one department will see what another department has done and realize his group is missing an opportunity. Middle management and analysts like the presentations because they include enough detail so people can see exactly how the analysis was accomplished. They learn new techniques and approaches to the analytic process. The three examples of business value described in the preceding section would be great feature presentations at a user forum.

A good meeting doesn't happen by accident. Find good presentation candidates with high business value by canvassing users on a regular basis. Once you find a good example, work with the user to create a clear, compelling presentation with lots of screen captures and a summary page that shows the dollar impact of the analysis. Rehearse the presentation with them, especially if they are not experienced presenters. This helps you, and them, get the timing down so your audience doesn't miss the punch line because the meeting went too long. Email a reminder a day or two ahead of time, and call everyone you'd like to be there to make sure they are going to make it to the meeting. If key folks, like the CEO or VP of Marketing, can't make it, consider rescheduling rather than have them miss out. If they are already on your side, it's good to have the show of support; if they are not converts yet, they could learn something by being at the meeting.

Schedule user forum meetings on a regular basis, about every six months or so. Don't be too proud to employ blatant marketing techniques to promote the meeting. The basics almost go without saying: food and drink are a must. Consider offering marketing swag as prizes. Since most BI teams are friendly with the marketing group, see if they'll let you raid their goodies closet.

It's a great idea to keep the presentations on file. After a year or two, you will have a library of powerful business value examples. Put a link to them on your BI portal. Print them out and make a welcome packet you can present to every new executive.

Educating Senior Staff

Your top educational priority in the long term should be to continuously and consistently inform senior management about what the DW/BI system is, why it's important, how it should be used, and what it takes to make it happen. The user forum helps achieve this objective, but the greater your access to senior management, the easier this education process will be.

Ideally, the head of the DW/BI system is part of senior staff and participates in their planning meetings. If not, try to get a regular slot on their meeting schedule to present success stories and plans and to hear about potential changes in business priorities.

Often, senior management will want to explore an idea to see if it's viable before launching any major new initiatives. Having a direct line to the DW/BI team can help senior management quickly triage ideas that should be abandoned and those that should be developed further. Once an idea begins to gain traction, the DW/BI team should make sure its development is accompanied by appropriate measurement and analytical systems. All too often, we've seen new initiatives taken on by senior management with no means to measure impact or value. If the data is not collected, you can't analyze it.

Bottom line: However you make it happen, you need to make sure someone on the DW/BI team is involved with senior management and understands where the business is headed so you can be prepared to support it.

Working with Steering Committees

If it's not politically possible for the DW/BI team leader to be part of senior staff, another way to get the information you need is to establish an ongoing steering committee for the DW/BI system made up of senior-level business representatives. If you don't have a steering committee, try to recruit people who you know will be able to work together, give you the information you need, and wield some influence in the organization. You might call this group the Business Intelligence Directorate (BID), or some other important sounding name with a nice acronym. It may seem trivial, but naming is a big part of the marketing process.

You may also have a different kind of business user steering committee made up of analysts and power users who help prioritize lower level tasks and identify technical opportunities for the BI system. You might call this the BI Technical Experts (BITE) group.

Conclusion

You may feel that because you've done a good job, you shouldn't have to continually market the DW/BI system, or educate the business community. Unfortunately, that's not the case. You need to continually gather concrete evidence of success and use that to educate senior management. You also need to be informed of and have some influence over the decision making process at the senior staff level, either through direct participation or via a steering committee. This may sound like a burden, but one positive result is that as senior management understands the business value of the DW/BI system, they no longer question your budget.

Conform the Nonconformed Dimensions

Master conformed dimensions contain the descriptive attributes and corresponding names, meanings, and values that have been agreed to across the enterprise. Using conformed dimensions ensures that the data warehouse is delivering consistently defined attributes for labeling, grouping, filtering, and integrating data from multiple business processes.

Unfortunately, many data warehouses and marts were developed without regard for this critical master data. Standalone data stores with independently defined dimensions are often constructed because it's the path of least resistance when deadlines loom. Rather than attempt to reach consensus on common reference data, isolated teams believe it's quicker and easier to just build autonomous dimensions. This approach may let these teams declare victory, but it doesn't support the business' desire for integration and consistency.

Some organizations wind up with independent data stores because developers purposely focused on delivering a departmental solution, likely due to the funding available. Without the vision and acknowledged need for an enterprise perspective, teams are often chartered to build with blinders on to meet a limited set of goals.

So what do you do if you're confronted with an environment that's been built without a foundation of common conformed dimensions? Can these stovepipes be rescued? In spite of the vendor hype, there's no magic elixir that miraculously delivers master dimensions. Technology can facilitate and enable data integration, but there's no silver bullet. The first step toward integration nirvana is to assess the state of the data, as well as requirements, expectations, and buy-in from the business. You can self-diagnose the issues related to nonconforming dimensions, but keep in mind that you're likely to face a long, uphill internal struggle and resistance to change if the business community doesn't perceive the need or incremental value in the project.

As we described in article 5.14, Data Stewardship 101: The First Step to Quality and Consistency, one of the most crucial steps in conforming nonconformed dimensions is to organize the appropriate resources to tackle this vexing problem. Data stewards must be identified and assigned responsibility and authority to determine common dimension attributes, define domain values and transformation business rules, and establish ongoing processes to ensure data quality. Obviously, that's no small feat, so it's critical to identify the right leader. Ideally, you want someone from the business community who is respected by senior management and who has knowledge and skills to achieve organizational consensus. Navigating the unavoidable cross-functional challenges requires experience, widespread respect, political acumen, and strong communication skills.

Not everyone is cut out to be a data steward. It's feasible for folks on the data warehouse team to serve as stewards, but they need to demonstrate all the traits and characteristics just described. Most important, they need the support of business management and the authority to push cross-enterprise agreement and adoption, even when unpopular compromise is required. Without this power, stewards face the prospect of endless tire spinning as they try to rationalize diverse perspectives.

Once the data stewards produce the specifications for a conformed master dimension, the skilled ETL staff builds the master dimension. Depending on the existence and/or quality of reference data in the operational source systems, this may require intricate record matching and deduplication. When merging multiple source systems, clearly defined rules of survivorship are needed to identify which data source takes precedence for each attribute.

With master dimensions built, it's then time to retrofit the existing data warehouse subject areas with standardized data. New surrogate key mapping tables for each dimension are used to recast existing fact table rows. In addition, aggregate summary tables and cubes will likely need to be reconstructed. While the implications for the ETL system are inevitably significant, hopefully the impact on the business intelligence layer of these underlying physical table changes can be minimized with an abstraction layer using views, synonyms, or your BI tool's metadata, depending on your platforms.

Create Surrogate Keys

Another data warehousing best practice is to create surrogate keys, typically a meaningless simple integer, for the primary key in each dimension table. The associated fact table rows use this same surrogate as a foreign key reference to the dimension tables.

Establishing, managing, and using surrogate keys may initially feel like an unnecessary burden on the ETL system, so many data warehouses have been constructed based on the operational natural keys, sometimes referred to as smart keys because of their embedded meaning, rather than using surrogates. At first blush, these natural keys may not present any obvious problems. But as the environment matures, teams often wish things had been done differently when the following situations arise:

• The natural keys in the operational system get recycled after a product has been discontinued or an account closed for more than a specified length of time. Two years of dormancy may seem like a lifetime to an operational system, but relying on reassigned natural keys can wreak havoc in the data warehouse where data is retained for extended periods.
• The business decides it's important to track dimension attribute changes after all. While this need may not have been envisioned up front, as the business develops expertise with the data, team members often want to see the impact when descriptive dimension attributes change. Naturally gravitating to the least onerous route, they may have developed the initial data warehouse with the premise that dimension tables would reflect the most current attribute values, overwriting any previous descriptors when they change using the SCD type 1 technique. But the rules may now be maturing, requiring the insertion of new rows into the dimension table to capture new profiles via SCD type 2 methods. Relying on the natural key as the primary key of the dimension table obviously doesn't allow for multiple profile versions, while using a concatenated key based on the natural key and effective dates has a negative impact on both query performance and usability.
• Performance has been negatively impacted by an inefficient natural key. In contrast to a tight integer, natural keys are often bulky alphanumeric fields that result in suboptimal dimension/fact table join performance and unnecessarily large indexes.
• You must create conformed dimensions to integrate reference data from multiple sources, each with its own unique natural key. You may need a default dimension primary key to represent the condition when a dimension value is unknown or not applicable to a given measurement event.

Each of these situations can be addressed by using surrogate keys as the primary keys for the dimension tables. So how do you implement surrogates when you're already working with a system that was built without them? In the simplest scenario, you would add the sequential surrogate key to the dimension table, leaving the existing natural key intact as an attribute, and recast the fact rows to reference the new surrogate key values. Depending on your BI tool, you may need to update the tool's metadata to reflect the change in join fields. If you need to support type 2 attribute change tracking, additional work would be required to source the historical attribute profiles and then repopulate the fact table with the surrogate key values that were valid and in effect when the fact row occurred.

Deliver the Details

Some people believe dimensional models are only appropriate for summarized information. They maintain that dimensional structures are intended for managerial, strategic analysis and therefore should be populated with summarized data, not operational detail. The Kimball Group vehemently disagrees; in our view, dimensional models should be populated with the most detailed, atomic data captured by the source systems so business users can ask the most detailed, precise questions possible. Even if users don't care about the particulars of a single transaction or subtransaction, their question of the moment may require rolling up or drilling down in these details in unpredictable ways. Of course, database administrators may opt to presummarize information to avoid on the fly summarization in every case, but these aggregate summaries are performance-tuning complements to the atomic level, not replacements.

Restricting your dimensional models to summarized information imposes serious limitations. Summary data naturally presupposes the typical business questions so when business requirements change, as they inevitably will, both the data model and ETL system must change to accommodate new data. Summary data alone also limits query flexibility. Users run into dead ends when the presummarized data can't support an unanticipated inquiry. While you can roll up detailed data in unpredictable ways, the converse is not true; you can't magically explode summary data into its underlying components. Delivering dimensional models populated with detailed data ensures maximum flexibility and extensibility.

So what do you do if you've inherited a data warehouse filled with preaggregated information, but the details are missing in action? The solution is straightforward: You need to source and populate the bedrock atomic detail. Because more detailed data is naturally more dimensional, this will almost inevitably require that new dimension tables be constructed as well.

As you're delivering the details, don't be lulled into thinking that summary data should be dimensionalized but that atomic details are better handled in a normalized schema. Business users need the ability to seamlessly and arbitrarily traverse up, down, and across both the detailed and summary data in a single interface. While normalization may save a few bytes of storage space, the resulting complexity creates navigational challenges and typically slower performance for BI reports and queries.

Reduce Redundancies

Many organizations take a piecemeal approach to their data warehouse design and deployment, so it's common for the same performance metrics to reside in numerous analytic environments, often segregated by business department or function. The multiple, uncoordinated extracts from the same operational source systems required to populate these redundant data stores are inefficient and wasteful. Variations of similar, but different, information result from inconsistent business rules and naming conventions, causing unnecessary confusion and requiring reconciliation throughout the enterprise. Obviously, conflicting databases that perpetuate incompatible views of the organization are distracting resource drains.

Take a more enterprise-centric approach, storing core performance metrics once to support the analytic needs of multiple departments or groups. The enterprise framework is defined and communicated via the data warehouse bus matrix. The matrix rows represent the business events or processes of the organization, while the columns reflect the common, conformed dimensions. The bus matrix provides the macro point of view for architecting the data warehouse environment, regardless of database or technology preferences.

But what do you do if you've discovered that your organization's key performance metrics and indicators are currently available in many different analytic environments? The first step is to assess the damage. You can create a detailed implementation bus matrix, described in article 5.6, The Matrix: Revisited, to gather information and document your current state. With these details in hand, you're ready to help senior executives understand the quagmire resulting from uncontrolled, standalone development. Because they're often the ones demanding more information (and by the way, they want it delivered yesterday), it's important that they comprehend the consequences of piecemeal development, which can significantly hinder the organization's decision making capabilities.

If the business appreciates the inefficiencies and obstacles of the current environment, they are much more likely to support a migration strategy to reduce the unnecessary redundancies. Unfortunately, this rationalization often has serious ramifications on the BI application layer when the underlying data stores are removed or consolidated.

Face the Realities

We've described four of the most common data fixes for more mature warehouses. It's worth noting that regardless of the opportunities for improvement with your existing environment, it's important to evaluate the costs versus benefits of taking any corrective action. It's similar to the decision process when confronted with maintenance of an aging automobile; sometimes it makes sense to spend the money, but other times you may opt to merely live with the dents or dump the heap of junk altogether.

Keep your eyes open for occasions when the impact of the corrective action is either less noticeable or less painful. For example, if your organization is implementing a new operational source system or migrating to a new ETL or BI tool platform, the upheaval with your existing transformation or analytic processes presents an opportunity for other corrections. Using the automobile maintenance analogy, if your car is already in the shop and the mechanic has lifted the hood to change the oil, he might as well check the windshield wiper fluid while he's in there.

Cut the Flab

In tough times, most organizations try to identify opportunities for cost savings. This often means significant reductions in budgets as well as possible staff reductions. We'll assume you are already running very lean; your budgets and staffing have already been evaluated and trimmed. After these obvious cuts, you'll want to look for other avenues to achieve cost savings.

Revisit the original justification for your DW/BI effort; often a number of cost savings goals were identified during the project justification effort. Have these goals been realized? If not, why not? Are the savings still available? If so, look for inexpensive methods that would help you achieve these goals.

Often the most significant cost savings assigned to a DW/BI effort are related to sunsetting older analytic environments, resulting in significant savings on hardware, software licenses, maintenance, and support resources. Try to determine why these savings have yet to be realized. We can often point the finger of blame at office politics. The new DW/BI environment may be well positioned to support the requirements of the existing analytic environment, yet a group of business users lacks the motivation to make the move. A mandate is required to force these stragglers to migrate to the new environment, and the current economic environment may provide just the incentive you need.

It's easy to just blame politics, but you need to talk to the holdout business users to understand their perspectives. The new solution may be almost everything these users need, but some vital component might still be missing. It could be very easy to solve the issue, perhaps with user training, a little applet or macro, modifications to reports, or possibly a minor change in the data or model.

Even in the best of times we want to sunset old applications, not only because of the operating costs but also because we want a single version of the truth. In these worst of times, we should be able to entice or force the stragglers to migrate away from the legacy environment to the new DW/BI solution and finally achieve the cost savings initially envisioned.

Monitor and Tune to Defer Spending

Organizations are also looking to defer planned costs. Perhaps in your surviving budget you have financial resources available to invest in additional hardware or software. Often, with a bit of creative thinking and hard work, these costs can be deferred or in some cases avoided altogether.

Inventory the software licenses you already have in place. Are they all being effectively used? Pay special attention to your BI tool licenses and how they are deployed. Many organizations find that the actual number of licenses in use is far below the number that have been deployed.

• First, understand why the deployed licenses aren't being utilized. Perhaps there are low cost training or support investments that will help users become more effective, leverage the existing licenses, and enable your organization to achieve the anticipated benefits.
• Second, if the licenses are not required where they were deployed, they should be shifted to other users. This realignment of licenses may allow you to defer a planned investment.
• Worst case, if there is no current or future need for the licenses the maintenance can be discontinued and some cost savings achieved.

Similarly, cast a critical eye over the overall performance of your DW/BI environment. Often an investment in performance tuning can push a planned hardware upgrade a number of months into the future, ideally out of the current budget cycle. Query performance is highly visible, so start there:

• Develop a log of system performance characteristics, if you're not already doing so. Your database systems and operating systems have features that enable you to capture a history of memory, disk, CPU usage, and other characteristics. You cannot tune performance if you don't understand performance.
• Tune the SQL for all standard reports and other BI applications. This is the lowest hanging fruit because you can usually modify the query syntax, such as adding a hint, without changing the report's fundamental design.
• Analyze the queries run in your environment looking for opportunities to improve your indexing and aggregation strategies. Tuning for ad hoc use continues to be something of an art form; you need to balance the value of an additional index against the cost of maintaining that index.
• Work with your hardware/software vendors to ensure that you are leveraging the capabilities already available to you. Ask your vendors to provide known DW/BI best practices and tips.

After you've done what you can to squeeze every ounce of performance from the existing environment, consider whether moderate changes to your system design might provide substantial performance benefits. Your fact tables and their associated indexes and aggregates consume the most resources in your DW/BI environment. You may be surprised by how much query performance can be improved by reducing fact table and index size. Review your fact table designs and consider the following:

• Replace all natural foreign keys with the smallest integer (surrogate) keys possible.
• Combine correlated dimensions into single dimensions where possible.
• Group tiny, low cardinality dimensions together, even if uncorrelated.
• Take all text fields out of the fact table and make them dimensions, especially comment fields. It seems counterintuitive to make a dimension out of a high cardinality, analytically insignificant field like a comment, but the comment field is omitted from most queries. Removing the large character field from the fact table and replacing it with an integer key can make the fact table perform much more nimbly.
• Replace long integer and floating point facts with scaled integers wherever possible.
• If, contrary to our longstanding advice, your dimensions are normalized into snowflakes, collapse each into a denormalized, single table flat dimension table. You can often implement this as an additional step at the end of the ETL process.

Review your ETL system. Look for performance bottlenecks and identify ways to remove them. ETL teams often overlook indexing approaches in staging tables that might improve overall ETL performance. In addition, revisit the ETL logic from the early days of your DW/BI effort. The ETL team is now more experienced than it was in the beginning. Inevitably your team has identified more efficient and effective techniques than those initially implemented.

Bulk Up the Bottom Line

Most organizations initially justify their DW/BI initiatives citing opportunities to grow the business and improve productivity. The benefits identified are real and tangible, but they're often hard to quantify. It can be challenging to measure enhanced business results, such as increased revenue growth, improved profitability, greater customer acquisition/retention rates, or improved customer satisfaction.

Now's the time to revisit the proposed growth opportunities to evaluate whether the DW/BI environment has actually helped attain the benefits promised. If the benefits are being achieved, be sure to let the world know! There's nothing wrong with taking credit for a job well done. Your recent budget negotiations might have gone more smoothly if you had better records of the business value resulting from the DW/BI system. Start keeping those records now, even if they are just DBMS usage logs.

Most of us will recognize desired benefits that have not yet been fully realized. Investigate why these benefits continue to elude you. This analysis will require the DW/BI team to cast a critical eye over activities and results. Be brutally honest with yourself. Have you done everything required to help the organization achieve these benefits?

DW/BI teams often lose sight of the ultimate goal. They get so focused on getting the data into the data warehouse they forget about making it all the way to the finish line—enabling the business users to easily use the data for decision making. Consider refocusing some of your resources on achieving those benefits that provide significant value to the organization.

Look for low cost, incremental improvements you can make that offer substantive value to the organization:

• Make sure you have documentation and training in place to help business people use the DW/BI system effectively.
• Look for areas where the addition of a few dimension attributes or additional metrics to existing schema will enable valuable new analyses.
• Look for opportunities to expose the existing portfolio of data and BI applications to a wider audience, perhaps tapping available software licenses identified as discussed earlier.
• Work with key, analytic business users to evaluate the effectiveness of BI applications that have already been deployed. Look for opportunities to improve these existing BI applications to provide keener insights into the business. Seek out ways to better understand the analytic processes utilized by the savviest users in order to capture and extend these benefits to additional BI applications.
• Enrich your data warehouse with new fact tables supporting additional business processes and new analytics.

It's unfortunate that the downturn in the economy has put many of us on an unwelcome budgetary diet, but we can also get lean and mean by hitting the gym and working out, figuratively speaking. We can focus carefully on our DW/BI environment's overall health and fitness and become far stronger and healthier for the long run.