Writing Back Time

Data analysts use database information not only to examine past business trends, but also to forecast future trends and allow for changes. Analysis Services' write-back capabilities let you do this kind of complex analysis. Write-back lets users change the underlying values stored in an OLAP cube. Analysts can use this write-back capability to develop forecasting, modeling, and data-entry systems. The creation of an employee time-tracking application for a consulting firm is a specific example of how to use Analysis Services to develop a complete write-back application. To track employees' billable time, consulting companies need to see not only what hours an employee has logged, but also what time is available for a project. And employees need to be able to enter changes to their billable time. In last month's Mastering Analysis column, "Tracking Time," June 2002, InstantDoc ID 24769, I explained the first part of how to use Analysis Services' write-back capabilities to develop a time-tracking application. I described how to design the cube, load it with data, and create a calculated member that determines the cost of your employees' work hours. This month, I complete the application by explaining how to automate cube maintenance and how to create a data-entry application.

Automating Maintenance
As I discussed in "Tracking Time," one problem with write-back applications is that changes to the data or the data's structure in the star schema might cause Analysis Services to delete and recreate the write-back table. The recreation is a problem because the write-back table contains all the information that the application's users have entered. So if Analysis Services recreates the write-back table in your time-tracking application, you could lose all the information employees have entered about their billable time.

To solve this problem, I created a Data Transformation Services (DTS) task that moves records from the write-back table into the star schema's fact table. After the records are in the fact table, you can safely reprocess the cube without being concerned about whether Analysis Services will recreate the write-back table. Also, limiting the number of records in the write-back table significantly improves cube performance. Regularly moving the write-back table's records keeps the number of records at a manageable level. For these reasons, I schedule my DTS task to run daily.

Creating the DTS task to move the records to the fact table is fairly straightforward. The most complex aspect of the process is that the write-back table and the cube's fact table are structured differently, as Figure 1 shows. The Tracker cube's fact table has dimension tables for Employee, Project, and Time values, and the write-back table contains all that information. The write-back table is a completely denormalized version of the star schema. Every dimension level in the Tracker cube has a column in the write-back table. This relationship is the reason that Analysis Services needs to frequently recreate the write-back table: Any change to the structure of a dimension leads to a change in the columns of the write-back table. The Author and ChangeTime columns of the write-back table contain the user's name and time that the write-back entry was made. This information about each write-back entry lets analysts determine the source of incorrectly entered information, so I created the same Author and ChangeTime columns in the fact table. This extra copy of the columns lets an analyst drill through to the fact table from a selected cube cell value to view who entered the value and when.

Listing 1 shows the SQL INSERT statement that moves the fact table records. This INSERT statement also joins the records in the write-back table to the dimension tables in the star schema to determine which foreign key values the fact table needs. For example, the write-back table contains a ProjectName level but not a ProjectID. The fact table requires a ProjectID, so the INSERT statement joins the write-back table to the Projects table by ProjectName to determine the correct ProjectID. Note that the format of the date in the write-back table differs from the standard date and time format that the Time dimension uses. This difference makes joining the tables to determine the TimeID difficult. I resolved the problem when I first created the Time dimension table by creating another column called TimeText in the TimeTable table that matches the format Analysis Services uses in the write-back table.

I won't cover all the details of creating and scheduling a DTS package that moves the records, but I'll walk through enough to get new DTS users started. Start Enterprise Manager, and in the tree view in the left pane of the window, find the server that contains your star schema tables. Then, right-click the Data Transformation Services folder. Select New Package to create and edit a new DTS package. After you define the data-movement and cube-reprocessing steps in the package, you can save it. When you right-click the package name, you can select Schedule Package to enter information about how frequently you want the package to execute.

Creating a Data-Entry Application
After you design and build the cube and you have a DTS package to maintain it, the next step in creating the complete write-back application is to create an application that lets users enter new data. I created two such applications for my Tracker cube. One is a simple Web-browser­based application that lets employees enter time data directly into the cube. The other, a desktop application for employees who work at home or travel for business, lets employees enter data when they're not connected to the company network. Later, they can use this application to upload the time information to a Web service, which will write the information back to the Tracker cube.