Configuration of Snapshot Datasources

A Snapshot server is of great benefit in an environment where there is a high volume of writebacks or cube structure updates (e.g. Dimension changes) taking place.

The reason for this is that Analysis Services, at some point, needs to drop its query cache or swap in new cube definition. When this needs to happen, it will lock the database, and any further queries will be queued up to wait while allowing (by default, up to 30 seconds) existing queries in progress to complete. Once all existing queries are completed or the wait time elapses, Analysis Services will terminate any remaining queries, swap in the new definition, release the lock and let the queued up queries to run again.

This wait time can cause bottlenecks in query processing which lead to very varied performance experiences to the end-users.

Configuring a Snapshot server in CALUMO alleviates this issue by allowing report developers to designate that a given report will run against the Snapshot server, not the Live server. It will also make CALUMO Views, by default, open against the Snapshot server, not the Live server.

Properties

Live Server

The Live Server is the SQL Server Analysis Services server instance of the Live or master version of the Analysis Services databases.

Snapshot Server

The Snapshot Server is the SQL Server Analysis Services server instance where the Analysis Services databases are synchronised to from the Live server.

Setting Up A Snapshot Server

A Snapshot Server is another instance of SQL Server Analysis Services either running on the same machine as the Live server instance or another server altogether.

To synchronise an Analysis Services database over from the Live server instance to the Snapshot server instance, follow these instructions from Microsoft.

Once this initial step is complete, a scheduled synchronisation of the database via a SQL Agent Job or a triggered SQL stored procedure should be set up. This process will run the required XMLA to have the Live and Snapshot databases keep in sync.

We recommend that the schedule is once a day, overnight.

CALUMO Behaviour

Snapshot Alias

Within CALUMO, when a datasource has a Snapshot, another alias becomes available for use. It is named by convention as <your alias>-SNAP. This alias is then usable in Published Reports so that the report queries the Snapshot server, not the Live server.

CALUMO Views

CALUMO Views will react a little differently to usual when opened against a datasource that has a Snapshot.

By default, views will always against the Snapshot datasource and show the new Query Mode interface that allows users to switch between which datasource they query.

A user can switch to the Live datasource by pressing the Live Query Mode action as highlighted above and back to the Snapshot datasource by pressing the Snapshot Query Mode action.

If you want to control what your users see and by what datasource a view will open to, you can use the following Query String arguments:

Argument Description
OverrideDatasource The datasource alias to use to override the views current datasource when opening this view.
OverrideQueryMode Use either Live or Snap to designate in which Query mode to open the view.
DisableQueryMode Use either True or False to either hide or show the Query Mode interface.

CALUMO Assets

When created, certain assets within CALUMO tie themselves to that datasource, e.g. Subsets and Calculated Members. For these assets, when using a Snapshot alias, they are used as if it were the Live alias.