Versioning (Analysis Services) - How can I fix it?

Versioning is a tool in Clarity enabling users to copy data from a set of source dimension elements to a set of target dimension elements. The most common implementation of it gives users a selection for a source (scenario, year) combination and a target (scenario, year) combination. The tool itself looks clear and straightforward, but the complexity and risk is just below the surface. Below is an example of a more advanced configuration. In this example, a user selects a source (scenario, year, period, account) combination and a target (scenario, year, period) combination. This allows whole years to be copied, or individual periods. It also allows individual accounts, or a whole income statement. This also has options for which cube to version data and how many months should be copied. This screen is very flexible and configurable. This discussion will focus on SQL Server Analysis Services implementations. Versioning in Essbase is handled differently.

How Its Supposed To Work

This is how versioning should work for users.

• User selects the source and target information
• User clicks calculate
• User can run reports and see the target data updated

Here are some of the common challenges that muddle this user experience

• Users need to wait for a nightly process to run before seeing their versioned data
• The webpage times out and users see errors
• The webpage takes a long time to run and users are not sure if the process completed or not
• The screen goes white while running and users are not sure if the process has crashed
• Line item detail is not included when versioning
• Capital or Human Resources detail is not included in versioning

Clarity Versioning Tool Screenshot

Versioning Algorithm

When implementing versioning for a client, here are the steps that need to be done to ensure a successful implementation and happy client.

• Discuss the expectations with the client to uncover which parameters should be displayed
• Update the Clarity Systems\ClarityServer\Web\Versioning\versioning.xml file to display those parameters and pass them to an underlying stored procedure (dbo.STP_VERSIONING)
• Write a stored procedure to do the versioning, typically dbo.STP_VERSIONING with the following pseudocode. This could also be done using an SSIS package, but the pseudocode would be the same.
• Check Parameters
• Make sure source and target are different
• Make sure target is not in an archive
• Strip off any MDX style formatting
• Clear Target
• Delete data from the target combination in 
• Each cube fact table
• Each module table (HR/Capital)
• Line Item Detail
• Copy Data
• Copy data from the source combination to the target combination in each of the above tables
• Start a Job to Reprocess The Cube
• At the end of the stored procedure, use something like the following to start a job that ONLY reprocesses the cube. This DOES NOT run the full nightly process. It only performs the ProcessFull process operation on the Analysis Services cube. If this is Essbase
  

  EXEC dbo.sp_start_job N'ProcessCube' ;

Following these steps, versioning will work fine and users will have a positive experience.

Versioning Options and Concerns

As the screen above shows, there are lots of options to consider. Here are some things to discuss with your implementation team to make sure you get all you can from this feature. 

• What is the timing? How long will versioning take?
• What about notifications? How do I know when it's done? ...failed?  ..emails?
• What do I do if I see an error? How can I troubleshoot this? What are my next steps?
• What grain of versioning do we want? OLAP? Capital? HR? LineItemDetail? Custom Tables?
• Who should have access to this tool?
• How can I make a backup first, in case I make a mistake?
• How can I restore a backup, after I've made a mistake?
• Should we use a stored procedure or a SSIS package?
• Should I notify users before running this? ...who? ...how?

If you would like help with your current Clarity implementation, please feel free to reach out to us and we can help. (support@clearinsight.ca)