MS SQL Data Warehousing - Re: Relational to DW.?

Author

Re: Relational to DW.?

Peter Nolan

2005-08-01, 3:23 am

Luis,
interesting question......I assume your 'relational database' is for a
transaction based system?

So, you will not want to much to convert the database as to create a
second database as the DW as it is not advisable to try to run a
transaction system on a DW model.

On my home web site there are lots of papers and presentations
discussing what a DW is and the various modelling techniques that are
commonly employed. The modeling technique you might use depends on what
you want the DW for....if you need to answer any question you ever
might want to ever ask from any data you ever had without havining any
idea about waht questions might be asked you need an archival data
store of some type.....

If you have a reasonable idea of what questions the DW needs to answer
and do not need to store every value of every change in every field
forever you can live without the archival data store and use all
dimensional models.

There are a lot of books/materials available about dimensional models
now....I have pointers to the ones I recommend on my beginners page.

If you finish up going with building a DW database you will need to
answer some of the FAQs I have on my FAQ page....what database? what
ETL tool, what query tool etc. So I suggest you browse thru the FAQ
page as well....

Since you are asking here, MSFT offer SQL Server, Analysis Server, DTS
and Report Services as their main BI offerings. Of course MS Office can
also be used to present data from the DW. These all work pretty well in
their space......well enough that I am actually building a BI product
on top of it all as I think MSFTs BI for the masses message is going to
stimulate demand for the product we are building (we hope!).

There are well documented/defined techniques for transforming
transaction based data to dimensional data. (I've published free code
to do so on my site.)

What is not well documented/published are all the ideas of how to
integrate data from many different places and combine them
effectively...butit sounds like you are not doing that yet....

Ralph Kimballs last book on ETL toolkit is also worth reading...I have
just finished it but have not put the pointer to it on my web site
yet....

Anyway, happy reading....I hope the materials I have published are
helpful.....if you feel there are things missing please let me know. I
am building my site as a place where 'newbies' can go and find lots of
useful information as well as useful links.....my little effort at
putting something back into DWing.. :-)

Best Regards

Peter Nolan
www.peternolan.com

Akinja

2005-08-04, 8:23 pm

This is very interesting timing. I am trying to find more information about
Analysis Services, Data Warehousing, and Data Mining and your website is a
great place to start. I have been interesting in Data Warehousing and
Analysis Services for some time. However, here is my problem (or rather
opportunity).

I need to convince my boss and his bosses that this is viable, needed, and
would benefit us without breaking us (cost wise). We are moving to new
servers and I have convinced them to move away from transactional and read
data on the same server. Now using replication and DTS packages, I am
moving data that is imported to one server that will do the initial process
to another server that our Web services and application will access. Sounds
like a perfect candidate for DW. This is especially true because our data
is now at 300+ GB and will most likely double in the coming year.

So what will DW buy me and my company? How much recoding of applications
will all this require? I have read a lot about DW or AS along with learning
MDX and this seems to be a daunting task. (I just love the smell of a fresh
challenge in the morning:) )

Any help here to where to point my nose next is greatly appreciated.

"Peter Nolan" <peter@peternolan.com> wrote in message
news:1122885468.591195.136090@g44g2000cwa.googlegroups.com...
> Luis,
> interesting question......I assume your 'relational database' is for a
> transaction based system?
>
> So, you will not want to much to convert the database as to create a
> second database as the DW as it is not advisable to try to run a
> transaction system on a DW model.
>
> On my home web site there are lots of papers and presentations
> discussing what a DW is and the various modelling techniques that are
> commonly employed. The modeling technique you might use depends on what
> you want the DW for....if you need to answer any question you ever
> might want to ever ask from any data you ever had without havining any
> idea about waht questions might be asked you need an archival data
> store of some type.....
>
> If you have a reasonable idea of what questions the DW needs to answer
> and do not need to store every value of every change in every field
> forever you can live without the archival data store and use all
> dimensional models.
>
> There are a lot of books/materials available about dimensional models
> now....I have pointers to the ones I recommend on my beginners page.
>
> If you finish up going with building a DW database you will need to
> answer some of the FAQs I have on my FAQ page....what database? what
> ETL tool, what query tool etc. So I suggest you browse thru the FAQ
> page as well....
>
> Since you are asking here, MSFT offer SQL Server, Analysis Server, DTS
> and Report Services as their main BI offerings. Of course MS Office can
> also be used to present data from the DW. These all work pretty well in
> their space......well enough that I am actually building a BI product
> on top of it all as I think MSFTs BI for the masses message is going to
> stimulate demand for the product we are building (we hope!).
>
> There are well documented/defined techniques for transforming
> transaction based data to dimensional data. (I've published free code
> to do so on my site.)
>
> What is not well documented/published are all the ideas of how to
> integrate data from many different places and combine them
> effectively...butit sounds like you are not doing that yet....
>
> Ralph Kimballs last book on ETL toolkit is also worth reading...I have
> just finished it but have not put the pointer to it on my web site
> yet....
>
> Anyway, happy reading....I hope the materials I have published are
> helpful.....if you feel there are things missing please let me know. I
> am building my site as a place where 'newbies' can go and find lots of
> useful information as well as useful links.....my little effort at
> putting something back into DWing.. :-)
>
> Best Regards
>
> Peter Nolan
> www.peternolan.com
>
>

Sponsored Links