The Mother of All Data Warehouses
You can put your data-warehouse project in jeopardy if you decide that right away you need to build a complete enterprise warehouse that addresses all your organization's data needs. Creating an enterprise data warehouse presents problems of scope and time. For example, one company that I worked with attempted to build an enterprise data warehouse from scratch because the people involved believed that was the right way to do it. Initially, the company's budget of $2 million seemed sufficient, but 18 months later, it had detailed documentation, no warehouse, and no money left.
Focusing your efforts will help ensure your success. Most companies start by building individual data marts. The processes involved in building a data mart are the same as those for building a data warehouse, but marts are smaller and typically focus on one department. By using the data-mart approach, you can pick a specific problem and solve it quickly. Solving one major problem in 3 months is usually far more successful than trying to solve all your organization's problems and delivering the benefit at the end of 2 years.
Data marts aren't without problems, of course. A major challenge with data marts is making sure you can later combine two or more marts into a warehouse. You have to design the data marts so that similar dimensions in different marts are identical in structure. When you use the same dimension structure across marts, the dimensions are called conformed dimensions. Creating conformed dimensions requires that you create one version of the truth for the organization. In other words, you need to clean up your data so that all departments are using one customer list, one product list, and so forth. As I explain in a moment, cleaning up data can become a significant project on its own. But spending extra time cleaning up data for your first data mart will help ensure that you produce conformed dimensions, will make the building of future marts easier, and will make the eventual rollup into a warehouse easier. Because the first few marts don't have to solve all your organization's problems—just the problems you want those data marts to solve—you typically don't have to define all the dimensions before you build the first mart. For example, your company might want to eventually build a warehouse that includes sales, marketing, manufacturing, human resources, and finance operations. But if the first data mart focuses on manufacturing, you don't need a dimension for sales promotions.
The first data mart you develop should be a quick-hit success: Plan to deliver the completed project in no more than 3 months. If that sounds like heresy, consider that several third-party vendors have built their businesses around delivering rapid data-warehousing solutions. These vendors start with nothing and build a data mart to solve one business problem. The jobs typically last 3 to 6 weeks, and although the vendors put limitations on such jobs, they're successful because they deliver a quick solution. If you want to implement a warehouse and your first deliverable is 12 to 24 months out, you should seriously reconsider your approach.
Oversized Cubes
A cube is the basic building block of a data mart or data warehouse. Because data warehousing is "cool" and most companies have little experience building warehouses, I often see IT departments trying to build cubes that contain every scrap of their organization's data. This is a real problem from an end user's perspective; such cubes are notoriously difficult to navigate.
Cubes have two uses: reporting and analysis. Reporting cubes are often necessarily more complex than analysis cubes, but typically, only IT report builders and analysts use reporting cubes. Analysis cubes are usually far less complex. For example, one of the world's largest vendors of Consumer Packaged Goods (CPG) data resells its data in cubes containing just four dimensions. These cubes are simple for users to navigate yet provide valuable data for the companies that buy them.
There are no set rules for when an analysis cube is too complex, but you can follow some guidelines for designing cubes. First, limit the number of dimensions in an analytical cube to eight. Most nontechnical end users find cubes difficult to navigate when the cubes contain more than eight dimensions. That's not to say a cube with 10 dimensions is automatically bad, but including too many dimensions can greatly reduce usability and user acceptance. I've worked with several companies that complained that users weren't working with the company's data warehouse. When I examined their cubes, I discovered that some had more than 25 dimensions. The users simply found such large cubes too difficult and confusing to work with, and the warehouses sat unused.
One way to create easily navigable cubes is to build simple cubes and link them to make larger cubes. Analysis Services lets you create virtual cubes, which are similar to views in SQL Server. You can use these virtual cubes to link two or more real cubes, or you can create virtual cubes that present simplified views of a more complex cube. For example, you might create a physical cube with 15 dimensions to use for reporting, then create two virtual cubes that contain just a few of the physical cube's dimensions. You can let users access only the virtual cubes, which helps enforce security and ensures that users deal only with the simpler cubes for analysis. Users should tell you how they want to see the data structured and what values they want to calculate.
Incorrect or Inadequate KPIs
Even when their cubes aren't too complex for general use, some warehouses fail because they don't provide the proper key performance indicators (KPIs) as the cube measures. KPIs vary from company to company, but they often include such metrics as sales, cost of goods sold, gross profit, profit margin, inventory turns, and customer retention. Defining and using the correct KPIs for your warehouse might seem like an obvious requirement, but without appropriate end-user input and executive sponsorship, the cubes that IT departments build typically include whatever data is available, not necessarily the data users need. You can't measure the success of a warehouse implementation if you don't understand the business requirements; you need to be able to compare these requirements to the results your warehouse delivers. Knowing your organization's KPIs helps you create an effective, usable warehouse.
Prev. page
1
[2]
3
next page 
