Solution requirements and architecture overview of BI planning solutions and scenarios


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2011-01-28

This article contains information about solution requirements and an architecture overview for planning solutions and scenarios.

In this article:

  • Intermediate knowledge of SQL

  • Intermediate knowledge of Microsoft SQL Server 2008 Integration Services for data integration

  • Intermediate understanding of OLAP concepts including star schemas and use of fact, dimension and hierarchy tables

  • Advanced knowledge of SQL Server 2008 Analysis Services

  • Intermediate knowledge of Microsoft Excel 2010 including on-sheet formulas and PivotTables

  • Intermediate knowledge of Excel 2010 with PowerPivot

  • Microsoft SQL Server 2008

  • Microsoft SQL Server Integration Services (SSIS)

  • SQL Server 2008 Analysis Services

  • Microsoft SQL Server Business Intelligence Development Studio

  • Microsoft SQL Server Management Studio

  • Microsoft Excel 2010

  • Microsoft SharePoint Server 2010

We will use SQL Server Analysis Services 2008 (SSAS) as the data model to support the sophisticated planning scenarios as called out previously. By using SSAS, the solution will be able to manage data models with millions of fact records and complex business logic. IWs will be able to open Microsoft Office Excel 2010 workbook templates from a central SharePoint Server 2010 portal and use pre-configured Pivot Tables to read and update data back to the SSAS server. SharePoint workflow will be used to control the business process around planning. Security will be defined on the SSAS database using roles, with each user’s security privilege limited to a particular set of geographies that they belong to. Business rules will be created and deployed to the SSAS cubes for IWs to consume seamlessly within Excel 2010 Pivot Tables for reports and data entry forms.

An alternative approach to data modeling in SSAS will be using Excel 2010 with PowerPivot and SharePoint Server 2010 to create data models in a workbook and publish to SharePoint for IWs to consume. Data security will be over the complete data model. End users will submit their plan data by modifying the shared workbook and saving it to SharePoint Server 2010. The PowerPivot model will be updated by creating linked tables associated to the PowerPivot model within the workbook. Collaboration on the data models will be maintained within the document library within the SharePoint environment. Note that this solution will best be suited for small teams of users, generally about five of so that users who can manage all the updates to the centralized workbook. This solution is not suited for corporate wide audience.