Lessons Learned: Deploying ProClarity 6.3 on SQL Server 2008 Analysis Services

  • Article

Author: Benjamin Wright-Jones
Contributors: David Brown, Akshai Mirchandani
Technical Reviewers: Steve Pontello, Alessandro Recino

This article describes the lessons learned as a result of a deployment using ProClarity Web Professional and ProClarity Analytics Server 6.3 with SQL Server 2008 Analysis Services. This also includes issues encountered during an in-place upgrade of SQL Server 2005 Analysis Services to SQL Server 2008 Analysis Services.

The following issues were observed during a deployment in a pre-production environment.

1. Last Processed (SSAS) vs. Last Updated (ProClarity)

SQL Server 2008 Analysis Services and ProClarity both report a date and time when the cube was last processed. For the purposes of this article, it is important to remember that SQL Server 2008 Analysis Services returns Last Processed whereas ProClarity returns Last Updated in the Desktop Professional client.

During the upgrade it was noted that there was a discrepancy in the processing time shown by both SQL Server 2008 Management Studio and the ProClarity Desktop Professional client.

The Last Updated time in the ProClarity client was showing a time which did not correlate with the Last Processed Time for the cube in SQL Server 2008 Analysis Services. 

The screenshot below is from the ProClarity Desktop Professional Client 6.3. Note the Last Updated time for the Adventure Works cube is 19/04/2010 13:57

image 

This screenshot below shows the properties in SQL Server 2008 Management Studio of the Adventure Works cube with a Last Processed time of 19/04/2010 11:04:39.

image

On further inspection, it transpires the Last Processed time for Adventure Works cube does in fact match the Last Updated time in the ProClarity client. 

The problem exists due to the way both applications report the processing time.  Processing an individual partition in SQL Server 2008 Analysis Services does not cause the Last Processed time for the cube to be updated (this is only shown for the individual partition) however the ProClarity client does show this processing time at the top level. 

The screenshot below shows the Last Processed time for the Reseller_Sales_2001 partition in the Adventure Works cube. This time correlates with the Last Updated time shown in the ProClarity client.

image

When ProClarity Desktop Professional initially connects to SQL Server 2008 Analysis Services, the application requests metadata using the MDSCHEMA_CUBES rowset which returns metadata for the cubes within a database. This can be viewed in a Profiler trace using the Discover Begin and Discover End event classes. The definition of MDSCHEMA_CUBES rowset is defined as follows:

LAST_DATA_UPDATE

DBTYPE_DBTIMESTAMP

The time that the cube was last processed.

Please refer to https://msdn.microsoft.com/en-us/library/ms126271.aspx.

The following XMLA code was executed, using MDSCHEMA_CUBES rowset in order to confirm the value returned from Analysis Services.

 <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

  <RequestType>MDSCHEMA_CUBES</RequestType>

  <Restrictions/>

  <Properties>

    <PropertyList>

      <Catalog>Adventure Works DW 2008</Catalog>

    </PropertyList>

  </Properties>

</Discover>

Note: XMLA returns the datetime in UTC as per the XML specification https://www.w3.org/TR/xmlschema-2/#dateTime.

According to the MDSCHEMA_CUBES definition, the </LAST_DATA_UPDATE> value refers to the ‘the time that the cube was last processed’ however this does not match the value shown in the cube properties dialog in SQL Server Management Studio.

What can we conclude from this?

ProClarity Desktop Professional 6.3 uses XMLA MDSCHEMA_CUBES rowset to return the data and time for the Last Updated value which takes into consideration the datetime for a processed partition or any data update i.e. the </LAST_DATA_UPDATE> returns the date and time that any data in the cube was last updated.

In comparison, the cube properties, shown in SQL Server 2008 Management Studio, shows the Last Processed time when a full process was completed for the cube. This behaviour is the same in SQL Server 2008 R2.

Definition

SQL Server Management Studio: Cube properties: Last Processed

Datetime when the cube a FULL process was occured

MDSCHEMA_CUBES: LAST_DATA_UPDATED

Datetime when any data in the cube was updated.

Any partition processed, or cache cleared due to real-time processing would cause this timestamp to be changed

