Published
Feb 16 2019 03:29 AM
465
Views
First published on TECHNET on Apr 28, 2016
Service Manager customers store their long term data in Service Manager Data Warehouse and create cubes over them for generating reports and analysing the data. Trend analysis and operating on data queried for different time periods is often required, but the cubes offered by Service Manager 2012 R2 Data Warehouse (DW) lack date dimensions for many common date type properties of Incidents, Problems, Change requests, Service requests, Activities etc which is not providing the SM users the time-based levels of granularity for analysis and reporting.
The inbox Data Warehouse cubes in System Center Service Manager 2016 Technical Preview 5 (SM) now have date dimensions for common date properties, which can be sliced based on years, quarters, months, days etc. for rich reporting and trend analysis.
You can watch a quick demo about the new date dimensions in Service Manager cubes in the following video. For more details, continue reading the blog.
Service Manager 2016 TP5 offers to either upgrade from SM 2012 R2 or do a fresh installation, to have the following date dimensions in the SM Data Warehouse cubes:
SystemCenterWorkItemsCube
SystemCenterChangeAndActivityManagementCube
SystemCenterServiceCatalogCube
Please note that cubes will start reflecting data in these new dimensions after successful MP sync and cube processing.
The customers upgrading from an existing SM 2012 R2 with any of the following date dimension solutions to SM 2016, will be able to see both the old and new date dimensions in their Data Warehouse cubes:
This solution can also be extended for creating date dimensions for date fields in the custom cubes by following these steps:
1. In the MP, defining the cube definition, add the named Calculations for the required field like mentioned below
<NamedCalculation ID="Incident_CreatedDate__DateKey" Target="IncidentDW!IncidentDim" ColumnType="Int">
<Calculation>isNull(CONVERT(nvarchar(8), CreatedDate, 112),'20000101')</Calculation>
</NamedCalculation>
Please note that NamedCalculation ID should have string "__DateKey" in the end, and this field in the DW should not be NULL or 0.
2. Seal the MP and import it into SCSM.
3. Run the MPSyncJob on the Data Warehouse and wait until the MP is marked as ‘Completed’.
4. Process all the cubes, or wait for automatically processing during the night.
5. Now the Cubes will have new date dimensions.
You can read about all System Center 2016 TP5 features from here and can download Service Manager bits from https://aka.ms/getSM2016
Please feel free to use the comments section below for any queries or to share your feedback. We look forward to hearing from you..
Service Manager customers store their long term data in Service Manager Data Warehouse and create cubes over them for generating reports and analysing the data. Trend analysis and operating on data queried for different time periods is often required, but the cubes offered by Service Manager 2012 R2 Data Warehouse (DW) lack date dimensions for many common date type properties of Incidents, Problems, Change requests, Service requests, Activities etc which is not providing the SM users the time-based levels of granularity for analysis and reporting.
The inbox Data Warehouse cubes in System Center Service Manager 2016 Technical Preview 5 (SM) now have date dimensions for common date properties, which can be sliced based on years, quarters, months, days etc. for rich reporting and trend analysis.
You can watch a quick demo about the new date dimensions in Service Manager cubes in the following video. For more details, continue reading the blog.
Service Manager 2016 TP5 offers to either upgrade from SM 2012 R2 or do a fresh installation, to have the following date dimensions in the SM Data Warehouse cubes:
SystemCenterWorkItemsCube
- Incident_CreatedDate
- Incident_ResolvedDate
- Incident_ClosedDate
- Problem_CreatedDate
- Problem_ResolvedDate
- Problem_ClosedDate
SystemCenterChangeAndActivityManagementCube
- ChangeRequest_CreatedDate
- ChangeRequest_ScheduledStartDate
- ChangeRequest_ScheduledEndDate
- ChangeRequest_ActualStartDate
- ChangeRequest_ActualEndDate
- ChangeRequest_ActivityCreatedDate
- ChangeRequest_ActivityScheduledStartDate
- ChangeRequest_ActivityScheduledEndDate
- ChangeRequest_ActivityActualStartDate
- ChangeRequest_ActivityActualEndDate
SystemCenterServiceCatalogCube
- ServiceRequest_CreatedDate
- ServiceRequest_CompletedDate
- ServiceRequest_ClosedDate
- ServiceRequest_ScheduledStartDate
- ServiceRequest_ScheduledEndDate
- ServiceRequest_ActualStartDate
- ServiceRequest_ActualEndDate
- ServiceRequest_ActivityCreatedDate
- ServiceRequest_ActivityScheduledStartDate
- ServiceRequest_ActivityScheduledEndDate
- ServiceRequest_ActivityActualStartDate
- ServiceRequest_ActivityActualEndDate
- ServiceRequest_ReviewActivityCreatedDate
- ServiceRequest_ReviewActivityScheduledStartDate
- ServiceRequest_ReviewActivityScheduledEndDate
- ServiceRequest_ReviewActivityActualStartDate
- ServiceRequest_ReviewActivityActualEndDate
Please note that cubes will start reflecting data in these new dimensions after successful MP sync and cube processing.
The customers upgrading from an existing SM 2012 R2 with any of the following date dimension solutions to SM 2016, will be able to see both the old and new date dimensions in their Data Warehouse cubes:
- Date dimensions for Incidents, Service Requests, Change Requests and Activities: http://blogs.technet.com/b/thomase/archive/2013/09/11/date-dimensions-for-incidents-service-re...
- How to Slice Incidents by Days, Months, Quarters: https://blogs.technet.microsoft.com/servicemanager/2012/12/07/how-to-slice-incidents-by-days-m...
This solution can also be extended for creating date dimensions for date fields in the custom cubes by following these steps:
1. In the MP, defining the cube definition, add the named Calculations for the required field like mentioned below
<NamedCalculation ID="Incident_CreatedDate__DateKey" Target="IncidentDW!IncidentDim" ColumnType="Int">
<Calculation>isNull(CONVERT(nvarchar(8), CreatedDate, 112),'20000101')</Calculation>
</NamedCalculation>
Please note that NamedCalculation ID should have string "__DateKey" in the end, and this field in the DW should not be NULL or 0.
2. Seal the MP and import it into SCSM.
3. Run the MPSyncJob on the Data Warehouse and wait until the MP is marked as ‘Completed’.
4. Process all the cubes, or wait for automatically processing during the night.
5. Now the Cubes will have new date dimensions.
You can read about all System Center 2016 TP5 features from here and can download Service Manager bits from https://aka.ms/getSM2016
Please feel free to use the comments section below for any queries or to share your feedback. We look forward to hearing from you..