It was not the first time I'd had a bout like this, but it was probably one of the worst. I was feeling like I was behind the times, an anachronism, a throwback. I was feeling as if time had passed me by. I was feeling...out-of-date. Did the fact that I wore a fedora and a necktie wider than some aprons mean I wasn't hip and happening? Did my choice of a '37 Packard as my current ride signal I wasn't keeping up? Was I living in the past?
I leaned back, feeling about as creaky as my office chair, when my phone rang.The tinny voice on the line asked for help.The caller, Gerold Newsideas, said he had a cube that wouldn't produce the numbers he wanted. I've had that same problem at the craps tables in Vegas, but that wasn't what he meant. I told him I'd be right over.
As I pulled up to the building housing Gerold Newsideas Manufacturing, I noticed that the sign had problems of its own. Some of the letters were burned out, so it spelled "...old News... Manufacturing" in red neon.
Inside, the building was so dim it seemed that the tile floor, the wooden desks, and even the employees' faces were all varying shades of gray. The brass pendulum in the large Regulator clock seemed muted to match its surroundings.As I stepped through the open door of the Otis elevator, a voice said, "What floor, sir?" I saw a young woman standing inside the elevator wearing white gloves, a narrow skirt, a short jacket, and a pillbox hat.
"I'm here to see Mr. Newsideas," I replied, a bit startled. The woman slid the gate closed and worked the elevator controls.
"I didn't expect this type of personal service," I commented."It's so rare these days."
"Not here at Newsideas Manufacturing," she insisted."We take pride in giving personal attention to each customer."
I was impressed by her enthusiasm."You should be in Marketing," I stated.
The young woman got a distant look on her face, like she was wrapping her brain around an idea that had never occupied that space before.Then, the elevator car came to stop.
The woman in Gerold Newsideas' outer office wore a pageboy haircut and a flowered dress. She kept a steady rhythm as she clacked out a letter on a manual typewriter. I introduced myself and told the secretary, Miss Simmons, I was expected. She punched a button on a small black box."Mr. Newsideas? There's a Mr. B.I. Powers here to see you," she announced, looking at me dubiously.
"Send him right in," came the reply. I walked through the inner door and introduced myself to Gerold Newsideas. He was a trim man with slick hair, a double-breasted suit, and black-and-white leather shoes. After the usual pleasantries, we got down to business.
"You said on the phone you're having trouble with a cube," I began."I assume it's not of the ice variety."
"That's correct," Mr. Newsideas confirmed. "I have an OLAP cube that's always out-of-date. My company depends on the data in that cube for minute-to-minute decision making as well as long-term planning. When that cube is out-of-date, it's like the entire company is living in the past."
I interrupted,"What platform does this cube exist on?"
"SQL Server 2005," he fumed."I was told it could decrease the latency of my OLAP structures. I guess I was sold a bill of goods."
"Not at all, Mr. Newsideas," I protested. "SQL Server 2005 can keep your OLAP cubes up-to-date." He looked at me skeptically. "It does, however, require the appropriate configuration to make it work. Let's take a look at the cube."
I opened SQL Server Management Studio and navigated to the cube on his Analysis Services server. Right-clicking the cube in the Object Explorer window, I selected Properties from the Context menu and the Cube Properties dialog box appeared. Finally, I selected the Proactive Caching page that Figure 1 shows.
I informed Mr. Newsideas that Analysis Services uses this page to determine how the values in the OLAP cube are stored and processed to keep them up-to-date. First, we discussed the different storage options. Relational OLAP (ROLAP) stores the cube's detail data and aggregates in a relational database. Multidimensional OLAP (MOLAP) stores the cube's detail data and aggregates in a multidimensional database. Halfway in between is Hybrid OLAP (HOLAP), which stores the cube's detail data in a relational database and stores the cube's aggregates in a multidimensional database. Mr. Newsideas assured me this OLAP cube should store all its data multi-dimensionally.
I pointed out that the default setting on this page was MOLAP. It did store the cube's data multi-dimensionally, but it didn't include any automatic processing to keep the cube up-to-date.This was the cause of the trouble here at Newsideas Manufacturing. To have automatic processing set up,we needed to look at the other MOLAP options. I explained that the Scheduled MOLAP setting processed the cube once every 24 hours, resulting in up to 1 day of latency. Not good enough, according to Mr. Newsideas.
I went over the other three MOLAP options. Automatic MOLAP has a maximum latency of whatever time it takes to process the cube.For example, if it took 6 hours to process the Newsideas Mfg cube, the maximum latency would be 6 hours. The Medium-latency MOLAP option produced a maximum latency of 4 hours, and the Low-latency MOLAP option produced a maximum latency of 30 minutes.With these last two options, I cautioned, query performance could be slow during cube processing because Analysis Services runs queries against the cube's underlying relational data once the cached values become too out-of-date. I also explained that the 4-hour and 30-minute maximum latency periods are default values that can be adjusted.
Mr. Newsideas determined that keeping the cube data current was most important for his applications. Following his instructions, I clicked Options and set the cube to the Low-latency MOLAP setting, as Figure 2 shows.As I saved this new setting in SQL Server Management Studio, the lights seemed to come up to full strength, bringing color back to everything. I graciously accepted the thanks of Mr. Newsideas, noting as I did, his close-cropped hair, sport coat and tie, and brown loafers.
On the way out of the office, I saw Ms. Simmons quietly typing on her keyboard before a large, flat-panel display. In the elevator, I pushed the button, and the door closed automatically. About halfway down, the elevator stopped for a young woman who bore a striking resemblance to the woman I had encountered in the elevator on the way up, but she now carried a folder labeled "Marketing Plan: 2006."
Outside, I looked back at the thoroughly modern glass, aluminum, and brick building. I noticed the sign was still in need of a few new light bulbs. "New...ideas Manufacturing" it said in bright blue letters. It's amazing how out-of-date information can affect an organization's entire outlook; almost like a magic spell, it keeps that organization trapped in the past.
As I drove away,I returned to my thoughts from earlier in the day.Was I as out-of-date as the Newsideas Manufacturing cube? Maybe there was a simple setting in some configuration somewhere that would bring me up to speed?Then I had another thought. Perhaps being able to use the latest business intelligence tools to solve other people's mysteries was as hip and happening as I needed to be.With that thought, I settled my fedora on my head and gunned my Packard down the road in search of my next case.
The Newsideas Manufacturing case was all about keeping up-to-date. My next case would require delving into the past.
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 [email protected].
