Speeding up Clarity with Analysis Services Compression

Overview

Some Clarity implementations use Microsoft Analysis Services as the OLAP engine. For those implementations, there are some system settings we can enable to speed things up. These should only be enabled in a production environment. Development environments where objects are changing should not have any caching or compression enabled.

Implementing Compression in Analysis Services

Below are the steps to implement caching and compression in Analysis Services.

Step 1 - Open SQL Server Management Studio

This should already be installed on your server. When you open the program, be sure to select 'Analysis Services' as the server type. We want to connect to the OLAP engine, not the database engine.

Step 2 - Open Service Properties

After connecting, you should be able to right click on the server name and select properties. This will open the service properties. If you do not have this option, you probably do not have administrative privileges on the service. By default, the only person who has administrative privileges on this service is the user account through which the service runs.

If you need to check which account this runs as, it can be seen on the Services control panel. In this system the service runs as Local System. It is also common for this to be a domain account with administrative privileges on both the application server and the database server.

Step 3 - Change Settings

Here are the recommended settings to improve caching and compression.

Network \ Requests \ EnableCompression         true
Network \ Responses \ EnableCompression       true

Step 4 - Restart the Analysis Services service

Since we've made changes to settings, they will not take effect until we restart the service.

Step 5 - Check that everything still works

Compression can have unintended side-effects in some environments. Test a few reports and templates to make sure they still retrieve and save data normally. If everything is fine, that's great. If anything is not working, just put the settings back the way they were and restart the service to rollback.

Conclusion

Some Clarity implementations on Analysis Services can benefit from enabling compression at the server level.

Speeding up Clarity with IIS Compression

Overview

Clarity is a web application, so web server compression can speed things up a bit for end users. By enabling web server compression, Internet Information Server (IIS) compresses all files sent to the client. If there are folks connecting on slow connections, this can really help. This is something that needs some baseline testing first. In some implementations, enabling compression can actually slow things down.

Implementing IIS Compression for Clarity

Below are the steps for implementing Internet Information Server server-side compression to support faster access over limited bandwidth connections to the Clarity server.

Step 1 - Baseline Test

Before doing anything, pick a long running report that returns a big dataset. Run this from a user's workstation and time how long it takes for the report to appear after selecting page options. This is now our baseline for timing performance.

Step 2 - Check current compression settings

From the server, review and run the following batch file to check what the current settings are, and if you have the same version of IIS running.

HttpCompressionSettingsCheck.bat

@ECHO OFF
ECHO.
ECHO Checking settings for IIS6 HTTP Compression
ECHO.

cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/deflate/HcFileExtensions 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/deflate/HcDoStaticCompression 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/deflate/HcDynamicCompressionLevel 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/deflate/HcOnDemandCompLevel 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/deflate/HcScriptFileExtensions 

cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/gzip/HcFileExtensions 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/gzip/HcDoStaticCompression 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/gzip/HcDynamicCompressionLevel 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/gzip/HcOnDemandCompLevel 
cscript C:\Inetpub\AdminScripts\adsutil.vbs get /w3svc/filters/compression/gzip/HcScriptFileExtensions

ECHO.
ECHO.

ECHO Changes completed. Do an iisreset for changes to have effect!
ECHO.
PAUSE

Step 3 - Enable compression

From the server, review and run the following batch file to turn on the maximum level of compression.

HttpCompressionSettings.bat

@ECHO OFF
ECHO.
ECHO Adding "Best practice" settings for IIS6 HTTP Compression
ECHO.

cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcFileExtensions "css" "htm" "html" "txt" "js" "rdf" "vbs"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcDoStaticCompression "TRUE"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcDynamicCompressionLevel "9"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcOnDemandCompLevel "9"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcScriptFileExtensions "aspx" "asmx" "asbx" "ashx" "axd" "php" "asp"

cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcFileExtensions "css" "htm" "html" "txt" "js" "rdf" "vbs"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcDoStaticCompression "TRUE"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcDynamicCompressionLevel "9"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcOnDemandCompLevel "9"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcScriptFileExtensions "aspx" "asmx" "asbx" "ashx" "axd" "php" "asp"

ECHO.
ECHO.

ECHO Changes completed. Do an iisreset for changes to have effect!
ECHO.
PAUSE

Step 4 - Restart IIS

At this point, we need to restart IIS. This can be done many ways. One way is to run this command line as an administrator.

