Migration for BI planning solutions and scenarios
Published: January 27, 2011
Migration is taking a solution from a development environment and bringing it to a production environment. This typically will involve three environments and two migration phases:
Migrate from development to test.
Migrate from test to production.
Development of the solution can typically be done on a single server that uses all the required services. This includes Microsoft SQL Server and Microsoft SQL Server 2008 Analysis Services (SSAS) installed locally. This is ideal as typically in development you want the following:
Full control on the environment for fast turnaround on issues that are not specifically related to solution development.
Simplified server setups.
Focus on solution design and requirements.
The main purpose of the test environment is to validate the design in the development environment on an environment that will most closely match the production environment. The test environment will focus on the following:
Business logic validation
Security and access control
The production environment is the go-live environment that will be accessed by the end consumers of the solution. This environment should have backups and redundancy built in so that there is no loss any significant data from IWs.
Migration of the planning solution will involve moving and reconfiguring the following assets from a source environment and into a target environment:
Planning database on SQL Server
Planning database on SQL Server Analysis Services
Excel form templates on Microsoft SharePoint Server 2010
ETL packages built by using Microsoft SQL Server Integration Services (SSIS)
Migration of the relational database can be performed by using a simple backup and restore process. Make a backup file of the SQL Server database by using SSMS:
The backup file can then be used to restore the database to the target environment’s SQL Server. Once the database is restored on the target environment, we can start with the restore of the multidimensional database.
Migration of multidimensional database can also be performed by using a backup and restore process similar to the relational database. Considerations on post database restore include the following:
Update role based security
Update data sources
Update role-ased security
Security is typically configured differently between the various migration environments. For instance, in the development environment, there will be many more expert users who have the permission to process and alter the multidimensional database than compared to that in a production environment. When setting up security, it is a good idea to consider the necessary security rights that are required for users within each migration environment and to remove any roles that are not relevant for that environment.
Development environment security
It is recommended to create a single SQL Server Analysis Services role that will contain all the expert users for the development environment. This development role will have permissions that are elevated over most other roles as measured by interactivity with the multidimensional database. For example database processing permissions and the ability to create/alter/remove database objects will be granted here. Creating a single role will let the administrator easily remove or update users in the role as it is moved from one environment to another. For improved manageability, use Active Directory groups when you are adding membership to the role. This will let IT to manage security without accessing into SQL Server Analysis Services at all.
Test environment security
In the test environment, our goal is to emulate the security setup that should be found in a production environment. When setting up security, there are some considerations to take. Depending on the complexities of the security requirements, we can have:
Role created based on each secured slice of data.
Role per IW, each that contains their own security definition.
For the most part, it is acceptable to have the development role available in the test environment. Also, for users who will only exist in the test environment for user acceptance testing (UAT), we recommend that you create separate roles for them and have them be clearly identifiable. When moving to production, development and test roles must be removed.
Production environment security
The production environment should not contain any development or test roles. Remove these roles after a restore of the multidimensional database when in a production environment. Ensure that the security roles that are set up are only for IWs that should have access to the production environment.
Update data sources
On the multidimensional database, make sure that you update the connection string property for the data source to point to the target environment’s SQL Server.
Ensure that the SQL Server Analysis Services server has access to the relational database server. To test that everything is configured correctly, perform a database process to reload multi-dimensional database.
Excel form templates
Transfer form templates from the SharePoint document library into another library created for the targeted migration environment. Update the connection string properties for the data connections used by the PivotTables and point them to the target environment’s multi-dimensional database.
Packages written for data transfer between source systems and the planning solution may have to be updated as we move the packages from a source to target environment. Depending on how the configuration properties are set up within the SSIS packages, updates to the connection strings may be required by working with the following:
XML configuration file
Parent package variable
For more information about how to manage connection strings in SSIS packages, see Understanding Integration Services Package Configurations.
For production and test environments, we recommend that you have the following four server configuration. The critical server that require being on stand-alone computer is the SQL Server Analysis Services OLAP server. For SQL Server Analysis Services based planning solutions, the core bottleneck will typically be from the SQL Server Analysis Services server as it is primarily responsible for interactivity between the multidimensional data model and Excel 2010 PivotTables. This includes answering all what-if and other ad-hoc queries. In addition, many calculations will also be evaluated dynamically by the SQL Server Analysis Services server when responding to query requests. Thus, it is typical to require both high computational capability and large memory allocation for the SQL Server Analysis Services server. However, the exact server configuration will depend on the complexity of the solution and its usage requirements by the IWs of the system.
ConceptsBasic planning scenarios in BI planning solutions and scenarios
Planning the data mart for BI planning solutions and scenarios
Planning modeling concepts in BI planning solutions and scenarios
Cube modeling for Write-back in BI planning solutions and scenarios
Performance considerations and approaches in BI planning solutions and scenarios
Cube modeling with Excel PowerPivot in BI planning solutions and scenarios
Create reports and forms for BI planning solutions and scenarios
Submit plan data for BI planning solutions and scenarios
Workflow actions, workflow diagram, and SharePoint workflow setup for BI planning solutions and scenarios
Audit tracking for BI planning solutions and scenarios
Administration for BI planning solutions and scenarios
Calculations for BI planning solutions and scenarios
Additional planning functions for BI planning solutions and scenarios
Migration for BI planning solutions and scenarios
Maintenance for BI planning solutions and scenarios
Corporate to subsidiary management for BI planning solutions and scenarios
Planning modeling and reporting guide for BI planning solutions and scenarios
Building planning functionalities guide for BI planning solutions and scenarios
Planning and budgeting calculation examples for BI planning solutions and scenarios