IF EXISTS
(
SELECT [name]
FROM [sys].[objects]
WHERE
[object_id] = OBJECT_ID(N'[dbo].[FIM_Report_Group_SP_GetHistory]')
AND [type] in (N'P', N'PC')
)
BEGIN
DROP PROCEDURE [dbo].[FIM_Report_Group_SP_GetHistory];
END
GO
BEGIN
--********************************************************
--* *
--* Copyright (C) Microsoft. All rights reserved. *
--* *
--********************************************************
/**********************************************************************************************************************
* http://sharepoint/sites/fim/Shared%20Documents/FIM%202010%20R2%20Technical%20Docs/Reporting/Functional%20Specs/Reporting_Spec_FIM2010_R2_M0.docm
* 9.4.3 REPORT DESIGN
* 9.4.3.2 DEFAULTS
* Report Column Descriptions
* Default Description
* Primary Sort Order Committed Date, Most Recent at top
*
* Secondary Sort Order Attribute Name, A at top
*
* Filter Unfiltered by default
*
* 9.4.3.3 FILTERING CRITERIA
* All columns in the report must be filterable.
*
* Current Group Display Name
* Group Object ID
* Operation Type
* Attribute Name
* Attribute Value
* Committed Date
* Request Object ID
* Requestor Display Name
* Approver Display Names
* Management Policy Rule Names
*
* Extended Data - Requested Additions by Test team
* ExtendedDataRequestorID (Guid as nvarchar(40))
* ExtendedDataApproverIDs (semicolon separated Guids as nvarchar(40))
* ExtendedDataManagementPolicyRuleIDs comma (semicolon separated Guids as nvarchar(40))
*
* 9.6.3.4 REMOVAL OF EXTENDED DATA PARAMETER
* Addition of RequestTargetDetailObjectType (Target), limiting the Group Data to @GroupTypeDimKey
* Forced literals as default datetimes for Start and end for query optimizer else query times out.
*
* 9.6.4.0 Refactor of the query to optimize performance when parameters are and aren't selective using steps
* 1. Translate/Set any necessary inbound parameters to report appropriate values
* 2. Determine if the query is selective by checking existence of Highly selective parameters
* i. Check potentially selective parameters to see if they're selective on the data set.
* 2a. If data is selective
* i. Return the data with one statement that joins to the Concatenated MPRs and Approvers.
* ii. EXIT THE SPROC
* 3. Gather up the set of keys and data (RequestKey, ObjectID, CommittedTime, CreatorID, and first Approver and MPR
* this set is optimized for providing the minimum sufficient keys and values to run the query in the final state.
* It's also indexed (sorted unique PK) on the request to ensure optimal joins (to the subsequent data)
* 4. If the query parameter @showAllApproversAndMPRs=1 then the query will create a new representation of
* Multiple rows by request of Approvers and MPRs concatenating them in one column for display. This
* is performed here as opposed to using the common view so as to ONLY apply the recursive logic for our limited
* list of requests.
* 5. Run the optimized non-selective query (all joins but one are on sorted/ints/that have high selectivity.
* NOTE: This query only runs if the user supplied ONLY search parameters that are non-selective
* (otherwise the code would have followed 2a and exited already). The WHERE clause is split
* between step 3. And 5 and should only contain non-selective ones.
* WARNING: If you change any criteria for Selective vs. non-Selective you must re-examine all the WHERE clauses to ensure that they match your criteria.
*
*
******************************************************************************************************************/
SET NOCOUNT ON;
BEGIN TRY
/*****************************************************************************************************************
*
* 1. Define tables and variables and set defaults
*
*****************************************************************************************************************/
--Get the ObjectTypeDIMKey for GROUP
DECLARE @groupObjectTypeDimKey INT,
@paramsAreSelective AS BIT = 0, -- 0 = Not Selective
@requestCountSelectivityBar INT = 100; -- Request Count threshold to presume Selective
SELECT @groupObjectTypeDimKey =
(
SELECT TOP 1 [otv].[FIMObjectTypeDIMKey]
FROM [dbo].[FIMObjectTypeDimvw] AS [otv]
INNER JOIN [dbo].[FIMGroupDimvw] AS [p] ON
[otv].[FIMObjectID] = [p].[FIMObjectTypeID]
);
IF(@attributeName = N'*')
BEGIN
SELECT @attributeName = NULL;
END
-- Set committed date end to 1 day + input time.
-- This is done because SQL server assumes @committedDateEnd 12:00:00 am
-- and data from the last day are omitted.
SELECT @committedDateEnd = DATEADD(day, 1, @committedDateEnd);
--First check Selective Parameters
IF
(
@currentGroupDisplayName IS NOT NULL
OR @groupObjectID IS NOT NULL
OR @attributeValue IS NOT NULL
OR @requestObjectID IS NOT NULL
OR @requestorDisplayName IS NOT NULL
OR @approverDisplayName IS NOT NULL
)
BEGIN
SELECT @paramsAreSelective = 1;
END
--If we haven't already identified a selective parameter then continue checking Possibly Selective Params
--First check attribute type
IF @paramsAreSelective = 0
BEGIN
IF @attributeName IS NOT NULL
BEGIN
DECLARE @nonSelectiveAttributes TABLE(AttributeName NVARCHAR(488))
--These values are subject to change depending on customer's use of data.
--Adding less selective attributes here may be valuable for some customers
INSERT INTO @NonSelectiveAttributes ([AttributeName]) Values (N'Manually-managed Membership') --ExplicitMember
IF NOT EXISTS
(
SELECT 1 FROM @nonSelectiveAttributes WHERE [AttributeName] = @attributeName
)
BEGIN
SELECT @paramsAreSelective = 1;
END
END
END
--Next Check MPRName
IF @paramsAreSelective = 0
BEGIN
IF @managementPolicyRuleName IS NOT NULL
BEGIN
DECLARE @requestCount AS INT;
SELECT @requestCount = COUNT([req].[FIMRequestDimKey])
FROM [dbo].[FIMManagementPolicyRuleDimvw] AS [mpr]
INNER JOIN [dbo].[FIMRequestHasManagementPolicyFactvw] AS [mprRequestFact]
ON [mpr].[FIMManagementPolicyRuleDimKey] = [mprRequestFact].[FIMRequestHasManagementPolicy_FIMManagementPolicyRuleDimKey]
INNER JOIN [dbo].[FIMRequestDimvw] AS [req]
ON [mprRequestFact].[FIMRequestDimKey] = [req].[FIMRequestDimKey]
INNER JOIN [dbo].[FIMRequestFactvw] AS [reqFact]
ON [req].[FIMRequestDimKey] = [reqFact].[FIMRequestDimKey]
WHERE
[mpr].[FIMDisplayName] = @managementPolicyRuleName
AND [reqFact].[FIMRequestCommittedTime] >= @committedDateStart
AND [reqFact].[FIMRequestCommittedTime] < @committedDateEnd
IF @requestCountSelectivityBar > @requestCount
SELECT @paramsAreSelective = 1;
END
END
/*****************************************************************************************************************
*
* 2a. If the Params are Selective then just run the query
* --Ignore @showAllApproversAndMPRs and joining to the concatenated view of Approvers and MPRS by request
*
*****************************************************************************************************************/
IF @paramsAreSelective > 0
BEGIN
SELECT DISTINCT
[GRP].[FIMDisplayName] AS [Current Group Display Name],
[GRP].[FIMObjectID] AS [Group Object ID],
[RTD].[FIMRequestRequestStatusMode] AS [Operation Type],
[AT].[FIMAttributeTypeName] AS [Attribute Name],
[RTD].[FIMRequestTargetDetailAttributeValue] AS [Attribute Value],
[Rf].[FIMRequestCommittedTime] AS [Committed Date],
[R].[FIMObjectID] AS [Request Object ID],
[RCe].[FIMDisplayName] AS [Requestor Display Name],
[RA].[ARCDISPLAYNAMES] AS [Approver Display Names],
[RMPR].[MPRDISPLAYNAMES] AS [Management Policy Rule Names],
[RCe].[FIMObjectID] AS [ExtendedDataRequestorID],
NULL AS [ExtendedDataApproverIDs],
NULL AS [ExtendedDataManagementPolicyRuleIDs]
FROM [dbo].[FIMGroupDimvw] AS [GRP]
INNER JOIN [dbo].[FIMRequestTargetDetailFactvw] AS [RTD]
ON [GRP].[FIMObjectID] = [RTD].[FIMRequestTargetDetailTarget]
INNER JOIN [dbo].[FIMAttributeTypeDimvw] AS [AT]
ON [RTD].[FIMAttributeTypeDimKey] = [AT].[FIMAttributeTypeDimKey]
INNER JOIN [dbo].[FIMRequestFactvw] AS [Rf]
ON [RTD].[FIMRequestDimKey] = [Rf].[FIMRequestDimKey]
INNER JOIN [dbo].[FIMRequestDimvw] AS [R]
ON [Rf].[FIMRequestDimKey] = [R].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMEntityDimvw] AS [RCe]
ON [Rf].[FIMCreator] = [RCe].[FIMObjectID]
LEFT OUTER JOIN [dbo].[FIMRequestApprovalResponseCreatorvw] AS [approver] -- Match single Approver
ON [Rf].[FIMRequestDimKey] = [approver].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestMprvw] AS [managementPolicyRule] -- Match single MPR
ON [Rf].[FIMRequestDimKey] = [managementPolicyRule].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestApprovalResponseCreatorsvw] AS [RA] -- Get Approvers
ON [Rf].[FIMRequestDimKey] = [RA].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestMprsvw] AS [RMPR] -- Get MPRs
ON [Rf].[FIMRequestDimKey] = [RMPR].[FIMRequestDimKey]
WHERE
[RTD].[FIMObjectTypeDimKey] = @groupObjectTypeDimKey
AND [Rf].[FIMRequestCommittedTime] >= @committedDateStart
AND [Rf].[FIMRequestCommittedTime] < @committedDateEnd
AND
(
@currentGroupDisplayName IS NULL
OR [GRP].[FIMDisplayName] = @currentGroupDisplayName
)
AND
(
@groupObjectID IS NULL
OR [GRP].[FIMObjectID] = @groupObjectID
)
AND
(
@operationType IS NULL
OR [RTD].[FIMRequestRequestStatusMode] = @operationType
)
AND
(
@attributeName IS NULL
OR [AT].[FIMAttributeTypeName] = @attributeName
)
AND
(
@attributeValue IS NULL
OR [RTD].[FIMRequestTargetDetailAttributeValue] = @attributeValue
)
AND
(
@requestObjectID IS NULL
OR [R].[FIMObjectID] = @requestObjectID
)
AND
(
@requestorDisplayName IS NULL
OR [RCe].[FIMDisplayName] = @requestorDisplayName
)
AND
(
@approverDisplayName IS NULL
OR [approver].[FIMApprovalResponseCreatorDisplayName] = @approverDisplayName
)
AND
(
@managementPolicyRuleName IS NULL
OR [managementPolicyRule].[MPRDisplayName] = @managementPolicyRuleName
)
ORDER BY
[Rf].[FIMRequestCommittedTime] DESC,
[AT].[FIMAttributeTypeName];
--We're done we can return
RETURN;
END
/*****************************************************************************************************************
*
* 3. Optimize for execution of a non selective query
*
*****************************************************************************************************************/
--Gather the set of Keys relative to the time window of requests
CREATE TABLE [#REQUESTIDS]
( [FIMRequestDimKey] INT NOT NULL,
[FIMRequestObjectID] NVARCHAR(40) COLLATE DATABASE_DEFAULT,
[FIMRequestCommittedTime] DATETIME,
[FIMCreatorObjectID] NVARCHAR(40) COLLATE DATABASE_DEFAULT,
[FIMRequestMinMPRDimKey] INT,
[FIMRequestMinApprovalDisplayName] NVARCHAR(488) COLLATE DATABASE_DEFAULT SPARSE
CONSTRAINT [PK_REQUESTIDS] PRIMARY KEY CLUSTERED
(
[FIMRequestDimKey] DESC
)
);
INSERT INTO #REQUESTIDS
SELECT DISTINCT
[reqFact].[FIMRequestDimKey],
[reqDim].[FIMObjectID],
[reqFact].[FIMRequestCommittedTime],
[reqFact].[FIMCreator],
MIN([requestMPR].[FIMRequestHasManagementPolicy_FIMManagementPolicyRuleDimKey]),
MIN([approverEntity].[FIMDisplayName])
FROM [dbo].[FIMRequestFactvw] AS [reqFact]
INNER JOIN [dbo].[FIMRequestDimvw] AS [reqDim]
ON [reqFact].[FIMRequestDimKey] = [reqDim].[FIMRequestDimKey]
INNER JOIN [dbo].[FIMRequestTargetDetailFactvw] AS [rtdFact]
ON [reqFact].[FIMRequestDimKey] = [rtdFact].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMRequestHasManagementPolicyFactvw] AS [requestMPR]
ON [reqDim].[FIMRequestDimKey] = [requestMPR].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMApprovalFactvw] AS [approvalFact]
ON [reqDim].[FIMRequestDimKey] = [approvalFact].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMApprovalHasApprovalResponsesFactvw] AS [approvalHasResponse]
ON [approvalFact].[FIMApprovalDimKey] = [approvalHasResponse].[FIMApprovalDimKey]
LEFT OUTER JOIN [dbo].[FIMApprovalResponseFactvw] AS [approvalResponse]
ON [approvalHasResponse].[FIMApprovalHasApprovalResponses_FIMApprovalResponseDimKey]
= [approvalResponse].[FIMApprovalResponseDimKey]
LEFT OUTER JOIN [dbo].[FIMEntityDimvw] AS [approverEntity]
ON [approvalResponse].[FIMCreator] = [approverEntity].[FIMObjectID]
WHERE
[reqFact].[FIMRequestCommittedTime] >= @committedDateStart
AND [reqFact].[FIMRequestCommittedTime] < @committedDateEnd
AND [rtdFact].[FIMObjectTypeDimKey] = @groupObjectTypeDimKey
GROUP BY
[reqFact].[FIMRequestDimKey],
[reqDim].[FIMObjectID],
[reqFact].[FIMRequestCommittedTime],
[reqFact].[FIMCreator]
ORDER BY [reqFact].[FIMRequestCommittedTime] DESC;
--CREATE Tables for persisting the concatenated Approvers and MPRs
--These are LEFT JOINED in the final query so they must exist regardless the value of @showOnlyFirstApproverAndMPR
CREATE TABLE [#REQUESTAPPROVERSGROUP]
(
[FIMRequestDimKey] INT NOT NULL,
[FIMApprovalResponseCreatorDisplayNames] NVARCHAR(4000) COLLATE DATABASE_DEFAULT
CONSTRAINT REQUESTAPPROVERSGROUP_PK PRIMARY KEY CLUSTERED
(
[FIMRequestDimKey]
)
);
/*****************************************************************************************************************
*
* 4. Get concatenated Approvers and MPRS if the box is selected
*
*****************************************************************************************************************/
IF @showAllApproversAndMPRs = 1
BEGIN
--Concatenate the Approvers String for the known set of requests
WITH [Request]
(
[FIMRequestDimKey]
)
AS
(
SELECT DISTINCT [FIMRequestDimKey]
FROM [#REQUESTIDS]
WHERE [FIMRequestMinApprovalDisplayName] IS NOT NULL
)
, [ApprovalResponse]
(
[FIMRequestDimKey],
[ApprovalResponseCreatorDisplayNames] )
AS
(
SELECT
[r].[FIMRequestDimKey],
(SELECT DISTINCT
CASE
WHEN [rarc].[FIMApprovalResponseCreatorDisplayName] IS NULL THEN N'; '
ELSE N'; ' + [rarc].[FIMApprovalResponseCreatorDisplayName]
END
FROM [Request] AS [rSub]
LEFT JOIN [dbo].[FIMRequestApprovalResponseCreatorvw] AS [rarc]
ON [rSub].[FIMRequestDimKey] = [rarc].[FIMRequestDimKey]
WHERE [rSub].[FIMRequestDimKey] = [r].[FIMRequestDimKey]
FOR XML PATH (''))
FROM [Request] AS [r]
)
INSERT INTO [#REQUESTAPPROVERSGROUP]
SELECT [ar].[FIMRequestDimKey],
CASE
WHEN [ar].[ApprovalResponseCreatorDisplayNames] !='''' THEN SUBSTRING([ar].[ApprovalResponseCreatorDisplayNames], 3, LEN([ar].[ApprovalResponseCreatorDisplayNames])-2)
END AS [ARCDISPLAYNAMES]
FROM ApprovalResponse AS [ar];
--Concatenate the MPR String for the known set of MPR's
WITH Request
(
[FIMRequestDimKey]
)
AS
(
SELECT DISTINCT [FIMRequestDimKey]
FROM [#REQUESTIDS]
WHERE [FIMRequestMinMPRDimKey] IS NOT NULL
)
, [ManagementPolicyRule]
(
[FIMRequestDimKey],
[ManagementPolicyRuleDisplayNames] )
AS
(
SELECT
[r].[FIMRequestDimKey],
(SELECT DISTINCT
CASE
WHEN [rmpr].[MPRObjectID] IS NULL THEN N'; '
WHEN [rmpr].[MPRDisplayName] IS NULL THEN N'; '
ELSE N'; ' + [rmpr].[MPRDisplayName]
END
FROM [Request] AS [rSub]
LEFT JOIN [dbo].[FIMRequestMPRvw] AS [rmpr]
ON [rSub].[FIMRequestDimKey] = [rmpr].[FIMRequestDimKey]
WHERE [rSub].[FIMRequestDimKey] = [r].[FIMRequestDimKey]
FOR XML PATH (''))
FROM [Request] AS [r]
)
INSERT INTO [#REQUESTMPRSGROUP]
SELECT [mpr].[FIMRequestDimKey],
CASE
WHEN [mpr].[ManagementPolicyRuleDisplayNames] !='''' THEN SUBSTRING([mpr].[ManagementPolicyRuleDisplayNames], 3, LEN([mpr].[ManagementPolicyRuleDisplayNames])-2)
END AS [MPRDISPLAYNAMES]
FROM [ManagementPolicyRule] AS [mpr];
END
/*****************************************************************************************************************
*
* 5. Run the non selective query
*
*****************************************************************************************************************/
SELECT DISTINCT
[GRP].[FIMDisplayName] AS [Current Group Display Name],
[GRP].[FIMObjectID] AS [Group Object ID],
[RTD].[FIMRequestRequestStatusMode] AS [Operation Type],
[AT].[FIMAttributeTypeName] AS [Attribute Name],
[RTD].[FIMRequestTargetDetailAttributeValue] AS [Attribute Value],
[request].[FIMRequestCommittedTime] AS [Committed Date],
[request].[FIMRequestObjectID] AS [Request Object ID],
[RCe].[FIMDisplayName] AS [Requestor Display Name],
CASE WHEN @showAllApproversAndMPRs = 1
THEN [allApprovers].[FIMApprovalResponseCreatorDisplayNames]
ELSE [request].[FIMRequestMinApprovalDisplayName]
END AS [Approver Display Names],
CASE WHEN @showAllApproversAndMPRs = 1
THEN [allMPRs].[FIMManagementPolicyRuleDisplayNames]
ELSE [managementPolicyRule].[FIMDisplayName]
END AS [Management Policy Rule Names],
[RCe].[FIMObjectID] AS [ExtendedDataRequestorID],
NULL AS [ExtendedDataApproverIDs],
NULL AS [ExtendedDataManagementPolicyRuleIDs]
FROM [#REQUESTIDS] AS [request]
INNER JOIN [dbo].[FIMRequestTargetDetailFactvw] AS [RTD]
ON [request].[FIMRequestDimKey] = [RTD].[FIMRequestDimKey]
INNER JOIN [dbo].[FIMGroupDimvw] AS [GRP]
ON [RTD].[FIMRequestTargetDetailTarget] = [GRP].[FIMObjectID]
INNER JOIN [dbo].[FIMAttributeTypeDimvw] AS [AT]
ON [RTD].[FIMAttributeTypeDimKey] = [AT].[FIMAttributeTypeDimKey]
LEFT OUTER JOIN [dbo].[FIMEntityDimvw] AS [RCe]
ON [request].[FIMCreatorObjectID] = [RCe].[FIMObjectID]
LEFT OUTER JOIN [#REQUESTAPPROVERSGROUP] AS [allApprovers]
ON [request].[FIMRequestDimKey] = [allApprovers].[FIMRequestDimKey]
LEFT OUTER JOIN [#REQUESTMPRSGROUP] AS [allMPRs]
ON [request].[FIMRequestDimKey] = [allMPRs].[FIMRequestDimKey]
LEFT OUTER JOIN [dbo].[FIMManagementPolicyRuleDimvw] AS [managementPolicyRule] -- Match single MPR
ON [request].[FIMRequestMinMPRDimKey] = [managementPolicyRule].[FIMManagementPolicyRuleDimKey]
WHERE
[RTD].[FIMObjectTypeDimKey] = @groupObjectTypeDimKey
AND
(
@operationType IS NULL
OR [RTD].[FIMRequestRequestStatusMode] = @operationType
)
AND
(
@attributeName IS NULL
OR [AT].[FIMAttributeTypeName] = @attributeName
)
AND
(
@managementPolicyRuleName IS NULL
OR [managementPolicyRule].[FIMDisplayName] = @managementPolicyRuleName
)
ORDER BY
[request].[FIMRequestCommittedTime] DESC,
[AT].[FIMAttributeTypeName];
END TRY
BEGIN CATCH
DECLARE @errorMessage NVARCHAR(MAX);
SET @errorMessage = ERROR_MESSAGE();
RAISERROR(@errorMessage, 16, 1);
END CATCH
END
GO
GRANT EXECUTE ON [dbo].[FIM_Report_Group_SP_GetHistory] TO reportuser
GO
Home