A road may twist and turn. At times, it may have an uphill direction, followed by a downhill segment. The road may even present a detour. Eventually, however, the road will lead to the ultimate destination. How did the Department of Revenue of the State of Washington manage to travel a straight path to data warehousing without encountering detours? It was accomplished by embracing industry standards that facilitated on-time, on- budget execution.

Originally deployed only in Fortune 100 companies, data warehouses are now much more common in organizations of all types, regardless of revenue. Through the use of lower cost industry standard technology, data warehouses are now much more affordable. The benefits that an organization can realize through a properly designed and architected data warehouse are innumerable. The State of Washington Department of Revenue was cognizant of the benefits that a data warehouse would provide, but in the past the cost was too great for this public agency to even consider. With the advent of more affordable data warehousing, capitalizing on the use of industry standard technology, Tremaine (Trim) Smith was anxious to introduce data warehousing to the Washington Department of Revenue. Smith was confident that data warehousing would enable the Department of Revenue to improve and enforce compliance with tax codes. It would also further the Department's strategic initiatives: to educate businesses to register and report properly, eliminate confusion and correct misunderstandings. Smith was determined not to let budget limitations or the lack of internal expertise compromise these goals.

As assistant director of the Audit Division, Smith has been with the Washington Department of Revenue for more than 25 years. "About two years ago," says Smith, "our director obtained limited funds for technology spending. We are very respectful to the executive branch, the governor of our state and the taxpayers regarding proper usage of funds. We had to decide how we could move forward with the $1.2 million available to us to make the most efficient and effective use of those funds to meet our objectives," comments Smith. With a data warehouse of tax information, the Department would be able to manage their information strategically. "In the past, the cost of data warehousing scared us away," he adds. However, now that data warehousing has become affordable, it was within the realm of possibility for the Department of Revenue to build the data warehouse they knew they needed.

The State of Washington depends on sales tax more than any other state in the nation. Smith explains, "We do not have an income tax. We have a business and occupation tax. It is a tax on the gross proceeds or gross receipts of business conducted in our state. We knew that there were people who were underreporting some of their tax obligation, but we could not identify them because of our lack of integration. Like most organizations, we have three or four legacy systems ­– all independently functioning and doing what they need to do but none of them interacting with the others." The Department chose to use the funds available to build a data warehouse, focusing the initial iteration on determining which companies were underreporting their taxes.

The Project: Tax Compliance Data Warehouse

Once the decision had been made to use the funds for a Tax Compliance Data Warehouse, the subsequent decisions made by the Department of Revenue were key factors in the project's success. The Department faced a tough challenge. In addition to the limited budget, the entire project –­ including billing –­ had to be completed by June 30, 2003, or the successful bidder would not be paid because the funding would be withdrawn.

Smith was designated as the executive sponsor of the data warehouse project. This decision was critical to this project's ultimate outcome. As executive sponsor, one of Smith's responsibilities was to clearly articulate the Department's goals for the project. "Probably like all users," says Smith, "we had some really grandiose ideas about what we could actually do with a data warehouse." Therefore, one of Smith's first tasks was to set a realistic scope for the data warehouse. While he had solid experience on the business side of the Department, he admits to being somewhat limited in his IT expertise.

"To assist us in defining the scope of the project and developing the business requirements that we could use when we put the project out for bid, we decided to hire Sierra Systems Group," Smith explains. "Part of our agreement with Sierra was that they could not be a bidder for the project implementation. A team from Sierra conducted business requirements analysis and, more importantly, helped us get an understanding of how much we could really get with the dollars available. The RFP [request for proposal] process, which usually takes anywhere from six to twelve months in government practices, was completed in approximately three months. Sierra Systems Group truly helped us expedite the process."

With the assistance of Sierra Systems, the Department of Revenue implemented a formal evaluation process. When all bids were received, three separate evaluation teams were staffed: a financial team, a management team and a technical team. "Each team conducted separate evaluations of all the bidders and submitted a subjective score. The scores were tallied, and the two bidders with the highest scores were brought in for demonstrations," states Smith.

The Selection Process

