Scsm DW

Veljko_Sero

Hi, I know this is not the right place to ask this question, but I will give a try. When we built SCSM we extended classes so we created change request support group and manual activity support group. The are in separate MPs and both are sealed. Our reporting group wants to build some reports based on Manual Activity support group. However they cannot find it in DW. It is easy to locate change support group but not MA support groups.  

Adam_Dzyacky

If I'm not mistaken - when it comes to extended classes (and specifically enum lists for Activities) those won't make it over to your DW. You also need an MP that will enable the DW to pull these new concepts over.

I can only assume this has something to do with the Manual Activity class being a child of the parent Activity class

Veljko_Sero

It could be. So is that a new MP available for downloads? I guess I am not the only one who extended Activities.

Adam_Dzyacky

I don't believe so, but I can certainly provide a framework for a Manual Activity Support Group Data Warehouse MP. However, I want to be very clear that I'm prefacing the XML MP code here with... 

• You should absolutely under all circumstances test this in a dev environment with an attached dev DW
• This MP will introduce new tables into your DW based on the names currently defined here. If you want to change the names of the tables, you must edit them here first before you import into SCSM. Again, test in dev!
• Edit lines 15, 16, 17, 29, and 33 where suggested with the "HERE" noun. I've also bolded them below so there is little to no room for error with how much of the word you need to replace
• This MP must be sealed after said edits in order to function

If you are the least bit leerie or unsure about making this change to your production data warehouse, I would advise against it and suggest you reach out to MSFT for support on this instead.

<ManagementPack ContentReadable="true" SchemaVersion="2.0" OriginalSchemaVersion="2.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Manifest>
    <Identity>
      <ID>ActivityMACustomizations.DW.Library</ID>
      <Version>1.0.0.0</Version>
    </Identity>
    <Name>ActivityMACustomizations.DW.Library</Name>
    <References>
      <Reference Alias="DWBase">
        <ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
        <Version>7.5.1561.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="ref1">
        <ID><b>SupportGroupManualActivityMPNAMEHERE</b></ID>
        <Version><b>SupportGroupManualActivityMPVERSIONHERE</b></Version><br>        <PublicKeyToken><b>PUBLICTOKENOfManualActivitySupportGroupMPHERE</b></PublicKeyToken>
      </Reference>
      <Reference Alias="ref2">
        <ID>System.WorkItem.Activity.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
    </References>
  </Manifest>
  <Warehouse>
    <Outriggers>
      <Outrigger ID="ManualActivitySupportGroupCustomization" Accessibility="Public">
        <Attribute ID="ManualActivitySupportGroupCustomization" PropertyPath="$Context/Property[Type='ref1!ClassExtension_<b>SUPPORTGROUPEXTENSIONGUIDHERE</b>']/SupportGroup$" />
      </Outrigger>
    </Outriggers>
    <Dimensions>
      <Dimension ID="ActivityDimMACustomizations" Accessibility="Public" InferredDimension="true" Target="ref1!ClassExtension_<b>SUPPORTGROUPEXTENSIONGUIDHERE</b>" HierarchySupport="IncludeDerivedClassProperties" Reconcile="true" />
    </Dimensions>
  </Warehouse>
  <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
      <DisplayStrings>
        <DisplayString ElementID="ActivityMACustomizations.DW.Library">
          <Name>Manual Activity Support Group DW Outrigger and Dimension</Name>
          <Description>Contains the DW Dimension and Outrigger for the Manual Activity Support Group class extension</Description>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
  </LanguagePacks>
</ManagementPack>

David_Morris1

SCutils have a great free tool to achieve this (https://www.scutils.com/products/dwmpcreator) it allows you to select your custom classes/properties from sealed management packs in your environment and create DW management packs for them, once imported and synced to your DW will create a new sql table and import the data. (be aware that it only brings in data of workitems that have been created/changed since import so you may have to run a script to change a background value you dont use to kick them all through to the warehouse)

Veljko_Sero

Thanks

fe_fe

@David_Morris1
Could you share a little more detail on the script you proposed?
You want me to write a Powershell script to chance (just edit) MA supportgroup on every single MA in the operational DB or in the SCSM DW?
And to edit in the DW, I would have to modify the tables with direct SQL querys, from Powershell, ofc.

David_Morris1

Hi Mate,

once you have created and imported a new DW management pack you SCSM DW should now contain a new table which will hold your support group data against the manual activity entity ID, so you can join the activity table and this new table using this field to pull reports for MAs with the support group field.

its likely the new custom table wont fill with activities that existed before you imported the DW management pack you will have to update the MAs to get there data into the dw, it doesnt matter what field in the MA you update as long as the "Last updated" field moves, this being the case i normally use "alt contact method" as this is not used in MAs, 

$MAClass = get-scsmclass system.workitem.activity.manualactivity

$MAs = get-scsmobject -class $MAClass

foreach($MA in $MAs){set-scsmobject -smobject $MA -property "ContactMethod" -Value 0}

this sample script sets all alt contact method to 0, this will change the last mod date/time on all MAs and when the DW next syncs they should then be processed into the new table

fe_fe

@David_Morris1
Thanks alot, mate.

I know exactly what you mean now!

fe_fe

@Conner_Wood
@David_Morris1

I got the outrigger working, however, I couldn't find the Dim in any cubes, only in the DWDataMart DB.
I would really like if it was a part of the default Service Request Cube or Change-Activity Cube. 
I read Conner's very detailed post about making your own cube, which is awesome.
However I think most of my needs would be met if I could just extend the existing Activity/Change Cube with my new Dim&Outrigger, MA Support Group extension.

Is that possible, or do I need to create a new custom Cube?

Link to Conner's post:
https://social.technet.microsoft.com/Forums/en-US/6aaa5cc2-fc9c-4f6a-bc51-d0a2f70b11f1/custom-fields-in-dw-and-cubes?forum=dwreportingdashboards 

Conner_Wood

@fe_fe

I highly recommend creating your own cube.

 You could do what AddCubeDateSlicers did where they reference existing cubes via [Warehouse > Extensions > CubeExtension] as opposed to creating a new cube via [Warehouse > Extensions > SystemCenterCube] .... However be warned that SCSM tries to limit the amount of Dimensions a Cube can have, despite them violating this constraint themselves with the Microsoft Change Cube.  Not to mention I had to end up running the powershell script when the date dimensions disappeared due to needing to rebuild the cubes (I don't believe I've had this problem since I perfected the SSAS Cubes....)

You can export the AddCubeDateSlicers.mp from SCSM Console to get the xml and verify, btw.

Again, I recommend making a new cube only with what you need and seal it with FastSeal.exe.....

Note that when you import your sealed Cube MP you'll need to wait for it to create the cube and the cube job.  I recommend that you use SSMS to Perfect the SSAS Cubes because cube jobs have caused me countless issues due to SCSM not syncing everything, I have gone onto each cube and right clicked on them and went to Properties.  I then went to Error Configuration:

1. Uncheck checkbox: Use Default Error Configuration
2. Keep Key Error Action as ConvertToUnknown
3. Select option: Ignore errors
4. Set Specific Error conditions to ReportAndContinue
5. Hit Ok to save changes

The reason we do this is because these errors are resolved automatically when the Data Warehouse does sync both the ticket data and the relationship data, so most of these errors only happen due to the flawed syncing which is usually corrected the next time it syncs.

fe_fe

@Conner_Wood
Thanks for the advice.
I recon the rerunning of the AddCubeDateSlicers.mp's Powershell script, sometimes the date dim's also magically disappear, and the script needs to be rerunned, not a major issue, but it's strange, however since it's pretty fast I haven't really tried to analyze the issue in-depth. 

I'll take your advice and create a custom cube.
I've looked a little into the XML code of the MP's you provided in your post on technet.
Doesn't look too bad, but I could use an example on how to include an enum in my custom cube.
Can I just use the outrigger I created?

First question:
Did they solve this bug you mentioned?

Bug causes 100% Data Warehouse MP Corruption

https://connect.microsoft.com/WindowsServer/Feedback/Details/2507482 

Second question:
What is included in the cube - Is all the properties from the classes you reference to included, like I'd have to refence to my custom extension of the ManualActivity class (hence, the MA SuppportGroup enum property). 

Conner_Wood

If you want examples of how to generate Dims and Facts in a cube, you really should consider exporting the Service Manager Configuration Management Cube Library [It contains the WorkItems Cube] from SCSM Console.  When prompted to include resources, select no.  Then find the file and open with NotePad++ or something.  There's others as well like System Center Data Warehouse Base Library where you can look at [Warehouse > Outriggers] to see how they declared Enumerations.

Ex.

<Outrigger ID="ServicePriority" Accessibility="Public">
  <Attribute ID="Priority" PropertyPath="$Context/Property[Type='System!System.Service']/Priority$" />
</Outrigger><br>

That's as far as I go with Cube Knowledge, good luck accomplishing your goals, however intuitive the solutions are!

As for the bug I mentioned, Microsoft didn't comment or release a patch, I suspect no one is even looking at the tickets and that the ticket was closed from being inactive for so long and my uploaded files have not become public when I first created the ticket.  So I'd assess that as NO meaning they did not solve the 100% Data Warehouse MP Corruption Bug.  Makes you think twice about what "Supported Product" really means.

fe_fe

@Conner_Wood
Thanks Conner,
Didn't think of exporting the Cube MP!
It's actually a pretty good idea!

Regarding the bug, well, guess MSFT doesn't really care about SCSM. But we already knew that.