SQL Server Magazine

Most analysis solutions I work on contain other people's data, and I maintain a professional distance from the confidential details that data might reveal. If you're a DBA or developer, you're probably in a similar situation. But you gain more insight about OLAP when you analyze data that means something to you personally. My first meaningful experience with OLAP came when a friend sent me a cube containing 13 years of college football data. The cube contained all the teams and their win and loss statistics in different playing situations. I spent a couple of hours intently pivoting, drilling down, charting, and creating custom measures, and I found some interesting trends in the data. For example, some college teams are more likely to win on the road than at their home stadiums.

One way you can set up a cube that has personal meaning is to put your Microsoft Money data into an OLAP cube to explore. If you don't track your finances in Money—or if looking at your finances would be too depressing—you might still find this example valuable because it demonstrates all the steps involved in constructing a cube. You can download a trial version of Money from the Microsoft Web site and experiment with the Money sample file. I haven't tried to extract data from another application such as Intuit Quicken, but I'm sure if you use one, you can find a similar technique to extract data and build a cube. I think you'll find that being a user of one of your own cubes will help you create better analysis solutions. To build a cube, you need to get the data out of the application (in this case, Money) and into a table format (I chose Microsoft Access), then design a cube in Analysis Manager and process the cube (load the data from the table format into the cube).

Extracting the Data
The first hurdle was to get the data out of Money. The task seemed trivial at first, but the process wasn't obvious. I use Money 2003 Deluxe, and the only export option I found created a Quicken Import Format (QIF) file containing the data from only one account. This data would be usable, but exporting the data from each of my accounts (e.g., checking, credit cards, savings, investment, IRA) individually would be a tedious process. Plus, QIF isn't particularly database friendly; every field is on a different line, and the fields are wrapped in punctuation marks. I'd need some time to develop a script or small application to massage the QIF data into a format that I could easily load into a database table. Then, I discovered that I could generate a report in Money and export the report data to a comma-delimited format (CSV) file. That was definitely the ticket because the applications I wanted to use—Microsoft Excel, Access, and Analysis Manager—can readily access CSV files.

My goal was to export all the individual transactions (such as a check or a credit-card charge) but ignore interaccount transfers (such as a payment from checking to my credit card). I didn't want these transactions because I wanted to limit my analysis to my true expenses and income. I discovered a way to achieve this limited focus by creating what Money calls a "Transactions by Category" report. After I generated this report, I chose the Customize option and changed a few settings. On the Rows & Columns tab, I checked the boxes to include all fields, I set subtotaling to None, and I selected the Show Splits box. These settings gave me the maximum amount of detailed transaction data. Next, I flipped to the Category tab and clicked Select All, then cleared the Include Transfers box to include all the data except the interaccount transfers. Next, I flipped to the Date tab and typed the date range of the transactions I wanted to export: 1/1/2002 to the current date. (If you're using the Money sample file, you should choose 1/1/2000 to 1/1/2002.) Finally, I clicked OK and saw the report containing all the data I wanted to export.

When you've generated your report, you'll see on the left side of the report under the heading Other Tasks the option Export to Microsoft Excel. This option generates a CSV file, launches Excel, and opens a spreadsheet that contains your exported data. Click the Export to Microsoft Excel option, and type the name of the CSV file (I chose Sample.csv). After you select the filename and click OK, you'll see your data in Excel.

Now comes the fun part. Any experienced DBA knows that data is never in the right format for a direct import. When you inspect the Money data, you'll see some problems. A blank line separates the transactions, the Memo field in investment transactions contains multiple pieces of information, the category field contains both the category and subcategory, and split transactions are a mess. You have to create a script or use an extraction, transformation, and loading (ETL) tool to fix all these problems. Every DBA has a favorite ETL tool, but I simply used Visual Basic for Applications (VBA) in Excel. I created a VBA macro that steps through Excel and reformats the data so that I can import it into Access and, from there, build my cube. For more information about the macro I created, see the sidebar "Money Macro."

If you have an OLE DB driver that reads Excel documents, you could go directly from this step to designing and processing the cube. I didn't have the necessary driver, so I first loaded the Excel data from Sample.xls (the Excel version of Sample.csv) into an Access table. To do this, start Access and choose File, New, Blank Database. Pick a filename such as MSMoneySample.mdb. From the File menu, select Get External Data, Import. Select Sample.xls, and Access will show you the Import Spreadsheet Wizard. In the wizard, you need to make a few changes. On the wizard's first page, select the First Row Contains Column Headings option. In the first column of the third page, change the field option called Indexed to No. Finally, click Finish, and close Access.