Home› SQL Server

How to access archived service requests

Casey_AverillCasey_Averill Customer IT Monkey ✭
in SQL Server

I am new to Cireson Portal and trying to learn as much as I can on the fly. Had a user asking why they could no longer see some tickets. I determined we are configured to purge closed tickets after 365 days. According to the setting "After a work item has been removed from the database, it is available only for offline reporting from the Service Manager data warehouse". Now I should add I am certainly no SQL expert either but can mange to get around. I am also using PowerBI. The problem is I can't figure out where this data is stored and how to retrieve. Any pointers would be greatly appreciated.


Regards,

Claude

Comments

  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    This thread should be under the sql server category

    Now then, all archived SCSM data ends up in the DWDataMart for a default retention period of 3 years which can be manually altered.  It is the DWDataMart that you should query from as that is the place the SCSM Reports and the SCSM Cubes grab their data from.

    There is a schema available for the SCSM Data Warehouse

    I have a few optimization tips regarding the DW. 


    You should be aware that the Data Warehouse does not archive ticket comments by default and must be manually added, personally this is the xml I used for my Management Pack and then Sealed using FastSeal.exe

    My.WorkItem.CommentLog.DataWarehouse.xml
    <ManagementPack ContentReadable="true" SchemaVersion="2.0" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Manifest>
    <Identity>
      <ID>My.WorkItem.CommentLog.DataWarehouse</ID>
      <Version>1.0.0.0</Version>
    </Identity>
    <Name>My.WorkItem.CommentLog.DataWarehouse</Name>
    <References>
      <Reference Alias="DWBase">
        <ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="IncidentDW">
        <ID>ServiceManager.IncidentManagement.Library.Datawarehouse</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="ProblemDW">
        <ID>ServiceManager.ProblemManagement.Library.Datawarehouse</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="ServiceRequestDW">
        <ID>ServiceManager.ServiceRequest.Library.Datawarehouse</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="ChangeRequestDW">
        <ID>ServiceManager.ChangeManagement.Library.Datawarehouse</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="WorkItem">
        <ID>System.WorkItem.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="CoreIncident">
        <ID>System.WorkItem.Incident.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="CoreProblem">
        <ID>System.WorkItem.Problem.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="CoreServiceRequest">
        <ID>System.WorkItem.ServiceRequest.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
      <Reference Alias="CoreChangeRequest">
        <ID>System.WorkItem.ChangeRequest.Library</ID>
        <Version>7.5.3079.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
      </Reference>
    </References>
  </Manifest>
  <Warehouse>
    <Dimensions>
      <Dimension ID="ActionLogDim" Accessibility="Public" InferredDimension="true" Target="WorkItem!System.WorkItem.ActionLog" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false" />
      <Dimension ID="TroubleTicketActionLogDim" Accessibility="Public" InferredDimension="true" Target="WorkItem!System.WorkItem.TroubleTicket.ActionLog" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false" />
      <Dimension ID="CommentLogDim" Accessibility="Public" InferredDimension="true" Target="WorkItem!System.WorkItem.CommentLog" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false" />
	  <Dimension ID="AnalystCommentLogDim" Accessibility="Public" InferredDimension="true" Target="WorkItem!System.WorkItem.TroubleTicket.AnalystCommentLog" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false" />
      <Dimension ID="UserCommentLogDim" Accessibility="Public" InferredDimension="true" Target="WorkItem!System.WorkItem.TroubleTicket.UserCommentLog" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false" />
    </Dimensions>
    <Facts>
      <!--Incident Comments to bind to our Datawarehouse-->
      <RelationshipFact ID="IncidentRelatesToTroubleTicketActionLogFact" Accessibility="Public" Domain="DWBase!Domain.IncidentManagement" TimeGrain="Hourly" SourceType="CoreIncident!System.WorkItem.Incident" SourceDimension="IncidentDW!IncidentDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasActionLog" TargetDimension="TroubleTicketActionLogDim" />
      </RelationshipFact>
      <RelationshipFact ID="IncidentRelatesToAnalystCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.IncidentManagement" TimeGrain="Hourly" SourceType="CoreIncident!System.WorkItem.Incident" SourceDimension="IncidentDW!IncidentDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasAnalystComment" TargetDimension="AnalystCommentLogDim" />
      </RelationshipFact>
      <RelationshipFact ID="IncidentRelatesToUserCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.IncidentManagement" TimeGrain="Hourly" SourceType="CoreIncident!System.WorkItem.Incident" SourceDimension="IncidentDW!IncidentDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasUserComment" TargetDimension="UserCommentLogDim" />
      </RelationshipFact>
      <!--Problem Comments to bind to our Datawarehouse-->
      <RelationshipFact ID="ProblemRelatesToTroubleTicketActionLogFact" Accessibility="Public" Domain="DWBase!Domain.ProblemManagement" TimeGrain="Hourly" SourceType="CoreProblem!System.WorkItem.Problem" SourceDimension="ProblemDW!ProblemDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasActionLog" TargetDimension="TroubleTicketActionLogDim" />
      </RelationshipFact>
      <RelationshipFact ID="ProblemRelatesToAnalystCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.ProblemManagement" TimeGrain="Hourly" SourceType="CoreProblem!System.WorkItem.Problem" SourceDimension="ProblemDW!ProblemDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasAnalystComment" TargetDimension="AnalystCommentLogDim" />
      </RelationshipFact>
      <!--Service Request Comments to bind to our Datawarehouse-->
      <RelationshipFact ID="ServiceRequestRelatesToActionLogFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Hourly" SourceType="CoreServiceRequest!System.WorkItem.ServiceRequest" SourceDimension="ServiceRequestDW!ServiceRequestDim">
        <Relationships RelationshipType="WorkItem!System.WorkItemHasActionLog" TargetDimension="ActionLogDim" />
      </RelationshipFact>
      <RelationshipFact ID="ServiceRequestRelatesToCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Hourly" SourceType="CoreServiceRequest!System.WorkItem.ServiceRequest" SourceDimension="ServiceRequestDW!ServiceRequestDim">
        <Relationships RelationshipType="WorkItem!System.WorkItemHasCommentLog" TargetDimension="CommentLogDim" />
      </RelationshipFact>
      <!--Change Request Comments to bind to our Datawarehouse-->
      <RelationshipFact ID="ChangeRequestRelatesToCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Hourly" SourceType="CoreChangeRequest!System.WorkItem.ChangeRequest" SourceDimension="ChangeRequestDW!ChangeRequestDim">
        <Relationships RelationshipType="WorkItem!System.WorkItemHasCommentLog" TargetDimension="CommentLogDim" />
      </RelationshipFact>
	  <!--Additional Comment Relationships to bind to our Datawarehouse (Portal could use End User Comment for Problems)-->
	  <RelationshipFact ID="ProblemRelatesToUserCommentLogFact" Accessibility="Public" Domain="DWBase!Domain.ProblemManagement" TimeGrain="Hourly" SourceType="CoreProblem!System.WorkItem.Problem" SourceDimension="ProblemDW!ProblemDim">
        <Relationships RelationshipType="WorkItem!System.WorkItem.TroubleTicketHasUserComment" TargetDimension="UserCommentLogDim" />
      </RelationshipFact>
    </Facts>
  </Warehouse>
  <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
      <DisplayStrings>
        <DisplayString ElementID="My.WorkItem.CommentLog.DataWarehouse">
          <Name>My WorkItem CommentLog DataWarehouse</Name>
          <Description>Stores our comment log dimensions and facts on incidents, problems, service requests, and change requests (Each links to Analyst and End User Comments).  Same format as System Center Data Warehouse Base Library [Microsoft.SystemCenter.Datawarehouse.Base]</Description>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
  </LanguagePacks>
</ManagementPack><br>


    Good Luck!
  • Conner_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    Also this should help if you eventually want to make your own DW reports:
    SCSM Simple Report Queries - SQL File for learning how to query the DWDataMart database to author reports
  • Casey_AverillCasey_Averill Customer IT Monkey ✭
    Thanks folks, that definitely points me in the right direction!
Sign In or Register to comment.