• Part 1 – Introduction & Building the Back-End Reporting Database
• Part 2 – Developing SSIS Package to Import Data from SharePoint Lists to Reporting Database
• Part 3 – Setup SQL Server Agent Job to periodically execute the SSIS Package
• Part 4 – Building and Publishing Excel Reports on SharePoint

Sharepoint is a rapid application development platform which allows developers or even super users to develop business applications in a very cost-effective way. When business data is captured by Sharepoint-based applications and stored in Sharepoint Lists, reporting functionality is often required to provide decision makers insights into their businesses. Normally, there are three ways to report on Sharepoint-based applications. However, sometimes, those reporting approaches may not be practical or cost-effective. Below lists the three reporting appraches on Sharepoint data and explain their weaknesses.

• Directly Reporting on Data Stored in Sharepoint Lists – Microsoft offers a number of reporting tools which are capable to build reports through directly querying Sharepoint data and render the reports in Sharepoint, e.g. PerformancePoint, SSRS (with Sharepoint Addon), and Excel Services. One obvious problem with this approach is the performance issue. Poor performance overall could occur if a query on Sharepoint lists returns a large number of items, especially when the number is over the recommended threshold. Although Sharepoint is not designed as a LOB system and it does not aim to store a large amount of transactional data, it is not uncommon that a sharepoint list contains tens of thousands items.
• An Entire Multi-Dimension Data warehouse Solution – Microsoft provides a stack of techniques, such as SSIS, SSAS, and SSRS, which can be used to build an entire multi-dimension data warehouse solution for Sharepoint-based applcaitons. However, it is time-consuming and costy to build this kind of solutions. Bosses are often reluctant to make that big investment, especially when sharepoint-based applications are often not used for managing business-critical,  Line-of-Business data.
• PowerPivot for Sharepoint Server – PowerPivot is a very good choice to report on Sharepoint data, especially you are able to schedule the data refresh at a quiet time when there is not many visits to the site (e.g. midnight). However, if you want to use powerpivot in Sharepoint Server mode, you have to have Enterprise Edition of SQL Server which is not as affordable as Standard Edition for SME  (Unless you are working for education sector where you can get most of Microsoft products in a surprisally good price).

This blog series suggests a lightweight reporting solution which is very quick and easy to implement through using SSIS and Excel Services (or SSRS). The motivation behind this idea is to find a balance point between reporting performance and cost-effectiveness.

There are three steps to implement the solution:

Step 1: Create a back-end reporting database.

Step 2: Create SSIS script to periodically (e.g. every night) import data from sharepoint lists to the reporting database.

Step 3: Build reports using Excel PivotTable/Chart and render the reports on Sharepoint through using Excel Services.

This blog series uses a fictional and very simple business scenario to demo those steps. The fictional business is a sports center which has a multi-purpose sports court. The sports center hourly let the court out to publics. The manager of this sports center is using a Sharepoint list, ‘Sports Court Log’ , to log the sports court usage.

This Blog series consists of four parts. This is the first part which goes through the steps to build the reporting database on SQL Server.

Firstly, we create a database on a SQL Server instance, called “DemoSportsCourtLog”, and then add a table (“FactSportsCourtLog”) with its columns mapped to the columns in the Sharepoint list.

Then, we need add another table called “DimTime” which is used as a time dimension table. Time dimension is one of most important and frequently used dimension in most of business reports (apart from snapshot-type of reports. A time dimension table (as shown below) supports users to analyse/aggregate data by a range of time attributes, e.g. Year, Quarter, Month, Week, Week Day, etc..

Also, you could add other dimension tables as required. For example, you could add a ‘DimSportItem’ table for the ‘Sport’ column in “FactSportsCourtLog” table. However, it is negotiable that whether or not a ‘Standard’ star-schema structure is really necessary for this kind of reporting solutions which does not aim to analyse a heavy load, millions of trasactional records. The core idea of the suggested reporting solution is to find the right balance point between performance and cost instead of implement a most ‘rigorous’ but often costy soluiton.

Now, the reporting database has been created with no data in it. The next part of the blog series will describe how to import data from Sharepoint list to the reporting database using SSIS, and also how to set up a Sql Server Agent Job to periodically execute the SSIS script.

• SSIS/SSAS/SSRS/MDX