JohnDesch

Using DMVs to Monitor and Terminate Long Running MDX Queries

2013-05-14T02:48:00Z

At one time or other, just about every DBA charged with administering a Microsoft SQL Server Analysis Services server has encountered a long running query that seems to consume machine resources with a voracious appetite. Those queries are typically executed by "Power Users" and almost invariably at a time that other users are attempting to run queries for month end reports resulting in a flury of calls asking if the server is down or encountering a performance issue. It's also rather interesting that the person registering the complaint almost always seems to be in the office of the CFO or CEO. That scenario does happen, and as one might expect, a common request within the Business Intelligence community is for the ability to identify and terminate long running or resource intensive queries that are executing on a Microsoft SQL Server Analysis Services Server. While a query governor would be very useful, unfortunately, Analysis Services doesn't have that type of functionality built into the product. Chris Webb's article on Killing session automatically with SSIS describes one approach to the problem using DMV queries in an SSIS Package. Tim Laqua's article Dealing with long runnign SSAS queries using PowerShell-SSAS is based on the same principle, but uses an unsupported API (Microsoft.AnalysisServices.XMLA.dll).

When a query is executed, significant amounts of memory can be allocated on the server, with much of the data going into a shared cache. The query may also be using data that is already resident in a shared data cache, so the task of determining how much memory is actually being consumed by a given query would be non-trivial. I've tried it before, and was anything but satisfied with the solution that I came up with. The conclusion that I finally reached was that the longer a query executes, especially if it's bottlenecked in Storage Engine, the more resources it's going to consume and the more users it's going to impact and the only viable option was to look at how long a query had been executing. Those efforts, however, lead me to come up with a very simple solution that is based on a single DMV query and an XMLA Cancel command.

The DMV query:

SELECT SESSION_SPID, SESSION_USER_NAME, SESSION_LAST_COMMAND, SESSION_LAST_COMMAND_ELAPSED_TIME_MS FROM $SYSTEM.DISCOVER_SESSIONS ORDER BY SESSION_LAST_COMMAND_ELAPSED_TIME_MS DESC

