SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Interview with Chuck Kelley - Part II

By Robert Pearl,

Last time, we spoke about the industry, how Mr. Kelley himself got started, the fundamental philosophies in building the data warehouse, and an overall look into the life of the data architect.  Let's continue our interview with Chuck Kelley, and have him clarify some DW terminology, answer some common questions, and drill-down on some Microsoft's new tools and features that can aid in the functional operation of the data warehouse.

RP: What is the difference between a data warehouse and a data mart?  Should the terms be used interchangeably?

CK:  A data warehouse is a central store of integrated, non-volatile, time-variant, and subject-oriented data.  It is where all the enterprise data comes together for a central meaning based on the corporate standards and definitions.  Data Marts are generally a subset of the data in a data warehouse that are used to solve a specific business need - like reporting, analysis, Finance, Marketing (promotion, customer relationship), etc.   So they should not be used interchangeably.

When do you use a star schema and when to use a snowflake schema?

DMR: Could you please let me know when to use a star schema and when to use a snowflake schema? What are the major differences between them?

CK: My personal opinion is to use the star by default, but if the product you are using for the business community prefers a snowflake, then I would snowflake it. The major difference between snowflake and star is that a snowflake will have multiple tables for a "dimension" and a start with a single table. For example, your company structure might be: Corporate >> Region >> Department >> Store

In a star schema, you would collapse those into a single "store" dimension. In a snowflake, you would keep them apart with the store connecting to the fact. -- (DMReview November 1, 2007)

DMR: What impact does data privacy legislation have on using and building customer/employee data warehouses? 

CK: It requires organizations to provide more security to specific information about any one customer/employee. However, it really does not change the basis of the data warehouse. You still want to be able to understand you customers/employees.  (June 1, 2007 DM Review Online)

RP: What do you think of Microsoft's new SSIS tools for ETL, and how they measure up with other vendor packages.

CK:  I certainly like them a lot more than the old DTS, but they are still not quite up to par with the leaders.  I look forward to running some 2008 tests.

RP: What are the advantages of using a tool such as SSIS over the traditional way of loading data using (and building) stored procedures?

CK: The same advantages of not writing everything in machine language but using Java, .NET, etc.  Easier to write; debug; and for someone else to pick up for maintenance.  Plus you get better documentation and good metadata if you use SSIS correctly.

RP: With respect to SSIS how would you compare it to DTS of SQL 2000, and how the changes have affected the typical DBA?

CK:  I think DTS was the Subaru and SSIS is Dodge Minivan.  Neither are quite the top performing Porsche, but SSIS is a lot better than DTS.  However, it (SSIS) is less friendly for the DBA than DTS was.

RP: What do you think of Analysis Services in SQL 2005?

[I actually referred Chuck to one excellent article on SSC.com that talks about BI in the enterprise and highlights some of the new features in Microsoft's Analysis Services 2005, entitled, Analysis Services 2005, the Year of BI]

CK:  It [AS 2005] has certainly grown up a lot over the previous version, but other tools are still more full featured.  I certainly think that MSAS as a lot to offer. Your article is good maybe you can put a pointer to it.  [OK, the article in question was authored by me - RP :-)] 

RP: What do you think of Microsoft's positioning of SQL 2005 as an end-to-end business intelligence platform?  Has it lived up to this?

CK:  Most certainly for small to medium size business (in production).  It certainly has the potential for the larger warehouses, but I do not believe it is quite there yet.

RP:  What are some of the advantages of 2005 over 2000, or the tools available in SQL 2005 that aid in the building or loading of the data warehouse?

CK:  I think putting a lot of the new commands added to the database engine as opposed to having them in the user communities' tools.

RP: What are some of the new features/SET commands in SQL 2005 that make querying and building reports easier?

CK:  I think PIVOT, UNPIVOT, EXCEPT, and WITH (common table expressions) are some of the most used features by me.

[For more detailed information on these new SQL 2005 t-sql enhancements and more, please see this SSC article on the New T-SQL Features in SQL Server 2005 Part 1.]

RP: What in your view is/should be the function of the CIO in any data warehouse project?

CK:  To be the Champion of the cause; be in constant communication with other Senior Management on the benefits and where we are and the benefits that we have seen; act as the arbiter for issues between the technologist and the business.

RP: How does one know/realize the need for a data warehouse?

CK:  When you need to understand the organization as a whole and not as separate companies, is a great indicator that you need a data warehouse.  Wanted to be able to change as quickly as the business changes is another indicator.  The best indicator is when upper management decides they need to take consolidation important and sees the data warehouse/business intelligence as the same as email and infrastructure - something that everyone needs.

