SQL View to pull Change Request Report in SCSM 2012, which can be integrated to QlikView (BI reporting tool)

Hello Folks,

Many times we used to think, why cant we have a separate view in SQL (DWDatamart) in SCSM dw db to pull all information on CR and Incident, as we do in reports.

Reports doesn't provide feasibility to integrate with some third party BI tools such as QlikView. But all the BI tools will support input from SQL DB directly.

In order to get wonderful reports from other BI reporting tools, we must create Views in SQL to pull all data.

I have created a View for CR and working on Incident request view to populate all data in a BI tool.

Below is the view for CR,which will pull all the information as the report does. To avoid duplication, we have added Distinct when selecting the columns. Also we have added on "Assigned to" user "Is Deleted = Null" to avoid getting multiple rows for the view.


CREATE VIEW [dbo].[Qlikview_ChangeRequest] AS

Select distinct changerequest.ID
    , changeRequest.Title
   ,
   changerequest.ChangeRequestDimKey,
   changerequest.CreatedDate,
   changerequest.ScheduledStartDate,
   changerequest.ScheduledEndDate,
   changerequest.ActualStartDate,
   changerequest.ActualEndDate,
     ISNULL(AssignedToUserDim.DisplayName, AssignedToUserDim.UserName) AS AssignedToUser,
    ISNULL(CreatedByUserDim.DisplayName, CreatedbyuserDim.UserName) AS CreatedByUser,
        PriorityEnumDS.DisplayName AS 'Priority',
ImpactEnumDS.DisplayName AS 'Impact',
ISNULL(StatusEnumDS.DisplayName, StatusEnum.ChangeStatusValue) AS Status,
AreaEnumDS.DisplayName AS 'Area',
ISNULL(CategoryEnumDs.DisplayName, CategoryEnum.ChangeCategoryValue) AS Category

    
from    ChangeRequestDimvw changerequest 
Left Outer Join    WorkItemDimvw workitem on changerequest.EntityDimKey = workitem.EntityDimKey 

Left Outer Join WorkItemAssignedToUserFactvw WIATU ON
Workitem.WorkItemDimKey = WIATU.WorkItemDimKey AND WIATU.DeletedDate IS NULL
Left Outer Join    UserDimvw AS AssignedToUserDim ON 
WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey 

Left Outer Join    WorkItemCreatedByUserFactvw WICBU ON
Workitem.WorkItemDimKey = WICBU.WorkItemDimKey
Left Outer Join    UserDimvw AS CreatedByUserDim ON 
WICBU.WorkItemCreatedByUser_UserDimKey = CreatedByUserDim.UserDimKey 


Left Outer Join    WorkItemAboutConfigItemFactvw AS WIACI ON
        WIACI.WorkItemDimKey = WorkItem.WorkItemDimKey

Left Outer Join ChangePriorityvw AS PriorityEnum ON 
PriorityEnum.ChangePriorityId = Changerequest.Priority_ChangePriorityId 
Left Outer Join    DisplayStringDimvw AS PriorityEnumDS ON
PriorityEnum.EnumTypeId = PriorityEnumDS.BaseManagedEntityId AND PriorityEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeStatusvw AS StatusEnum ON 
StatusEnum.ChangeStatusId = Changerequest.Status_ChangeStatusId
Left Outer Join DisplayStringDimvw AS StatusEnumDS ON
StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId AND StatusEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeAreavw AS AreaEnum ON 
AreaEnum.ChangeAreaId = Changerequest.Area_ChangeAreaId 
Left Outer Join DisplayStringDimvw AS AreaEnumDS ON
AreaEnum.EnumTypeId = AreaEnumDS.BaseManagedEntityId AND AreaEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeImpactvw AS ImpactEnum ON 
ImpactEnum.ChangeImpactId = Changerequest.Impact_ChangeImpactId
Left Outer Join DisplayStringDimvw AS ImpactEnumDS ON
ImpactEnum.EnumTypeId = ImpactEnumDS.BaseManagedEntityId AND ImpactEnumDS.LanguageCode = 'ENU'

Left Outer Join ChangeCategoryvw AS CategoryEnum ON 
CategoryEnum.ChangeCategoryId = ChangeRequest.Category_ChangeCategoryId 

Left Outer Join DisplayStringDimvw AS CategoryEnumDS ON
CategoryEnum.EnumTypeId = CategoryEnumDS.BaseManagedEntityId AND CategoryEnumDS.LanguageCode = 'ENU'



GO


Thanks,
Dinesh