Corporate to subsidiary management for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Corporate to subsidiary management

Often is the case in large corporations that requirements for planning span multiple business units, requiring corporate to implement a centralized planning solution in the goal to have a more standardized, accurate and efficient process. With increased complexities from the requirements between corporate and subsidiary, a single monolithic planning solution might no longer be ideal and include such factors as:

Too many users who access one system.
Too many complex models required to satisfy all subsidiaries.
Processes from one subsidiary can impact others.
Updates to one subsidiary can impact others.
More difficult to get changes through as the impact is company wide.

In such scenarios, we can create multiple planning solutions with the corporate solution acting as the template for all subsidiaries, alleviating the stress put on any single planning solution.

With the multiple planning solution approach, we will have a single solution per business unit, each with its own relational data mart and SQL Server Analysis Services database. The corporate solution will act as the primary data source to all subsidiaries, sharing out the corporate view of the models, dimensions and hierarchies. The corporate SQL Server Analysis Services database will act as the template structure to each subsidiary. Standardized business rules are implemented and maintained at corporate and copied to the subsidiary solution as required.

Minor updates made to the corporate solution should trigger sync with subsidiary solutions, for instance the adding of a new account to the ‘Chart of Accounts’ or updates made to fact data for shared models. Updates to dimension members and properties from the corporate solution can be auto-sync’d by tagging the members in the subsidiary solution as belonging only subsidiary, allowing ETL to be easily defined for updates between subsidiary and corporate.

Major updates made to corporate might require a recopy of the corporate template to each subsidiary.

Steps to create subsidiary data model:

Copy corporate solution’s relational database and restore to subsidiary environment.
Copy corporate solutions SSAS database and restore to subsidiary environment.
1. Update data connections to point to subsidiary environment.
2. Update user security roles on SSAS database for use by subsidiary.
Create ETL packages to sync data between shared data models between corporate and subsidiary solutions.
Apply subsidiary specific data to the copied corporate database. For instance, add accounts to the Chart of Accounts that are not part of the corporate hierarchy. Members added to the subsidiary should be appropriately tagged as specific to the subsidiary. This will allow for ETL to continue syncing corporate data with that in subsidiary.
Create subsidiary specific models for data capture and reporting.

Benefits of multiple planning include the following:

Greater flexibility for each subsidiary to design and schedule their own planning process without impacting company-wide processes.
Changes to subsidiary planning solution has no impact on other planning solutions.
Potentially better performance; planning solutions can be geographically located to each business unit’s primary user base.
Simplified security model for each business unit versus a union of all IW belonging to each of the business units, potentially in the tens of thousands.

To take advantage of the corporate solution and its data model, we’ll identify some of the items that can be shared:

Share fact, dimension and hierarchy tables from corporate to subsidiary.
Share corporate business rules on cube (MdxScript) to subsidiary.
Share stored procedures from corporate to subsidiary.

Dimension, hierarchy and fact tables for the subsidiary solution can source its data directly from the corporate solution by using ETL process. Periodic sync can be scheduled between the tables in order to get the latest up to date corporate data into subsidiary. Since data in corporate is already cleansed, ETL should be rather straightforward.

For standardized business rules, we can reuse the definitions from the corporate solution and apply it to the subsidiary. Mdx Scripts that exist on the corporate SQL Server Analysis Services database can be copied and added to the subsidiary’s SQL Server Analysis Services database. Stored procedures can also be copied and applied to subsidiary’s relational data model using the SSMS ‘Script Stored Procedure as’ feature.

Corporate to subsidiary management for BI planning solutions and scenarios

Corporate to subsidiary management

See Also

Concepts

Additional resources