Data Warehouse – Anatomy of DW/Reporting Deployment

In my last post I went over the management pack synchronization process that brings over MP’s from Service Manager and how those MP’s drive the structure, data, and reports for data warehouse and reporting. Once those MP’s are synchronized between Service Manager and the data warehouse, we need to get the data and/or reports deployed for user consumption.

Sequentially, deployment works in this way (see figure below):

1. Once all identified MP’s are synchronized with DW, MP sync triggers the report deployment workflow
2. Since DWStagingandConfig is the final destination of the MP’s that have been synchronized, the deployment workflow will query the DWStagingandConfig database for any new or changed reports to deploy or any reports to remove.
3. The deployment workflow will then publish any new or updated reports to the SQL Server Reporting Services server via the SSRS webservices.
4. SSRS then stores the reports and appropriate metadata.
5. Schema deployment workflow is triggered by MP sync
6. Once again, information that is driving schema changes is retrieved from the DWStagingandConfig database based off the newly synchronized MP’s that are driving the changes.
7. The schema changes are then deployed to the DWRepository.
8. Any necessary changes to Extract, Transform, and Load modules are made to the DWStagingandConfig database

MP’s that contain only Service Manager specific information will not trigger the deployment activities to execute. They will only be triggered for new DW/Reporting specific elements. In my next post I will dive into what is Extract, Transform, and Load (ETL), its benefits, and why deployment makes changes to it.