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
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.
