SQL Server 2005 Documentation

An updated version of the SQL Server documentation and tutorials is available from Microsoft. This version corrects some errors and omissions in the initial documentation release. If your version of documentation has comments like "insert image here" you know it needs updating.

Upcoming Presentations

I have two presentations coming up in January. The first one will be on Analysis Services in SQL Server 2005 for the Toronto .NET Users Group. More information on that session is available here.
My second presentation is on SQL Server Integration Services (SSIS). In case you’re wondering, SSIS replaced Data Transformation Services. SSIS is the ETL (Extract, Transform and Load) tool that is used to take data from one place and put it in another. SSIS is so powerful, Microsoft would have no trouble selling it as a separate product. We use it to build data warehouses, stage data mining scenarios, and to clean and consolidate data for analytics. Since this is new product, my talk will cover basic but common scenarios in moving data into SQL Server. The presentation takes place at the Toronto Code Camp. Unfortunately this event is sold out. If your group or organization would like either of these presentations performed at your location, please drop me a line.

Analysis Services Time Dimension

Thanks to everyone who attended the Analysis Services session last week. The event was full (175 had registered) but we managed to get extra chairs for those of you who came in late.

I wanted to follow up on a question asked regarding time dimensions. You will almost always include a time dimension in your cubes. It’s normal for comparisons to be made over time. Same store sales year over year is a good example for the retail world. You would usually use your own time dimension to relate to the fact table. That said, SQL Server will create a time dimension for you if you so choose.

To do this, simply create a new server time dimension. Right mouse click on the dimensions folder to begin the process. You will be prompted with a date range and calendars to create like fiscal, manufacturing, and so on. You will need to generate a schema however so the cube has some data to bind to. Make sure you select the populate data checkbox otherwise your new table won’t be of much value. You will also need a column to bind to in the fact table which will likely be a date value. The schema name of the newly created table is the data source name – something to be aware of when dealing with security.

I hope this address the question about the server time table. I’ll probably use my own generated tables since they will be imported from the host system using SSIS. But you know that this option is always available to you.

Intelligent Mailing

We moved about six months ago to a home closer to the downtown core. Part of the moving process entails notifying credit card companies, licensing agencies and so on that our address has changed. I notified a marine supply store of our change since I participate in their loyalty program. I stood in the store and watched the teller enter the new information into the computer.

But it seems that my address never really changed! I just received the store’s Christmas flyer but it was mailed to the old address and forwarded by the post office. What does this have to do with business intelligence?

The store isn’t taking mailing information from the in house database and it’s costing the company money. Let’s say 5% of the population moves annually and the store chain has 500,000 customers and that each flyer costs $0.25 to mail, excluding the cost of production and printing. The store sends out flyers four times per year. The cost of flyers being thrown away is a potential $25,000. From the store’s perspective, it’s a sunk cost. They were prepared to spend that money anyway. But why spend money needlessly? Oh, that’s in year one. If the database is not cleaned up, the numbers become worse in year two and so on.

How can you turn a loss into a potential profit? There are companies that clean and verify mailing lists. This example alone shows you the value. But why not try something else to get your customers into your store while verifying the mailing list. Offer your customers a discount on their next purchase if they come to the web site and verify their address. This will cost the company money in the short run but they will keep a clean mailing list, and more importantly keep a conversation going with the customer. If a customer does not respond after a certain number of mailings, perhaps you don’t want to mail to that person anymore, regardless if they’re on your list or not.

Spending money on the people who are interested in your product or service is intelligent marketing.

The Apprentice Meets Marketing Measurement

I’m a big fan of the Apprentice. In the show, contestants vie for a job with the Donald Trump organization that pays $250,000 annually. Martha Stewart hosts a similar show but it lacks the edge that only The Donald (does anyone call him that anymore) can provide. The contestants or job candidates are put into two groups and each week the groups must complete a business related task. The team that fails the task must meet Donald Trump in the boardroom where someone from the group is fired and sent home.

While the show portrays itself as a thirteen week job interview, it’s also about marketing. Product sales or promotion are usually an integral part of each task. General Motors, Stetson, Dominos Pizza and Braun have had major representation during the show. One task had the contestants create a display for a new Buick Lucerne. The show ended up being something of an infomercial.

Microsoft participated in one of these events to promote Live Meeting. The software allows people working in different locations to collaborate on the same documents at the same time. I found this article in the Seattle Post Intelligencer worthy of note. It seems that Microsoft was interested in participating in an episode that had the most viewers and they would follow up by analyzing different metrics after the show. Marketing measurement - amazing!

This is news because so few companies understand what business metrics and performance measurement can do for them. Does the marketing effort increase brand awareness? Are we reaching our target audience? What follow up activity is required after this effort? What questions are being asked by people interested in the service? Do people understand what we are trying to offer? In what markets did we see the greatest change in sales as a result of our marketing?

Some of the questions raised require additional follow up marketing efforts but others can be measured immediately as long as the company is set up for it. Measuring marketing efforts is simple common sense. When you buy advertising space, don’t you think that reaching the largest possible audience would be a good thing? That’s a metric. How many people will see this ad? What’s the demographic of people who are viewing this ad?

Marketers typically do not have measurement plans in place. They simply have budgets to work with. Their only real measurement tool is revenue. At the recent CMO Council’s 2005 worldwide Marketing Performance Measurement (MPM) Forum Series the following information was evident.

“One common theme heard throughout: While MPM systems are top of mind with most marketers, many have yet to implement them.”

Microsoft recognizes that marketing performance measurement is extremely important since the long term result is an increased bottom line. Your organization can benefit by tying in measurement of marketing to financial performance to operations and customer satisfaction. The largest software company in the world is doing it and you can to. It does not require a massive cash outlay. The tools are out there and organizations like ours can provide the services necessary to implement the programs.