Jack was gracious enough to show me his Analysis Services project in the Business Intelligence Development Studio. The fact table holding Jack's novelty manufacturing data contained a datetime field called DateOfManufacture. I opened the data view that the OLAP cube was using and added two named calculations to the ManufacturingFact table (Figure 1).These named calculations provide expressions for the month and the year for each record in the fact table. To extract the month and the year, the named calculations use expressions based on the DateOfManufacture field. "These calculations will create the month and year hierarchy of our extrapolated date dimension so that you can do your fruit...I mean, date rollup," I explained. Finally, I used the Dimension Wizard to create a time dimension based on the DateOfManufacture field and the two named calculations in the Manufacturing-Fact table (Figure 2).
"You see," I concluded,"when you have a datetime field in your fact table, you don't need to manually create all of the date members of that dimension."
Jack looked down at his line of fruit. He turned to Nancy. "I don't suppose I can return these?"
Nancy turned without a word and headed for the elevator. Jack called after her, "By the way, did you know you're out of toilet paper in the men's room?"
I was able to obtain several figs that had not yet been dated and returned home to prepare my fig surprise.The dish lived up to its name; Melissa was indeed surprised by the meal. I'm sure it will be a date she will always remember.
In this case, Jack Stone wanted a dimension of dates. In my next case, I meet a woman who wants it all.