HP Services was one of the companies invited to demonstrate its capabilities for the Department of Revenue. Tim Hartill, .NET Solutions practice principal from HP Services, was one of the members of the team providing the demonstration. "The Department of Revenue had a fixed and, relatively speaking, limited budget for this data warehouse project. We provided really good references for them, and we presented a demo using the technologies we planned to implement if selected," says Hartill.

The State of Washington Department of Revenue is a Microsoft-centric organization, with the exception of its legacy systems. When the project was scoped, Sierra Systems Group helped to guide the Department of Revenue to the types of vendors that could provide the platform that would be user-friendly and relate well with the Department's existing platform. The technologies HP Services demonstrated met the Department's requirements and are recognized as some of the leading industry standard technologies for affordable business intelligence and data warehousing. "Some of the vendors with the most government experience were not Microsoft platforms," states Smith. "As executive sponsor of this project, that made me nervous."

And The Winner Is...

HP Services was the successful bidder, and the Department of Revenue requested that Hartill be named senior project manager. Contract negotiations were completed, and the Tax Compliance Data Warehouse project commenced on September 17, 2002. With less than nine months to complete the project, HP Services began working on the first iteration of the warehouse. The goals of this iteration were to determine under-reporting accounts, enhance the Department's audit selection capabilities, enable targeted education efforts and identify common taxpayer reporting errors. Data was sourced from the Department's three legacy databases ­– the business registration system, the excise tax system and the taxpayer accounts receivable system.

The Technology

The use of industry-standard technologies for deploying business intelligence applications makes data warehousing more affordable than the tools and technologies used in the early years of data warehousing. Today, projects can be completed at overall lower cost, achieve faster implementation times and use standardized tools instead of custom coding. HP Services was confident this project would be completed on time and on budget. This confidence was grounded in the industry-standard technologies and resources they planned to use. HP Services selected a Windows industry standard open architecture for this project. The data warehouse was built on Microsoft SQL Server 2000 Enterprise Edition, with Microsoft's Data Transformation Services (DTS) handling the extract, transform and load functions. The hardware needs were handled by an HP ProLiant DL580 G2. HP's ProLiant servers are designed for industry-standard computing and exceptional scalability. The Department also purchased four ProLiant servers for separate development and test environments. The Data Clustering Engine from Search Software America was used for soft matching, and BusinessObjects was chosen for the reporting and analysis package.

The Team

Led by Tim Hartill, the HP Services team numbered seven individuals at the high point of the project, including several of their offshore resources. Says Hartill, "We actually brought a few resources onshore from our India division. It worked really well. This was a very cost-effective solution; and, at the same time, we mitigated the risk by making sure that there were experienced U.S.-based HP consultants playing the senior roles on the project."

In addition to Smith –­ the project's executive sponsor –­ the Department of Revenue team included Kathy Oline, business lead; Colin Corbin, technology lead and Amos Chong, technology lead. Chong, one of the managers of the Department's database group, had extensive Microsoft data warehousing expertise. "He had a real willingness to roll up his sleeves and understand the HP strategy," notes Smith.

"The Department did a very good thing," comments Hartill. "They assigned two technical people to the project on a full-time basis to facilitate knowledge transfer. You don't find too many customers that are willing to make that sort of investment, and it has definitely paid off for them." In addition to Smith and the three full-time resources, the Department's involvement consisted of a variety of IT staff, security staff and business users including managers from the primary operating divisions. "The first iteration of this data warehouse was delivered about three weeks ahead of schedule," notes Hartill. "We followed our methodology in a very structured way throughout the project –­ the requirements analysis, architecture, design and rollout of the first iteration. The customer was very amenable to following this very structured process. There were no significant issues, and all the tools used have done what they were supposed to have done. It has been a model project from start to finish," he states.

The Second Iteration

"We essentially repeated the process for the second iteration," says Hartill. The second iteration addresses issues from a tax- enforcement standpoint, with a goal of identifying unregistered accounts and non-reporters. "For this iteration, we brought in external data," explains Smith. "We were faced with a unique challenge because of our business structure. Other states that have an income tax require companies to use their Federal ID number when filing their taxes, but our State uses a unique business identifier number. It has no connection to other federal or state agency data. Thus, we had an added complexity when integrating this data. Additionally, this data was provided on tape, adding yet another level of complexity to the project. For privacy and security reasons, we assumed responsibility for getting the data from the tape into tables that could be integrated into the data warehouse. HP Services assisted in the identification of the data fields, but they were not allowed to touch a lot of the data," notes Smith.

Project Time Line

July 1, 2001
Funding obtained and three-phase project commenced

February 2, 2002
Phase 1 –­­ Project Planning
Sierra Systems Group selected to guide Department of Revenue formal "kick-off"

  • Project charter
  • Project scope
  • High-level business and technical requirements

April 1, 2002 Phase 2 –­­­ Business & Technical Requirements Analysis (also with Sierra Systems)

  • Interviews were conducted with the business users to identify, analyze and document business requirements
  • Interviews were conducted with IT staff to identify and document technical requirements
  • Sierra Systems guided Department of Revenue through RFP process to select an implementation vendor

September 17, 2002 Phase 3 ­–­­ Implementation

  • HP Services selected as implementation vendor
  • Iteration 1 completed, followed by Iteration 2

Each iteration included:

  • Detailed Business Requirements Analysis
  • Architecture and Detailed Design
  • Implementation and Testing
  • Knowledge Transfer and Production Deployment

June 27, 2003 Project Completed –­­­ On Time and On Budget!

The second iteration of the data warehouse was completed on June 27, 2003 –­­­ ahead of the deadline! Both Hartill and Smith credit each other for the project's success. "The Department took the time up front to identify what they wanted to accomplish, and they focused on it all the way through. We mutually negotiated a contract that had very clearly defined deliverables. We had everything spelled out, and everybody kept their sides of the bargain," states Hartill. Smith provides his point of view, saying, "From my standpoint, it was very, very successful. We had weekly status meetings with our team and with the HP team. I closed almost every status meeting with the reminder ­ 'Okay, we have to be done by June 30.' Tim and I communicated well and had phone calls weekly in addition to our weekly status meetings. We really stayed on top of things all the time. The team that we developed from both sides really seemed to meld well, and I give HP Services a lot of credit for that. I also give our internal team a lot of credit for that too. I really don't think we'd be where we are today if that weren't the case." Hartill concurs, acknowledging the Department of Revenue project management effort, saying, "The customer did a great job of managing the process. That was a key success factor."

The Future

Now that the Department of Revenue is on the data warehousing road, they are already making plans for the next iteration. This iteration will be accomplished solely by the internal team, thanks to the effective knowledge transfer throughout the project implementation. Smith enthusiastically comments, "Quite frankly, when HP completed the project –­­­ before June 30 I might add –­­­ our people were ready! They are already thinking about the third iteration, and they are meeting with some of the users to establish goals for this iteration. Currently we are trying to narrow some management philosophies that I have in my mind now that I'm getting more comfortable with what the data warehouse is capable of doing. One possibility for the next iteration is to help us identify some internal workload efficiency. Right now a lot of our tax administration divisions have a real historical approach to processing. I think we might be able to better utilize this data warehouse to determine whether or not there are areas we could focus on workload efficiencies. I also want to start using this data much more from the management planning and analysis standpoint, and as soon as we can get payment information ­ which is potentially another iteration –­­­ we would be able to rank and score taxpayers as to which ones may or may not be likely to pay if they have a balance due. We also might be able to predict whether or not an account might file bankruptcy, and that could be a real benefit to us. I'd like to use, or at least consider using, information in the data warehouse to help me determine our effectiveness in the audit program. Are we auditing the right kinds of accounts? Are there different ways for us to analyze and predict what type of accounts might be more beneficial for us? Where might we see some efficiencies if we are able to increase some parts of our automation internally for the audit division?" The road is obviously becoming an information highway!

Travel Tips

Smith words serve as advice for future data warehouse road warriors. "We knew that we had limited funds and limited data warehousing experience. We also realized that it is very important to conceptually define the best process, recognizing that we needed help to bring this project to fruition. I think we really did a good job, and both Sierra Systems Group and HP Services did a marvelous job. They really did," concludes Smith.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access