November 24, 2014 at 1:05 am
How to write Stored Procedures to load the Data Model from OLTP to DWH ? Please Help me with this....
November 24, 2014 at 1:44 am
Take it step by step, first write the SQL statements to do each transform, once you've got it working then wrap it in a stored proc. Easier to debug as ad-hoc SQL statements
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 2:11 am
Hey,
Can you give one example ?
November 24, 2014 at 2:17 am
Really?
Look at the massive amounts of detail that you've given us. What kind of example do you really think I can give you based on a one line question with no detail of any form?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 2:17 am
Based on what?
November 24, 2014 at 2:47 am
Any General Example.....
November 24, 2014 at 3:06 am
Hope someone can provide an example as I have no idea what it is you are trying to accomplish beyond moving data from an OLTP system to a data warehouse.
How this is done is dependent on the structure of both the OLTP and DW systems. With nothing to work from hard to give you any examples, at least for me.
November 24, 2014 at 3:09 am
INSERT INTO <data warehouse table> (<column list>)
SELECT <relevant columns transformed as necessary>
FROM <OLTP tables>
WHERE <whatever conditions are applicable>
Repeat for all DWH tables
Honestly, you may as well have just asked "Please help me write a query." and it would have been just as specific and easy to answer.
Look at your OLTP database, look at your data warehouse, identify the mappings (should be documented), identify what columns determine whether the row should be copied or not, write queries to implement that mapping and filter. Normal, straightforward SQL queries, just like you would write for any other purpose.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 3:47 am
Thanks Gilamonster
November 24, 2014 at 10:18 am
If, for reasons of scaling and performance, you think that your DW database will, or may, reside on a different SQL instance from your OLTP database, you should also consider running the ETL using a tool such as SSIS. This allows you to decouple your databases (so that database A contains no references/dependencies to database B).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2014 at 6:16 am
One of the Kimball Group's books, The Microsoft Datawarehouse Toolkit deals specifically with implementation using the MS stack which will likely have good examples (I haven't read it myself, but based on the original Datawarehouse Toolkit book, I'm sure it would help). Amazon carries it for around $40-50 US.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
November 25, 2014 at 6:56 am
lshanahan (11/25/2014)
One of the Kimball Group's books, The Microsoft Datawarehouse Toolkit deals specifically with implementation using the MS stack which will likely have good examples (I haven't read it myself, but based on the original Datawarehouse Toolkit book, I'm sure it would help). Amazon carries it for around $40-50 US.
There are a couple of very similar named books, and as you mention MS stack, I think you are thinking specifically of this one The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset
Amazon URL (not working) = http://www.amazon.com/gp/product/0471267155/qid=1128605285/sr=2-1/ref=pd_bbs_b_2_1/002-4814565-4213606?s=books&v=glance&n=283155
However there is also a more general ETL one and from memory is DB non-specific, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data
Amazon URL (not working) = http://www.amazon.com/gp/product/0764567578?ie=UTF8&tag=ralphkimballc-20&lin%20kCode=xm2&camp=1789&creativeASIN=0764567578
The book in first URL is 2005 specific, though it has a lot of good techniques you may find that its a little dated in terms of the SSIS processes which have been improved in SQL 2012.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 25, 2014 at 7:06 am
Jason-299789 (11/25/2014)
lshanahan (11/25/2014)
One of the Kimball Group's books, The Microsoft Datawarehouse Toolkit deals specifically with implementation using the MS stack which will likely have good examples (I haven't read it myself, but based on the original Datawarehouse Toolkit book, I'm sure it would help). Amazon carries it for around $40-50 US.There are a couple of very similar named books, and as you mention MS stack, I think you are thinking specifically of this one The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset
Amazon URL (not working) = http://www.amazon.com/gp/product/0471267155/qid=1128605285/sr=2-1/ref=pd_bbs_b_2_1/002-4814565-4213606?s=books&v=glance&n=283155
However there is also a more general ETL one and from memory is DB non-specific, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data
Amazon URL (not working) = http://www.amazon.com/gp/product/0764567578?ie=UTF8&tag=ralphkimballc-20&lin%20kCode=xm2&camp=1789&creativeASIN=0764567578
The book in first URL is 2005 specific, though it has a lot of good techniques you may find that its a little dated in terms of the SSIS processes which have been improved in SQL 2012.
Yup, that's what I was thinking of.
Point being, of course, there are any number of excellent resources with examples that would do to answer the OP much better than the limited format of a forum.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
November 25, 2014 at 7:34 am
Very true, and there are so many different ways to do ETL, into a data warehouse, and a lot depends on things like whether your dimension is a Type 1 and Type 2 dimension or one of the hybrid types, how are Fact table loads (contra/correction or snapshot) designed.
Hopefully those books will point him in the right direction, but there are so many out there, Id also recommend Building a Data Warehouse: With Examples in SQL Server by Vincent Rainardi which is a solid beginners guide to DW implementation, compared to the Kimball books which can be heavy reading at times.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 27, 2014 at 3:26 am
Don't you just feel for junior team members who are dropped in it from a great height with little or no training in the relevant subject matter, have no senior developers to refer to and have no reference materials. These poor souls are then forced to turn to technical forums like this where English is not their first language.
I know the OP didn't cover himself in glory, but it is clear he doesn't even know how to frame the problem, let alone formulate the solution.
Go easy on him guys..
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply