Can Reporting Services help a time traveler cut his workload?
Melissa was my brother's kid. Twelve going on twenty-five. She was sure she had no use for school 'cause she knew it all. She came to stay with me while my brother and his wife got away for a while.When my brother called to make the arrangements, I wondered just what they were getting away from. After a week with Melissa, I didn't have to wonder.
The tirade on October 1, 2005, was about history. Melissa slammed shut her book about the 1960s. "Why do we have to study this ancient history, anyway? I'm never going to use this stuff!"
I'll admit I wasn't too enamored with history when I was Melissa's age, but nowadays, I have more appreciation for the subject. Perhaps you have to have a past before you can truly appreciate the past. Besides, a detective who doesn't keep track of history is doomed to repeatedly solve the same case.
I was about to attempt to defend our public education system in general and the study of history in particular, when mercifully, my phone rang. It was an IT supervisor named Nancy Summers. She wanted to see me right away. I told Melissa to keep studying and I'd be back in time for dinner.
"I have a security problem, Mr. Powers," offered Ms. Summers when I arrived at her office."One of my employees has been falsifying security logs."
"Trying to cover his tracks by erasing information?" I ventured.
"No, he's actually creating entries in the security log where no entries were before." Ms. Summers explained. "In several places, the log shows a Windows logon for one of my administrators, Ed Century, that wasn't there before. The strangest part is, these new entries show up for dates that are months in the past."
I gave Ms. Summers a quizzical look. "So, just what is Mr. Century up to?"
Ms. Summers leaned toward me across her desk. "That's what I want you to find out."
I agreed to take the case, and in no time I was staking out Ed Century's cubical. It wasn't too exciting. This guy just sat at his desk and worked hard. He worked real hard. If there's one thing I've learned, it's that crooks are crooked because they don't want to work hard. There had to be another explanation for the strange security log entries.
All of Mr. Century's hard work must have made him tired. It certainly made me tired; which is probably why I fell asleep during the surveillance.When I awoke, the office was dark and deserted. I'd missed my dinner date with my niece. I was about to grab my cell phone and give Melissa a call when I heard someone coming down the hall. It was Mr. Century! Apparently, he hadn't worked hard enough during the day and was back for more.
Mr. Century, wearing the same white shirt and khakis I had seen him in earlier in the day, went to his cubicle and began typing on his computer, just the same as he had done all day long. But what happened next was not the same. There was suddenly a bright flash of light in the cubicle and Mr. Century was gone!
I ran to the cubicle to see what had happened. It was completely empty except for a computer flashing "March 1,2005" on its screen.
As I stood baffled, there was another flash of light. I closed my eyes to shield them from the glare. When I opened them, Mr. Century was again standing in the cubicle. However, he was wearing a blue shirt and jeans. He looked at me with mild surprise and said, "Mr. Powers, what are you doing here?"
My cover was blown, although I couldn't for the life of me figure out how it had happened. I decided to use the direct approach. "I'm here to investigate why a seemingly conscientious administrator is making spurious security-log entries."
Mr. Century smiled. "But you solved that mystery months ago."
"Very well, wiseacre," I growled, "would you care to explain how months ago I solved a case that I just accepted this afternoon?"
"From my perspective, it was months ago," Mr. Century explained, "you see, I've come here from April 1, 2006."
"Sorry, Century, you're still making no sense."
"I suppose I'm not," Mr. Century apologized. "Allow me to explain. We've been using Reporting Services here for about a year. Everything worked great except for the Budget Statement."
"You had a problem creating the Budget Statement report?" I interrupted.
"Not at all," Mr. Century continued. "The report worked fine. The problem occurred when the finance department started asking for older versions of the report."
"Older versions?" "They wanted to see what the Budget Statement looked like two months ago or four months ago or six months ago. I had to save old versions of the report layout. I had to save old versions of the data. I had to make sure I matched the appropriate version of the layout to the appropriate version of the data whenever I ran an old report. It was such a hassle. That was when I invented the time machine."
I picked my jaw up off the floor and made sure I had heard this young man correctly. "You use a time machine to go back and run old versions of the report and then take them to the future?"
"Exactly," Mr. Century confirmed. "At least I did until you told me about history snapshots."
"I told you?" I said, feeling very confused. "Of course. You showed me how to schedule history snapshots by using the Report Properties dialog box in SQL Server Management Studio. (See Figure 1.) Now, on the first day of every month, Reporting Services stores a history snapshot of the Budget Statement. (See Figure 2.) I can request a display of any history snapshot by clicking it. You also told me that I could do all of this by using the Report Manager just as easily as I did by using Management Studio."
"Anything else I told you?" I asked,my head still spinning.
"You told me the best thing about history snapshots is that they save both the report layout and a copy of the data at the time the snapshot is created. I don't have to save old versions of the data or the report layout. It's all done for me in the history snapshot."
"Just one more question," I said, "if you're using history snapshots, why did you time-travel back here tonight?"
"I began using history snapshots with the November 2005 report. When finance requests anything earlier than that, I still need to use the time machine."
With that, I let Mr. Century log on, run his report, and be on his way. Moments after the blue-shirted Mr. Century flashed back to the future, the white-shirted Mr. Century reappeared as he returned from the past. "Who are you?" he said with a start.
"I am B.I. Powers and I've come to make your time travel a thing of the past."
I told the present Mr. Century about history snapshots, just as the future Mr. Century said I would. He was extremely grateful and asked if there was anything he could do to repay me. I looked at my pocket watch, saw the hands creeping close to midnight, and knew the form that repayment would take. With a little help from Mr. Century's time machine, I made it home by 5:45 P.M., with enough time to fix franks and beans for Melissa and me. Over dinner, I told Melissa how truly important history can be.
This case involved travel through the dimension of time. In my next case, I discover that working with the time dimension of an OLAP cube can be just as tricky!
B.I. Powers shares an office at Superior Consulting Services in Minneapolis with Brian Larson, a frequent presenter about SQL Server business intelligence and author of Delivering Business Intelligence with Microsoft SQL Server 2005 (Osborne/McGraw-Hill, 2006). Powers is too cheap to get his own email box but may be contacted through Larson's address at firstname.lastname@example.org.