Reference for doing Data Warehousing in SQL Server

Question

Post reply

Reference for doing Data Warehousing in SQL Server

pietlinden

SSC Guru

Points: 63503
More actions
April 23, 2024 at 5:56 pm

#4414650

Everybody (well almost) knows that if you want to learn the design patterns for building a data warehouse, you read a book like Ralph Kimball's "The Data Warehouse Toolkit". That book gives you a bunch of theory about Slowly Changing Dimensions etc... Is there a book that's sort of a companion to that where they walk you through implementing some of those things in your database? (The ETL book or something?)
Kimball's book is good, but a little practice stuff would be super helpful. (I see there's a 3rd edition... is it that much better than 2nd edition?)
Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 2

If I have some time, I plan to do something about that next year. Stay tuned!

pietlinden SSC Guru Points: 63503 More actions · Answer 3

pietlinden

SSC Guru

Points: 63503

August 5, 2024 at 7:42 pm

#4442587

Nope, still 2024.

Drat!

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Martin,

have you read Joe Caserta's book on ETL (from the Kimball collection)? ISBN: 978-0764567575

I'm just trying to get my head around doing the ETL etc and building proper dimensional models in the data warehouse, and any good pointers on "do it this way, NOT that way". =)

Thanks,

Pieter

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

I haven't and can unfortunately not comment on it, but Joe is well-known and respected for his work with the Kimball Group. It would definitely not hurt to read his book.

In my opinion, you're going to have to be willing to do a lot of reading and a lot of experimentation...or pay for training/consulting if you'd rather fast-track your learning. Most of the people who are experts in this area have spent thousands of hours perfecting those skills.

pietlinden SSC Guru Points: 63503 More actions · Answer 6

thanks Martin. I'll probably just get a used copy. If I don't like it, I'm only out like $10.

pietlinden SSC Guru Points: 63503 More actions · Answer 7

Martin,

I'm trying to work out options. Would you use something like a system-versioned table to handle the ingredient slowly changing dimension? Or can I just use a few fairly simple T-SQL queries that are kicked off after all the week's purchase information has been imported and entered? (Basically, after all the new purchase records are in the database). I guess try both and see which works better? I suppose a system-versioned table would be better for the Ingredients, because I can just do something like update the unit price and "last updated" timestamp, and the have the table be like the automatic Slowly Changing Dimension source. The rest is (1) Add new version of all Products containing any ingredients that have changed price, then (2) create new version of Recipe so it points at most recent set of ingredients to determine unit price. (3) maybe update the new Products with the new unit cost.

Is my logic at least right?.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 8

I personally don't like system-versioned tables because they require a datetime type, and you don't control the values that are being inserted. I prefer to do that myself with effective and termination dates that don't have a time component, and I add a "current record" indicator to make it easier to identify the most recent version.

pietlinden SSC Guru Points: 63503 More actions · Answer 9

Okay. The other way I was thinking of doing it was to write a query to find changed prices each week (since he only sells cnce a week) and then use that to find all recipes that need to be updated. Then use CROSS APPLY (TOP 1) to get the most recent purchase price for each ingredient (or get the most recent record id) and then insert that into the RecipeIngredient SCD. (basically walk up the Purchase--Ingredient--Recipe--Product chain.

Is that a sensible way to handle it? It's not perfect, but I'm just trying for good enough. Because I'm trying to graph the overall change in price and the price for each ingredient

So I should be able to do that with just a few fairly simple queries, right?

Martin Schoombee SSCoach Points: 19128 More actions · Answer 10

I think you're crossing lines with another post maybe.

It's impossible to comment whether your approach is a sensible way without the necessary context of what the design looks like and exactly what it is that you're trying to accomplish (with some examples).