Monday, February 27, 2006

Graphical Information

I’ve put up a few examples of the type of work we do. We start with a very strong foundation in business process comprehension and then we determine where the data is coming from to support those business processes. After cleaning, consolidation and processing we determine the best ways to display the information we’ve extracted from the data. So we use both OLAP and Data Mining techniques to accomplish this. What we do is very complex but we try to make it look simple.

The demo I put up uses a proportional mapping algorithm and key performance indicators to show metrics scaled by the size of the metric. We’re using SSIS, MDX, SVG, XMLA, XSLT, Javascript and a few other technologies to create actionable information.

I don’t feel information needs to be presented as hundreds of reports. Who has time to look at that stuff? How do you make sense of it all? The graphical images displayed are gateways to more actions. These could be phone calls to support staff, or they could prompt drill out or down depending on the graphic. We drill down to support Microsoft Office and Excel so the analyst can number crunch. Everything is driven by Analysis Services on the back end so query response time is extremely quick, even if the original database size was in the hundreds of gigabytes.

Have a look at some of our examples at’ll be putting up more images up to show you some of the many unique things we’re doing to display information.

Thursday, February 16, 2006

Modeling Standard Costs

In this blog I discuss different ways of hiding changes in costs. Most examples are from the boating world, a place I visit frequently.

I attended the annual Toronto boat show this past January. My wife and I own a lovely sailboat and get out as often as possible. We are planning a Trans Atlantic voyage in the next few years and are slowly accumulating the proper gear needed for such an extended trip.

I track the boat prices from year to year to know what my current boat is approximately worth and what my next dream boat will cost. Of course that dream boat seems to get larger every year. I was tempted by a shiny Tartan 4100 cruiser loaded with all the bells and whistles but that’s another story. What I was mainly interested in was pricing and this year it seems that boat prices have increased by only a minor amount.

Pricing is an important part of any sales process. Too little and you aren’t making any profit and too much and you aren’t making any sales. Either way, the business won’t last long without a proper pricing strategy. One person’s price becomes another person’s cost, particularly if you are buying something for resale. So for the rest of today’s blog, think of what you purchase and how you model those costs every cycle. I’ll use the term cost now instead of price to suggest that we are buying something that we will eventually resell.

While boat costs seem to have increased only slightly, it was difficult to tell where the actual differences were from prior years. After some analysis I discovered the biggest variance was in the decrease of boat show incentives. Customers often buy boats at shows because of deep discounts on typically purchased options. If those items are included in the price of the boat, then you don’t have to pay extra for them. These “options” can sum up to tens of thousands of dollars since there’s nothing cheap on a boat. A boat cost increase is effectively hidden when the dollar amount of “free” items is reduced. So in reality boat costs (remember we’re buying for resale) have gone up, and not stayed the same.

Manufacturers will often create anniversary or special editions of their products. You might be persuaded that you are getting additional features for a limited time only. So buy today, right? I noticed that the anniversary version of our boat had all kinds of options included for a one time special price. Interestingly, these were standard items when we purchased our boat. I wonder if these items are included as standard in next year’s model. If these are indeed options then that becomes a cost increase.

Some times it’s difficult to compare models year to year. Manufacturers will often change the name of the product and you really don’t know if you are getting the same thing as last year or if it’s just in a different package. Bed and mattress companies are notorious for this. It’s impossible to compare the same thing at different stores since the manufacturer creates that name only for that store. SleepKing in one store might be the same as SlumberBunny in another store.

What other factors can affect the cost of something yet remain hidden? How about the price of money? Over the last few years we’ve seen the cost of American boats go down significantly because the Canadian dollar is getting stronger all the time. This year the costs for identical boats were similar to last year yet the dollar has gone up over the same time. This means that the manufacturer’s costs have actually gone up and if currency swings the other way, the net effect will be a big cost increase to purchasers next cycle.

Sailboats require commissioning. This includes putting the boat together and making sure everything works. These costs are far from standard. Some dealers will include freight into this figure, while others will leave it out.

I’ve put together a short checklist of items to include when you create analytics applications that include costing.

1.) Create a standard configuration and compare similar feature sets.
2.) Build a standard configuration cost but allow for some leeway for specialty options that aren’t always available.
3.) Compare values using the same currency. Determine the effect of currency fluctuations if there is a long lead time to delivery.
4.) List typical ancillary costs that are associated with the final delivery.

You can create a much longer list but you see that modeling costs is never about the final cost. There's information hidden in price increases as well and the intelligent business tracks those changes so they know about future changes before they happen.

Sunday, February 12, 2006

More Blogging

I’ve been asked by Microsoft Canada to share some of my thoughts on Business Intelligence on the Canadian IT Managers Blog. I’ll probably talk more about Microsoft related items on that site but to be honest, I’m still thinking about the focus that I want to put on that blog. If you have any ides, you can share them with me of course! I’m currently working on a Data Mining project and that will likely be the source of my next comments.

SQL Server Integration Services Presentation

I will be presenting a session on SQL Server Integration Services on March 26, 2006 at Microsoft Canada for the Toronto SQL Server User Group. The session will start at 6:45 PM and last for about 90 minutes. I’ll be talking about common issues that arise when you try to import data into SQL Server. Some of the issues discussed will include data type transformations, calculations, poor source data architectures, error handling, and near match lookups. A link for registration will be placed here as soon as one is available.