SQLServerCentral Article

Interview with Chuck Kelley - Part II

,

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?

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.

mailto:rsp05%40pearlknows.com
http://www.pearlknows.com/

Copyright ) 2008 - All

Rights Reserved.

Note: Not to be

reprinted or published without express permission of the author.

     

 

Rate

3.71 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

3.71 (7)

You rated this post out of 5. Change rating