Returns the user SPID, User Name (I definitely don't want to kill a session opened by the CIO, CFO, or CEO), the last command executed, and the duration of the command in milliseconds. And I get something that looks like the following:

SESSION_SPID SESSION_USER_NAME SESSION_LAST_COMMAND                                                                                                                                                                                                                                                                                                SESSION_LAST_COMMAND_ELAPSED_TIME_MS
4255                    NORTHAMERICA\jdesch SELECT measures.members on 0, ([product].[product categories].members,[customer].[customer geography].members) on 1 from [Adventure Works]                                                                                                                                                                    11928 4239                    NORTHAMERICA\jdesch SELECT SESSION_CONNECTION_ID, SESSION_USER_NAME, SESSION_LAST_COMMAND, SESSION_LAST_COMMAND_ELAPSED_TIME_MS FROM $SYSTEM.DISCOVER_SESSIONS order by session_last_command_elapsed_time_ms desc 0
3707                    NORTHAMERICA\jdesch MDSCHEMA_FUNCTIONS                                                                                                                                                                                                                                                                                                                                                                        0
3703                    NORTHAMERICA\jdesch MDSCHEMA_FUNCTIONS                                                                                                                                                                                                                                                                                                                                                                        0

SPID 4239 is me executing my DMV query, so I don't want to have my connection commit suicide. I am, however, interested in what's happening with SPID 4255. Armed with that information, I can now execute an XMLA cancel command to terminate that particular connection and its associated session by executing the following:

<Cancelxmlns=http://schemas.microsoft.com/analysisservices/2003/engine>

</Cancel>

It wasn't a big jump from that to a simple C# application that uses a timer to periodically execute a single very simple DMV query against the server to check for long running queries and cancel sessions associated with queries that exceeded a configurable threshold.

Analysis Services Proactive Caching Operations and Errors

2012-12-17T23:51:33Z

One of the more interesting aspects of being involved in supporting Business Intelligence applications is the opportunity to investigate behaviors that are either unexpected or perhaps not so intuitively understood. Proactive Caching is one aspect of Analysis Services that is not well understood by some and can present some interesting issues, especially when errors related to data quality occur. A behavior that was encountered recently involved Proactive Caching entering a loop when partitions in a cube were configured for Proactive Caching using either Client Notifications or SQL Notifications and the Proactive Caching operation failed. When that situation occurs, the Proactive Caching operation is immediately re-executed then fails and continues executing in a loop. When this condition occurs, the only way to terminate the loop of Proactive Caching operations is to stop and re-start the service.

The key point to this discussion is that using the default ErrorConfiguration settings, data quality errors (i.e. orphaned fact rows, referential integrity issues, etc.) as well as other data errors will result in processing failures and will not cancel the alert that triggered the Proactive Caching process operation. While it may be seen as an unexpected behavior, this is the designed behavior of Proactive Caching. For these reasons, Proactive Caching should be used with care and closely monitored. While eliminating data quality issues in the relational source would be the most desirable way of controlling this, doing so is not always feasible. The recommended practice is to enable custom ErrorConfiguration for partitions with ProactiveCaching enabled using either Client Notifications or SQL Notifications. Custom ErrorConfiguration options would include setting the ErrorConfiguration to report and ignore errors to make sure the ProactiveCaching operation doesn't enter into a loop.

Using the RS.EXE utility to deploy a Report Server Project and Shared Dataset.

2012-12-17T19:30:00Z

Since broadening my area of concentration and branching out from Microsoft Analysis Services to the broader Microsoft Business Intelligence Stack, I've encountered quite a few new challenges and opportuities. One of the more recent challenges was how to deploy a Reporting Services project. When using BI Development Studio or, in the case of the SQL Server 2012 tool set, SQL Server Data Tools deployment is a relatively trivial matter. This deployment task, however, involved programmatic deployment of Data Sources, Reports, and Shared DataSets. It didn't take long to discover that Reporting Services ships the RS script hosting utility, which can be used for any number of administrative tasks related to a Reporting Services implementation.

The RS utility is a script hosting utility that uses VB.NET script, so if you're familiar with VB.NET and scripting, it's relatively easy to create .rss files to perform just about any task that you might want to perform. There are a number of sample scripts that are available in Books On Line and there are several examples of .rss scripts available on the web. David Elish has posted a useful .rss script for automation of some tasks. Jasper Smith has made the Reporting Services Scripter utility available. Armed with that knowledge and a false sense of security, I thought this should be easy.

Just one minor problem. Shared Datasets were new with the 2008 R2 release and there were no updated samples that shipped with that release. The familiar Reporting Services 2005 and Reporting Services 2008 classes were there, but they didn't support Shared DataSets or deployment to a Reporting Services server configured for SharePoint integrated mode. Fortunately, there was a new Reporting Services 2010 class that introduced a CreateCatalogItem method and enabled the RS utility to be used with Native mode as well as SharePoint Integrated Mode. Coincidentally, the CreateCatalogItem method can be used with Reports, DataSources, and DataSets, so after a bit of reading I was ready to code.

Note that the RS utility is run from a command prompt, and on Windows Server 2008 R2 or later, it needs to be run from a command prompt with elevated permissions. In order to run the tool, you must have permissions to connect to the report server instance against which the script will be run. What follows is the code for an RS script that can be used to deploy a Reporting Services Project, including Data Sources, Shared DataSets, and Reports, to a Report Server. Hopefully you'll find it useful.

The command line for running this script is:

rs.exe -i <path_to_script> -s <url_of_report_server> -v ReportFolder="<report_folder_name>" -v DataSetFolder="<DataSet_Folder_Name>" -v DataSourceFolder="<DataSource_Folder_Name>" -v DataSourcePath="/<DataSource_Path>" -v filePath="<Path_To_Report_Server_Project_Files>" -eMgmt2010

'Begin Script

Dim definition As [Byte]() = Nothing

Dim bytedefinition as [Byte]() = nothing

Dim warnings As Warning() = Nothing

'Main Entry point of utility

Public Sub Main()

Console.WriteLine()

Console.WriteLine("Initiating Deployment")

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Try

'Create the shared data source

CreateFolders(DataSourceFolder,"/","Data Sources","Visible")

'Create the folder that will contain the shared data sets

CreateFolders(DataSetFolder, "/", "Data Set Folder", "Visible")

'Create the folder that will contain the deployed reports

CreateFolders(ReportFolder, "/", "Report Folder","Visible")

Catch goof As Exception

Console.WriteLine(goof.Message)

End Try

ReadFiles(filepath, "*.rds")

ReadFiles(filepath, "*.rsd")

ReadFiles(filepath, "*.rdl")

'Publish the report

'PublishReport(ReportName)

UpdateDataSources(ReportFolder, DataSourcePath)

End Sub

'Utility for creation of folders

Public Sub CreateFolders(ByVal folderName as string, ByVal parentPath as string, ByVal description as String, ByVal visible as string)

Console.WriteLine()

Console.WriteLine("Checking for Target Folders")

'CatalogItem properties

Dim descriptionProp as new [Property]

descriptionProp.Name = "Description"

descriptionProp.Value= description

Dim visibleProp as new [Property]

visibleProp.Name = "Visible"

visibleProp.value= visible

Dim props(1) as [Property]

props(0) = descriptionProp

props(1) = visibleProp

Try

rs.CreateFolder(folderName,parentPath,props)

Console.WriteLine("Folder {0} successfully created", foldername)

Catch goof as SoapException

If goof.Message.Indexof("AlreadyExists")>0 Then

Console.WriteLine("Folder {0} already exists",foldername)

End If

End Try

End Sub

'Utility for reading files from the Report Sevices Project

Public sub ReadFiles(filepath as string, fileextension as string)

Console.WriteLine()

Console.WriteLine("Reading Files from Report Services Project")

Dim rptdirinfo As System.IO.DirectoryInfo

rptdirinfo = New System.IO.DirectoryInfo(filepath)

Dim filedoc As FileInfo()

filedoc = rptdirinfo.GetFiles(fileextension)

Try

For rptcount As Integer = 0 To filedoc.Length-1

If Not filedoc(rptcount).Name.ToString.Trim.ToUpper.Contains("BACKUP") Then

SELECT Case fileextension

Case "*.rds"

CreateDataSource(filedoc(rptcount).tostring.trim)

Case "*.rsd"

CreateDataSet(filedoc(rptcount).tostring.trim)

Case "*.rdl"

PublishReport(filedoc(rptcount).tostring.trim)

End Select

End If

Catch goof as Exception

Console.WriteLine("In ReadFiles " + goof.message)

End Try

End Sub

'Utility for Creating Shared Data Sets contained in the project

Public Sub CreateDataSet(ByVal filename as string)

Dim valstart as integer

Dim valend as integer

Dim DSDefinitionStr as string

Dim DataSourceName as string

Dim QueryString as string

Try

Dim stream As FileStream = File.OpenRead(filePath + "\" + filename )

definition = New [Byte](stream.Length-1) {}

stream.Read(definition, 0, CInt(stream.Length))

stream.Close()

For i As Integer = 0 To definition.Length - 1

DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(definition(i).ToString)))

valstart=DSDefinitionStr.ToString.Indexof("<DataSourceReference>")

If valstart > 0 Then

valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceReference>") + 21

valend = DSDefinitionStr.ToString.IndexOf("</DataSourceReference>")

DataSourceName=DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

Console.WriteLine(DataSourceName)

End If

Catch e As IOException

Console.WriteLine(e.Message)

End Try

filename=filename.tostring.replace(".rsd","")

Console.WriteLine("Attempting to Deploy DataSet {0}", filename)

Try

Dim item as CatalogItem

item=rs.CreateCatalogItem("DataSet",filename, "/" + DataSetFolder, false, definition, nothing, warnings)

If Not (warnings Is Nothing) Then

Dim warning As Warning

For Each warning In warnings

if warning.message.tostring.tolower.contains("refers to the shared data source") then

Console.WriteLine("Connecting DataSet {0} to Data Source {1}",filename, DataSourceName)

Dim referenceData() as ItemReferenceData = rs.GetItemReferences("/" + DataSetFolder + "/" + filename,"DataSet")

Dim references(0) as ItemReference

Dim reference as New ItemReference()

Dim datasourceURL = DataSourcePath + "/" + DataSourceName

reference.name=referenceData(0).Name

Console.WriteLine("Reference name = " + reference.name)

reference.Reference=datasourceURL

references(0)=reference

rs.SetItemReferences("/" + DataSetFolder + "/" + filename, references)

else

