locked
DW design doubts... RRS feed

  • Question

  • Hi experts,

    I have a quick question...

    Someone configured a DW to a Datamart flow; that I am now supporting and it's a real mess... Data is taken from the DW to the DM via sql jobs... that most of them have enormous MERGES... (I am talking about not less than 100 jobs). (Problem is that when there is a huge update in the DW, it cascades to the jobs, and many of them start failing...)

    Thinking about it, having 'replication' set up, or log shipping would have been way easier!, but that the information before going to the Datamart has some logic involved (removing columns, aggregating some data, filtering columns)... I know you can filter in 'replication' but not sure if you can add logic to a 'replication' setup...

    I have 2 questions here:

    1- is this setup normal? do you have any experience with similar environments? or is it that... that person was completely wrong and that should have been set up using replication.

    2- I do have experience with DW! but, I am not an expert... do you recommend any books that will talk/discuss about similar issues form above?

    Thanks in advance!!

    Friday, February 5, 2016 8:53 AM

Answers

  • Hi maca128,

    How do you want to aggregate the data in your tables and what logic do you want to add? Could you please post an example?

    With replication, you are able to create partitions of data to be published to subscriber by using filters. Replication offers four types of filters. You can review more details about replication filters in this article to check if they applies to your scenario.

    •Static row filters, you can choose a subset of rows to be published. All Subscribers to a filtered publication receive the same subset of rows for the filtered table.

    •Column filters, you can choose a subset of columns to be published.

    •Parameterized row filters, you can choose a subset of rows to be published. Parameterized row filters use a data value supplied by the Subscriber to send Subscribers different subsets of rows.

    •Join filters, you can extend a row filter from one published table to another.

    Additionally, for Data Warehousing issues, please post the  question in the SQL Server Data Warehousing forum. It is appropriate and more experts will assist you.



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, February 8, 2016 7:02 AM

All replies

  • Hi maca128,

    How do you want to aggregate the data in your tables and what logic do you want to add? Could you please post an example?

    With replication, you are able to create partitions of data to be published to subscriber by using filters. Replication offers four types of filters. You can review more details about replication filters in this article to check if they applies to your scenario.

    •Static row filters, you can choose a subset of rows to be published. All Subscribers to a filtered publication receive the same subset of rows for the filtered table.

    •Column filters, you can choose a subset of columns to be published.

    •Parameterized row filters, you can choose a subset of rows to be published. Parameterized row filters use a data value supplied by the Subscriber to send Subscribers different subsets of rows.

    •Join filters, you can extend a row filter from one published table to another.

    Additionally, for Data Warehousing issues, please post the  question in the SQL Server Data Warehousing forum. It is appropriate and more experts will assist you.



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, February 8, 2016 7:02 AM
  • Is this a frequent activity? I mean the bulk data changes? Otherwise you could schedule it towards a weekend where you will have more processing window. Also not sure on what your 100 + jobs are doing. Does all these data has to be processed always? If not you could see if you can implement an incremental strategy. 

    When you say DM is it a relational database or is it Analysis Services cube? If its a cube you can even think of applying partitions to the measure groups and make sure you only touch the required partitions each time for processing without touching the full cube always


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 8, 2016 7:19 AM