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.
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.