Business Intelligence

Microsoft SQL Server MVP

2010-02-06T05:14:00.000-08:00

I've been renewed as a Microsoft SQL Server MVP for 2010!

Techdays Halifax

2009-10-27T05:30:00.000-07:00

I will be presenting at the next Techdays in Halifax on Monday, November 2. The topic of discussion will be "Using Microsoft Dashboards, Scorecards, and Analytics to Monitor the Health of your IT Infrastructure". For more information go to http://www.techdays.ca/.

I will be talking about an application built around SSIS, SSAS and then integrated and delivered via Sharepoint. There's a lot of content in this presentation and there are lots of demos.

Techdays Toronto

2009-09-19T05:10:00.000-07:00

I will be presenting at the next Techdays in Toronto on Tuesday, September 29th. The topic of discussion will be "Using Microsoft Dashboards, Scorecards, and Analytics to Monitor the Health of your IT Infrastructure". For more information go to http://www.techdays.ca/.

On another note, I have to mention that I am using Windows 7 now. What a great operating system. It has a great UI and it's fast.

SQL Server 2008 Reporting Services

2009-03-31T06:41:00.000-07:00

I will be presenting at the next Toronto Code Camp on Saturday, April 25th. The topic of discussion will be SQL Server 2008 Reporting Services. For more information go to http://www.torontocodecamp.net/.

The Mars Lander

2009-02-27T11:47:00.000-08:00

I recently watched a show on PBS about the Mars Lander and noticed interesting similarities between what that science team does and what business metrics are all about. Business intelligence (BI) is a hot topic these days but I find that few customers really know what BI is about. Many install reporting solutions without any thought to why they’re doing it or what they’ll do with the information once they get it. They also don’t know the difference between information and noise. I’m particularly interested in noise because the internet is full of it and it’s negatively affecting my search experience. More on noise another day, I want to talk about the Mars Lander!

They Had A Goal

That’s a simple but profound statement. The Mars team knew they were in search of a specific thing. They wanted to find out about the possibility of life on Mars. Notice they didn’t want to find life on Mars, although that would have been a nifty thing. They just wanted to know if Mars was able at some time to support life.

What is life? This likely has different meanings depending on who you ask. Let’s keep an open mind and not limit this to humans. In fact living organisms span a broad range of creatures: two legged, finned, winged, air breathing, and many others. There’s a vast taxonomy and classification system that describes life as we know it. But we don’t know anything about life on Mars. What we do know are common traits as to what life needs in order to survive on this plant. Things like water, soil ph levels, temperature ranges and so on. So these are things that the Mars team decided to look for some 228 million kilometers away from Earth.

Have a goal in mind when you venture out on your voyage of discovery.

I have more to talk about concerning the Mars Lander and I hope you’ll visit again to read what I have to say.

SQL MVP Award for 2009

2009-01-05T10:45:00.000-08:00

I've been recognized by Microsoft for my SQL Server technical expertise and contributions to the Business Intelligence community. This means that I am a SQL Server MVP for 2009. I speak frequently on data, data warehousing, obtaining actionable information from databases, key performance indicators, aligning data to corporate strategy and so on. I’m working on a couple of seminar projects at the moment and I hope to see you at one of them.

Tech Days

2008-09-27T07:31:00.000-07:00

Techdays is a mini Tech Ed conference that is touring Canada.

The presentations were taken directly from the huge Tech Ed events for IT Pros and Developers so you know the content is top rate. Tech Ed always sells out so if you couldn’t make that event you might want to look at Techdays. It’s very inexpensive and you get a bunch of software and goodies as part of the registration price.

I will be speaking about SQL Server 2008 and hope to see you there!

More information can be found here: http://www.microsoft.com/canada/techdays/default.aspx

Run As Radio Interview

2008-04-06T06:03:00.000-07:00

I did an interview for the folks at RunAsRadio a few weeks ago. Our discussion revolved around Business Intelligence (BI) and some general guidelines to keep in mind when starting a BI project. The single most important factor is to have a strategy in place for the company. It’s surprising how many companies implement solutions to problems without first of all determining what vision is in place for the company and then creating tactical initiatives to realize that vision. The interview can be found at http://www.runasradio.com/. It’s episode 51 and it will go into the archives after a week so look for the interview there if you don't see it on the front page of the site.

