none
What is DQS_STAGING_DATA for ?

    Question

  • Quick newbie design question.

    What's the best uses for the DQS_STAGING_DATA and why would you bother using this database as opposed to any other staging database that was part of your BI solution ?

    Best Regards,

    Bob

    Monday, October 07, 2013 3:57 PM

Answers

  • Even if you don't use DQS_STAGING_DATA database, DQS process still need it.

    DQS matching results are exported in two phases: first, the matching results are exported to the temporary tables in the DQS_STAGING_DATA database, and then moved to the table in your destination database.

    So, you can use custom staging database, but then you'll have 2 staging databases at the same time, which can be confusing sometimes.

    Also, if you use custom staging database, you can have security issues if you forget to grant your user access to both databases simultaneously.

    • Marked as answer by Bob DuffyMVP Wednesday, October 09, 2013 7:07 PM
    Wednesday, October 09, 2013 5:29 PM

All replies

  • Even if you don't use DQS_STAGING_DATA database, DQS process still need it.

    DQS matching results are exported in two phases: first, the matching results are exported to the temporary tables in the DQS_STAGING_DATA database, and then moved to the table in your destination database.

    So, you can use custom staging database, but then you'll have 2 staging databases at the same time, which can be confusing sometimes.

    Also, if you use custom staging database, you can have security issues if you forget to grant your user access to both databases simultaneously.

    • Marked as answer by Bob DuffyMVP Wednesday, October 09, 2013 7:07 PM
    Wednesday, October 09, 2013 5:29 PM
  • Bob, you are absolutely correct!

    Another reason is that the SQL Server database that contains your source data that you want to cleanse or match must be in the same SQL Server instance as Data Quality Server, otherwise, your database will not appear in the drop-down list in the mapping stage during the knowledge discovery, data cleansing, or data matching processes in DQS.

    Similarly, while exporting the cleansed or matched data to a SQL Server database on the Export page in a data quality project, the destination database must be in the same SQL Server instance as Data Quality Server, otherwise, your database will not appear in the drop-down list.

    To save you the trouble of first having to copy your database to the same SQL Server instance as Data Quality Server, and then grant permissions on it to the required users. DQS provides you with the staging database to quickly get started.

    Thanks
    Vivek
    (SQL Server Documentation | Twitter: @vivek_msft)


    NOTE: Please remember to appropriately vote a post as "helpful" or mark as "answer" to help the community.



    Thursday, October 10, 2013 2:45 PM