Solution requirements and architecture overview of BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

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

In this article:

Solution requirements
Building the solution

Solution requirements

Skill set requirements

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

Product requirements

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

Building the solution

Solution architecture overview

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.

Solution requirements and architecture overview of BI planning solutions and scenarios

Solution requirements

Skill set requirements

Product requirements

Building the solution

Solution architecture overview

See Also

Concepts

Additional resources