[On the flip side, whereas the CIO on this project was actually the impetus for building the data warehouse, here is a posting from one reader who realizes the need for a DW, has yet to convince the boss that one is needed]

 How do I convince my reluctant boss to begin work on a data warehouse?

DMR: My boss is reluctant to begin any data warehouse (DW) activity until we have thoroughly planned, staffed and gained total commitment to a DW initiative. My problem is that I can't get the resources to perform any of these activities until my boss gives me the go-ahead. How do I convince her that we need to start doing something?

CK:  It sounds, to me, that your boss is not ready for or does not comprehend what a DW can do for the organization. I would spend some time discussing with her what you want to do and why going through the whole process as you outlined is a mistake. Work to gain credibility on why it should be done a different way. Also, I would go talk to the business community to find a champion/evangelist for the DW and use that person to drive the process. (December 24, 2007 - DM Review Online)

RP: Some of the biggest problems in building a data warehouse occur before even setting out to create the initial design. BI tools only aid in the design of a well mapped out DW strategy, and cannot do the job alone. The most important task is identifying the data warehouse requirements, as well as communication between the business users and the IT community.  Author Janet Wong, who often contributes to SSC.com on the topic of data warehousing, wrote an article entitled 'Problems In Building a Data Warehouse'   In your opinion, does this author aptly describe the common problems in designing a DW?  What are your thoughts on her analysis?

CK: I think it is right on the money!

RP: So, have you enjoyed your stay in NYC?

CK:  Absolutely.  My wife joined me here and we had a blast taking in the tourists' traps, playing on Broadway, and the work was very exciting.

[In conclusion, I'd like to end this final installment of our two-part interview with a parable, or tale, if you will.  It's a great piece of career advice that may help every one of us at some point in our professional life, and I asked Chuck to recount here.]

 RP:  Tell us the tale of the three envelopes?

CK:  As a manager was leaving, his replacement came in.  He told his replacement that he was leaving 3 envelopes in the desk.  You may wish to follow them as things start going sour.  Well, things were going well and then it start to gone downhill, she opens the desk and sees the 3 envelopes.  So, being curious, she opens the first one.  It says, "Blame It on Your Predecessor".  What a great idea.  She blames her predecessor and things start moving up again.  After a bit of time (these are normal business cycles!), a downturn starts.  Quickly she gets the second envelope and opens it.  "Reorganize".  Great idea!  She reorganizes the whole organization and, sure enough, things get better.  Just as the downturn starts again, she quickly runs and gets the last envelope and opens it - "Make Three Envelopes"

 What is surprising to me is how somewhat true this tale seems.

 ----------------------------------------------------------------------------End Interview Part II

Well, that's it!  I hope you enjoyed reading this interview, as much as I did putting it together!

In addition, I hope this provided some insightful and useful information with respect to the world of business intelligence, and the life of the DW architect - which is all ultimately built around the data warehouse. 

I want to personally thank Chuck for his valuable time and agreeing to the interview. It has been truly an honor and pleasure to work with him, and look forward to the opportunity to collaborate with him again on future projects!

If  you a looking for a great resource dedicated for business intelligence, performance management, analytics, integration and enterprise data warehousing as well as emerging areas that include business process management and technology architectures, be sure to book mark  DMReview.com.  You'll find interviews, articles and columns written by the best consultants, hands-on practitioners and technology solution leaders the industry has to offer, including our esteemed interviewee. 

 And don't forget, you can post your DW and other related questions at DM Review online, so don't hesitate to Ask the Experts!


Written by: Robert Pearl, President
Pearl Knowledge Solutions, Inc.


Copyright ) 2008 - All Rights Reserved.

Note: Not to be reprinted or published without express permission of the author.



Total article views: 1206 | Views in the last 30 days: 3
Related Articles

Snowflaking Dimensions

Snowflakes where the flaked dimension is also used in a fact table


Presentation Slides for Building an Effective Data Warehouse Architecture

Thanks to everyone who attended my session “Building an Effective Data Warehouse Architecture” for P...


Problems In Building a Data Warehouse

Building a data warehouse usually isn't a small project, but somehow management sometimes sees it as...


Creating warehouse in SQL 2008..Unified Metadata layer to build report in Cognos

Creating warehouse in SQL 2008..Unified Metadata layer to build report in Cognos


Building Business Intelligence Data Warehouses

New author! Tom publishes his first article with us by writing about how business intelligence and d...