Business Intelligence Best Practices - BI-BestPractices.com

Collaboration. Communication. Community.

 
 
 Printer-friendly
 E-mail to friend
ADVERTISEMENT
Data Warehousing on a Limited Budget - A Hypothetical Case Study

by Tim Feetham
Could you build a data warehouse with one additional staff member, a little outside help, and a technology budget of $50,000? We have decided to take that challenge here and see where it leads.

Data warehousing has become ubiquitous in large companies and fairly common with medium-sized organizations. However, judging by some of the feedback TDWI receives at its conferences, small firms are taking a look at data warehousing and wondering what the threshold is for getting into the game.

This article explores data warehousing options for a publishing company with about 50 employees. We will make use of suggestions contained in the last FlashPoint, "Selecting the Right Technology for Your Data Warehouse." We will also want to consider organizational needs and architectural options, keeping in mind that a successful data warehouse is about much more than technology.

The most important trap that we will want to avoid is cutting corners that will jeopardize our data warehousing program on down the line. Data warehouses make sense in a social setting. Nobody can pull off data warehousing development on his/her own, and personal database and reporting technology can rarely, if ever, be stretched to address the complete needs of an organization of any size.


Company Profile

Our fictitious firm, Tek'sMag Publishing, produces four information technology related monthlies, with a small staff of editors and regular columnists, plus a slightly larger group of contributing authors. The editors also solicit articles from freelancers. One individual manages subscriptions and two persons work in advertising sales. A marketing analyst maintains mailing lists designed to bring in new subscribers. Others work with printers, do artwork, ship product, and handle administration.

Advertising sales would like to have a system where they can pull up the advertising history across all four monthlies of the customer that they are currently working with. They would also like to have a history of articles published by Tek'sMag Publishing plus plans for future articles that might relate to their customer. They have been reading about data warehousing and ask the CEO if Tek'sMag Publishing can have one. He agrees.

The CEO assigns the task of creating a data warehouse to Tek'sMag's tech manager. He tells her that she can add one additional person to her staff and that she has a technology budget of $50,000. She can also hire some consulting if necessary.

The tech manager and her able assistant have already performed miracles by implementing a sound email system, good network file and print services, and a passable Web site. Fortunately the tech manager also knows a little about data warehousing and begins to explore the feasibility of a data warehouse in this environment.


Starting Off on the Right Foot

The Tek'sMag data warehouse has had a few things going for it from the start. It is being driven by the real needs of the business and the CEO has, at least temporarily, bought into the idea. Once in a while, an IT skunk works decision support effort that initially lacks business sponsorship will blossom into a sound data warehouse program. However, this is high-risk behavior. Fortunately, this is not the situation at Tek'sMag, but the tech manager still has challenges ahead.

The tech manager knows that she will indeed need to add to her staff. She also knows that one additional person cannot cover all of the functionality needed to implement a sound data warehouse. However, she understands that consultants and contractors specializing in data warehousing are available. With the right management direction they will be able to help Tek'sMag.

The tech manager also knows that she will have to assume the role of the data warehousing program manager and she will need some additional training to be able to manage the data warehouse initiative. Without training, she may have trouble selecting the right people to help her. Although she might hire an architect to design Tek'sMag data warehouse, she will be smart to study the difference in data warehousing design options before she makes a move.


Putting the Team Together

Going outside for data warehousing development work will also be necessary. However, this can be risky if Tek'sMag staff does not follow what is being done. If the new program manager only hires one additional staff member, a good bet would be to hire a person that is equally at home with business users and technologists. This person can act as a liaison between Tek'sMag and the development staff and as the on-going administrator for the data warehouse. A good understanding of data warehousing and sound report writing skills will also help that person be able to extend the value of the warehouse and support ad hoc query activity.

Data warehousing architects, database specialists, ETL developers, and access technology trainers will not necessarily come from the same organization. Some may be independent consultants, others from consulting organizations, and others from vendors. The program manager can network through professional organizations and conferences plus use a service like www.connectknowledge.com to identify the best resources.

The program manager should keep in mind that these external staff will be important for the growth of the data warehouse. Start-up costs for new data warehousing projects can be minimized if external staff can be hired on a retainer basis.


Building the Server

Tek'sMag data warehousing program manager knew that the cost of data warehousing was not going to be small. She also knew that the CEO really did not have any idea what the cost would be. However, she set a goal of trying to bring her initial technology budget in under the $50,000 target that the CEO set, with the idea that if she were to be successful, additional technology needs could be addressed later.

Tek'sMag uses Microsoft technology for email, file, and print services. They also have a small application running on SQL Server. The program manager determined that she would need another server and a new license for her data warehouse, but she did not want to consider other vendors because of the cost of training. She would stay with a Windows2000 Server and Microsoft SQL Server, Standard Edition for her database. SQL Server also has some data warehousing features built in such as a basic ETL tool and a multidimensional server, which would save her money (database server software/ETL cost: ~$5000).

Our program manager selected a fast, (1 GHz) single processor, Intel-based server from Compaq with the thought in mind that many software vendors charge by the CPU. She wanted to err on the safe side of memory and go with 1 GB. She added a high-speed tape backup and two 36 GB drives. With this configuration she felt that she could handle the initial number of users and data volumes with no problems. (Total hardware and operating system cost: ~$5500.)

