MPElementsExtract: The Story and Use Cases (Part II)

Part I can be found here.

I was pretty busy with designing new reports for a well known MP during last weeks (we haven’t released it yet, so I skip the name). I have found a couple of bugs in MPElementsExtract tool and fixed them. So, since bug fix release for MPElementsExtract is published now, it is a high time to speak about simplifying reports development.

Extracting reports

When I started with OpsMgr reports development  I have noted that lot of reporting-related elements are just embedded into MP, they cannot be edited using Authoring Console. Namely these elements are :
Element
Format
Purpose
Editor
Report/Definition
XML
Report definition (.rdl)
BIDS
Report/ParameterBlock
LinkedReport/ParameterBlock
XML
Report parameter block (.rpdl)
Text editor
LinkedReport/Parameters
XML
Report parameter values (linked reports only)
Text editor
DataWarehouseScript/Install
DataWarehouseScript/Uninstall
DataWarehouseScript/Upgrade
Text
Data warehouse sql scripts
SQL Server Management Studio
ReportResource/ResourceData
Binary
(Hex string)
Report resources (images)
Image editor
There are lot of cases which require extracting all that thing from existing MP. You may want to investigate how reporting is implemented in this or that management pack (you may just want to learn how it is done or you may be involved in development). Extracting everything in one click will be the first thing you’ll think of. That’s why I have build MPElementsExtract.
Here is how it works for extraction:
  1. Run MPElementsExtract in “extract” or “putplacemarks” mode (see documentation for further details)
  1. Following folders will be created:
DWScripts (folder) – /ex only
*.sql
DWSubScripts (folder) – /ex or /p
Some folders – depends on names of subscripts
*.sql
LinkedReports (folder) – /ex or /p
*.rpdl
*.parameters
ReportResources (folder) – /ex or /p
*.*
Reports (folder) – /ex or /p
*.rdl
*.rpdl
So, when done, you may open BIDS, create new SSRS project, add extracted elements (*.rdl, *.rpdl and resources) into project, create shared data sources and start playing with extracted stuff. You may use debug reports and use “deploy” feature to test your parameter blocks (don’t forget to fix data sources)
For example, here is what you’ll get for Microsoft.SystemCenter.DataWarehouse.Report.Library after running in “putplacemarks” mode:
  • DWSubScripts
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail_install.sql
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail_uninstall.sql
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertDetail_upgrade.sql
      [skipped – too many lines :)]
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.Performance_install.sql
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.Performance_uninstall.sql
    • Microsoft.SystemCenter.DataWarehouse.Report.Script.Performance_upgrade.sql
  • LinkedReports
    • Microsoft.SystemCenter.DataWarehouse.Report.AvailabilityHealth.parameters
    • Microsoft.SystemCenter.DataWarehouse.Report.AvailabilityHealth.rpdl
  • ReportResources
    • AlertSeverity_CriticalError16x16.png
    • AlertSeverity_Information16x16.png
      [skipped – too many lines :)]
    • reports_icon.png
  • <DIR> Reports
    • Microsoft.SystemCenter.DataWarehouse.Report.Alert.rdl
    • Microsoft.SystemCenter.DataWarehouse.Report.Alert.rpdl
    • Microsoft.SystemCenter.DataWarehouse.Report.AlertDetail.rdl
    • Microsoft.SystemCenter.DataWarehouse.Report.AlertDetail.rpdl
      [skipped – too many lines :)]
    • Microsoft.SystemCenter.DataWarehouse.Report.PerformanceTop.rdl
    • Microsoft.SystemCenter.DataWarehouse.Report.PerformanceTop.rpdl
    • Microsoft.SystemCenter.DataWarehouse.Report.PerformanceTopInstance.rdl
    • Microsoft.SystemCenter.DataWarehouse.Report.PerformanceTopInstance.rpdl

Embedding reporting-related elements

All extracted elements can be injected back into MP after modification. Also, when you create a new report (or linked report) in your MP, you may use a dummy report definition and omit injecting RPDLs (for reports and linked reports) and parameters section (for linked reports) – this will be done for you by MPElementsExtract.
DW scripts injection works like .vbs scripts injection does – just use place marks. Report definitions, parameter blocks and parameters are different because “Definition”, “ParameterBlock” and “Parameters” elements should contain XML, not text. Thus, place marks cannot be used. MPElementsExtract takes XML content from files stored side-by-side with MP and injects it into respective elements (it also can create optional “ParameterBlock” and “Parameters” elements). IDs and file names are used to match report (linked report) in MP and content stored on disk. The same story about “ReportResource” element: it should contain binary data.
Important note: creating new .rdl (or .rpdl or anything else) and putting it into folder will not cause creation of new report or whatever in your MP. MPElementsExtract injects content into existing reports / linked reports / DW scripts / resources. It doesn’t create them.
That’s all I can tell you about embedding reporting-related elements. It just works and saves development efforts.
One good example comes into my mind: one day I have regenerated parameter blocks for 19 linked reports. How do you think, how long it could take to re-inject them manually? Saying nothing about increased risk of human mistake…

Splitting data warehouse scripts into subscripts

Scripts splitting logics was described in details in Part I. Same logics implemented for data warehouse scripts.
As you may know, data warehouse script contains 3 parts: install, uninstall and upgrade. Script structure may vary and will depend on your scripting style, but, basically, you’ll create, alter and drop objects. Pattern for stored procedures may look like this:
Install:
-- ##### MySP_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'MySP')
BEGIN
  EXECUTE ('CREATE PROCEDURE dbo.MySP AS RETURN 1')
END
GO

ALTER PROCEDURE MySP
AS
-- some TSQL code here
GO
GRANT EXECUTE ON dbo.MySP TO OpsMgrReader
GO
Upgrade:
Same as install.
Uninstall
-- ##### MySP_Drop.sql
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'MySP')
BEGIN
  EXECUTE ('DROP PROCEDURE dbo.MySP')
END
GO
Thus, it is a good idea to reuse code pieces. MPElementsExtract allows that – place marks can be used for DW scripts. (see documentation for further details).

P.S.

I have some ideas on adding new features into this tool. However I’m not sure about:
  1. When I’ll have time;
  1. What will be provided by Microsoft with OpsMgr 2012. I can recall some talks at MMS’2010 about reusing code in MPs;
  1. I have discovered all related features and limitations 🙂
Should you have some relevant needs and/or suggestions, please don’t hesitate to drop me a line.

Leave a Comment