Blog: William McKnight Subscribe to this blog's RSS feed!

William McKnight

Hello and welcome to my blog!

I will periodically be sharing my thoughts and observations on information management here in the blog. I am passionate about the effective creation, management and distribution of information for the benefit of company goals, and I'm thrilled to be a part of my clients' growth plans and connect what the industry provides to those goals. I have played many roles, but the perspective I come from is benefit to the end client. I hope the entries can be of some modest benefit to that goal. Please share your thoughts and input to the topics.

About the author >

William is the president of McKnight Consulting Group, a firm focused on delivering business value and solving business challenges utilizing proven, streamlined approaches in data warehousing, master data management and business intelligence, all with a focus on data quality and scalable architectures. William functions as strategist, information architect and program manager for complex, high-volume, full life-cycle implementations worldwide. William is a Southwest Entrepreneur of the Year finalist, a frequent best-practices judge, has authored hundreds of articles and white papers, and given hundreds of international keynotes and public seminars. His team's implementations from both IT and consultant positions have won Best Practices awards. He is a former IT Vice President of a Fortune company, a former software engineer, and holds an MBA. William is author of the book 90 Days to Success in Consulting. Contact William at

Editor's Note: More articles and resources are available in William's BeyeNETWORK Expert Channel. Be sure to visit today!

June 2007 Archives

This article from TechWorld is titled “UK business intelligence projects not hitting target” and starts with “A huge 87 percent of business intelligence projects are failing to meet their original objectives, and nearly a quarter are going over budget, according to new research.”


Now, I’m not one to overhype business intelligence success, which I think most of what I read actually does. However, let’s dissect this using the facts of the study.

“The survey of 68 senior IT decision makers in the UK, conducted by the National Computing Centre for data management vendor Sybase, revealed serious disappointment in business intelligence projects, after 85 percent of those interviewed had implemented the technology in order to improve management decision making.“

OK, the only fact there is that 85 percent of those interviewed had implemented BI. The “serious disappointment” is developed in the rest of the story. Or is it?

“Nearly a third of the IT managers were disappointed that the business intelligence operations themselves were slow and laborious, and over a fifth found the data failed to reveal important information.”

So, 2/3 were not disappointed in one potential negative of BI – that is is slow and laborious. And almost 80% found the data revealed important information.

“Only just over half, 54 percent, said end user satisfaction levels were as expected or better.”

You could replace “only just over half” with “More than half”. It’s also difficult to apply one satisfaction metric to an entire body of users. This could be taken to mean that 46% of implementations have just one user for which BI is not meeting expectations.

“Nevertheless, two thirds of interviewees reported that business performance was improved by the technology and 56 percent saw more accurate data. And 54 percent of respondents were confident enough in the technology to extend it to other parts of the business.”


I don’t see where the 87% in the opening comes from because that does not jive with the statistics mentioned. Anyway, attention grabbing headlines like this need to be balanced so that's why this blog entry is so titled.

Technorati tags: Data Warehouse, Business Intelligence

Posted June 26, 2007 1:05 PM
Permalink | No Comments |

Here is some code I’ve been dragging around in my toolbox for many years. Some data warehouses populated their date dimension through only 2007 or 2008 because that seemed to be a long time from when the data warehouse project began. Well, now it’s time to add on a few more years of data to the date dimension. However, with the auto-population capabilities with current releases of database management systems for date dimensions, manually populating the date dimension has become a lost art. So, whether it’s useful or a stroll into the past, this code populates Year, Quarter, Month, Week, Day and Date dimensions.

The table names are as follows:

Year – Year_D
Year_Key - SK
The_Year - Int

Quarter – Quarter_D
Quarter_Key - SK
The_Quarter - Int

Month – Month_D
Month_Key - SK
Quarter_Key - Int [Ref: Quarter_D.Quarter_key]
The_Month - Int

Week – Week_D
Week_Key - SK
The_Week - Int

Day – Day_D
Day_Key - SK
The_Day - Int

Date – Date_D
Date_Key - SK
Year_Key - Int [Ref: Year_D.Year_Key]
Month_Key - Int [Ref: Month_D.Month_Key]
Week_Key - Int [Ref: Week_D.Week_Key]
Day_Key - Int [Ref: Day_D.Day_Key]
The_Date - Date


create procedure Load_Date_Dimension_Sp @year_start int, @year_end int
declare @yr int
set @yr = year_start
while (@yr <= year_end)
insert into year_d (the_year) values (@yr)
set @yr = @yr + 1

declare @qt int
set @qt = 1
while (@qt <= 4)
insert into quarter_d (the_quarter) values (@qt)
set @qt = @qt + 1

declare @mo int
set @mo = 1
while (@mo <= 12)
insert into month_d (the_month) values (@mo)
set @mo = @mo + 1

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=1)
where the_month in (1,2,3)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=2)
where the_month in (4,5,6)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=3)
where the_month in (7,8,9)

update month_d
set quarter_key = (select quarter_key from quarter_d
where the_quarter=4)
where the_month in (10,11,12)

declare @wk int
set @wk = 1
while (@wk <= 52)
insert into week_d (the_week) values (@wk)
set @wk = @wk + 1

declare @dt1 int
set @dt1 = 1
while (@dt1 <= 31)
insert into day_d (the_day) values (@dt1)
set @dt1 = @dt1 + 1

declare @dt datetime
declare @dt_end = ‘12/31/’ + @year_end
declare @day_k int
declare @week_k int
declare @month_k int
declare @year_k int

set @dt = '01/01/’ + @year_start
while (@dt <= @dt_end)
select @year_k=year_key from year_d where the_year = year(@dt)
select @month_k=month_key from month_d where the_month = month(@dt)
select @day_k=day_key from day_d where the_day = day(@dt)
select @week_k=week_key from week_d where the_week = datepart(wk,@dt)
insert into date_d (the_date, day_key, week_key, month_key, year_key) values (@dt, @day_k, @week_k, @month_k, @year_k)
set @dt = @dt + 1


The Procedure can be executed as:

Exec Load_Date_Dimension_Sp 1985, 2030

…which will populate the date dimension for the years 1985 through 2030.

All the syntaxes are based on SQL Server TSQL.

Technorati tags: Data warehouse, Dimensional Model, Date Dimension

Posted June 15, 2007 8:51 AM
Permalink | No Comments |

Montana, Idaho, Wyoming, Utah, North Dakota, South Dakota, Kansas, Arkansas, West Virginia, Vermont, New Hampshire, Maine, Alaska, Hawaii

Posted June 12, 2007 6:49 AM
Permalink | No Comments |

Link to article. I guess my entry from May 24 was timlier than I thought. This really legitimizes master data management as a force. Stratature had not made great strides, but it does have a nice complement of the requisite MDM functionality that I discuss in my fullday MDM course including the hub, publish/subscribe, and modeling facilitation. Although Stratature hasn't made short-lists in my recent MDM strategies with Fortune clients, I expect its presence to increase now.

I expect more midsize companies to now get involved in MDM and, over time, for the price points for enterprise MDM software to settle.

Posted June 9, 2007 11:19 AM
Permalink | No Comments |

$25M was awarded to Informatica last week based on technology infringement in Data Integrator, but the debate between the 2 companies continues over the post-award rhetoric. This article from CRN Australia sums it up.

It remains to be negotiated how much the Data Integrator pathing forward will be affected by this.

Technorati tags: Informatica, Data Integration, Business Objects

Posted June 8, 2007 9:52 AM
Permalink | No Comments |

Search this blog
Categories ›
Archives ›
Recent Entries ›