The great SQL query "template" from edition 1 of Data Warehouse Toolkit

View previous topic View next topic Go down

The great SQL query "template" from edition 1 of Data Warehouse Toolkit

Post  cube.head on Mon Aug 03, 2009 3:57 pm

Way back in the first edition of the book, there was a great explanation of how adding columns and group-by to a query is like drilling down. In fact, there was a nice example of a "template" SQL query that accompanied the explanation.

I couldn't find this in the 2nd edition book, and I can't even FIND a 1st edition book!

Does anyone have the text of this short explanation, please?

Thanks...

cube.head

Posts : 6
Join date : 2009-08-03

View user profile

Back to top Go down

Re: The great SQL query "template" from edition 1 of Data Warehouse Toolkit

Post  BoxesAndLines on Mon Aug 03, 2009 9:51 pm

Not quite sure what you're referring to, but on pg. 44 version 2 is this:
"Drilling down in a data mart is nothing more than adding row headers from the dimension tables. Drilling up is removing row headers. We can drill down or up on attributes from more than one explicit hierarchy and with attributes that are part of no hierarchy."
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: The great SQL query "template" from edition 1 of Data Warehouse Toolkit

Post  cube.head on Tue Aug 04, 2009 1:37 pm

Thanks.. Yep - I see that in the the 2nd edition. The first edition has an expanded explanation that I want to give to my client here. But the book is at home.. Oh well, I'll grab it on the weekend.

cube.head

Posts : 6
Join date : 2009-08-03

View user profile

Back to top Go down

Re: The great SQL query "template" from edition 1 of Data Warehouse Toolkit

Post  cube.head on Wed Aug 05, 2009 9:01 am

I managed to locate the book on Amazon: http://amzn.com/0471153370 (this is an Amazon "permalink")

And then by clicking on the "Click to look inside!" I was able to browse various parts of the book. One of the ways you can preview while in this mode is a button called "Surprise Me" that takes you to a random sampling of about 4 pages in the book. Well, incredibly, one of my surprises was landing on page 14, and the topic is: "The Standard Template Query".

Here is the query Ralph used to describe the standard template:

SELECT p.brand, sum(f.dollar), sum(f.units)
FROM salesfact f, product p, time t
WHERE f.prductkey = p.productkey
AND f.timekey = t.timekey
AND t.quarter = ’1Q1995’
GROUP BY p.brand
ORDER BY p.brand

I'll rewrite it to conform to today's JOIN standards:

SELECT p.brand, sum(f.dollar), sum(f.units)
FROM salesfact f
INNER JOIN product p ON f.prductkey = p.productkey
INNER JOIN time t ON f.timekey = t.timekey
WHERE t.quarter = ’1Q1995’
GROUP BY p.brand
ORDER BY p.brand

In this query, brand is a "row header". Adding additional attributes to the SELECT and the GROUP BY "drills down".

cube.head

Posts : 6
Join date : 2009-08-03

View user profile

Back to top Go down

Re: The great SQL query "template" from edition 1 of Data Warehouse Toolkit

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum