ProClarity 6.3 SP3 reached RTM on Friday, October 9, and is now available on the Microsoft Download Center:
· ProClarity Analytics Server 6.3 SP3 (ENU). This package includes the components for updating ProClarity Analytics Server 6.3 and for distributing an updated version of ProClarity Professional 6.3.
· ProClarity Desktop Professional 6.3 SP3 (ENU). This package includes the components for updating ProClarity Professional directly on a local computer.
· ProClarity 6.3 SP3 documentation, see: ProClarity SP3 Readme and ProClarity SP3 Hardware and Software Requirements
ProClarity 6.3 SP3 is being released in 17 languages (including English). The localized products will be released by tier, beginning in mid-November. All languages are being released to the following channels:
· Microsoft Download Center. Localized products will be available beginning in mid-November.
· Volume licensing (VL). The English version will be available in early December; localized products will be available in early January.
· MSDN. All languages are expected to be available by mid-December.
Microsoft Office PerformancePoint Server 2007 SP3 will be released to manufacturing this Thursday, October 15.
Many thanks to everyone who helped with this release!
What is ProClarity’s metadata store? Metadata store is a fancy name for they way ProClarity products store data about your data. In this case, your data is your Briefing Book pages and your My Views pages. The data about your pages includes information such as the SSAS servers, databases, cubes, attributes, hierarchies, and members queried when the page is executed. This metadata is stored in local Briefing Books – in both .BBK format and the more accessible .XML format. Also, it is stored in the ProClarity Analytics Server respository database in the XMLData fields in the MDStores and BookElements tables. See the Analytics Server Administration Tool Help file for more information.
The easiest way to investigate the metadata is to save a local Briefing Book and select the .XML format when doing so. You’ll get at least three files when you do this. One file describing the entire book - <bookName>.xml, on file for each page in the book, <bookName>_BookPage.xml, and <bookName>_MDStore.xml. Subsequent pages will be appended with incrementing whole numbers – BookPage1, BookPage2, etc.
When ProClarity Professional opens the page, it will try to organize the information to execute the query and will do so based on the contents of _BookPage.xml. For example, Pro might try to attempt to retrieve [Account].[Accounts].DEFAULTMEMBER and its associated dimension and hierarchy objects based on IDs (MDID).
- <<Axes>
- < <Background>
< <SortTuple Tuple="Yes" />
- < <Dimensions>
- < <Dimension Name="[Account]" MDID="0">
< <Hierarchy Name="[Account].[Accounts]" MDID="1" />
- < <MemberSet>
- < <Member>
< <Member Name="[Account].[Accounts].DEFAULTMEMBER" MDID="2" />
</Member>
ProClarity then works with both the BookPage and MDStore files to validate objects such as this member against the current cube/session. The OnMetaDataCannotFind event will be fired and the Missing Data Wizard will appear for each item that cannot be validated. The metadata store works to minimize the disruption of missing objects by trying to limit the number of times the user has to work with the wizard.
Thank you to Steve Caminiti for your help!
-Joey
Last November I wrote a post about an error when trying to connect ProClarity Professional to SSAS servers. "Warning The security database on the server does not have a computer account for this workstation trust relationship."
Well, on my Windows 7 RC and RTM machines, I was getting the same error when trying to connect to some pretty clean installations of SSAS 2005 and 2008.
I checked to make sure that SSAS allowed remote settings, that both the client and the server were on the same domain, and that my account had security access to the cube. I also checked to make sure that this wasn’t the known AES aware issue.
Finally, I checked the SPNs on the SSAS boxes – there were no entries for MSOLAPSvc.3. However, I was doubtful it would help to add them – I hadn’t needed to do so in the past. I added the SPNs to both machines, JP-Data and JP-Data2 as follows:
MSOLAPSvc.3/jp-data.northamerica.corp.microsoft.com
MSOLAPSvc.3/jp-data
And,
MSOLAPSvc.3/jp-data2.northamerica.corp.microsoft.com
MSOLAPSvc.3/jp-data2
After restarting my SSAS services and rebooting my client box, everything works!
-Joey
I've updated our paper on aggregation in ProClarity 6.3. I've added information on the SCOPE_ISOLATION property.
In Analysis Services 2000, all calculations were evaluated in the same calculation pass – unless a specific solve order was specified in the calculated member definition. This means that the query scoped MDX could specify a different solve order than the MDX in a cube calculated member - allowing calculations in the query to be solved before calculations in the cube. This ability to change the order of operations gave some flexibility but also added complexity.
In SSAS 2005 the behavior was changed and cube calculations were always evaluated first – regardless of the solve order value sent with the query MDX. This resulted in some broken legacy calculations and many users wanted a way to continue to control the solve order of client side calculations. “In certain scenarios, you may want to run the session-scoped calculated members or the query-defined calculated members before you run the cube-level calculations.” http://support.microsoft.com/kb/917929.
The SCOPE_ISOLATION property, introduced in a hot fix and widely distributed in SSAS 2005 SP1, allows the insertion of solve order of the client calculated members into the cube’s MDX script when the calculated member is evaluated. http://support.microsoft.com/kb/917929.
Please see Scenario 5 in the updated paper and feel free to send us any feedback or questions.
Thank you Steve Pontello for all your help!
-Joey
In Analysis Services 2000 the vast majority of query and calculation work was done on the client machine via Pivot Table Services. This included numeric value formatting, and so the client machine locale id was taken into account when the formatting was done. This meant that a client machine with a US locale id and a client machine with an ES locale id could connect to the same Analysis Services server via ProClarity and have the values rendered on their machine in a format appropriate to their regional settings.
However, with SSAS 2005 an architectural change was made to do almost all querying and calculation work on the server. This solved a lot of issues for client applications such as ProClarity, particularly in the area of performance, but meant that value formatting was no longer based on the client regional settings, but rather on the server. This of course means that for clients connecting with ProClarity Professional, all formatting will be the same. This post will explore the options for formatting values based on client regional settings for ProClarity Professional connections to SSAS 2005.
The first option for setting the value formatting appropriate to the client locale id is to modify the grid properties in ProClarity to show either numeric or custom values. This will set ProClarity to modify the format of the value that is sent from the cube, and therefore cause regional settings to be accounted for in the application.
Another option is to use a stored procedure (also sometimes referred to as a UDF) in SSAS to set the language via script. This does require a small customization, but is implemented on the server side, and therefore the more robust option. For a sample of what this stored procedure might look like, you may refer to this post on the Microsoft Connect site. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=468521
For reference, SSAS determines the appropriate formatting based on the settings for the server in this order:
- Script (MDX query or calc script)
- Cube
- Database
- Server (in msmdsrv.ini)
- Default (US)
Though untested for this blog post, in theory it should also be possible to set formatting based on cube or database language settings, or regional settings for the server hosting SSAS. Of course this would require considerable overhead in copying cubes or replicating information between SSAS instances hosted on seperate servers, and then would require work to insure users are connecting to the appropriate server/database/cube for their client locale. It's likely that both the modification of grid properties and the stored procedure referenced above would be a much more efficient and cost effective solutions.
Thanks to Didier, Manlon, and Amanda for their work in researching the question of how formatting based on the client locale can be accomplished, and especially to Didier for his work in putting together the stored procedure example and making the post to MS Connect.
When attempting to install ProClarity Desktop Professional 6.3, you may receive the following error:
"InstallShield Wizard Completed.
The Wizard was interrupted before ProClarity Desktop Professional 6.3 could be completely installed. Your system has not been modified. To complete installation at another time, please run setup again. Click Finish to exit the wizard."
Try this:
1. Go to the C:\WINDOWS|SYSTEM32 folder and locate MSVCR80.DLL & MSVCP80.DLL
2. Rename both with a .BAK extension
3. Run the Proclarity Desktop Installer setup
4. Remove the .BAK extensions to the two files
-Joey
Customers may want to launch ProClarity Professional and customize the experience for the end users. For example, they may want to open Pro in a frame in a portal and open a specific view - hiding the toolbar, Briefing Book pane, or Open Cube options in the process.
One of the most common ways of launching a customized Professional experience is using the default.htm file located here (C:\Program Files (x86)\ProClarity\SDK\Samples\ProClarity Professional\Web\PProClarityCtrl), when installed with the Microsoft ProClarity Software Developers Kit 6.3. The advantages of this method is that it is simple and easy to understand and implement. The disadvantages is that it opens Pro using very few options and it takes more effort to customize the way Pro is opened.
Here is a simple default.htm that opens a local .BBK Briefing Book file. C:\Users\joprue\Documents\temp.bbk. Soon, I'll be posting a sample file that opens a view from PAS and hopefully automatically closes the Briefing Book pane after opening.
-Joey
<
HTML>
<
HEAD>
<
META name=VI60_defaultClientScript content=VBScript>
<
META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<
TITLE></TITLE>
</
HEAD>
<
BODY>
<
OBJECT classid=clsid:F97F35F6-5E4B-4C39-B588-9D94AC7796F6 id=KProClarityCtrl1 height=100% width=100%>
</
OBJECT>
<
SCRIPT LANGUAGE="VBScript">
Sub Window_OnLoad()
With KProClarityCtrl1
.StartupBook
"C:\Users\joprue\Documents\temp.bbk", "", "", "", "", ""
.ToolbarVisible =
False
.MenubarVisible =
False
.StatusBarVisible =
False
End With
End Sub
</
SCRIPT>
</
BODY>
</
HTML>
See attached Word document for some good information on ISA server, Kerberos and ProClarity.
Sean Flanagan [MSFT]
"Summary
One of the most common case subjects at Microsoft support is with regard to permissions being passed from one server to another in a distributed environment. Security best practices recommend that company web servers be separated from database servers, domain controllers and other core service servers. The separation of roles to different servers presents the challenge of authenticating to a chain of those servers. By far, Kerberos is the method of choice for delegating credentials in a distributed environment. It is also the only protocol in a Microsoft Windows Active Directory environment capable of passing credentials over a second hop (that is, over a connection from a middle tier server to one or more back end servers when the connection to the middle tier is initiated by a third client machine). By design, Kerberos has a number of security restrictions which make it difficult to use outside of a trusted domain or forest. Microsoft Internet Security and Acceleration Server (ISA Server) can effectively extend the boundaries of Kerberos Delegation outside the domain so users accessing the company website over the Internet (without a VPN) can take advantage of Kerberos Delegation and not be prompted for credentials at each ‘hop’ in the environment. ISA Server can do this by taking advantage of Kerberos features which do not require a Ticket Granting Ticket (TGT) from the client workstation to access resources."
The bottom line on connecting to SSAS is that you need some sort of trusted Windows authentication. When you don't have a domain, this means that you will need to use Pass-Through authentication. You will need to have matching usernames and passwords on both the client machines and the SSAS box.
In ProClarity Desktop & Web Professional, there is an Advanced section that can be used to supply credentials when the current Windows credentials are not trusted by SSAS. In a test where my client machine was on a domain and my SSAS server was not, I could not connect using the Advanced section. If you cannot configure a non-domain or domain/non-domain environment with SSAS, you may want to investigate HTTP cube access or contact the SSAS support group.
Here are some of the errors you might see when trying connect to a non-domain SSAS instance while not using trusted Windows authentication (domain or Pass-Through):
"Warning
Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<serverName>' server"
"Warning
An error was encountered in the transport layer"
On a side note, when you try to connect to a named SSAS instance without including the instance name, such as server\ instead of server\instance, you may see this error:
"Warning
The following system error occurred:"
-Joey
In version 6.3.2217.x (SP2) we shipped ProClarity Desktop and Web Professional with the ability to use the SSAS OLE DB provider 10.0 (SSAS 2008). By default, the 9.0 is installed and should remain as the default setting. But, if 10.0 has been installed on the client and is used to connect and build views, then it will cause problems if the Missing Data Wizard is later used in conjunction with the view. To avoid this, please switch back to the 9.0 provider. The 9.0 provider will provide connectivity to both SSAS 2005 and 2008.
Thanks,
-Joey
A few users on Windows Vista have been having trouble installing ProClarity Desktop Professional. The users are prompted to update Microsoft Windows Installer to version 3.1 v2, reboot, and then they see the request to update the Installer again.
The workaround is simple, just run Setup.exe from InstallationPackLocation \Desktop Install\Install
For more information, please see this Microsoft KB article: http://support.microsoft.com/kb/934072. It's unknown if this is an issue on the new Windows 7 beta - but, we'll be on the lookout for this issue on current and future Windows 7 releases. (On a side note, I love Windows 7 - nice work Windows team!)
Thank you TJ for letting us know this item needed extra attention!
-Joey
With ProClarity and SQL Server 2005 SP2, users may experience slower than expected query results in some circumstances. One example is when users want to filter out empty cells while using totals. The WITH MEMBER clause used to calculate totals on the fly is treated in a certain way by SSAS 2005. 2005 It sees this query scoped, on-the-fly member and switches to a "naive" type of non empty filtering. This type of filtering is less efficient than other methods due to the fact it has to check every cell for the presence of data.
What are the options?
- Educate users.
- Optimize your cube - such as limiting the number of members, partitioning data, etc.
- Use SQL Server SSAS 2008 - it can use intelligent nonempty filtering much more often than SSAS 2005. For more information on the improvements, seethe section titled: "MDX Query Performance: Block Computation" at this link: http://msdn.microsoft.com/en-us/library/cc278097.aspx.
- Change the query in the ProClarity Professional MDX editor to use NONEMPTY and execute - (remember that you will lose this NONEMPTY keyword if it is not specified in the MDX editor before every query).
A BIG thank you goes out to Steve Pontello for his help on this issue.
-Joey
This is an updated version of the ProClarity and Kerberos Delegation instructions. Please let us know if you have any feedback or questions!
-Joey
We are all very excited about ProClarity being supported on the 2008 server platform. Server'08 and IIS7 have the unique ability to run both 32 and 64 bit web applications side-by-side. This will help a lot of companies to save costs. For directions as well as some issue fixes we found when testing SSRS 2005 and PAS together, you will want to take a look at the attached document.
Sean
Please see the attached document for information on the latest Microsoft ProClarity 6.3 SP2 requirements.
-Joey
