PAS and MDX errors

I’ve been working with a Client to release a Microsoft BI Solution, they started to have problems with ProClarity pages not rendering in a Performance Point Dashboard and producing a message saying the “Your request could not be completed.”

Running the Profiler on the AS Server highlighted the fact that the mdx was causing an error.

The set in the WHERE clause cannot contain multiple measures.

Searching the knowledge based return an interesting article on the error and how to get around it.




Turns out PAS really doesn’t like errors. If a published PAS report produces an error, it crashes the IIS worker process thus rendering the PAS Service useless. The only way I could see to get this working again was to recycle the application pool.



Back to my error, from the article there are quite a few scenarios that could produce this error, the example below produces the error because Berkshire isn’t a city in Canada (or anywhere for that matter, a county perhaps) and the measure is in the where clause.



SELECT
[Product].[Category].MEMBERS ON COLUMNS
FROM
[Adventure Works]
WHERE
([Measures].[Order Count],
[Geography].[City].&[Berkshire]&[ENG],
[Geography].[Country].&[Canada])


The whole scenario is not ideal and does limit the type of reports that can be published to PAS. I’m sure if I ask Microsoft they would tell me ProClarity was designed that way like so many of their other features I've stumbled across. So just be careful if you are using PPS filters or ProClarity filters on you PAS reports, end users might bring down your PAS service.

Requirements Gathering

I had some time to catch up on the 70 odd blogs I haven't had a chance to read and stumbled across an article written by Neil Raden "Requrements Gathering: Don't Be Naive". An interesting read.

He basically discusses the possible downfalls in requirement gathering for BI Projects.

XML Data Destination

I had the pleasure of coding a Data Source that would output data in an XML format, thought I would share the code. I cannot take full credit for the code as it originated from Jamie Thomson’s blog and I’ve just adapted it so it’s a little more dynamic and capable of multi-levels within a row. There is potential to improve the code further or to customise it to your needs.

Why bother creating a Destination?

Seems a sensible question to ask when you could quite easily use an Execute SQL Task and have a ResultSet of XML. I found this worked well on small sets of data, but as the volumes of data grew, the time it took to execute the task grew exponentially. The package also seems to be incredible resource intensive on the server. Creating the script reduced loads from hours (some loads never finished as the package threw an error after 10 hours processing) to a few minutes.

The Code:

For the code to work the naming of the columns into the destination task need to be in a specific format, for those of you who may have used SQL with a FOR XML EXPLICIT clause, it’s the same structure.

Pass in all the columns that you want to output in XML Format, as shown below:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components


Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Imports System.Reflection

Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
Dim xWriter As XmlTextWriter
Dim NewMainTag As Integer
Dim strAttributeName As String
Dim columnValue As PropertyInfo
Dim strValue As String


Public Overrides Sub PreExecute()
'Read Only variables (strDestinationFolder and strXMLFileName)
Dim gsPickUp As String = Me.Variables.strDestinationFolder.ToString
Dim gsPickUpFilename As String = Me.Variables.strXMLFileName
Dim fileName As String = gsPickUp & "\" & gsPickUpFilename
fileName = fileName & (Format(Now(), "yyyyMMddhhmmss").ToString) & ".xml"
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteStartElement("document")
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'declaration of varaibles
Dim column As IDTSInputColumn90
Dim rownumber As String
Dim rowType As Type = Row.GetType()
Dim StringIndex As String
Dim TagName As String
Dim objColumn As Object
Dim FirstTimeForTag As Integer = 0
If Row.Tag = 2 Then
'if start of document
If NewMainTag = 0 Then
NewMainTag = 1
xWriter.WriteStartElement("Row")
Else
'create new row and end pervious
xWriter.WriteEndElement()
xWriter.WriteStartElement("Row")
End If
End If

'for each column in the row
For Each column In ComponentMetaData.InputCollection(0).InputColumnCollection
'convert tag number to string
rownumber = CType(Row.Tag, String)
'create tag lookup in column (e.g. !2! or !3!)
StringIndex = "!" + rownumber + "!"

'if tag number matches column number
If column.Name.IndexOf(StringIndex) > -1 Then
'extract name of tag from column before tag number(e.g. row from row!2!)
TagName = column.Name.Substring(0, column.Name.IndexOf(StringIndex))

'if not the main tag and first entering the loop out put a tag header
If Not (Row.Tag = 2) And FirstTimeForTag = 0 Then
xWriter.WriteStartElement(TagName)
FirstTimeForTag = 1
End If

'attribute names do not contain exclamation marks, remove !
strAttributeName = column.Name.Replace("!", "")
'get column values
columnValue = rowType.GetProperty(strAttributeName)
objColumn = columnValue.GetValue(Row, Nothing)

'if column is not null output it to xml file (does not include blanks)
If IsNothing(objColumn) Then
Else
strValue = objColumn.ToString()
xWriter.WriteStartElement(column.Name.Replace(TagName + StringIndex, "").Replace("!element", ""))
xWriter.WriteString(strValue)
xWriter.WriteEndElement()
End If
End If
Next column

'end of element part
If Not (Row.Tag = 2) Then
xWriter.WriteEndElement()
End If
End Sub

Public Overrides Sub PostExecute()
'EOF
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End Sub

End Class


And that should be about all you have to do.

Happy Exporting!