Creating the Cube
Now you have the data in a format that you can load into a cube. Launch Analysis Manager to start the cube-creation process. Table 1 shows the dimension structure I chose for the Money cube. In addition to the dimensions that Table 1 shows, I considered making a dimension out of the Reconciled column, but I couldn't determine any value in analyzing reconciled transactions versus unreconciled transactions.
I won't bore you with all the steps that Analysis Manager takes to create the cube, but I'll give you a couple of hints that might help if you haven't created a cube before. First, right-click the Analysis Server you want to use, and choose New Database to create a database. Next, right-click the Data Sources folder in your new database, and select New Data Source. In this step, you can choose the Microsoft Jet 4.0 OLE DB driver if you have the data in Access, as I did. Finally, right-click the Cubes folder, and select New Cube, Wizard to step through the process of creating your dimensions and measures.
After you've defined your dimensions and measures, I suggest making two changes from the defaults in the cube editor. First, format the Amount measure as currency, and second, enable drillthrough. You can change the display format of a measure by selecting the measure in the cube editor, displaying the properties, and flipping to the Advanced tab of the properties. You'll see a property labeled Display Format that has a combo box of choices. You can also enable drillthrough from the cube editor. From the Tools menu, select Drillthrough Options. In the Cube Drillthrough Options dialog box, click Enable Drillthrough, and select the columns you want your OLAP front-end application to display when you drill through a cell of the cube. (I recommend selecting all the columns.)
Drillthrough lets you see individual transactions that make up cell values in the cube. For example, say your money-management application says you spent $1543.21 on dining out in March, and you can't believe it. Drillthrough lets you see exactly which transactions you, your spouse, or someone else in the family entered in the Dining Out category for March.
If you're like me, every chart or grid of numbers you see that's based on the Money data will answer one question and prompt you to ask two more. If you have a good OLAP front-end tool, you can quickly navigate the data by pivoting in and out of dimensions. Drill down or up, and you'll learn things about your finances that you never knew before. You could learn these things by running reports in Money, but analyzing the reports would take you a long time, and you might give up before you got any information.
Using What You've Learned
The approach I used to build a cube from my Money data reveals problems you might encounter when sourcing data for any analysis application. For example, you need to be sure to explore the source system's reporting capabilities as an option for extracting data. As I discovered in Money, you might find the results that the reporting capability returns to be closer to the data format you want. Also, the technique I used in Excel to massage the data is an example of how to be creative in fixing data-quality or format problems when you're performing ETL of your source data.
Besides learning about your finances and becoming more financially responsible (yeah, right), you can reap another benefit when you browse data that's meaningful to you: You learn what it's like to be a user of your own analytic application. This experience will make you better at your job of creating analysis solutions for other people. For example, after you browse your own data for about 5 seconds, you'll realize that drillthrough isn't optional. You'll no longer take a number at face value; instead, you'll want to see what factors created that number and whether it's legitimate. I hope you enjoy going through this exercise as much as I enjoyed creating it!
End of Article
Prev. page
1
[2]
next page -->