Console.WriteLine(warning.Message)

end if

Next warning

Else

Console.WriteLine("DataSet: {0} published successfully with no warnings", filename)

End If

Catch goof as SoapException

If goof.Message.Indexof("AlreadyExists")>0 Then

Console.WriteLine("The DataSet {0} already exists",fileName.ToString)

Else

If goof.Message.IndexOf("published")=-1 Then

Console.Writeline(goof.Message)

End If

End Try

'UpdateDataSetSources(filename,DataSetFolder, DataSourceFolder,DataSourceName)

End Sub

'Utility for creating Data Sources on the Server

Public Sub CreateDataSource(filename as string)

'Define the data source definition.

Dim dsDefinition As New DataSourceDefinition()

Dim DataSourceName as string

Dim valstart As Integer

Dim valend As Integer

Dim ConnectionString As String

Dim Extension As String

Dim IntegratedSec As String

Dim DataSourceID As String

Dim PromptStr As String

PromptStr=""

Dim DSDefinitionStr As String

DSDefinitionStr = ""

DataSourceName=filename.tostring.trim.substring(0,filename.tostring.trim.length-4)

Console.WriteLine("Attempting to Deploy Data Source {0}", DataSourceName)

Try

Dim stream As FileStream = File.OpenRead(filepath + "\" + filename)

bytedefinition = New [Byte](stream.Length) {}

stream.Read(bytedefinition, 0, CInt(stream.Length))

stream.Close()

For i As Integer = 0 To bytedefinition.Length - 1

DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(bytedefinition(i).ToString)))

Catch goof As IOException

Console.WriteLine(goof.Message)

End Try

If DSDefinitionStr.ToString.Contains("<ConnectString>") And DSDefinitionStr.ToString.Contains("</ConnectString>") Then

valstart = DSDefinitionStr.ToString.IndexOf("<ConnectString>") + 15

valend = DSDefinitionStr.ToString.IndexOf("</ConnectString>")

ConnectionString = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

End If

If DSDefinitionStr.ToString.Contains("<Extension>") And DSDefinitionStr.ToString.Contains("</Extension>") Then

valstart = DSDefinitionStr.ToString.IndexOf("<Extension>") + 11

valend = DSDefinitionStr.ToString.IndexOf("</Extension>")

Extension = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

End If

If DSDefinitionStr.ToString.Contains("<IntegratedSecurity>") And DSDefinitionStr.ToString.Contains("</IntegratedSecurity>") Then

valstart = DSDefinitionStr.ToString.IndexOf("<IntegratedSecurity>") + 20

valend = DSDefinitionStr.ToString.IndexOf("</IntegratedSecurity>")

IntegratedSec = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

End If

If DSDefinitionStr.ToString.Contains("<DataSourceID>") And DSDefinitionStr.ToString.Contains("</DataSourceID>") Then

valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceID>") + 14

valend = DSDefinitionStr.ToString.IndexOf("</DataSourceID>")

DataSourceID = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

End If

If DSDefinitionStr.ToString.Contains("<Prompt>") And DSDefinitionStr.ToString.Contains("</Prompt>") Then

valstart = DSDefinitionStr.ToString.IndexOf("<Prompt>") + 8

valend = DSDefinitionStr.ToString.IndexOf("</Prompt>")

PromptStr = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)

End If

dsdefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsdefinition.ConnectString = ConnectionString

dsdefinition.Enabled = True

dsdefinition.EnabledSpecified = True

dsdefinition.Extension = extension

dsdefinition.ImpersonateUser = False

dsdefinition.ImpersonateUserSpecified = True

'Use the default prompt string.

If PromptStr.ToString.Length=0 Then

dsdefinition.Prompt = Nothing

Else

dsdefinition.Prompt = PromptStr

End if

dsdefinition.WindowsCredentials = False

Try

rs.CreateDataSource(DataSourceName, "/" + DataSourceFolder, False, dsdefinition, Nothing)

Console.WriteLine("Data source {0} created successfully", DataSourceName.ToString)

Catch goof as SoapException

If goof.Message.Indexof("AlreadyExists")>0 Then

Console.WriteLine("The Data Source name {0} already exists",DataSourceName.ToString)

End If

End Try

End Sub

'Utility to Publish the Reports

