October 26, 2011 at 8:15 am
Hi,
Our application is used by 3 clients and all data we are storing in SQL server 2008 R2 (cluster server) at backend.
As part of reports , we are writing sql scripts and pulling data and providing reports in excel to clients.
Now we are planning for datawarehousing project for our internal operational dataware house.
we'd like to get a periodic data extract from the db server so that we can begin building our internal reports on enrollment, usage, status, etc. and correlate that with things like customer care data etc.
Just wondering where and how to start? Any help is appreciated.
Thanks!
October 26, 2011 at 8:32 am
I have a stored procedure that moves all of the data from the application tables into a data warehouse table. This is a nightly job, but you can set it for as often as you wish. Only move new data. Make sure you have the proper keys set up in this table such as a time key (TimeKey BIGINT) and for your purposes, I would guess a client key.
Jared
Jared
CE - Microsoft
October 26, 2011 at 9:17 am
Have you thought about using SSIS? Its the tool that is really designed for moving data around, particularly into a data warehouse. There is a steep learning curve if you haven't used it before but there are a number of good books out there on it. I really liked "Knight's 24 Hour Training Microsoft SQL Server 2008 Integration Servers".
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 28, 2011 at 9:17 pm
Thanks Kennath!
question: we have some 300+ tables in production server. lets say we identified 100 tables for data extract. do I need to add 100 oledb source's and 100 oledb destination's? if I want to add transformations, do i need to go each table dataflow and add/change? because I could not see adding more than 1 table in dataflow.
also do we need to have same kind of servers(source and destination)?
Our production server: SQLcluster active/passive
windows 2008 R2
sql server 2008 R2
Basically what are the things we need to take care to start on creating dataware house?
Thank you.
October 31, 2011 at 10:31 am
Well I should probably say I'm not an expert in SSIS so there may be an easier way than this, but I would start out with the Import/Export wizard. It will create the 100's of sources & destinations for you. When you get to the end tell it to save the package and not run it. Then open it in BIDS and start editing anything that needs changing.
Personally I prefer to have 1 source & 1 destination per data flow but you can certainly put more. In fact if I remember correctly the wizard will put them all in 1 data flow.
If it helps you can copy and paste most objects in a package, including connection managers, data flows, sources and destinations.
One of several ways to get to the wizard is to right click on a database name in object explorer, go to tasks, then at the bottom of the menu there are entries importing and exporting.
As far as importing/exporting to the same type of servers, SSIS is designed to move data between completely different environments (or the same for that matter). You can go from SQL to a flat file, a flat file to Oracle, SQL to SQL, DB2 to SQL etc. You will be amazed at the possibilities. For these purposes clustering has no affect whatsoever to the best of my knowledge.
Last but not least I belive there is a seperate form for SSIS questions. You might consider asking there if there is something better than the import/export wizard for creating the 100's of data flows that you are needing. I would guess you will get the best SSIS specific help there.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 31, 2011 at 10:40 am
The wizard will put 5 source & destinations in to 1 DFT, but from what I know, you can only do 1 table between a source and a destination, should you need to copy 100 tables you need 100 source's and 100 destination's
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply