Monday, November 5, 2012

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.

2 comments:

  1. Hello Salman, do you provide Sharepoint Development services? I am having a difficult time getting sharepoint development. I need an external site built to collect client data and then reporting functions. nathan@ersgreen.com

    ReplyDelete
  2. Thanks for sharing this information with us. And i bookmark this blog for the future use.

    SharePoint Development

    ReplyDelete