Business intelligence in SharePoint using SharePoint List

we needed to create fairly complex reports (containing KPI, charts, advance filters) on data contained inside sharepoint list, so first thing is to create Business intelligence Site, creating BI Site as Root is fairly simple where if you want to create BI as Sub-Site, follow this blog.

Using Performance Point SharePoint Dashboard Designer

I was disappointed to find out you cannot build advance reports using Performance Point SharePoint Dashboard Designer with List as a Data Source, so we have to create Analysis Service Cube as Data Source but you cannot create Analysis Service Cube directly on top SharePoint Database as its not relational database, which means we have to create a Data Warehouse

So below contain the complete process involved

Where above process can be broken down into following phases

Phase 1: Creating relational Data Warehouse

As we know SharePoint database is not relation, we need to create a relational Database do check out SLAM if it works for you then it can be huge time saver.

Phase 2: Creating Analysis service (OLAP) Cube, Dimensions, Measures

After your data warehouse created you can now create your SSAS (OLAP) Cube, Dimensions, Measures, Translation, Parent Child Hierarchy, Partitioning etc

Phase 3: Creating Reports using SharePoint Dashboard Designer

After your SSAS is setup you can then add some dummy data in your Data warehouse and process your Cube and then finally create Reports :)

Where we are using SharePoint Dashboard Designer as reporting tool but there are other options you can use as mentioned below

1-      SQL Server Reporting Service

2-      Excel powerpivot.

3-      Any 3rd Party reporting Tool that support OLAP e.g. DevExpress

Phase 4: Transferring Data from SharePoint Database to Data Warehouse.

To keep your Data Warehouse updated, you have following options

1-      SLAM:  A codeplex project where it take your lists and create relation Database as well as keep updating database by sharepoint Event receivers, if it works for you its can be huge time saver!!.

2-      SSIS Package : Using SQL Server Business Intelligence Development Studio, you can create SSIS Package for ETL (Extract, transform, load), where you will need SharePoint list as data source which is not available out of the box in Intelligence Studio, so you have to use SharePoint List Source and Destination a codeplex project.

3-      Custom:  If you like custom you can obviously build your own ETL solution, in any mechanism you prefer.

Phase 5: Processing Cubes.

To keep our calculation and Data Updated in SSAS, it needs to regularly process.

Summary

Above Phases contain just brief description, where as you digg into each phase there allot into it :) for example SSAS is whole new world it takes time to understand its concepts!  Where i will also write detailed blogs on each Phase so that it might help someone.

Please leave comment if you find it useful or want to point out some mistake.

Upgrading or Redeploying SharePoint 2010 Workflows

While creating several State Machine SharePoint 2010 workflows using visual studio for a client I had some concerns related to upgrading and redeploying those workflows because as we all know, changes are inevitable!

Where following are the concerns and my solution to them

1-      How can we redeploy or upgrade workflow?

I found a nice article which goes in details of upgrading workflow approaches, and as per my analysis Versioning workflows seems to be the only feasible solution.

2-      When it’s required to version my workflows and how can I minimized it?

Versioning is required only when UI changes are done on workflow e.g. modification in state, activities etc. as this will break running instance of workflows due to failure in deserialization.

As a strategy we chose multi-layer architecture, which means whole logic in a separate Class library (business) and workflows in separate Library where workflow only defines flow as shown below.

  

This means for code level changes or bug fixes, we only redeploy business solution wsp not affecting workflows wsp.

3-      How to Version Workflow solution and any step by step instructions?

While goggling I could not found comprehensive step by step instructions.

Where following contain the step by step instruction how I implemented it.

Step 1: Upgrade Assemble 

·         Go to Workflow Solution -> AssemblyInfo Class and update assembly version and file version as shown below.

 

 Step 2: Upgrade Workflow Element.xml

Go to modified workflows and Open their Element.xml files then

·         Update 'CodeBesideAssembly' attribute assembly version.

·         Update ‘Name’ with Version number at end e.g. xyz version 1.0.0.1 (for your convenience).

·         Update 'ID' attribute with a new GUID (Tools->Create GUID).

          