Healthcare KPI Workshop

2008-04-06T06:01:00.000-07:00

I’ve been very busy lately. Currently my efforts concern analytics in healthcare. We’ve been using SQL Server Analysis Services as the engine to drive financial reporting and clinical analytics at several hospitals in the GTA. We're consolidating data from sources like Meditech, Winrecs and others into a consolidated, high availability, reporting environment. In addition to that, I’m building a KPI Workshop (Key Performance Indicator) for healthcare that will teach hospital administrators how to create and use KPIs to manage processes and personnel, improve the customer experience and manage costs. We plan on extending the workshop into other areas like retail and mining as well.

SQL 2008 Product Launch

2008-02-10T14:30:00.000-08:00

Val Matison (that's me) will participate at the SQL 2008 product launch as an expert in the "Ask the Experts Area" at the Toronto Launch on February 27. I focus on Business Intellgince solutions but feel free to ask me anything about SQL Server 2008. I will make sure your question is addressed.

SQL Server SSIS Presentation

2008-01-27T10:01:00.000-08:00

Val Matison will be presenting a session on documenting SQL Server Integration Services ETL packages at the next Toronto Code Camp, March 1, 2008.

Documenting ETL is awkward at best. SSIS supports some task level text elements but that’s the extent of it. How do non-SQL Users view ETL? What can be done within the packages to facilitate documenting the ETL process? This discussion will look at leveraging XML both in SSIS and SQL Server to create documentation that is stored in SQL Server and can be accessed via reporting services.

More information on the session is available here:

http://www.torontocodecamp.net/Sessions/tabid/55/CodecampId/1/SessionId/10/Default.aspx

SQL Server 2008 Presentation

2008-01-26T06:26:00.000-08:00

I will be presenting a session on SQL Server 2008 at the Metro Toronto .Net Users Group on February 7, 2008. This session is an overview of SQL Server 2008 and some of the many enhancements to this already feature rich database engine.
More information is available here:

http://metrotorontoug.com/User+Group+Events/696.aspx

Please register so the event organizers know how many people to expect. Thanks!

SQL Server MVP 2008

2008-01-06T12:36:00.000-08:00

Microsoft has been kind enough to name me as a Microsoft SQL Server MVP for 2008. It's an acknowledgement of my skills and the work that I do in the community to disseminate information about Microsoft SQL Server.

In my work I am most concerned about creating actionable information from existing applications and databases. SQL Server’s rich data engine, ETL toolset and Analysis Services provides a very strong foundation on which to build robust analytics applications. It’s a cost effective and feature rich tool.

I will be presenting at a number of locations in the next few months and will post notices here as to dates and times.

Key Performance Indicators (KPI)

2007-11-08T09:00:00.000-08:00

I was recently asked for my opinions on Key Performance Indicators (KPIs). The entire article can be found here. http://www.microsoft.com/canada/midsizebusiness/theknow/kpi_10-04-2007.mspx
KPIs or critical success factors are measures of an organization's overall health and well being. They measure what is done well and what could be done better. There are two important things to remember when working with KPIs. First of all, it’s important to have a strategy in place before implementing a program. The measures should have an actionable component in them. For example if you are measuring manufacturing defects the actionable component would be quality control. The second thing to consider is to not have hundreds of KPIs for any given process. They become unmanageable and create seemingly contradictory results that basically end up as statistical noise.

Business Intelligence Life Cycle

2007-09-10T09:11:00.000-07:00

I'm presenting a seminar on the lifecycle of a BI application towards the end of September. My goal is to get some senior management out for this session to see what can be accomplished with a BI project and how to go about doing it. Here's the session abstract:

Join Microsoft on September 26, 2007 for the next topic in the FS for TechNet Event Series - Business Intelligence Life Cycle. Companies often fail to focus on an important area when developing a Business Intelligence application – the business. Information technology departments focus on areas like concept, design, implementation, and user interface components. But business users need to look at other areas like assessment, study, insight, knowledge transfer and change. Maximizing performance during an information lifecycle requires an understanding that information is a resource that is a component of any business process. In this session Val Matison, a MVP of Microsoft® SQL Server, will demonstrate different aspects of the business intelligence lifecycle using Microsoft-based Business Intelligence tools in SQL Server™ 2005.

Follow the link to register:

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032350115&Culture=en-CA

What's new as of September 10, 2007

2007-09-10T09:00:00.000-07:00

Hi Everyone. It's been a while since I've posted anything to this blog but I did want to make you aware of what I'm up to. First of all, I'm working on changing the way my consulting practice works in an effort to provide more value add services for clients. I'm now focusing on core support areas for Business Intelligence applications. These include areas like data structures and business rules. The goal is to center on business value obtained from information stored within the corporation. I still use Microsoft SQL Server as my platform of choice (great value) but these services are independent of any particular platform and can be applied to Oracle, DB2, or other heterogeneous environments.

I'll be presenting a seminar on The Business Intelligence Life Cycle in September but will talk about that in a separate entry.

Data Mining SQL Server 2005

2007-03-19T18:34:00.000-07:00

Val Matison (that’s me) will be presenting a session on data mining, specifically clustering, in SQL Server 2005 at the Toronto Code Camp on Saturday March 31. Registration is free but space is limited. More information is available here: http://www.torontocodecamp.net

SQL Server MVP

2007-01-03T05:57:00.000-08:00

Val Matison has been recognized as a SQL Server MVP for 2007!

SSIS Presentation Online

2006-11-12T07:40:00.000-08:00

I had fun at my latest presentation about using SQL Server Integration Services (SSIS) to about 100 people at Microsoft Canada. We covered the basics including sources and destinations, some transforms and of course the overall architecture of SSIS.

This is a brand new product that happens to ship with a great database engine called SQL Server. I make this comment because it’s important to determine at design time where actions are taking place. Sometimes, it’s better to perform operations within SSIS and other times you should take advantage of the database engine to perform an operation. You can handle both from within SSIS.

Microsoft has recorded the session and posted it online here:

SSIS Presentation

2006-09-20T07:03:00.000-07:00

Val Matison will be presenting a session on SSIS (SQL Server Integration Services) for Microsoft Technet on October 24th at Microsoft Canada. SSIS is the ETL (Extract Transform Load) tool that ships with SQL Server 2005. It's feature rich, highly customizable and built for enterprise data migration. You can sign up for the session here:

http://www.microsoft.com/canada/events/event_details.aspx?event_id=1032310086

Please sign up quickly as these sessions often sell out (it's free).

Customer Lifetime Value

2006-03-31T10:48:00.000-08:00

Customer Lifetime Value measures a customer’s total profit contribution to an organization. In its simplest form it’s measured by using this simple formula:

(Revenue - Cost) * Retention.

Retention is the length of time the customer frequents the business. There are more complex ways of looking at CLTV including the addition of a discount rate to determine the present value of future cash flows.

Customers don’t stick around for ever although most companies would like them to. Some businesses don’t keep their current customers for much longer than a few years due to aging. Clothing stores that sell to the teenage market will typically see customers for a few years and then leave as tastes and lifestyles change with age. Customers who don’t stick around are the fuel for churn analysis models. Anyone in a service business needs to understand why customers leave in order to maximize profits.

The CLTV metric can be created from different areas of the business independently and then analyzed overall to understand the profit contribution from each area. For example, some areas might not be as profitable as others but they might be a key component in bringing in new customers to the business. Cross selling into this area is likely not going to add to the bottom line but customers who are part of this business unit would be ideal candidates for cross selling opportunities into other areas of the business.

In addition to CLTV you might want to consider creating a Customer Lifetime Market Basket (CLTMB). Companies that want to reduce inventories might look at certain products as being unprofitable and decide to remove them from the regular inventory. By determining what makes up a customer’s basket, a company might decide that certain inventory items are required for customer retention.

Julian Dates

2006-03-15T09:57:00.000-08:00

I need to work with Julian dates since they will facilitate some MDX queries I am creating for customer lifetime value. Unfortunately SQL Server does not support Julian dates but they’re easy to create so that’s what today’s discussion is all about. I’ll be using the dates more than once so creating a user defined function is probably a good idea.

User defined functions consist of several parts. Data types of parameters and return values must be specified and you have to name the function so you can call it. For more information, check out books on line. You’re probably wondering why the functions start with January 1, 1753. This is where SQL Server starts with date arithmetic so we can’t go back any further than that. The Julian value of that date is 2361331 so that’s where that number comes from. These functions simply add or subtract integer values from that baseline. I normally don’t hard code values into programs but the baseline for Julian dates is constant so it will never change.

Here are the two functions. Just type them into the SQL Query editor and run them. They will automatically be created and are visible in the Programmability/Functions/Scalar Functions folder of the database you created them in.

CREATE FUNCTION udf_datetojul (@any_dte datetime)
RETURNS INTEGER
AS
BEGIN
RETURN DATEDIFF(D,'1/1/1753',@any_dte)+2361331
END

CREATE FUNCTION udf_jultodate (@julian_dte INT)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(D,@julian_dte-2361331,'01/01/1753')
END

Here’s the call to convert the system date to a Julian value:

SELECT dbo.udf_datetojul(getdate())

If you want more information on Julian dates, here’s a great reference: http://aa.usno.navy.mil/data/docs/JulianDate.html

Does Your Data Support The Information Your Business Needs?

2006-03-15T09:00:00.000-08:00

We are currently working on a demonstration for a retail merchandising application. We begin with an architectural drawing of a retail environment. An overhead view of the entire store is used and we also look at each display unit from a head on perspective; the same way a shopper would view the display. We map all of our business metrics into the retail environment. This includes sales, supply chain, product and employee performance metrics. Our tools can display cross selling opportunities, product associations, and the effects of both internal and external marketing efforts. We can also show the same shelf and how it changes over time, profit contributions, metrics by employee or customer type or store and so on.

Here’s the problem we’re faced with. We wanted to map raw data into a mock up from an existing retail database but the appropriate data simply isn’t there to support the information metrics we need to run the strategic and tactical side of the business. The operational part of the OLTP database is fine and you wouldn’t think there is anything wrong with it. That’s one of the problems with databases and commercial applications.

An off the shelf application rarely encompasses the strategic vision of the company that uses it.

We wanted to create metrics for product categories and sub categories like gross revenues for the men’s wear department and then men’s shirts. But all of the raw data is focused on the performance of the sales rep. We can create basic metrics but we can’t create key performance indicators against the products, only against the employees. Each one of them has a goal to achieve but there’s nothing for the elements within the store. We also wanted to establish metrics for customer lifetime value and the data support for that was also sorely lacking. There are more examples but you get the idea. This retail database simply does not have sufficient raw data of the type needed to perform anything more than simple analysis.

What do you do if you’re faced with this problem? You have to go out and get a new retail application, right? This is what companies often do and they pay big money for shiny new apps because they feel that new applications must capture the information they need to run the company. Unfortunately new applications often don’t address the problem you are trying to solve.

Determine what your business information requirements are before you go out and get the latest software app. You probably already capture most of the data already in your current applications. Our retail example had 80% of what we needed. If something is missing, consider having a small app written to capture the missing pieces. It will likely be cheaper to implement since you get exactly what you’re missing and you won’t have to change everything else you already do. Create your business intelligence reporting from a centralized database so that users always see the same view of the data. This is how business intelligence solutions work anyway – centralized reporting of consolidated data.

Graphical Information

2006-02-27T17:47:00.000-08:00

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 http://www.infoinfoinfo.com/graphicalinfo.htmWe’ll be putting up more images up to show you some of the many unique things we’re doing to display information.

Modeling Standard Costs

2006-02-16T08:41:00.000-08:00

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.

More Blogging

2006-02-12T06:27:00.000-08:00

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

2006-02-12T06:20:00.000-08:00

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.

SQL Server Integration Services - Toronto Code Camp

2006-01-18T06:56:00.000-08:00

I wanted to thank the organizers and sponsors for the first (of many I hope) Toronto Code Camp. There was great content, with tracks on .NET development, ASP.NET, Data/Security and Future Technologies. Several hundred attendees registered and the event was sold out. Early feedback shows that people were very happy with the event as it provided information from some of the best technical minds in the country for a great price – free! There were eleven MVPs in attendance and a couple of regional directors so there were plenty of resources available.

I presented on common transformations in SQL Server Integration Services (SSIS). We started off with simple stuff but quickly moved to more complex issues like normalizing a poor data structure. It’s common to see data structures with repeating values like sales01, sales02…sales0n in a table. The repeating values problem is the cause of programming hacks when it comes to table inserts, deletes and reporting. They are very common and no one knows about them until application modifications have to be made or the data must be used for analytical applications. The Unpivot transform in SSIS makes this it very easy to move the data to the proper structure.

We also talked about importing data like address lists into a table. The problem here is to remove duplicates or to find near matches. Let’s say you have just purchased a mailing list and you want to see how many of the addresses in the purchased list match those in the list you already own. But names might not be spelled exactly the same as the names in your list. The name “John Smith” might be “John Smyth” or “Jonathon Smith” in your list. The fuzzy lookup transform allows you to perform a lookup and then decide whether or not to accept the new row based on a numeric confidence level. You decide how tolerant you want to be and the transform then allows you to keep only the rows that meet a certain threshold.

Customer information is often entered into independent systems for different reasons like accounting, service, sales, and technical support. When you need to join a query across all these systems you often end up with may more customers than you actually have! This one transform is great if you want to consolidate the same information form multiple databases into a single place.

GM Ignores The Market Place

2006-01-18T06:53:00.000-08:00

It’s auto show season again and the major players are not doing very well, particularly GM and Ford. I find GM the more perplexing of the two since it has decided to ignore what’s going on in the world.

GM has decided to invest more money into SUVs. Yes, that’s right. Those are the vehicles that require a fuel based umbilical cord. Has anyone at GM noticed the price of gas lately? North America is finally feeling what Europe has known for a long time. Gas is expensive and the price is not going down.

In a recent edition of the Toronto Globe and Mail, there was an article about how consumers are now reconsidering vehicles like SUVs for more fuel efficient options. In that same paper, GM says it’s going out on a limb to be the only player in the SUV space even though other manufacturers are working on more fuel efficient offerings. GM said that fuel efficiency was dramatically improved on newer SUVs but admitted that it was only relative. The SUVS still require large amounts of fuel.

GM wants to be a player and own a particular market segment. The reason that no one is staying in that space is because there is less demand than before and that fuel prices will only go up in the future. That market segment is shrinking.

The only way they can survive this logic is they build an SUV that gets 40 miles to the gallon. Otherwise, GM you’re toast. Business metrics says give the consumer what they want and you will do well. Don’t and you might not be around another day. Someone at GM will do well on their performance measures in a year or two down the road. We’re number one in SUV sales! Looks great, right? Not if you take into account that you only sold five vehicles and that tooling and other infrastructure costs were in the billions!

When I originally wrote this GM announced that they are lowering prices. The article states that GM sales dropped by 5% despite the employee discount plans offered last year. Lowering prices for products that people don’t want will only hurt the company even more.

GM, go back to your sales and production history and determine what products sold well. Determine the features that consumers wanted. Do some more market research and determine what consumers will likely want in the next few years. Build a platform that allows you to tool up quickly based on customer wants. You have the platform. You displayed it at the car show several years ago but I don’t know if it ever made it into the marketplace.

If you are an investor, my suggestion would be to run, don’t walk away from this company. Or, short the stock. GM is not using business metrics combined with accumulated knowledge to make sound business decisions.

SQL Server 2005 Documentation

2005-12-21T13:38:00.000-08:00

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

2005-12-20T05:39:00.000-08:00

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

2005-12-12T08:21:00.000-08:00

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

2005-12-10T13:27:00.000-08:00

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

2005-12-09T06:35:00.000-08:00

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.

Bad Meat

2005-11-25T06:43:00.000-08:00

We bought some bad meat the other day. We returned the meat to the butcher and the manager promptly, and without hesitation, replaced it and doubled our original order at no charge to us. This is one of the reasons we go back to the same butcher, time and time again. We’ve moved recently and this butcher is no longer in our neighborhood yet we still travel quite some way to get to his shop. We go there because he provides good quality at fair prices and excellent service. We will always shop there as long as he maintains the current levels of quality, price and service.

What does this have to do with business intelligence? Service is one of the components in a customer satisfaction scorecard. Keeping track of how customers perceive your organization is important particularly when something goes wrong. Notice how I’m not berating the butcher for providing us with a faulty product. First of all the butcher has historically helped us with selections and good service during our visits. Secondly, sometimes a product breaks or malfunctions or in this case, tastes bad. If a business handles a problem quickly and responsibly, the problem will go away quickly and the customer will know that if an issue does arise that he can expect a quick and fair resolution.

What else can the shop owner learn from the bad meat? Why was it bad in the first place which looks at quality control metrics. Perhaps it was the wrong meat for the job? This was actually the issue for us. The meat wasn’t bad really but it wasn’t the right cut for our intended use. This leads to employee training. Did the person at the counter understand the customer and the intended use of the product? Will employees be trained to ask more specific questions as to what the customer wants? Will employees be trained to handle these types of customer returns?

Or will the entire incident be forgotten?

It’s up to each business to decide whether they want to learn from the daily transactions that make up a business day. Do you want loyal customers? If so then you need to continually train employees, manage product quality and understand your customer. All of these factors will lead to a healthy bottom line.

Analysis Services Presentation

2005-11-25T06:40:00.000-08:00

I will be presenting a session on Analysis Services for SQL Server 2005 on December 7 at Microsoft Canada.

Here’s what I’ll be talking about:

Analysis Services in SQL Server 2005 benefits from rich data model support, an enhanced development environment and strong management tools. We’ll begin our session by working with a cube, with emphasis on dimensions and attribute hierarchies. We’ll then discuss the Unified Dimensional Model, data source views and the rich meta data that is used to develop multidimensional models. We’ll also look at some of the built in modeling support provided via business intelligence wizards. Finally we’ll discuss some of the new management capabilities available within SQL Server Management Studio. The user will leave the session with an introduction to the components that make up SQL Server Analysis Services 2005.

You can register here. I’d love to see you.

GM – What Were You Thinking?

2005-11-22T08:18:00.000-08:00

General Motors announced yesterday that they are laying off 30,000 employees as part of a measure to curtail costs due to GM’s abysmal sales record. GM is hurting. Hurting bad. They lost billions of dollars last year. The company might not even be salvageable.

Dealerships have to be hurting as well. Some Buick dealerships were reporting sales of only four units per month. Basic economics says if your prices are already rock bottom and you are selling only four units per month then something has to break.

Why is this happening all of a sudden? Why does GM have to shut down so much in one gargantuan swoop? Don’t they measure things? Didn’t they realize a long time ago that cars simply weren’t selling?

Sales is the most basic and yet most important of all the metrics! It’s the king – ruler of all metrics – the penultimate – the metric that all others aspire to be. What did we sell this quarter compared to last quarter? What are our strongest product lines? What were the weakest lines? What kinds of products are people buying? Are we cross selling services or other products? Do we have any dogs (no point in making them if we’re not selling them)? Who are our strongest partners? What parts of the country are selling the best? Who’s buying our products? I could go on and on and on.

GM might have known something was up when they started to introduce employee pricing. Sales were slow so they had to do something. That employee discount was a dumb idea right from the outset. How are you going to recover from cheap prices? It’s easy to lower prices – everybody likes that. But once you lower them, it’s very hard to raise them back up again. Competing on price is one of the least effective ways of making a sale or even staying in business. You might gain some short term market share but if I purchased a GM vehicle on an employee discount, you can bet I would expect that discount forever.

Setting a price for a product or service is clearly important and non trivial. The company may want to maximize profits or market share. But selling on price alone is one dimensional and likely does not address the real concerns of why cars aren’t selling. Did GM look at demographics? What about features like gas mileage, warranties or customization? Scorecarding of buyer behaviors could be used to ascertain likes and dislikes. This information could then be sent out to production so changes can take p[lace quickly. Selling more of what people don’t really want will just create a lot of cheap used GM cars in a few years time.

I wonder if GM’s suppliers were measuring orders for parts and services? Such a big customer can make or break a supplier. If GM continued to produce cars regardless of sales, then there’s a really big problem with the way they manufacture vehicles.

How can GM recover? Let me ponder on that and talk about it another day.

SQL Server 2005 Product Launch

2005-11-21T04:57:00.000-08:00

SQL Server 2005 and Visual Studio 2005 launched on November 8 here in Toronto. A lot of people showed up for the event – I’m guessing about 3,000. The day long session was comprised of a SQL Server track and a Visual Studio track but began with a lackluster opening keynote by the president of Microsoft Canada, David Hemler and Craig Symonds, VP of the Developer Tools Division at Microsoft, Redmond.

The keynote was not what I would call inspiring. I guess I’ve been to so many of these that perhaps I’ve become immune to the usual message that this release of (insert product name here) is the greatest ever. Faster than a speeding bullet, able to leap tall buildings in a single bound, allows you to turn back time, cholesterol free and calorie reduced. I’m not sure why they even talked about BizTalk 2006 since this product is not even shipping and won’t be until 2006.

Ok, I’m getting a bit sarcastic since I really like both SQL Server 2005 and Visual Studio 2005, particularly since we use them to build and support our services. They provide us with a rich, deep toolset that allows us to tailor cost effective business intelligence services for our clients. That said, the demos that were presented at the keynote were mundane at best and did not showcase the products at all. The keynote is meant to be a short overview and the details should be presented in break outs but I wanted at least some wow factor. It should be easy because the new releases offer so much for the developer.

I had some involvement with the SQL Server 2005 sessions. I was part of a small group of individuals asked to contribute feedback and suggest content for the sessions. We talked about presentation, content of course, and what was important to show given the limited time available. In fact that was a big part of our discussions – what to exclude. There is so much material to talk about that when we received the early drafts of the sessions we thought each topic consisted of several days of presentation! Bias alert on! I think Barnaby and Damir did a great job of presenting so much material in just three short sessions. Bias alert off!

I was also in the Ask the Experts Cabana, answering questions on SQL Server 2005. I got some standard questions like how much faster is the new release. This is a “it depends” type of answer as to what the database operations are. You have to make sure that we are comparing apples to apples. For example, some operations in SSIS (the ETL tool that ships with SQL Server 2005) are extremely fast. That said, it would be difficult to compare to the SQL 2000 version directly since many operations can be combined into a SQL 2005 package that simply were not possible in the prior release. In a SQL database you are rarely performing just one operation so comparisons without detailed specifics are not simple. Overall the new release is a lot faster but a factor of x times faster simply can’t be supplied.

I have to address one semi sarcastic comment I made earlier about SQL Server 2005 turning back time. This was not all that facetious. The MDX query language for Analysis Services allows us to go into the database history to retrieve anything we want for a given time. This means that numbers can be compared over time, related to events that may have affected the outcome those numbers. This also means that forecast models can be created based on history and tested and then used moving forward. Data mining algorithms can also be used to help users with future events. I’ll talk about forecasting another day.

I was also asked about technology investing and whether Cognos is a good stock to buy. Some folks in the equities world see this release of SQL Server 2005 as digging into Cognos’ share of the business intelligence market. You could ask the same about database vendor Oracle. Both companies got to be where they are in the market place because they make good products. New product releases do not instantly change the face of the market.

I think businesses don’t necessarily want more software. They want more answers. They want something that will make them money, be more competitive or create market share. When innovative companies take these new software tools and provide a cost effective service, then yes, I think competitors share prices will be affected. But it won't happen overnight. Of course, if I could foresee the future with 100% accuracy then I’m not sure I would be telling you or anyone else what I would be investing in.

Back to the product launch! So while I thought the keynote lacked pizzazz, it was still a great event and a lot of people went home with an introduction to some really great new tools.

What's In A Name?

2005-11-19T04:37:00.000-08:00

People have been a bit confused by the term “cube” when it comes to a multidimensional data set. A “cube” allows you to ask questions with multiple dimensions associated with the query. It would go something like “I want to see sales and costs for Arizona, by city, by quarter, by product category, by high profit customers.” Do you notice how there are lots of “by this, by this, by that” type qualifiers? A cube implies three dimensions. There were more than three criteria when retrieving the sales information but we still call the structure a cube. Cubes are highly optimized for these types of queries. Other database structures like the ones you likely use everyday are not optimized for these types of queries. In fact cubes can hold many dimensions with detailed granularity, if that’s what’s required.

So don’t let this “Cube” term mislead you. If it does, perhaps you can think of it as a multidimensional cube or MD Cube.

...Can I Just Install It?

2005-11-18T05:46:00.000-08:00

Today’s post picks up where we left off yesterday.

We met to discuss what was needed but the meeting did not go well. The call center people wanted access to the data – all of it. Their database was quite large and although the client had excellent infrastructure, it was simply impossible to allow the call center managers to access every last piece of information as part of an analysis environment. I wanted to know what they needed to see so the proper cubes could be built or at least a starting point created. No matter how I asked the question, I got the same reply – “We need to see all the data”.

I was frustrated as you can well imagine. I wanted to help the client and they needed assistance but we weren’t getting anywhere. So then I stated the following, “Imagine that you have the entire database at your fingertips. Responses to any questions you might have are instantaneous. What are the first twenty questions you are going to ask?”

The room was silent. There were no replies. No one knew what they were going to ask. No one knew the benefits they would receive by implementing this new solution. No one could tell me the relationships they needed to see.

It can be difficult to quantify return on investment for business intelligence. We’ll address that in another blog. However some guidelines do apply as to what you want it for in the first place. A BI solution whether it contains key performance indicators or data mining or analytics can’t succeed unless you have at least some idea of what you expect to achieve by implementing any or all of these components.

How will the solution improve internal processes?
How will customer service be improved?
How will employees benefit from monitoring performance?
Can the company expect increased revenues, reduced costs or improved cash flow from the business intelligence implementation?
Are there hidden relationships within the data that might exist that will help with any of the prior points?

By using these five guidelines, you can create your own ideas of areas within your organization that could benefit from a business intelligence solution. You can't just add a Business Intelligence solution. It doesn’t make any sense from a practical or technical perspective. A solution must have guidance from the consumers of the information combined with direction from the implementers.

Business Intelligence – Can I just install it?

2005-11-17T06:18:00.000-08:00

Do people really think that they can simply add a Business Intelligence score carding tool to their environment and that business will benefit from it?

Let me tell you a story about an experience I had at a major account. I had been working on an analysis application dealing with financial forecasting. We were able to predict future receivables extending forward three years using an Analysis Services cube I built. One of the benefits of cubes is that query times are very short. This means that algorithms can be built and tested against the cube for things like forecasting. We created several forecasting models and then went into history and forecasted into the present to test the validity of the algorithms. We finally arrived at a good model that made sense and the end result was that the client could forecast receivables with greater accuracy than before. They eventually factored their receivables and were able to lower the interest rate due to the forecasting model, saving them a great deal of money.

The same client had a very large call center and wanted the call center information tied to financial information. This was a great analytics application. There were all kinds of metrics we could build and report on and much to be gained in the way of customer service improvements, employee training benefits, business process improvements and so on.

This story will be continued in my next post…

Initial Post

2005-11-16T19:49:00.000-08:00

Welcome to our business intelligence blog.

This will be a place where we can discuss many things relating to BI. We'll talk about what we're up to of course but we'll also try to relate stories about what we see as opportunties and issues pertaining to BI. We'll talk about technology and the latest and greatest tool sets. We'll discuss challenges that businesses face when dealing with BI. And we'll talk about using BI in the workplace and how it helps businesses deal with everyday issues.

Please feel free to add your comments. We'd love to hear from you.