Public Sub PublishReport(ByVal reportName As String)

Try

Dim stream As FileStream = File.OpenRead(filePath + "\" + reportName )

definition = New [Byte](stream.Length) {}

stream.Read(definition, 0, CInt(stream.Length))

stream.Close()

Catch e As IOException

Console.WriteLine(e.Message)

End Try

reportname=reportname.tostring.replace(".rdl","")

Console.WriteLine("Attempting to Deploy Report Name {0}", reportname.tostring)

Try

Dim item as CatalogItem

item=rs.CreateCatalogItem("Report",reportname, "/" + ReportFolder, false, definition,nothing, warnings)

'warnings = rs.CreateCatalogItem(reportName, "/" + ReportFolder, False, definition, Nothing)

If Not (warnings Is Nothing) Then

Dim warning As Warning

For Each warning In warnings

Console.WriteLine(warning.Message)

Next warning

Else

Console.WriteLine("Report: {0} published successfully with no warnings", reportName)

End If

Catch goof as SoapException

If goof.Message.Indexof("AlreadyExists")>0 Then

Console.WriteLine("The Report Name {0} already exists",reportName.ToString)

Else

If goof.Message.IndexOf("published")=-1 Then

Console.WriteLine(goof.Message)

End If

End Try

End Sub

'Utility to Update The Data Sources on the Server

Public Sub UpdateDataSources(ReportFolder as string, DataSourcePath as string)

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item as CatalogItem

Dim items as CatalogItem()

Try

items=rs.ListChildren("/" + ReportFolder, False)

For Each item in items

Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)

For Each ds as DataSource in dataSources

Dim sharedDs(0) as DataSource

sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)

rs.SetItemDataSources(item.Path, sharedDs)

Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")

Console.WriteLine("Shared data source reference set for reports in the {0} folder.", ReportFolder)

Catch goof As SoapException

Console.WriteLine(goof.Detail.InnerXml.ToString())

End Try

End Sub

'Function to Reference Data Sources

Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource

Dim reference As New DataSourceReference()

Dim ds As New DataSource

reference.Reference = sharedDataSourcePath & "/" & dataSourceName

ds.Item = CType(reference, DataSourceDefinitionOrReference)

ds.Name = dataSourceName

Console.WriteLine("Attempting to Publish Data Source {0}", ds.Name)

GetDataSource=ds

End Function

WebPage Error when running a parameterized report with parameters.

2012-05-16T19:34:47Z

I was recently onsite visiting one of my customers when a rather interesting issue was brought to my attention. The customer was running Reporting Services 2008 R2 in SharePoint Integrated Mode and was testing a very simple report that contained a single parameter with 485 possible values. If they selected 484 of the possible 485 parameter values, then the report rendered exactly as expected. If they selected all 485 of the possible parameter values, then Reporting Services returned an error similar to the following:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT
6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR
3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3; .NET4.0E; Tablet PC 2.0)

Timestamp: Tue, 15 May 2012 17:49:04 UTC

Message:
Sys.WebForms.PageRequestManagerParserErrorException: The message received from
the server could not be parsed. Common causes for this error are when the
response is modified by calls to Response.Write(), response filters,
HttpModules, or server trace is enabled.

Details: Error parsing near '

<!DOCTYPE html PUB'.

Line: 5

Char: 62099

Code: 0

After searching around quite a bit (with very little luck), one of my colleagues pointed me to KB article http://support.microsoft.com/kb/2661403. The error messages there didn't exactly match the error message that we were seeing, but it was close enough that we decided to test adding the following value to the <appSettings> section of the web.config file for the IIS Server:

After saving the web.config and running IISReset, we were able to successfully run the affected reports without encountering an error.

Filters aren't applied to related tables in PowerPivot unless a measure is added to the PowerPivot Workbook

2012-05-16T19:13:00Z

A colleague, with whom I work on a regular basis, recently pointed out a somewhat unexpected feature of PowerPivot PivotTables. What my colleague was seeing was that when creating a PowerPivot PivotTable, placing a column from one of the Dimension tables on rows then adding a column from a related (Parent) table in one of the slicers and selecting a specific member from the slicer the action did not apply a filter to the related dimension that had been placed on rows. When a measure was added to the PivotTable, however, the dimension that was on rows the filter was magically applied. This behavior is relatively easy to reproduce using a few tables from the Adventure Works relational database. So for those who want a reason to just play with PowerPivot (take your pick of the version), here are some relatively simple steps to reproduce the behavior.