iisreset

Step 5 - Test against the baseline
Now that we have compression enabled, run the same test as before. Compare the time of the report from before and after to see if compression has had any effect. Be sure to run it a few times though. The first time, may run the same time or slower, but subsequent runs should be faster.

Step 6 - Decide if you want to rollback or not

At this point, the system could be faster, slower or unchanged. If you want to rollback and put things back the way they were, here is a batch file to set everything back to the default settings. If you do not want to rollback, then we are done.

HttpCompressionSettingsUndo.bat

@ECHO OFF
ECHO.
ECHO Reverting "Best practice" settings for IIS6 HTTP Compression to Default
ECHO.

cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcFileExtensions "htm" "html" "txt" 
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcDoStaticCompression "FALSE"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcDynamicCompressionLevel "0"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcOnDemandCompLevel "10"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/deflate/HcScriptFileExtensions "asp" "dll" "exe" 

cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcFileExtensions "htm" "html" "txt" 
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcDoStaticCompression "TRUE"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcDynamicCompressionLevel "0"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcOnDemandCompLevel "10"
cscript C:\Inetpub\AdminScripts\adsutil.vbs set /w3svc/filters/compression/gzip/HcScriptFileExtensions "asp" "dll" "exe" 

ECHO.
ECHO.

ECHO Changes completed. Do an iisreset for changes to have effect!
ECHO.
PAUSE

Conclusion

As a web application, Clarity can benefit from some internet information server configuration tweaks, like enabling compression. Above are steps to check current compression settings, enable compression and rollback changes. As these are administrative tasks, there are risks involved. If you would like assistance with any of these items, please reach out to us at http://www.clearinsight.ca and we will be happy to help.

Virtualizating an IBM Clarity Server

Virtualize Clarity?

   Recently, I worked with a client whose IT department was going through the process of virtualizing physical servers into the cloud. For them, moving to the cloud would reduce their maintenance effort, and provide a safe off-site location for all their data. Their IBM Clarity servers were physical boxes located in their office datacenter. In this case, the client used Azure, but SoftLayer is another good option. For these folks, virtualizing was the strategic decision.

Experiment First

   As an experiment, they shutdown the development server, took an image of it, and uploaded it to Azure. This required a few hours of downtime for the whole thing, but it was all planned, so users knew what to expect. The corporate network was setup so any of their Azure servers still appear as a regular server in their domain. There were no VPN clients required to access the server. When the virtual machine started up, it came online and just worked. The server still had the same machine name, same domain and same corporate IP. When we first logged into the box and ran through some of the application, we found the speed picked up too. Through testing, we identified disk access times were 75% faster. Making backups, restoring backups and copying large folders all seemed much faster now. 

Pros vs Cons

   In general, there are some really nice benefits to virtualizing any environment. Here are some to think about. Should you do it? Well, it depends. It depends on how you view some of these pros and cons. 

Pros	Cons
Everything is off-site, so it's safer from a disaster recovery perspective Less internal IT maintenance required Monthly expense cost vs upfront capital cost for hardware Increasing CPU, RAM and disk resources is really fast and easy	Less control of the server Data may be located in a different country More internet traffic from the corporate network to get to the cloud servers Dependency on internet connections

User Experience

  From a user's perspective, we were curious how they would experience the change. Since Clarity is a web-based planning tool, many users just access the website. Their bookmarks still worked. Their network credentials still worked. They could still perform all their same actions. Saving was the same. Retrieving was faster. The most important observations were that it worked, and performance was the same or better.

Conclusion

  Whether to virtualize a server or not has many considerations. For these folks, it worked very well. Any organization considering this should think about these questions:

• Are there strategic reasons for or against? 
• Are there monetary reasons for or against?
• Are there legal reasons for or against?

   From a technical perspective, I think there are strong technologies out now to support server applications living in the cloud. In a day when many people work remotely, do we really care where the server lives? IMHO, as long as it works and we can get our jobs done servers can be virtualized.

Only Send Changed Rows - Why do I care?

What Does This Do?

  Recently, a client was trying to figure out why a template wasn't working and it came down to just one checkbox, the Only Send Changed Rows checkbox. By default, this is checked when you start building a template, but what does it mean, and why do I care? If we are working with a grid of 100 accounts, showing the projections for each month of the year (12 months), we have 1200 cells that could be updated (12 months * 100 accounts). If we change the projection for only one account in one month, then only one cell is being changed. When saving, we need to send data for cells back to the database. If we only changed one cell, sending one cell back is faster than sending 1200.

