Virtualising SQL Server Analysis Services (SSAS) Databases using Delphix AppData

Introduction

SQL Server Analysis Services (SSAS) deliver online analytical processing (OLAP) for business intelligence applications. Analysis Services support OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as MS SQL Server.

SSAS hosts aggregated cubed data and stores it into a SSAS database.   End users query pre-aggregated data from the SSAS database.  In many cases the SSAS database can satisfy all such queries, but some queries may require access to the underlying SQL Server database.

SSAS Pinch Points

1      Estimates suggest that the primary storage of an SSAS multidimensional database can be about one third size of the original data.

2      SSAS cannot support an unlimited number of concurrent users or queries.  A common technique is to distribute the workload across multiple dedicated query servers, which has many advantages, yet the downside is an inefficient use of expensive storage resources given that each query server requires a separate database copy.  See Scale-Out Querying for Analysis Services with Read-Only Databases

Enter Delphix

Delphix can assist with two scenarios

1      Enable SSAS database generation by provisioning virtual copies (VDBs) of the underlying SQL Server database(s)

This offloads the SSAS generation process from production databases.

2      Scale out access to SSAS databases using AppData provisioning and corresponding MS SQL Virtual Database provisioning.

This allows expansion of access to SSAS data with minimal storage overhead at maximum speed.

How do I do that?

Setup Source Environment (description based on SQL Server 2008 R2)

1. Import AdventureWorks2008R2 sample MS SQL database into a source SQL Server Instance.
2.  Install Analysis Services and Business Intelligence Development Studio on both source and target SQL Server Instances.
3. Run the SQL Server Analysis Services Tutorial on the source SQL Server Instance.

Ingest Sources

1. Ingest the AdventureWorks2008R2 MS SQL database into Delphix
2. Ingest SSAS database, located at “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP” using AppData (virtual files).  Note since this is a database it may need to be quiesced before ingest to guarantee consistency.
3. Ingest the Visual Studio Project, located at “C:\Users\delphix_admin\Documents\Visual Studio 2008\Projects\Analysis Services Tutorial”, using AppData

Note: files can only be ingested via a server running the Delphix connector.  I used an SMB share to the target server.

Provision Clones (Virtual Copies)

All steps below apply to the target server.

1.  Provision virtual copy of AdventureWorks2008R2 as a VDB, named “vAdventureWorks2008R2”.
2. Stop the SSAS Windows service
3. Rename “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP”
4. Provision virtual copy of SSAS database onto “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP”
5. Start the SSAS Windows service
6. Provision virtual copy of the Visual Studio Project, called Analysis Services Tutorial into “C:\Users\delphix_admin\Documents\Visual Studio 2008\Projects\Analysis Services Tutorial”.
7. Using SQL Server Business Intelligence Development Studio, modify the data source connection string to connect to the Virtual Database, “vAdventureWorks2008R2”.
8. Test by selecting Database -> Process -> Run, to prove the cube can be refreshed from the MS SQL database

Complete.

Conclusion

Now you have a virtual copy of the entire SSAS analysis stack provisioned in minutes with almost no storage overhead.  You can collate all three components together into a JetStream container and manipulate them as a single data and application environment.