Marco & Alberto's Methodology Comparison

I seem to be getting worse at keeping up to date with reading my ever growing list of blogs. Currently stands 277 unread.

Stumbled across a blog from Marco Russo Methodology comparison: Kimball, Inmon and SQLBI
Well worth reading.

I am proud to announce a public draft of the first paper about the SQLBI Methodology. I and Alberto Ferrari tried to define a consistent methodology which covers the construction of the back-end of a BI Solution using Microsoft SQL Server and its complementary services.

The importance of Attribute Relationships

An unusual finding around hierarchies and attribute relationships, hopefully it will convince you to perhaps double check your attribute relationships.

In the case where a user hierarchy has not been defined properly and there’s a warning symbol glaring at you (perhaps in the hope you might fix it), it could make specific types of queries produce incorrect results. A certain scenario produces the All level total rather than the sub cube value.

The scenario seems to only occur when the following is true:

• The user Hierarchy’s attribute relationships are not set properly
• The hierarchy is in the where clause
• An attribute from the hierarchy is on columns or rows (must be at a higher level in the hierarchy)
• And brackets are used in the where clause {}
  

To fix it:

• Setup the attribute relationships in the user hierarchy correctly
• Or alter your MDX query, not always that easy

Code as follows (Adventure Works DW Database)

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY [Product].[Days to Manufacture].[All Products] ON ROWS
FROM
[Adventure Works]
WHERE
[Product].[Manufacture Time].[Days to Manufacture].&[0]

Returns: $1,040,532.57.

Add {} to WHERE clause. It returns: $29,358,677.22 which is incorrect

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY [Product].[Days to Manufacture].[All Products] ON ROWS
FROM
[Adventure Works]
WHERE
{[Product].[Manufacture Time].[Days to Manufacture].&[0]}

I couldn’t find reference to a cumulative update to this and I haven’t had a chance to test to see if it affects SSAS 2008. So I am not sure if they have fixed this.

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!

My very first blog

So I've finally got some spare time to write my first blog, just a little introduction, hopefully the next one won't take 6 months to write. And with some luck it will be of some use to others.

I’ll probably write about the Microsoft BI space as this is the main area I have worked in for the last four years, although I may post some random things I stumble across from time to time.

Until next time