Building planning functionalities guide for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Building planning functionalities guide

Creating planning forms by using SharePoint lists

A SharePoint list is useful for capturing data from IWs. Data entered to a SharePoint list can be translated and loaded to other parts of your data model through an ETL process.

To create a SharePoint list, you must have the correct permissions for creating lists, documents, and so on. From the home page of Microsoft SharePoint Server 2010, click Lists, and then click Create. We can select from various list type, but for our sample application, we will use a Custom List.

Once on the main list page, we can start by creating columns for our form. Under Manage Views, click Create Column to create the Title, DataType, Column Settings, and Column Validation rule.

Once all the columns are created, the list can be published and shared for users throughout a user group to collect data. In this example, we have set up an HR Pay Rate Plan that lets users add new rows and forecast their expected pay rates for a position. The user would be able to enter the data through form based entry. SharePoint lists also integrates with Microsoft InfoPath to create additional customized forms for entry.

Planning workflow setup (single level)

Step 1: To create a new workflow, click Edit Site in SharePoint Designer within the Site Actions Menu. This will open the SharePoint Designer to its main page. Navigate to Workflows on the side navigation pane and then click on Reusable Workflow under New.

Step 1a: For SharePoint Lists, workflows can be created in a similar method. However, some additional steps are required. To create a new workflow for List, click Edit Site in SharePoint Designer within the Site Actions Menu. This will open the SharePoint Designer to its main page. Navigate to Workflows on the side navigation pane and then click on Lists and Libraries. Under advanced settings, select the Require content approval for submitted items for changes to the List. Then we can create a new workflow by clicking Create, under workflows for this Library Lists.

Step 2: Provide a name and description for this workflow.

Step 3: Starting with workflow Step 1 (Name: “First Level Task Assigned”), under Actions, you can select Collect Data from a User. By clicking the highlighted Data, we step into the Task Wizard. We give this task a name & description, and add a custom field for Review Status. This can be set up as a choice drop-down for Approved and Rejected states.

We can also setup another field for Reviewer’s Comments, which can be set to multiple lines of text.

Step 4: After setting the Data field we can set Users to Approvers user group (or any other pre-configured user or group). We must have to then create a new variable for collection output. For this example, we will use the name “Document Review ID” and leave the type as “List Item ID”. Once completed, we will set the workflow variables for Review status and Review Comments.

Step 5: For Review Status, we will click on Set Workflow Variable under Action. Then set “Review Status” as the variable name and select string value. For value, we will select the following:

“Association: Task List” as the data source
“Review Status” as Field from Source
“ID” as Field
“Variable: Document Review ID” as Value

Step 6: For Review Comments, we will again click on Set Workflow Variable under Action. Then set “Review Comments” as the variable name and select string value. For value, we will select the following:

“Association: Task List” as the data source
“Review Comments” as Field from Source
“As String” for Return Field
“ID” as Field
“Variable: Document Review ID” as Value

Now that we’ve completed Step 1 for this workflow, we will create Step 2 (Name: “Final Level Approval”) for e-mail notifications.

Step 7: We start by setting a conditional on step 2. For the first if clause we will set the following:

“Workflow Variables and Parameters” for data source
“Variable: Review status” for Field from source

The equals will be set to “Approved” and then we will set an action of Send Email. The email is custom to any template desired. For this example, we will set the following within the template:

“User who created current item” on the To field
“Current Item title” on the Subject field
“The document has been approved” for the Body Text

Step 8: We will now set up an Else-If Branch for a new condition for Reject state. To set up the Rejected state, follow the instructions of step 7, but set the equals’ condition to “Rejected”. Within the email template, the body of the text can be change to reflect the Rejected state. In addition, we can add a lookup of the Review Comments variable to bring in the comments left by reviewer.

Step 9: We can now save this workflow in its finished state.

Planning Workflow Setup (Multi Level)

In between the first initial approval step and final step (email notification), we can set up multiple levels of approval steps in between. This is useful when there is multi-tier approval process involved during the planning process.

Step 10: Setup a new step after “First Level Task Assigned” and name it “First Level Approval & Second Level Assigned”. First follow steps 7 & 8 to set up email notifications for the Second Level Approvers.

Step 11: If the Review Status is approved, the submission is passed to the next level of approvers. We will now collect the approval status and comments for the next Approved state. Under the last action within the Approved condition, follow steps 3 through 6 to set the approval status and comments.

To collect data from the second level of approvers, you must have a user or group that was defined for this role. In this example, we set up a name and description for the new dataset called “Second Level Manager Approval” from a group named “Second Level Manager”. We also setup new names for the custom form fields for the user to complete:

Second Level Approval: set as a choice drop-down for Approved and Rejected states

Second Level Comments: set to multiple lines of text

These two form fields are then used to set the Review Status and Review Comments if approved.

If the submission is rejected, the owner who committed the last submission would only receive an email notification. The contributor will continue to resubmit to the same level approver until it is approved.

Step 12: For additional levels of approval needed within this workflow, we can repeat steps 10 & 11 for the appropriate user and groups.

