May 13, 2009 at 2:39 pm
I want to try to design a data warehousing system but I don't know what is the best approach to take so I'm looking for some suggestions to point me in the right direction.
What I have is 20 plus databases that are the back end for a point of sale system in remote locations. I want to consolidate all the data in to one central server so that I can create reports on things like sales information, inventory counts etc. All of the databases have the same schema and dimensional data will be the same in each.
For a project like this should I be looking at SSIS? Or is this more of a replication type project?
As I said, I'm just looking for advice to point me in the right direction. Any input is appreciated.
May 13, 2009 at 4:27 pm
SOP on this would be SSIS, though there are some other ways to go.
Replication does not seem like a good choice as what you really need to do is to merge 20 different sources together, which is definitely not Replication's strong suit.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2009 at 2:37 am
Thanks for the reply RBarryYoung. I will take a look at SSIS.
May 18, 2009 at 5:58 am
Look up the following books:
The Data Warehouse Toolkit
and
The Data Warehouse ETL Toolkit
Both by Ralph Kimball
They are critical resources for any DW designer/developer
May 18, 2009 at 10:14 am
Thanks Samuel. I purchased Professional SQL Server 2005 Integration Services by Brian Knight at the weekend and I've gone through the first few chapters today. I will take a look at the books you've suggested there as well.
May 18, 2009 at 11:08 am
My suggestion would be to look at Data Warehouse design before looking at the ETL tools.
There are two different approaches for Data Warehouse design; the original authors are Ralph Kimball and Bill Inmon -plenty of info in the net.
The good news is that both Kimball and Inmon see Data Warehouse as a separate entity from OLTP and any Legacy application you may have around.
Just to state my position please let me say that even when I can see pros and cons on both methodologies I consider myself a Kimball follower in terms of Data Warehouse design.
If you like Kimball's approach I would suggest to attack one datamart at a time; start with an easy one so you can develop your design, ETL and reporting skills and processes as you go.
Just my two cents. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply