How to write Stored Procedures to load the Data Model from OLTP to DWH ? Please Help me with this....

  • Krishh

    Ten Centuries

    Points: 1028

    How to write Stored Procedures to load the Data Model from OLTP to DWH ? Please Help me with this....

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Krishh

    Ten Centuries

    Points: 1028

    Hey,

    Can you give one example ?

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Lynn Pettis

    SSC Guru

    Points: 442359

    Based on what?

  • Krishh

    Ten Centuries

    Points: 1028

    Any General Example.....

  • Lynn Pettis

    SSC Guru

    Points: 442359

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Krishh

    Ten Centuries

    Points: 1028

    Thanks Gilamonster

  • Phil Parkin

    SSC Guru

    Points: 244733

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • shoestringdba

    SSCertifiable

    Points: 6206

    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.

  • Jason-299789

    SSC-Insane

    Points: 21601

    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

  • shoestringdba

    SSCertifiable

    Points: 6206

    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.

  • Jason-299789

    SSC-Insane

    Points: 21601

    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

  • aaron.reese

    SSChampion

    Points: 13415

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply