For Immediate Release 4/22/2005

Case Study - Creating a BI Datamart

Situation
Founded in 1977 by former Dallas Cowboys quarterback Roger Staubach, The Staubach Company is a global real estate advisory firm that delivers cost-effective solutions for the users of office, industrial, and retail space. The company's areas of expertise include strategic consulting, site selection, acquisition, disposition, construction consulting, project management, real estate administration, portfolio management, financing, and capital solutions.
A key business process for their offices/brokers is the ability to record and track revenue information on accounts. The system they have to accomplish this is stable and robust in handling the transactional nature of this process. Reporting information on a single transaction is straightforward.  However, it does not lend itself to reporting aggregate information, or analyzing the information by “slicing” the data in various ways.
An annual report was created and distributed to various constituents within the organization related to the revenue information. These reports took multiple IT and analyst resources weeks to produce through a manual, time-consuming process. As the transactional systems grew in functionality, the time to provide information to the business users was growing exponentially, yet the requests for this information continued to grow.

Critical Issue
The Staubach Company needed to increase the velocity in which it was able to deliver information from the transactional systems to the business user and analyst communities.  IT resources were being consumed by creating reports. The goal was to provide the business community the tools to analyze the data for themselves, thus freeing up IT resources to pursue more strategic technical initiatives.
Another critical component was knowledge transfer. The Staubach Company desired to not only have the solution implemented, but to work closely with the consulting partner to gain knowledge in the development and design of multi-dimensional datastores.

Solution
The solution was to create a data mart that would allow the business and analyst communities to use Microsoft Excel to create their own reports, thus moving the tools into the hands of the end users.
The architecture included creating an ETL (Extraction, Transformation and Load) process by which information was moved from the transactional system on a nightly basis into a data mart.  From here, Microsoft Analysis Services Cubes were used to create a data repository that their business and analyst communities could access via the Microsoft Excel Business Accelerator Add-in. This gave them the ability to quickly create various reports by simply “dragging and dropping” columns into a spreadsheet. Further, they were able to “slice and dice” and “filter” the data through various dimensions defined within the cube.
Some of the reports desired lent themselves better to a more structured/canned approach. In these cases, Artis Consulting worked with The Staubach Company to write reports using Microsoft Reporting Services against the Analysis Services cube. Thus, we provided two avenues in which to access the data depending upon the audience/user. However, the IT staff had to create and maintain the data in only one location using one set of processes.
Finally, during the entire process, Artis Consulting worked closely with the Staubach Company’s IT and analyst staff to provide training, knowledge transfer and some co-development.  This enhanced the skill set of the internal staff as well as providing an immediate solution.

Technology Used
Microsoft Windows 2000, Microsoft SQL Server 2000, Microsoft Reporting Services, Microsoft Data Transformation Services, Microsoft Analysis Services.

Result
The work was completed “on time and on budget”. The time to produce the reports went from “person-weeks” to minutes. Whereas, the reports were produced annually, they now have the ability to view the reports at any time, with data from the transactional system that has a latency of less than one day.
However, the real success of this project was providing the ability to the business community to analyze the data, with minimal IT involvement. Every company has challenges. The question is, “What is the quality of the challenge that you are working on?” By providing this toolset, the challenge for IT now moved from “getting the annual reports created” to more strategic issues. The challenge for the business community moved from “How can I get the data I need?” to “Now that I have the data, how will I change my business to make it more productive?”. These are good challenges to have.