When Would I Change This?

  This sounds like something that should always be enabled, right? Nope. There is a very specific, but common, case when this checkbox needs to be disabled. Imagine you are building a template where folks enter driver projections for their budget. Maybe they enter how many units they expect to sell over each of the next 12 months. In a hidden range, there are formulae that take the driver and multiply it by standard rates to figure out ancillary revenue or additional costs to be budgeted. If a user changes a driver in the top section, Clarity knows that rows has changed and marks it correctly. Clarity does NOT know the values in the hidden section have changed, so those never get saved back to the cube. This creates a very frustrating experience for the person developing this report. It appears that the saving just isn't working. The numbers from the hidden section never get updated in the cube. The Excel formulae are perfect. The only challenge is that Clarity doesn't know it needs to send these cells back to the cube. If you look at the olap datamap properties page, shown above, and ever see the combination shown for BudgetNR, you NEED to disable this feature. The combination of Allow Update and Preserve Formulas requires that the Only Send Changed Rows feature be disabled. This is when we uncheck the Only Send Changed Rows checkbox. This auto-magically fixes our issue and everything works as expected. The calculated values in our hidden range are saved back to the cube and we are done.

Any Downside?

   The only downside is that each time this template saves, it MUST send back every cell. In our example above, we are sending back all 1200 cells to be saved every time. For 1200 cells, the difference won't even be noticeable. Over 10,000 cells, the template will start feeling clunking and quite slow. As a rule of thumb, templates should take less than 3 seconds to save. From my experience, 3 seconds is about the amount of time someone can wait without losing their train of thought, or losing their feeling of flow. When our train of thought or flow is lost, we easily get frustrated. As with all template development, make sure you only show the cells you need, use bottom level leaves and avoid making people scroll around the screen. Following those simple principles will always lead to happier users, and fewer support calls.

Restarting COM Objects

Clarity uses Component Object Model (COM) objects to handle all the server-side operations. This includes querying, saving, configuring and more. For a variety of reasons, the COM objects can get confused. Often times, the system can go right back to normal if the COM objects are restarted. There are a couple of ways to do this.

IISRESET

By running iisreset, as an administrator, from the command prompt, the Internet Information Server (IIS) service will be stopped and restarted. Since the COM objects live inside IIS, this effectively stops and restarts them. For more information on this, here is an IBM article.

Restarting the COMs

If iisreset doesn't work, restarting the COMs directly may help uncover the issue, or just magically fix it. To restart the COMs, you can follow these steps.

• Open Start..Administrative Tools..Component Services
• Navigate COM+ Applications node on the left work pane, as shown below
• Right-click on ClarityQueuedComponents and select Shutdown
• Right-click on ClarityComponents and select Shutdown
• Right-click on ClarityComponents and select Start
• Right-click on ClarityComponents and select Start

At this point, the server should be working again. You may have encountered an error message that will lead you towards the real reason Clarity isn't working. The properties of these two objects can also be tweaked to improve performance in a variety of ways. Turning on queuing is one such optimization.

What Clarity Version Do I Have?

If you ever ask IBM Support or a consultant for help with Clarity, they will probably ask you, "What version of Clarity are you using?". This is meant to be a simple question, but for folks that don't know how to find the version, it can be a bit confusing. Here are some simple ways to identify which version of Clarity is installed on a given server.

Mouseover on the application title text

If you login to a Clarity application, you'll usually see some text describing the application on the top left of the screen. By default, this says, "Clarity 7". Often times, implementation teams will update this to a client's logo, or add the word 'Dev' to denote the development server. This helps users visually see which server they are using. By holding the mouse over this text, the exact version number of the Clarity product will be displayed, as shown above.

Properties of an Clarity DLL file

Sometimes, using the mouseover will not work, if the text is replaced by an image or logo. In that case, we can still find the Clarity version by looking at the properties of any Clarity.* DLL in the Clarity Systems\ClarityServer\Web\bin folder. 

• Open Clarity Systems\ClarityServer\Web\bin
• Right-click Clarity.ActivityTracker.dll and select properties (Any Clarity...DLL will work)
• Select the Details tab
• Look at the Product version property on the details tab

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)