Newbie at Data Modeling

I have always heard the term of "Data Modeling" and I have even built a few reports using a data warehouse. However I have really never been involved in the data warehouse creation process. I just recently took a position as a DBA and we have started talking about creating our own Data Warehouse. I have a feeling that we are going to use dimensional modeling and I understand the big idea, however, when I start reading about it I get very confuse on the terms and how to go about it. Since I'm very new to this concept, where would I start reading and training on data modeling with the goal to create a data warehouse? This is a huge project that at this point is out of my comfort zone and skills. My employer is big in training and very supportive.

Thank you in advance for any guidance.

Hi hespinoza

I think your approach is a good one. The best starting point is probably the "core concepts" page on the KG website:

http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/kimball-core-concepts/

That will probably point you towards the most suitable Kimball books and thereby courses to suit you.

I would suggest that before you go too far, have a go in a "proof of concept" mode at a single fact with a couple of dimensions based on your own data. Pick something simple and unambigous. This will generate some good questions you can ask at training or on this forum.

Good luck!
Mike

Thank you for the links to those documents. That really helped understand better the main concepts of a fact table and its dimensions. Do you have a link to a small tutorial where it shows the process of the data modeling? I understand the request to start looking at our data and picking a small business process, however, if you know of a tutorial that goes from the relational database to breaking down business process, creating the ER for the Datawarehouse and even ETLs would be great. I maybe asking for too much so I apologize for that. We are probably be using Microsoft products and the BI Solution for data transfer.

Thank you again so much for your advice.

Hi hespinoza,

A "small tutorial" that covers end-to-end design, build and test of the complete data warehouse solution on a particular technology stack is probably a fantasy. The reality is that you are looking at a series of rich, complex, interrelated topics which seem impractical to try to skim through.

You'd probably be best served by buying this book (as an ebook if possible) as a roadmap which you can slowly work through, without trying to learn too much in one go:

http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/data-warehouse-books/booksmdwt/

This probably seems a bit daunting but from my perspective the weight of IT & project spending is tilting more and more in this direction every year, and at the same time the pool of organisations wanting these solutions is growing as the technology trickles down - I think it is a good career path.

Good luck!
Mike

I was afraid that I alread new your answer. Somedays I wish I could make my fantasies come true : ) I will definetely talk with my organization and ask them to invest in these resources. They won't have a problem with it. Thanks a bunch for your help and advice. I really like working with databases and data modeling is just an extension. I find the topic very intesting. Eventhough it feels overwhelming I find it very fascinating.

Thanks again for your help.

I have started reading the set of books for the datawarehouse series written by Mr. Kimball. I believe I have a good start and I'm working towards trying to identify our business processes to develop the datawarehouse structure. I know there's a long road ahead of me, however, I'd like to read a little bit about ETLs and what triggers these processes. I guess I'm confused and need direction. Where can I find good articles/books where I can find out more about ETLs and how they work? Mostly, I think I can work through the process of the ETL I have done some DTS before to transfer data but never work in a data warehouse environment where not 100% of the data gets copied across. How does the ETL know which data to copy across to the datawarehouse?

Thank you. I'm trying to take on the elephant one piece at time, the data warehouse is a huge elephant.

There are a host of different ETL tools, all of which are software products that use proprietary mechanisms to build a process. Any specifics in doing so are specific to the particular tool you are using. Any such literature would be for that specific product.

Looking at them generically, all tools perform three basic functions:
E - Extract data from a source (usually the operational database)
T - Transform the data... manipulate values, assign surrogate keys, etc...
L - Load the data into the target database

You can always write code from scratch to do all this, but it is very time consuming. The various tools usually provide a graphic interface and built in capabilities to handle the data without the drudgery of dealing with the technicalities of accessing databases, moving data, and so on.

If you are using SQL Server, it already has that type of functionality built in: SSIS (formerly DTS). If that is where you are at, look for books on SSIS. And, if you still have DTS, upgrade to the latest version of SQL Server... the tools are much better.

Hi hespinoza,

Regarding the "not 100% of the data" point, I take the minimalist approach i.e. each source system attribute is ignored by default, unless it is specifically needed to meet a reporting/analysis requirement. So I start from the reporting/analysis requirements and work backwards to build up the list of required source system attributes.

The obvious risks of this approach are balanced by the flexibility of the Kimball DW design - it is relatively easy to add an attribute to an existing dimension or a measure to an existing fact - as long as you got the grain & keys right first time around.

This approach also supports an Agile methodology where your first sprint builds and delivers to a very tight scope, then you expand that with future iterations to get to your full elephant.

Good luck!
Mike