Step 13: Once the Workflow was added to available workflows within our SharePoint Environment, we can then select the workflow that we’ve created to our document library. Within the document library, we can go to Workflow Settings, under Library and add a new workflow to the library.

Step 14: Select the newly created workflow, enter a unique workflow name and enable workflow to be started on all newly created documents. This will then start the newly created workflow on all documents within this library.

There are many components within the workflow that can be customized to a user environment. However, this example will provide a baseline for a typical planning, budgeting and forecasting process for approval & rejection cycle.

Add new lines

Add new line is a common scenario for IWs when they perform a budget. It is often the case that the input forms that are pre-configured do not adequately cover all the available inputs an IW wants to perform. For example, there might be a need to budget and plan for several new headcounts for the upcoming fiscal year yet there are no areas on the input form to collect the data inputs. New dimension members ideally would have to be created dynamically, and added to the data model for the budget. Here, we will provide a brief overview of the pros and cons of taking two approaches

Placeholders (Basic Approach) - No customization	Dynamic Members (Advanced Approach) - requires customization
Simple to implement	More complex to implement
No processing of OLAP dimension required	Processing of OLAP dimension required
No additional relational database changes	Save to relational database new member information
IW must use available placeholders	IW can add new members as needed
Cannot change the placeholders properties	IW can add new members with associated properties

Basic approach

In our employee dimension, pre-create a fixed number of placeholder members. In the solution, we’ve generated a series of members starting with TBH (to be hired) for use in budgeting of new employee hires.

Notice that only the valid employees for “Northeast Division” are being shown on the budget form. This is achieved by defining the underlying MDX to hide empty rows. Note that NED in our MDX query means Northeast Division on our geography dimension.

NON EMPTY

{
   {[Geography].[Geographies].[NED]} 
   *{ HIERARCHIZE (DESCENDANTS([Employee].[Employees].[All Employees], 0, AFTER), POST)}
   ,
   {[Geography].[Geographies].[NED]} 
   *{[Employee].[Employees].[All]}

} ON ROWS

Because we are hiding empty rows, it is important that we put data to the placeholder members so that when the query is executed the placeholder member rows will also be returned.

Advanced approach

The advanced approach requires a customization to be built on the platform to satisfy the requirements of the IW. The customization will have the following components:

A SharePoint list based on an external content type to collect new employees and their properties. For more information, see How to: Create External Content Types. SharePoint’s External Content Type feature is great at exposing application data, such as dimension and hierarchy tables directly to the IW.
VBA in Excel to process Analysis Services dimension. (Note: The user will need processing permissions on the Analysis Services database.)

The IW will then do the following when you add new lines:

Go to SharePoint and to add new employee and their details
From their Excel form, execute the VBA for dimension processing
Continue budget from Excel Pivot Table

In our example, adding a new list item through SharePoint List will create a new table record on the dimension table from our data store. Based on the setup of the hierarchy definition in SSAS, this new record will then be automatically processed to a member of a hierarchy after they perform a process command on the SSAS dimension. In essence, we could allow IWs to directly make the necessary updates to records that they are familiar with that will then directly affect the way they see the data from PivotTables and other client tools.

It is important to note here that updates are performed on a flat list of items with the SSAS hierarchy structure being derived from the property fields of those items. This is true for both pc and level based hierarchies.

VBA for Analysis Services dimension processing

The following is an example of processing new dimensions members from a macro defined in an Excel 2010 workbook. The macro will connect to an instance of SSAS server and issue an XMLA command for dimension processing. Notice that we used ‘ProcessAdd’ as the Process type. This is important as this is the fastest option when you are adding new members to a dimension. Avoid the need to ProcessFull as this will cause the related cubes to be processed.

Sub ProcessAddEmployee()

 Dim connection As New ADODB.connection
 Dim xmlaCommand As New ADODB.Command

 Dim ServerName As String
 Dim DatabaseName As String
 Dim DimensionName As String

 ServerName = "kepion02"
 DatabaseName = "AdventureWorks Planning"
 DimensionName = "Employee_All_Employee"

 connection.Open("Provider=MSOLAP;Data Source=" & ServerName _
       & ";Initial Catalog=" & DatabaseName & ";" _
       & "Integrated Security = SSPI;")

 xmlaCommand.ActiveConnection = connection
 xmlaCommand.CommandTimeout = 120 ' 120 seconds

 xmlaCommand.CommandText = _
 "<Batch https://schemas.microsoft.com/analysisservices/2003/engine"">" & _
 "<Parallel>" & _
  "<Process>" & _
   "<Object>" & _
    "<DatabaseID>" & DatabaseName & "</DatabaseID>" & _
    "<DimensionID>" & DimensionName & "</DimensionID>" & _
   "</Object>" & _
   "<Type>ProcessAdd</Type>" & _
   "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & _
  "</Process>" & _
 "</Parallel>" & _
 "</Batch>"

 xmlaCommand.Execute()
 connection.Close()

End Sub

This macro can also be associated to a button in Excel Quick Access Toolbar. This lets the user quickly and easily find the macro when planning and budgeting.

Share via

Building planning functionalities guide for BI planning solutions and scenarios