Our program manager also wanted a data modeling tool. She put Computer Associates and Embarcadero on her shopping list and budgeted $4,000 for a single copy.


Providing Data Warehouse Access

The basic access needs for data warehousing center on published reports, on-demand reports, and simple queries. Our program manager wanted to address the needs of the advertising staff with on-demand, electronic reports that included basic drill-down capabilities. However, the value of the data warehouse really comes through when additional business needs can be met with minimal additional effort.

The CEO had also expressed some interest in tracking advertising revenue. Our program manager knew that she would have that data available and wisely planned to provide him with email alerts to changes in revenue that were beyond expectations. She had seen a demonstration of email alerts that allowed drill down to supporting detail. This technology did not require any technical expertise beyond deftness with a mouse. She knew that this would be a way to cement executive support for the data warehouse.

While talking with the advertising staff, our program manager found that they were interested in making publishing data available to their clients via an extranet. She expressed enthusiasm for the idea, but wisely suggested that the extranet project should be handled as a follow-on increment to the data warehouse. However, this information helped her formulate her initial requirements for data warehouse access technology.


Access Management

Our program manager wanted the new data warehouse administrator to be able to track warehouse query activity from the start. She wanted this information not only for tuning purposes, but also to identify who was using what parts of the warehouse. Although few would be using the data warehouse at first, she expected its use to grow. Usage information would be important in being able to balance the costs of the data warehouse with the business benefits gained through its use. The costs of the data warehouse are usually pretty easy to track. By knowing who is using the data warehouse, the administrator, and program manager will know whom to ask about the business benefits.

Our program manager also knew that she did not want to cut corners and put off creating a meta data management strategy. Having technical meta data readily available would be especially important when new contractors were hired to develop future increments. Business meta data will be important as well, as new business users start to take advantage of the data warehouse.

With a limited budget, some of these technologies might initially appear to be beyond the reach of the Tek'sMag data warehouse program. However, planning for them when creating those first tool requirements was a smart move.


Access Technology

With the server, database, ETL, and modeling tools costs coming in at a little under $15,000 -- that left about $35,000 for access and management technology.

The program manager drew up a list of potential vendors. Her basic criteria included Web-based query and reporting capabilities for 25 users, including limited extranet support, a single administrative development package, and publishing capabilities. Email alert capabilities and query tracking were two big pluses if she could get them. She would pay special attention to how these tools supported meta data from other technologies. She also wanted to know for future planning, what each vendor's portal strategy was, and if the vendor also provided OLAP and data mining capabilities.

The program manager found that Business Objects, Cognos, Hummingbird, and Information Builders all supplied the basic functionality that she was after. Each vendor took a slightly different approach to bundling its tool set. Costs ranged from $28,000 to $33,000. Most also included email alert capabilities, although some were more limited than others. She found that Information Builders also included a query tracking facility in its package. Others provided that capability at an additional cost while including other features with their basic packages.


Future Increments

Our project manager took the incremental approach to not only data warehouse development, but also access functionality. For example, she knew that OLAP capabilities would be needed as interest in the data warehouse grew. But there was only a passing reference to it in the initial business requirements. Her strategy was to at first enable basic spreadsheet access to the multidimensional engine that comes with SQL Server. Later, after the first budget cycle, she would add a tool from either Knosys, Cognos, Business Objects, or other OLAP tool vendor.

She also included a future portal strategy, which would help her with her tool selection. She found that Hummingbird offered a portal product, while the others offered limited portal capabilities that included open strategies for working with other portal vendors.


Your Mileage May Vary

The hypothetical Tek'sMag data warehouse case study was designed to give potential data warehousing managers an idea of what might go into building a data warehouse on a limited budget. The details of your implementation will obviously vary.

Some variations on the above scenario might involve a firm that has excess server capacity and/or already has a license for an Oracle, IBM, Informix, or Sybase database instance. This company might want to purchase an ETL tool such iWay ETL that starts around $25,000 for a single processor Windows2000 machine.

One thing that became obvious in writing this FlashPoint was that some vendors have sales strategies that exclude them from a $50,000 technology budget. Others, such as the ones mentioned above, have found a way to address the needs of the limited budget market, although they may also have fortune 100 companies that have spent millions with them. This article in no way listed all of the potential data warehousing vendors offering technologies for those with limited budgets.

Conclusion

Data warehousing on a limited budget is possible with the right strategies. Some may argue that some of the technology mentioned here is not as scalable or stable as its competitors. However, this story was not about tool comparisons. It was about finding out whether the basic technical requirements of a data warehouse could be met for $50,000 and minimal adds to staff. Our program manager was able to do it with a couple thousand dollars left over.

Tim Feetham - Tim is an independent consultant who specializes in data warehousing for small to medium sized businesses. He has worked in sectors ranging from travel, health care, finance and software, to higher education. He helped design the Data Resource Management Certificate at the University of Washington and has taught in that program for more than 10 years. Feetham is also a former senior research analyst for TDWI. He continues to contribute to TDWI publications and events.