Step 3: Workflow Feature Update

·         Remove any existing feature that deploy workflows from solution

·         Add a new Site Scope Feature with 'Name' e.g. PrjectNameWorkflowsVersionX.X.X.X

·         Add only modified Workflows inside this feature (Step 2 modified ones only)

Step 4: Upgrade Solution Package

   Double Click Package and then

·         Update the 'SolutionId' with a new GUID.

·         Update 'Name' with modified version.

Step 5: Deployment

·         Build and deploy the new workflow wsp solution and IIS Reset.

     Note: this will register a new version of the workflow assembly in the GAC.

Step 6: Association and No New instance

·         Associate newly deployed workflow e.g. xyz version 1.0.0.1

·         Go to Associated list Workflow Settings -> Remove workflow and Put the old version workflows as "No New instance".

Note:  As my solution contains several workflows so I created a utility for Programmatically performing step 6 operation, here are some methods that I created that might be helpful.

''' <summary>

''' To Programmatically associate workflow with a List or library

''' </summary>

Public Shared Sub AssociateWorkflow(ByVal web As SPWeb, _

                                        ByVal WorkflowGUID As String, _

                                        ByVal WorkflowName As String, _

                                        ByVal ListName As String, _

                                        ByVal TaskListName As String, _

                                        ByVal HistoryListName As String, _

                                        ByVal AllowManual As Boolean, _

                                        ByVal AutoStartChange As Boolean, _

                                        ByVal AutoStartCreate As Boolean)

        'Bind to lists.

        Dim ListToAssociate As SPList = web.Lists(ListName)

        Dim TasksListToAssociate As SPList = web.Lists(TaskListName)

        Dim workflowHistoryList As SPList = web.Lists(HistoryListName)

 'Get workflow Template

        Dim workflowTemplate As SPWorkflowTemplate = web.WorkflowTemplates(New Guid(WorkflowGUID))

        If ListToAssociate.WorkflowAssociations.Count > 0 Then

            Throw New Exception(String.Format("List '{0}' is already associated with Workflow '{1}', Please Disassociate this workflow first.", ListToAssociate.Title, ListToAssociate.WorkflowAssociations(0).Name))

        End If

        'Create workflow association.

        Dim workflowAssociation As SPWorkflowAssociation = SPWorkflowAssociation.CreateListAssociation(workflowTemplate, WorkflowName, TasksListToAssociate, workflowHistoryList)

        'Set workflow options.

        workflowAssociation.AllowManual = AllowManual

        workflowAssociation.AutoStartChange = AutoStartChange

        workflowAssociation.AutoStartCreate = AutoStartCreate

       ' Hint: WorkflowAssociation.Enabled = false means 'No new instantace' 

        'Add workflow association.

        ListToAssociate.WorkflowAssociations.Add(workflowAssociation)

    End Sub

''' <summary>

''' To programmatically start workflow already associated with a List or library

''' </summary>

Public Shared Sub ManuallyStartWorkflow(ByVal objWeb As SPWeb, ByVal listTitle As String, ByVal itemID As Integer)

        Using elevatedSite = New SPSite(objWeb.Url, SiteHelper.GetSystemUserSecruityToken(objWeb))

            Using web = elevatedSite.OpenWeb()

                web.AllowUnsafeUpdates = True

                Dim elevatedList As SPList = web.Lists(listTitle)

                'Get Associated Workflow

                 Dim item As SPListItem = elevatedList.GetItemById(itemID)

                    If item.Workflows.Count = 0 Then

                        Dim myAssociation As SPWorkflowAssociation = GetWorkflowAssociation(elevatedList.WorkflowAssociations)

                        elevatedSite.WorkflowManager.StartWorkflow(item, myAssociation, myAssociation.AssociationData)

                    End If

                End If

                web.AllowUnsafeUpdates = False

            End Using

        End Using

    End Sub

Where even after reading this article you don’t like any of the solution and you want to develop few workflows that are not complex, then go with Event Receivers you can build your logic by code resulting in better performance and you don’t have to worry about versioning or anything J

SharePoint 2010 Security using Object Model

1-    Permission Assignment:

Mostly we want to assign SPUser/SpGroup to a SpWeb/SpList/SpListItem, below contain a generalize method to perform such operation

    ''' <summary>

    ''' Assigning SPUsers to any Securable Object

    ''' </summary>

    ''' <param name="web">Root SPWeb</param>

    ''' <param name="item">SPWeb or SpList or SplitItem

    ''' where it should be Elevated to avoid any problem during update</param>

    ''' <param name="User">Any SPUser</param>

    ''' <param name="spRoleType">Enumeration of default Permission Level</param>

    ''' <remarks></remarks>

    Public Shared Sub AssignSPUserToSecuribleObjectWithUniquePermission(ByVal web As SPWeb, ByVal Item As SPSecurableObject, ByVal User As SPUser, ByVal spRoleType As SPRoleType)

        EnsureUniquePermissions(Item)

        Dim roleAssignment As New SPRoleAssignment(User)

        Dim rolDefination As SPRoleDefinition = web.RoleDefinitions.GetByType(spRoleType)

        roleAssignment.RoleDefinitionBindings.Add(rolDefination)

        Item.RoleAssignments.Add(roleAssignment)

        'update

        UpdateSecurableObject(Item)

    End Sub

By using above method SpUser is assigned permission to SecurableObject as per SPRoleType.

Where above method can be used for assignment of SPGroup by just replacing SpUser param to SpGroup .

 ''' <summary>

    ''' Assigning SPGroup to any Securable Object

    ''' </summary>

    ''' <param name="web">Root SPWeb</param>

    ''' <param name="item">SPWeb or SpList or SplitItem

    ''' where it should be Elevated to avoid any problem during update</param>

    ''' <param name="Group">Any SPGroup</param>

    ''' <param name="spRoleType">Enumeration of default Permission Level</param>

    ''' <remarks></remarks>

    Public Shared Sub AssignSPGroupToSecuribleObjectWithUniquePermission(ByVal web As SPWeb, ByVal Item As SPSecurableObject, ByVal Group As SPGroup, ByVal spRoleType As SPRoleType)

        EnsureUniquePermissions(Item)

        Dim roleAssignment As New SPRoleAssignment(Group)

        Dim rolDefination As SPRoleDefinition = web.RoleDefinitions.GetByType(spRoleType)

        roleAssignment.RoleDefinitionBindings.Add(rolDefination)

        Item.RoleAssignments.Add(roleAssignment)

        'update

        UpdateSecurableObject(Item)

    End Sub

2-    Breaking Inheritance:

As we know In SharePoint there is a hierarchy as shown below

Where by each arrow Security gets inherited however we can break this security inheritance by using below method, it’s important to note that Web Application scope security is assigned globally.

    ''' <summary>

    ''' Assigning Unique Permission

    ''' </summary>

    ''' <param name="item">SPWeb or SpList or SplitItem

    ''' where it should be Elevated to avoid any problem during upate</param>

    ''' <remarks></remarks>

    Public Shared Sub EnsureUniquePermissions(ByVal item As SPSecurableObject)

        If Not item.HasUniqueRoleAssignments Then

            item.BreakRoleInheritance(True)

            UpdateSecurableObject(item)

        End If

    End Sub

    ''' <summary>

    ''' Generalize method to update Securable Object

    ''' </summary>

    Public Shared Sub UpdateSecurableObject(ByVal item As SPSecurableObject)

        If TypeOf item Is SPWeb Then

            CType(item, SPWeb).Update()

        ElseIf TypeOf item Is SPListItem Then

         If CType(item, SPListItem).ParentList.BaseType = SPBaseType.DocumentLibrary Then

                'no version update document library

                CType(item, SPListItem).SystemUpdate(False)

            Else

                CType(item, SPListItem).Update()

            End If

        ElseIf TypeOf item Is SPList Then

            CType(item, SPList).Update()

        End If

    End Sub

This post discusses basic two operations that are mostly commonly used however SPSecurableObject got some other good stuff to explore especially DoesUserHavePermissions method which is good for validation.