Start by creating a new PowerPivot workbook that uses the Adventure Works DW 2008 (you can also use Adventure Works DW 2008 R2) as the data source. Launch the Import Wizard and importing the following tables:

DimCustomer

DimGeograpy

DimProduct

DimProductCategory

DimProductSubCategory

FactInternetSales

If relationships don't exist between the DimProduct, DimProductCategory, DimProductSubCategory and FactInternetsales tables aren't automatically imported, create them manually.

Create a new PowerPivot PivotTable, then put DimProductCategory.EnglishProductCategoryName in a vertical slicer (you can use a horizontal slicer if you want). Next drop DimProductSubCategory.EnglishProductSubcategoryName on rows. Now for a bit of fun and entertainment, select "Bikes" from the slicer and pay careful attention to what doesn't happen with the EnglishProductSubcategoryName members that are on rows. Now that we're all satisfied that the filter hasn't been applied, add InternetSalesFact.InternetSalesAmount to the values area and note what happens with the DimProductSubCategory.EnglishProductSubcategoryName members that are on rows.

This is designed behavior. Cross filtering in slicers works in relationship to measures and is "turned off" until a measure is added to the PivotTable. So in this case, even though there are relationships between the DimProductCategory, DimProductSubCategory, DimProduct, and FactInternetSales tables, the filter applied to the DimProductCategory.EnglishProductCategoryName has no impact on the DimProductSubCategory.EnglishProductSubCategoryName members on rows until a measure is added to the PivotTable.

Using PowerPivot Workbooks from a Mid-Tier Server Configured for Kerberos Authentication

2012-04-23T20:03:26Z

I'm not entirely certain how widely known, but it is quite possible to use a PowerPivot Workbook as a data source for other client applications. For example, both Excel and Reporting Services can use a PowerPivot workbook named AdventureWorks.xlsx that resides on a PowerPivot Mid-Tier server by specifying the data source as something like:

http://my_server/powerpivot/AdventureWorks.xlsx.

As a result, we would get a connection to a database with a name something like "AdventureWorks Sandboc 3de9890a-fa74-4fb9-be04-858b75366b68" and a cube named "Model". What really happens behind the scenes is that when the client application connects to the PowerPivot workbook, the connection is routed through a Redirector service to the Analysis Services server running in PowerPivot mode. The client then actually connects to the Analysis Services server. That's all well and good assuming that the connection to the Analysis Services server involves a single hop or that the Analysis Services PowerPivot server and SharePoint are not configured for Kerberos authentication.

Obviously if the connection involves a double hop, as would be the case in a client workstation (machine A) connecting to a Reporting Services server (machine B) that uses a PowerPivot Workbook on a PowerPivot Mid-Tier Server (machine c), is going to require Kerberos authentication. Likewise, if the PowerPivot mid-tier server is configured for Kerberos authentication then it is going to expect a Kerberos ticket as part of the authentication process. In either of these scenarios, out of the box, the likely result is going to be an authentication failure with error code 401 being returned from IIS.

Remember that when the data source is the PowerPivot Mid-Tier hosted PowerPivot workbook that the connection goes through a Redirector service and is ultimately routed to the Analysis Services PowerPivot instance. The 401 authentication failure error will be returned because the out-of-the-box customBindings for the Redirector service are configured to use "Ntlm" authentication which results in an Ntlm token rather than a kerberos token being used for authentication. In order to access the data source in this scenario, it is necessary to modify the web.config file associated with the Redirector.svc. To enable use of Kerberos authentication, it is necessary to change both of the CubsomBindings for the Redirector.svc service.

Before going further, I'll point out that enabling use of Kerberos authentication necessitates manually editing the web.config file that is associated with the Redirector.svc. Before editing the web.config file, it would be an extremely good idea to create a backup of the file and store it in a separate directory so that it can be restored in the event that the web.config file becomes corrupted while being edited.

The default location for the file is %SystemDrive%\program files\common files\web service extensions\14\ISAPI\powerpivot. Open the web.config file with a text editor and locate the <binding name="RedirectorBinding"> tag and the <binding name="RedirectorSecurBinding"> tags and change the authenticationScheme value from "Ntlm" to "Negotiage", as shown below:

