SSAS, SSIS/ Python: Extending Project Server OLAP Cubes via push-mode processing
Introduction and problem
Microsoft Project Portfolio Management (PPM) platform - Project Server includes various reporting and analytic capabilities based on Microsoft Business Intelligence Stack.
Data Warehouse part (DWH) of the Project database (previously a separate database – RDB) contains data consolidated and transformed from project base tables and SharePoint for reporting purposes.
Project Server OLAP Cubes are built on the top of the DWH.
The DWH and the OLAP Cubes are managed automatically by project server processes and the processes are transparent for end-users – changes made via
project server web user interface (PWA) by Project Server administrators are automatically propagated to the DWH and OLAP Cubes.
While this is a good example of self-service business intelligence implementation the drawback is in the limited number of extension points.
Push-mode SSAS processing allows you to update dimension member properties without modifications of DWH or Cubes structures.
While the technologies above are rather common they are rarely used together in order to solve the extension task for Project Server.
This article consolidates links and key points for an extension solution, which use SSAS push-mode processing.
Solution
1. To modify existing dimension property values:
- Add Data Flow task to SSIS package
- Add Dimension Processing from SSIS Data Flow toolbox
- Construct Source/Transformations with new dimension values and connect it to the Dimension Processing destination
- Schedule the package after scheduled in PWA cube process
2. To add attributes with custom dimension values:
- Add empty custom field in PWA
- Add the custom field to desired cube in PWA
- Use 1 to update empty values in the dimension
Restrictions and Usage
The approach was tested on updating dimension values. Probably the approach can be used together with Partition Processing destination
Python Solution
As an alternative to SSIS one can manually handle PushedDataSource of [MS-SSAS]: SQL Server Analysis Services Protocol via Python, for example.
Conclusion
SSAS push-mode processing allows you to extend existing cubes without modification of DWH or Cube structures.
It can be useful in delivering fast POC (proof of concept) solutions or when there is no access to ETL and cube building processes.
As an example key points of Project Server analytical system extension were considered.
Resources
Product documentation
SSAS, SSIS
Analysis Services 2005 Processing Architecture
Dimension Processing Destination
Project Server
Project Server 2013 architecture
Configure an OLAP cube in Project Server 2013
Cube Build Service
The Reporting Database and Report Data Service
Community
Introduction to the Dimension Processing Destination in SSIS 2012
Different Kinds of SSAS Processing
Project Server SQL SSAS OLAP Cube-making Demystified