UK Consulting Links
UK Consulting Blogs
[Names to appear soon]
In some environments it is not possible to install Dashboard Designer using the normal process (i.e. allow it to install automatically when first opened from SharePoint). Some Citrix environments for example have permissions that prevent ClickOnce apps from being able to install.
In these cases people sometimes look for alternative methods (e.g. using an installer / MSI). Unfortunately, there is no MSI available for download for Dashboard Designer from Microsoft. However, it is possible to put together an alternative method for installation. The approach outlined below is one approach I have used in a couple of customer environments.
The process is in two parts:
Note – This is not an officially supported way of installing Dashboard Designer, but it has worked where I have used it in the past. If thinking of using this, you should thoroughly test it in your environment as your environment may throw up issues I haven't encountered. Also, when updates to Dashboard Designer are released (e.g. in SharePoint Service Packs) it will be necessary to repeat this alternative installation procedure to get the updated versions deployed.
This process does not install the pre-reqs for Dashboard Designer:
You should ensure these are also deployed to the target environment prior to installing Dashboard Designer.
These pre-reqs apply irrespective of whether you are installing using the normal ClickOnce method or an alternative approach (the ClickOnce installation does not install these pre-reqs).
This approach requires a machine* that you can install Dashboard Designer on in the normal way (i.e. download the Click Once app from SharePoint). On this machine, we’ll grab a copy of the Dashboard Designer files, ready to install on another machine (i.e. where the Click Once apps don’t work).
(*This machine also needs Visual Studio or another .NET SDK on to be able to use mage.exe).
We are now in a position to test the install. Note - the files simply need to be copied to the target machine.
On one machine I tested this on, the Designer crashed after the SharePoint URL was first entered. Upon reopening however, the designer was fine (and the URL had been saved correctly). This crash appeared to only happen on the very first time Dashboard Designer was opened – subsequently it always opened without any problem.
Dashboard Designer can be installed to any folder.
For the purposes of this example, assuming that we are installing to c:\Dashboard Designer
So, simply copy the above files to the following paths (no MSI is needed, no DLL registration is needed):
This process does not create the user.config file. This will normally be created automatically the first time Dashboard Designer is run. Again, on some environments this may not be possible and creating it as part of the installation process may be desirable.
When Dashboard Designer is installed using this alternative approach, Dashboard Designer will create this file at the following location (where in my case [username] is xchrisbailiss):
C:\Users\[username]\AppData\Local\Microsoft_Corporation\DashboardDesigner.exe_StrongName_wl4f4koizhxra1ieka4fa0ofam43g0lh\14.0.0.0
If creating this file manually, you also probably want to set the following settings within this file:
Author: Chris BailissTechnical Reviewers (Kerberos/Claims): James Noyce, Paul Williams
This post is part of a series of posts describing the authentication methods supported by the SharePoint 2010 Business Intelligence service applications. Please see Part 1 for an overview of this series of posts.
This post summarises the previous six posts.
The following table summarises how each of the BI Service Applications can authenticate to a SQL Server data source (Relational Database or Analysis Services – see above for test cases):
Key:
Service Application
Web Application Authentication Mode
Classic –
‘Windows’
Claims –
Windows-Claims
FBA-Claims
Excel Services
Yes: Per User + Shared
Yes: Shared Only
PerformancePoint Services
Reporting Services
PowerPivot for SharePoint
Yes: N/A
No
Visio Services
Notes:
This post describes how to test that user identity is being delegated through the BI service applications in a web application utilising FBA-Claims. It also covers some differences in the functionality that is supported by FBA-Claims.
For a third time I’ll walk through each of the BI Service Applications tested previously – again accessing them in the claims web app, only this time authenticating as a forms based user (FBA-Claim).
To state the obvious, FBA-Claims have no associated Windows identity. This section will describe the consequences of this and how it can be worked around.
Accessing the test workbook created earlier in the Claims site allows the same test to be repeated:
C2WTS is now unable to obtain a windows security token and the connection to SQL Server fails. There is therefore now no way to delegate the user identity to the back-end.
It is still possible to connect to the back-end using a credential stored in the Secure Store Service. This is configured on the connection properties in the Excel application:
Enter either a specific Secure Store Service ID (SSS ID) or select None to pick up a default credential configured in the Secure Store Service and Excel Services. The workbook can then connect to the SQL Server database using the stored, shared credential:
Accessing the test dashboard created earlier now results in:
As the error message helpfully explains, it has not been possible (for the C2WTS service) to obtain a windows identity from the non-windows claim.
To work around this again requires using a shared credential stored in the Secure Store Service. However, it is still possible to pass the identity into Analysis Services (albeit via a mechanism that is harder to work with in Analysis Services) using the PPS Data Connection Authentication option “Unattended Service Account and add authenticated user name in connection string”.
The dashboard then appears as:
The user name (in claims format) then appears in the Custom Data measure.
Reporting Services was already configured in the previous section to work with a shared account. This continues to work with FBA-Claims:
As described previously, PowerPivot for SharePoint works only with Classic-mode authentication.
Accessing the test web drawing created earlier now results in an error:
Again, this is due to there being no windows identity to authenticate to SQL Server with. Specifying a trusted shared account in the Secure Store Service allows the drawing to work, albeit again without the original user identity being delegated to the back-end:
It is worth noting that the Secure Store Service can only be used for web drawings that use an ODC file to specify the connection.
So, the FBA-Claims tests show that none of the key BI services are able to transition an FBA-Claim to a windows security token.
This may seem surprising; since the C2WTS service can obtain a windows security token, provided it is presented with a UPN claim (see MSDN). This offers the theoretical possibility that adding a UPN claim into the set of claims might allow C2WTS to magically make the BI services work.
Well, it is possible to add additional claims using a Claims Augmenter (see MSDN), including adding the UPN claim:
Unfortunately, the BI service applications will only invoke the C2WTS for Windows-Claims. Each claim is tagged with the provider that issued it. This tag is immutable, so you cannot create a UPN claim from a claims augmenter that will be usable by SharePoint.
So, no, claims augmentation is not the answer :-(
[Aside: The custom membership provider, custom role provider and claims augmenter were contained in a single assembly for development purposes. This assembly was deployed into the farm using a wsp solution, which dropped it into the WFE. It was necessary to also deploy it manually into the GAC on the the application servers – without this the service apps were failing on these servers].
Continue reading in Part 7.
This post describes how to test that user identity is being delegated through the BI service applications in a web application utilising Windows-Claims. It also covers some differences in the functionality that is supported by Windows-Claims (vs Classic-authentication).
I’ll now walk through each of the BI Service Applications tested previously – this time accessing them in the claims web app as a Windows User (i.e. using a Windows-Claim).
Uploading the test workbook created earlier into the Claims site allows the same test to be repeated:
This shows that we are able to delegate the user identity back to the SQL Server instances. Behind the scenes, the C2WTS has successfully performed a protocol transition from a Windows-Claim to a Windows Kerberos Ticket (two in fact, one for the Relational Engine and another for Analysis Services).
The first challenge in working with PerformancePoint on a Claims-enabled site is opening Dashboard Designer. Dashboard Designer (a Click-Once application) is not Claims-aware. Attempting to open it on a Claims-enabled site (even using a Windows-Claims as here) results in:
This can be worked around by extending the web application (see Technet), to enable access via Classic-mode authentication. In my lab environment, I extended the claims web app, to http://claimsext, from where Dashboard Designer can be successfully opened:
The same test dashboard as shown previously can then be built and published. This can be viewed via either the http://claimsext URL (not shown) or http://claims URL (shown below):
Again, the highlighted cells show that the current connection from PPS has been authenticated using Kerberos and that the user identity has been delegated through to Analysis Services correctly.
When opening Report Builder from a Claims-mode site, it is not possible to retrieve / save reports, data sources, etc from / into the SharePoint site. Instead, the following prompt is thrown up:
It is impossible to enter a credential into this dialog – it simply won’t go away.A similar prompt is thrown up when attempting to deploy into a Claims-mode site from Visual Studio.
As with PPS Designer, extending the web application to provider a Classic mode URL will enable both tools to access content in the site.
Uploading a copy of the report created earlier allows us to test running a report in Reporting Services. However, attempting to pass the user identity to the back-end now results in an error, e.g. attempting to test the Data Source:
Windows authentication / per-user identity delegation is not supported in SQL Server Reporting Services 2008 R2 when the web application is in Claims Mode. Instead, a shared account must be used (see MSDN). That account will either be the default trusted execution account for the server (in which case the fall back will be transparent) or the account can be configured explicitly on the data source:
Of course, the disadvantage of using a shared trusted account is that the user identity is no longer passed through to the back-end:
This restriction is due to the fact SQL Server Reporting Services 2008 R2 is not claims-aware. It runs as a separate web-server outside of SharePoint. In SQL Server 2012 Reporting Services becomes a SharePoint service application and is better suited to supporting this type of scenario.
Attempting to open a PowerPivot workbook from the Claims-enabled site initially appears successful – just after opening the workbook it appears as:
However, at this point, no PowerPivot functionality has been invoked, only Excel Services has been used, which is showing data that was cached in the worksheet. The PowerPivot service application only comes into play when Data >> Refresh… is selected from the toolbar, or the slicer selection is changed. Trying either of those actions results in:
At the current time, PowerPivot does not support Claims-enabled sites of any kind. Only web applications running Classic-mode authentication are supported.
Uploading the test web drawing created earlier into the Claims site allows the same test to be repeated:
Again, this shows we have successfully delegated the user identity back to SQL Server.
Continue reading in Part 6.
This post describes my claims-mode web application – “claims”. The functionality of the BI services accessed via this web application are described in the following posts.
After getting the portal web application running, I configured a second web application – “claims”. The claims web app uses the Claims-mode authentication provider.
The claims web app utilises the same set of service applications as the portal web app. This means it will pick up the existing working set of service applications and their Kerberos-related configuration.
Note - even though this web application uses claims, Kerberos is still relevant. This is because the back-end SQL Server instances are not claims-aware – they are geared to work with windows authentication. For outbound authentication to the back-end, SharePoint uses the Claims to Windows Token Service (C2WTS), part of the Windows Identity Foundation, to obtain a windows identity (Kerberos ticket) from the set of claims. To do this, the C2WTS performs a Kerberos Constrained Delegation Protocol Transition – hence Kerberos is still relevant.
The claims web app supports both Windows-Claims and Forms Based Authentication (FBA-Claims).
You may expect that a user authenticated with a Windows-Claim will be able to access the same functionality from the SharePoint BI service applications as a user authenticated into the Portal web app using Classic-mode windows authentication. This is in fact not always true and varies by service-application as I’ll describe in the next couple of posts.
FBA-Claims do not have a windows identity associated with them, so will obviously not be able to delegate a windows identity. Could C2WTS help here? Not really, as I’ll also describe in a later post.
Upon navigating to the claims URL, the default screen is presented where the user is asked to select the authentication method:
If Windows Authentication is selected, the user is automatically logged in based on a Windows-Claim based on their active directory account.
If Forms Authentication is selected, the user is redirected to a second login screen:
These FBA tests used a simple custom membership provider and a simple custom role provider. References:
A simple Claim Viewer web part was also created, which lists the users claims. A sample of a similar viewer can be found at MSDN.
When logged in with a Windows Claim, the following set of claims is displayed:
The UPN claim (http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn) is worth noting. The UPN claim is used by the Claims to Windows Token Service (C2WTS) to obtain a windows security token that can be delegated to systems that aren’t claims aware such as the SQL Server Relational Database Engine and Analysis Services.
When logged in with an FBA Claim, the following set of claims is displayed:
Continue reading in Part 5.
This post describes how to test that user identity is being delegated through the BI service applications in a web application utilising classic-mode authentication.
Let’s quickly walk through some authentication tests for each service application.
Note, for brevity, the tests illustrated here don’t cover all possible delegation paths (extend the tests in your own time, for example, to test delegation via PerformancePoint Services to SQL Relational Engine, via Reporting Services to SQL Analysis Services, etc).
To test identity delegation via Excel Services to the Relational Engine, first create a SQL Server view based on the SQL statement in the previous post. Then create a new Excel Workbook, connect to SQL server and create a new PivotTable based on this view. Expected results are shown in the screenshot below.
Testing identity delegation via Excel Services to Analysis Services is a little more involved. First, create a simple cube in Analysis Services (or modify an existing one, even AdventureWorks - the makeup of the cube doesn’t matter at all). Create some calculated measures based on the MDX query in the previous post. The equivalent MDX for defining measures in a cube is (paste this after the CALCULATE statement):
CREATE MEMBER CurrentCube.Measures.User as UserName();CREATE MEMBER CurrentCube.Measures.[CustomData] as CustomData()
Deploy the cube. Now, re-open the Excel Workbook, connect to Analysis Services and base a second Pivot Table in the Excel Workbook on the cube (only use the two measures created above – ignore whatever else is in the cube).
Upload the workbook into a document library in SharePoint. View it via Excel Services (after opening, you may need to select Data >> Refresh… to update the contents):
The highlighted cells (on the left show) that the current connection from Excel Services has been authenticated using Kerberos. They also show that the user identity has been delegated through to the SQL Server Relational Engine correctly.
The highlighted cell on the right shows that the user identity has been delegated to Analysis Services correctly. Given there are multiple hops and multiple protocol transitions involved, it’s a reasonable conclusion that Kerberos is working.
Performance Point Services
To test identity delegation via Performance Point Services to Analysis Services requires creating a simple test dashboard in Dashboard Designer. Use a connection configured with the ‘Per User Identity’ authentication setting. Then create an analytical view either based on the cube created/modified above or simply use the MDX query defined in the previous post.
Deploy this to SharePoint and view:
The highlighted cells show that the user identity has been delegated through to Analysis Services correctly.
By creating a report based on the SQL View described above, delegation via SSRS can be proven:
PowerPivot for SharePoint doesn’t directly connect to the back end data source when a user is viewing a workbook containing PowerPivot data. Therefore, no identity delegation tests are applicable.
To test delegation via Vision Services to the Relational Engine requires creating a web drawing and linking some shapes to external data (in this case, SQL Server). First, create a simpler version of the above SQL view that returns information just about your connection:
select s.Session_Id, s.Login_Name, s.Host_name, c.Auth_Scheme,case when c.Auth_Scheme = 'KERBEROS' then 1 else 0 end IsKerberosfrom sys.dm_exec_connections cinner join sys.dm_exec_sessions son s.session_id = c.session_idwhere c.session_id = @@SPID
When run from Management Studio, this returns a single record:
Next, create a new Visio Diagram. Link the shapes to this external data view:
Save this as a Web Drawing (*.vdw) into SharePoint, open it in Visio Services and click Refresh:
This shows that the user identity is successfully being delegated back to SQL Server.
Continue reading in Part 4.
This post describes my classic-mode web application – “portal” – and outlines some basic user identity tests for SQL Server. These tests will be applied to the BI service applications running in the two web applications (“portal” and “claims”) in the coming posts.
I configured the “portal” web app first, running with classic-mode authentication.
Although my VM environment is only for test purposes, to be more life-like, each service application is running under a different service account, granted a minimum set of permissions.
Kerberos Constrained Delegation has been configured through the service applications to the SQL Server instances (to both the Relational Database Engine instance and the Analysis Services instance).
The web application is configured to use the Negotiate (Kerberos) protocol.
Let’s spend a few moments talking about how to test that the user identity is reaching the back end systems…
[ASIDE: Since the SQL Server Relational Database Engine and Analysis Services Engine aren’t Claims aware, the tests described below are also relevant to testing that identity delegation is working for the Claims web app as described in later posts].
As stated earlier, I’m not going to spend any time on how to configure the Kerberos protocol with SharePoint. That information is available in detail at:
However, it is worth noting a couple of quick ways to test that identity delegation is working, which we’ll be using later on.
A very handy piece of SQL for testing whether Kerberos is working is:
select s.Session_Id, s.Login_Name, s.Host_name, c.Auth_Scheme,case when c.session_id = @@SPID then '<<<<<<<<<< **YOU** <<<<<<<<<<' else null end Current_Connectionfrom sys.dm_exec_connections c inner join sys.dm_exec_sessions s on s.session_id = c.session_id
This makes use of a couple of DMVs to list the current connections into SQL Server - plus a handy additional field that identifies your connection (hey, I like to be lazy efficient). Running this from different servers/clients and client applications enables a quick test of Kerberos / Kerberos Delegation paths. Listing all the connections also gives a quick feel for the types of authentication mechanisms being used at a given point in time.
[NB: this SQL statement requires that the view server state permission be granted to the caller].
For example, in my environment, running the above statement from SQL Server Management Studio on the client machine shows:
This shows that I (cblab\usertest) am authenticated against the server using the Kerberos protocol. Note – any connections from clients running on the SQL Server itself will use NTLM, by design.
We’ll use the same SQL statement again later.
Testing that delegation to Analysis Services is working is more difficult since it doesn’t expose the same level of connection detail. However, the following MDX query can help:
with member Measures.User as UserName() member Measures.[CustomData] as CustomData()select {Measures.User, Measures.[CustomData]} on columnsfrom [Adventure Works]
The User measure in this query will return the user identity associated with the query inside Analysis Services, thus showing whether the user identity has been successfully delegated. The CustomData measure shows the value that has been passed into Analysis Services on the connection string (this is client application dependent – we’ll use it later).
For example, in my environment, running the above MDX query from SQL Server Management Studio on the client machine shows:
This shows that my identity (cblab\usertest) has reached Analysis Services. Note - this doesn’t prove anything to do with Kerberos in this example (we could have authenticated using NTLM). However, when this MDX query is used via other client applications in a double-hop scenario / via a SharePoint service application, it’s a reasonable test that Kerberos Delegation is working. (If you want to be absolutely certain, turn on Kerberos logging or use NetMon).
Continue reading in Part 3.
This series of posts provides an overview of the authentication methods and associated functionality supported by the Business Intelligence Service Applications in SharePoint 2010, covering:
I have assumed that you have an understanding of SharePoint concepts such as web applications, service applications, etc.
This article isn’t going to repeat lots of detailed material available elsewhere about how to configure each of the above services.
Rather, the aim is to provide an overview of what works and what doesn’t from a BI perspective (I’m a SQL BI Consultant). Particular attention will be paid to differences in BI-functionality between the Classic-mode and Claims-mode authentication options in SharePoint, and how this supports or doesn’t support passing user identity to back end systems.
SharePoint authentication methods covered will include:
There is a lot to cover so this article will be split into a series of parts:
Screen shots will be shown to illustrate the text – they are functional, not pretty!
If you’re not interested in how the testing was carried out, skip to the summary at the end.
Examples will be shown from my scaled-out Hyper-V lab environment, consisting of:
All are running Windows 2008 R2 SP1 and SQL 2008 R2 SP1 / SharePoint Server 2010 SP1.
Wherever references are made to Kerberos within this article, this is all within the same domain.
Two web applications have been configured in this environment:
These will be described and compared in more detail in the coming posts.
Continue reading in Part 2.
Are you an experienced Microsoft Business Intelligence professional?
Have always wanted to work for Microsoft in the BI arena?
Want to work on large, complex BI projects based on the latest Microsoft BI Strategy?
Then join Microsoft on the journey of changing business insight, that journey starts here http://bit.ly/mgYUVt
New whitepapers recently released on MSDN focused on Enterprise Data Warehousing with Microsoft technologies http://msdn.microsoft.com/en-us/library/hh147624.aspx
It is possible to create custom reports, using SQL Server 2008 Reporting Services, against the System Center 2007 R2 data warehouse.
Reports can be designed in SQL Server 2008 Reporting Services to provide the end user with a better representation of the data since the built-in System Center reports are not ideal.
Fortunately the OperationsManagerDW database schema is documented on MSDN http://technet.microsoft.com/en-us/library/gg508713.aspx along with code samples which makes the task of creating a custom report a little easier.
Stored procedures were implemented to return the data from the OperationsManagerDW schema. I prefer this approach as it provides more control over the code rather than embedding logic in the report itself. Incidentally, the stored procedures were hosted in a separate database to avoid support issues embedding these in OperationsManagerDW database.
An example of the stored procedure logic to query the OperationsManagerDW is below:
1: /********************************************************************************************
2: *
3: * (c) Microsoft 2010 All rights reserved
4: *
5: * The code contained in this file is provided "as is" without any warranty of any kind.
6: * The code is for reference purposes only and must not be relied on in connection with
7: * any operational purposes.
8: * Please refer to the terms and conditions which cover the provision of consulting
9: * services to you.
10: *
11: ********************************************************************************************
12: *
13: * The following parameters are required:
14: * pSlot (nvarchar, 255)
15: * pCurrentDate (datetime)
16: *
17: ********************************************************************************************
18: *
19: * Stored Procedure Creation Script
20: * [usp_ReportPerfCounterHealth]
21: *
22: *===========================================================================================
23: * Modification History
24: *-------------------------------------------------------------------------------------------
25: * Verion Date Author Description
26: *-------------------------------------------------------------------------------------------
27: * 01.00.00 10/01/2011 B Wright-Jones Created
28: ********************************************************************************************/
29:
30: CREATE PROCEDURE [Schema].[usp_ReportPerfCounterHealth]
31: @pSlot nvarchar(255),
32: @pCurrentDate datetime
33: AS
34:
35: SET NOCOUNT ON;
36:
37: SELECT
38: [vME].[Path] AS [ServerName],
39: [vPR].[ObjectName],
40: [vPR].[CounterName],
41: [vPRI].[InstanceName],
42: AVG(SampleValue) AS Average,
43: MIN(SampleValue) AS Minimum,
44: MAX(SampleValue) AS Maximum,
45: SUM(SampleValue) AS SumOfValue
46:
47: FROM
48: [dbo].[vPerformanceRule] vPR
49:
50: INNER JOIN [dbo].[vPerformanceRuleInstance] vPRI
51: ON vPR.[RuleRowId] = vPRI.[RuleRowId]
52:
53: INNER JOIN [Perf].[vPerfRaw] vPRW
54: ON [vPRI].[PerformanceRuleInstanceRowId] = [vPRW].[PerformanceRuleInstanceRowId]
55:
56: INNER JOIN [dbo].[ManagedEntity] vME
57: ON [vPRW].[ManagedEntityRowId] = [vME].[ManagedEntityRowId]
58:
59: WHERE
60: [vPRW].[DateTime] BETWEEN DATEADD(Hh, -24, @pCurrentDate) AND @pCurrentDate -- Filter for the last 24 hours
61: AND vPR.[CounterName] = '<Performance Counter goes here>' -- Filter for the specific performance monitor counter
62: AND vPRI.[InstanceName] = '<Instance goes here>' -- Filter for the specific slot
63:
64: GROUP BY
65: [vME].[Path], [vPR].[ObjectName], [vPR].[CounterName], [vPRI].[InstanceName];
The design time report is shown below. The line graph shows specific counter values over the period of a month e.g. processor utilisation, web service requests etc. whereas the tablix controls display critical events, uptime, hits and so on for each server in the farm.
A datetime parameter was implemented in order to allow the user to select a date from the calendar control
The uptime field displays the result of a custom counter which is the number of seconds since the service was started. I implemented a vb function (shown below) on the report body to transform this into days, hours, minutes which is more readable for the end user.
The expression references the custom code using the following syntax:
=Code.SecondsToText(Fields!AppPoolUpTimeSec.Value)
The Visual Basic function to format the seconds as days, hours, minutes is below:
1: Function SecondsToText(Seconds) As String
2: Dim bAddComma As Boolean
3: Dim Result As String
4: Dim sTemp As String
5: Dim days As String
6: Dim hours As String
7: Dim minutes As String
8:
9: If Seconds <= 0 Or Not IsNumeric(Seconds) Then
10: SecondsToText = "0 seconds"
11: Exit Function
12: End If
13:
14: Seconds = Fix(Seconds)
15:
16: If Seconds >= 86400 Then
17: days = Fix(Seconds / 86400)
18: Else
19: days = 0
20: End If
21:
22: If Seconds - (days * 86400) >= 3600 Then
23: hours = Fix((Seconds - (days * 86400)) / 3600)
24: Else
25: hours = 0
26: End If
27:
28: If Seconds - (hours * 3600) - (days * 86400) >= 60 Then
29: minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)
30: Else
31: minutes = 0
32: End If
33:
34: Seconds = Seconds - (minutes * 60) - (hours * 3600) - _
35: (days * 86400)
37: If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)
38:
39: If minutes > 0 Then
40: bAddComma = Result <> ""
41:
42: sTemp = minutes & " minute" & AutoS(minutes)
43: If bAddComma Then sTemp = sTemp & ", "
44: Result = sTemp & Result
45: End If
47: If hours > 0 Then
48: bAddComma = Result <> ""
50: sTemp = hours & " hour" & AutoS(hours)
51: If bAddComma Then sTemp = sTemp & ", "
52: Result = sTemp & Result
53: End If
54:
55: If days > 0 Then
56: bAddComma = Result <> ""
57: sTemp = days & " day" & AutoS(days)
58: If bAddComma Then sTemp = sTemp & ", "
59: Result = sTemp & Result
60: End If
61:
62: SecondsToText = Result
63: End Function
64:
65:
66: Function AutoS(Number)
67: If Number = 1 Then AutoS = "" Else AutoS = "s"
68: End Function
69:
The report can be executed directly by passing parameters in the URL as documented here http://msdn.microsoft.com/en-us/library/ms155391(v=SQL.100).aspx. This was useful to test the report execution. I used two parameters, an example of this URL structure is shown below:
&rs:Command=Render&pName=Test&pDate=11/01/2011
This can be achieved both in native and SharePoint integrated mode.
The fact that the OperationsManagerDW schema is documented has made the whole process of creating a custom report a lot easier and the flexibility of Reporting Services provides a better representation of the data to the end-user.
I had unfortunately picked up the (bad) habit of debugging my SSIS packages by using messagebox.show at design time. The SSIS data flow Script component does not allow debugging using this approach so I had to find an alternative. I discovered that it is possible to output parameters at design time using the FireInformation in the Script component. This puts the parameters in the progress window.
// Output parameters into the SSIS progress window bool FireAgain = true; this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of time is " + Row.ParamValueTime.ToString(), "", 0, ref FireAgain); this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of environment is " + Row.ParamValueEnvironment.ToString(), "", 0, ref FireAgain);
I like this approach although I should probably output the parameters to one of the other console windows.
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.
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
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.
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.
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 http://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>
<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 http://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.
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
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 http://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 http://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).
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 http://support.microsoft.com/kb/955392.
The steps we followed are listed below.
If the following error is encountered, restart the upgrade.
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.
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.
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.