A status model is normally used to track submission statuses as part of a workflow cycle, but in realty can be used for any model where the appropriate aggregation method for the values is min instead of sum.
In a Generic model all the values of the children below a parent are summed together to produce the value of the parent. In a Status model, the value of the parent will be the minimum value of the children values. For status reporting this works very nicely because statuses can be stored as numbers in the database with 0 indicating the member is open for input, 1 indicating the member has been submitted for first level approval, 2 indicating the member has received first level approval and has been submitted for second approval, and on up the approval levels. With the min aggregation method a parent will only show approval level 1 when all the children reach that approval level.
Caution: Unlike normal Analysis Services reporting, a child member with an empty value is not the same as child member with a value of zero. Empty children members are ignored when determining the minimum value the parent should have. This may mean in some cases you will want to initialize all appropriate members with a zero value before starting a workflow cycle.
Setup status reporting with a workflow cycle
- Create a Status model – create a model and assign the same dimensions to the model that are assigned as Cycle Dimensions in the Workflow Cycle. Set the model type to “Status”.
- Assign as Assumption model – assign the new status model as an assumption model to the model the workflow will be used with.
- Workflow Status Model – on the Workflow Cycle definition at the top level node of the definition set the Model parameter to the model the workflow is being used with and set the Status Model to the new status model set as an assumption model. (Note: Only Assumption models with a type of Status will be presented in the selection dialog box to select.)
- Save the Workflow Cycle.
With this, the status model is ready for use. When users submit a workflow assignment for approval or approvers approve or reject assignments, status values are automatically updated in the status model. The values in the status model are available for reporting in Excel just like any other data, by using the Measure for the assumption model with the statuses. The values for the statuses will be 1 when an assignment is submitted for approval, 2 when the assignment is approved, or 0 if the assignment is rejected for modification and resubmission.
Resetting Status values
Status values are not automatically reset with the beginning or end of workflow cycles. To reset status values use the Data Manager to clear to appropriate values in the database. Be sure to select the appropriate Measure for the status model.
By using a SQL script it is also possible to automate the clearing of status values when a Cycle is closed or started. Following is a simple SQL script and instructions on creating the Business Rules.
The following SQL script will create a stored procedure to delete all the records from a FACT table for a requested model. The query here can also be easily modified to limit the records that are deleted or perhaps to reset the existing values to 0.
CREATE PROCEDURE brp_ClearAllFactRecords
@ModelName nvarchar(255)
AS
BEGIN
declare @FactTableName nvarchar(255)
declare @Query nvarchar(255)
set @Query = 'delete from FACT_' + @ModelName + '_default_partition'
exec(@Query)
END
Business Rules
Define a SQL Business Rule in the Status model to call your stored procedure like the following.
Define a Library Business Rule in the main model to call the rule in the Status model using the “Include another Business Rule” rule with parameter settings like the following.
To clear the statuses automatically when a Workflow Cycle is started within the Workflow Cycle definition on the Execute Business Rule Events node add the Cycle Start event and assign your new business rule to this even. You will also need to assign a user to the Cycle Start – Action Users. Then when this user starts a new Cycle from the Excel workflow dialog box, the business rule will be automatically executed as part of starting the new cycle. (Note: The Workflow Cycle Actions are not automatically triggered as part of maintaining a Workflow Cycles and Cycle Instances in the Modeler. Also, the Workflow must be Checked In for the user to start a Cycle from Excel.)