ProClarity Desktop Professional 6.3

See MDSCHEMA_CUBES LAST_DATA_UPDATED

2. ProClarity: Web page looping back to the list of Briefing Books

The second issue we encountered occurred when the ProClarity web page, which contains the list of Briefing Books, was not functioning as expected.  Selecting a book was returning the user to the root level in the web page when in fact it should load the list of reports.  Initial impressions suggested that this did not appear to be an Analysis Services issue but rather a configuration issue so we stared with the IIS server which was hosting the virtual website for ProClarity Analytics Server.  We discovered that the NTFS permissions on the inetpub\wwwroot sub folders were not correct.  In our instance, this was hosted on E:\Apps\inetpub\wwwroot. Fortunately, the correct list of permissions are listed in the following knowledge base article https://support.microsoft.com/kb/927947 List of the NTFS permissions that ProClarity Analytics Server requires, and also in the ProClarity Analytics Platform 6.3 installation guide https://office.microsoft.com/download/afile.aspx?AssetID=AM103875331033 which mentions this KB specifically under Installing ProClarity Analytics Server 6.3 on Windows Server 2008 (SP2 or R2). 

3. Upgrading to SQL Server 2008 Analysis Services

The following procedure was used to perform an in-place upgrade from SQL Server 2005 Analysis Services to SQL Server 2008 Analysis Services. SQL Server 2008 Service Pack 1 and Cumulative Update 6 were merged into the source files in order to reduce time required for the update. Please refer to the following knowledge base article for further information: How to update or slipstream an installation of SQL Server 2008 https://support.microsoft.com/kb/955392.

The steps we followed are listed below.

Pre-requisites
  1. Ensure your login has local administrative rights on the server
  2. Ensure either BUILTIN\Administrators or your login is listed as a member of Analysis Services server administrator role (see important note below)
Installation Steps

  1. Launch the SQL Server 2008 installer

  2. Install software installer pre-requisites

    • Microsoft .NET Framework 3.5.1
    • Microsoft Windows Installer Update (this is included by default on Window Server 2008 R2)

  3. Re-boot the server

  4. Run the SQL Server configuration checker to ensure the pre-requisites are met

  5. Ensure that the SQL Browser service is running prior to starting the upgrade otherwise the upgrade will fail.

  6. Select the ‘Upgrade from 2005 to 2008’ option

If the following error is encountered, restart the upgrade.

image

  1. Install SQL Server 2008 Service Pack 1 (if required)

    • This may require a re-boot prior to installation if there are locked files (however it can usually be avoided by stopping Windows Management Instrumentation wmipsrv.exe to complete the update, either via the services.msc console or via the command line e.g. net stop winmgmt)

  2. Install SQL Server 2008 Service Pack 1 Cumulative Update 7 (if required)

  3. Validate the upgrade was successful by a) checking the log files and b) connecting to the Analysis Services instance and confirming the build version number.

 

Important Note

If your login is not a member of the SQL Server 2008 Analysis Services server role or the BUILTIN\Administrators has been removed then unexpected errors may occur during the upgrade. For example, we encountered the following dialog during the first upgrade attempt.

image

An inspection of the error log in the Setup Bootstrap folder contained some further information.

2010-04-28 14:37:13 AS: Warning: Error occurred while trying to fill up folder values. Exception: The 'ServerProperty' with 'Name' = 'DataDir' doesn't exist in the collection.

This did not appear to be a known issue and did not occur in other environments so we checked the permissions and discovered that we were not able to view the Analysis Services server properties in SQL Server 2008 Management Studio.

In an environment where there is a clear separation of duties i.e. Windows and Database Administrators, and BUILTIN\Administrators has been removed then your login must be explicitly added as a server administrator role (the screenshot below illustrates where this is configured). This will ensure that sufficient privileges are granted to perform the upgrade.

image

It should be noted that from SQL Server 2008 onwards, the setup process prompts to the administrator to specify logins for the Analysis Services server administrator role. Unfortunately, our login was a local administrator but was not a member of the Analysis Services server administrator role so the upgrade initially failed until this was corrected.