It was the last day of my niece Melissa's visit with me while my brother and his wife took a two-week break from the rat race, the daily grind—and a certain 12-year-old. Tomorrow, the vacation was over and Melissa was heading home, so tonight I planned to make my famous fig surprise. I was sure Melissa would remember this meal for years to come.
There was just one problem: As I rounded the banana display in my local grocery, I saw an empty bin where the figs ought to be. They'd been cleaned out! Apparently, I wasn't the only uncle sending his niece off with a steaming plate of fig surprise.
I wheeled my cart to the service desk, where I spotted the produce manager, whose name tag said, "Hello, I'm Nancy, Your Helpful and Friendly Produce Manager."
"Excuse me," I began.
Nancy turned, and I could see she held a phone to her ear. "Can't you see I'm on the phone?" she snapped.
While I waited for Nancy to complete her call, my cell phone rang. As I answered the phone, I heard Nancy say, "Can I speak with B.I. Powers?" A second later, the voice in my earpiece said, "Can I speak with B.I. Powers?"
I lowered my cell phone and spoke to Nancy."I'm B.I. Powers."
Nancy scowled at me."Shhhh! I'm on the phone with B.I. Powers." I raised the phone back to my mouth. "I'm B.I. Powers," I repeated loudly.
"Oh, so you are," said Nancy as a look of sudden realization crossed her face. Just as quickly, the realization was replaced by a look of accusation."Are you like those detectives on TV—wherever you go some calamity occurs?"
"No," I said firmly, not wanting to be banned from the store—or at least from the produce department—for life."This is just one of those happy coincidences."
"Well," Nancy the Produce Manager retorted,"I wouldn't say it's happy. Something very strange is going on."
"Something strange?"
"Yes. All of the dates have disappeared," Nancy explained.
"All of the figs are gone as well," I offered.
Someone at the other end of the service desk chimed in,"There are no more Mexican jumping beans in this counter display either."
"There's no more toilet paper in the men's room," added an older man going past with his cart.
"That's not my problem," Nancy shouted after him.
I suggested to Nancy that we question the cashiers. One of them remembered something out of the ordinary."A man came through my line with two carts heaped full of dates, figs, and Mexican jumping beans. Had to take him out to the loading dock to weigh the carts before I could ring him up."
At that moment, the three of us noticed the automatic door, which kept opening and closing even though no one was there. Nancy and I walked to the door for a closer look. There, jumping up and down on the floor mat that activated the door, was a Mexican jumping bean.
"A trail!" I said excitedly.
"Come on." As Nancy and I rushed through the door, the checkout boy called after us."Hey, did you know we're out of toilet paper in the men's room?"
"That's not my problem," Nancy called back.
Nancy and I followed a trail of flattened dates and mashed figs across the parking lot, down the sidewalk, and into the elevator of a nearby office building.We stopped at every floor until we picked up the trail on the fourteenth floor. Nancy and I followed the trail around a corner, then pulled up short. Before us was a long line of dates and figs along with the occasional Mexican jumping bean. Squatting at the far end of this strange line was a man diligently writing on each piece of fruit. Closer inspection revealed he was scrawling a month, day, and year on the produce.
"Who are you and what are you doing with my fruit?" Nancy demanded.
The man, somewhat startled, got to his feet."I'm Jack Stone," he replied."I'm creating a time dimension."
"A time dimension," I repeated, beginning to understand,"made out of dates."
"Exactly," said Jack with some pride.
"And the figs?" Nancy pressed.
Jack caught site of Nancy's name tag and realized who was grilling him. "You really should stock more dates. You didn't have enough, so I had to substitute. I'm using the figs for weekends and holidays."
"And the jumping beans?" Nancy continued, annoyed that her produce management skills were being questioned.
"Those are the leap days," Jack stated.
"I think I know an easier way," I interjected."You're creating a time dimension for your SQL Server 2005 OLAP cube?"
"Yes," said Jack with amazement."How did you know?"
"Simple," I explained. "I saw the SQL Server 2005 books on your shelf.Now,tell me what you intend to use your OLAP cube for."
"My company makes novelty items." Jack replied."You know, handshake buzzers, whoopee cushions, that sort of thing. We're building an OLAP cube to help us analyze our manufacturing process. The cube will have dimensions for the product code, batch number, the machine the product was run on, and date of manufacture."
"And the fruit?" I prompted.
"I wanted to lay out the time dimension before I manually entered all of the members. Besides, I thought a time dimension made of dates added a certain comic irony to the whole project. Being in the novelty business, we're encouraged to make things fun. Say, do you know what I get when I use my time dimension to get annual manufacturing numbers? A fruit rollup!"
Jack laughed uproariously at his own joke, while I simply shook my head.
"Does your fact table contain a datetime field?" I asked, finally putting an end to the merriment.
"Yes," Jack confirmed.
"If you'd spent a bit more time with those books and less time raiding Nancy's produce section," I pointed out, "you'd know that there's no need to enter the time dimension members by hand. You can extrapolate a time dimension from a single datetime field in your data source."
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.