<binding name="RedirectorBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpTransport manualAddressing="true" authenticationScheme="Ntlm" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
<binding name="RedirectorSecureBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpsTransport manualAddressing="true" authenticationScheme="Ntlm" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>

to

<binding name="RedirectorBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
<binding name="RedirectorSecureBinding">
    <webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
    <httpsTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>

That should resolve the 401 error when attempting to use a PowerPivot Workbook, hosted on the PowerPivot Mid-Tier server, as a data source.

Parameterized Reporting Services Reports with Analysis Services as a Data Source

2012-04-16T18:23:00Z

Microsoft Analysis Services is a commonly used data source for Microsoft Reporting Services reports. The combination provides an environment for creating and rendering interactive reports that are highly performant, flexible and rich in functionality. One of the features of Reporting Services that provides end users with the ability to interact with the data is the use of parameters within the MDX queries that allow users to request different data sets. Just about anyone who has created a Reporting Services report has had an opportunity to create parameterized queries, which might look something like the following:

SELECT NON EMPTY { [Measures].[Internet Freight Cost] } ON COLUMNS,

NON EMPTY { ([Customer].[Customer Geography].[Customer].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@Date, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])

WHERE ( IIF( STRTOSET(@Date, CONSTRAINED).Count = 1,

STRTOSET(@Date, CONSTRAINED), [Date].[Calendar].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

SELECT NON EMPTY { [Measures].[Internet Freight Cost] } ON COLUMNS,

NON EMPTY { ([Customer].[Customer Geography].[Customer].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@Group, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works])

WHERE ( IIF( STRTOSET(@Group, CONSTRAINED).Count = 1,

STRTOSET(@Group, CONSTRAINED), [Customer].[Customer Geography].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The STRTOSET() function is what really enables the use of parameters in the MDX queries used within Reporting Services. The convention for parameters in Reporting Services is to place the “@” symbol in front of the parameter name. I’ve been around Analysis Services and MDX long enough to know that DATE is the name for an attribute type (making it a reserved word) and GROUP is an MDX Keyword. (I know, what was I thinking using a reserved word and a keyword as parameter names?) When using either of the above MDX queries with Analysis Services 2005 or Analysis Services 2008, the queries would return the data as expected. Being the intrepid soul that I am, I therefore very confidently expect that when the data source is upgraded from Analysis Services 2008 to Analysis Services 2008 R2 that my report will reliably return the data sets that I expect. Unfortunately, what I’m going to get are the error messages below:

Parser: The syntax for ‘Date’ is incorrect

Parser: The syntax for ‘Group’ is incorrect

This is a known issue with the query parser in Analysis Services 2008 R2 (the published KB article is http://support.microsoft.com/kb/2638216). The issue was addressed in Service Pack 1 Cumulative Update 4 (build number 10.50.2796) and is not present in the SQL Server 2012 Analysis Services release. I’d be surprised if everyone reading this blog were familiar with all of the reserved words and keywords used with Analysis Services, so rather than simply saying “you can look ‘em up in Books On Line”, you should be able to find them at the following hyperlinks:

Configure Attribute Types

MDX Reserved Words

Planning for Disaster Recovery for PowerPivot Mid-Tier Server

2011-11-28T20:07:00Z

Every experienced DBA is familiar with the software equivalent of “Murphy’s Law” and the notion that if something can go wrong, it will. We all know that hardware can and does fail. Now that I think about it, that’s where I first became acquainted with the term “mean time between failures.” As a result, bad things can occasionally happen to good databases and it’s usually at a very inopportune time. That probably explains why, as a group, we tend to be real concerned with having something that resembles a well-designed and tested plan for backing up and restoring databases after a disaster. It’s not terribly surprising that most of us are reasonably familiar with the SQL Server Recovery Models and the implications of each for disaster recovery. Analysis Services ships with SQL Server, which probably explains why we’re administering this type of server in addition to the relational database servers. Like any other database, it can fall victim to hardware failures, corruption, fire, flood, pesky rodents or other issues that make one or more databases inaccessible.

Those activities typically involve:

Regular backups of the database(s) to minimize data loss.
Storage of backups in a secure on site or off site location
Periodically testing the backup and recovery strategies before a real failure occurs.
Assessment of the recovery plan using a cold standby server

There is a relatively new variant of Analysis Services that is integrated with SharePoint 2010 in the PowerPivot Mid-Tier Server that was released with SQL Server 2008 R2. PowerPivot Mid-Tier Server is essentially an instance of Analysis Services, but the architecture is a bit different than the traditional Unified Dimensional Model due to the integration with SharePoint. Unfortunately, most DBAs aren’t intimately familiar with SharePoint Server and the concept of SharePoint Server Farms. SharePoint, like any other type of server, is subject to disaster so it’s a good idea to have a disaster recovery plan in place.

The documentation on SharePoint 2010 Disaster Recovery is relatively sparse. One of the more comprehensive documents on SharePoint Server 2010 Disaster Recovery is on Technet (http://technet.microsoft.com/en-us/library/ff628971.aspx). Todd Klindt discusses SharePoint 2010 Disaster Recovery and Randy Williams discussed Step-by-Step SharePoint Disaster Recovery in some detail. As they point out, SharePoint stores configuration, content and administration data in SQL Server databases. So obviously, the first part of any plan for disaster recovery involves regular backups of the Configuration, Content, and Administration databases (and if you aren’t already taking regular backups, you obviously haven’t had the panic attack that we’ve all experienced with a notification that a production server is down). With PowerPivot Mid-Tier server, data for the PowerPivot Service Application is also stored in a SQL Server database.

What the documentation doesn’t really tell you is that with the PowerPivot Mid-Tier, the uploaded PowerPivot workbooks are stored in the Content database, so having a valid backup of that database is a definite must. Having said that, this is what I learned from the pain of experience attempting to test a disaster recovery plan using a cold standby SharePoint 2010 Server. I should note that even though I lost quite a bit of hair and am now completely gray, no databases were harmed in the process of testing this.

In order for any disaster recovery plan to succeed, the cold standby SharePoint 2010 server that the production SharePoint 2010 server will need to reside in the same domain and preferably configured to use the same service accounts. Failing either of those conditions, you could be in for a lot of failures (not to mention as bald and gray as me) when attempting to pull up any Excel Workbooks that have been stored in the PowerPivot Mid-Tier server. After more hours than I care to think about, this is what I came up with:

Setup Primary PowerPivot Mid-Tier server

Install SharePoint
Install PowerPivot (New or Existing Farm). Which one you do depends on whether or not you ran the Configuration Wizard after installing SharePoint. If you ran the Configuration Wizard after installing SharePoint, you'll want to select the "Existing Farm" option. If you did not run the Configuration Wizard after installing SharePoint, you'll want to select the "New Farm" option.

Test the PowerPivot Site
Setup Cold Standby PowerPivot Mid-Tier server using same setup strategy that was used for the Primary
Test the PowerPivot site on the Cold Standby
Create a Test PowerPivot workbook and upload to the mid-tier server
Verify that Data Refresh works
From SSMS on the Primary server, backup the SharePoint_Content_.... Database
Copy the SharePoint_Content_...bak file to the Cold Standby
From SSMS on the Cold Standby, restore the SharePoint_Content_.... database from the .bak file
From SharePoint Central Admin on the Cold Standby, select "Manage content databases"
From the Central Administration > Manage Content Databases page, click on the existing SharePoint_Content_... database. That will open the Central Administration > Manage Content Database Settings page
Check the "Remove Content Database" checkbox
That will cause a "Message from webpage" with the following and rather ominous warning:
    "Warning: There are sites stored in this content database. If you
     remove the database, those sites will no longer be accessible to
     users. Click OK to remove the database, or Cancel to leave the
     database connected."
Click OK
Click OK
From the Central Administration > Manage Content Databases page, click "Add a content database"
Set the Database Name field to the name of the database that was restored in step 9.
Click OK
Test the PowerPivot site on the Cold Standby server. At this point, any Excel PowerPivot Workbooks that were stored in the Primary Server should be visible and accessible on the Cold Standby server.
Schedule a Data Refresh to run "Also refresh as soon as possible"
Verify that the Data Refresh was successful.

Your Disaster Recovery test should have been successful.

For purposes of disclosure, I do work for Microsoft.