Matt's BI Blog

I'm on the move

2009-03-05T19:35:00.004Z

I have decided to move my blog as it takes longer to publish one, than to write one here. Suppose with being a Microsoft BI Consultant it is quite fitting it is on the windows live site

http://mattbi.spaces.live.com/

If I can figure out how to move old blogs from here painlessly, I will

And for those of you have may have seen my website, then laughed rather hard (don't blame you). That is also having a revamp and will probably be down as a new server is being set up.

SQL Refector

2009-02-05T12:34:00.004Z

I’ve been using SQL-refactor the last few days to reformat my sql. Not because I am lazy when I write it, but when you write SQL in Reporting Services or Analysis Services (DSV) it removes all the fancy formatting you may have done. On the occasions when you return to edit the SQL, if like me, you can spend some time reformatting it to make your changes.

So I thought there must be a tool out there that can quickly reformat my SQL and that’s when i stumbled on SQL Refactor by Redgate. It does some other clever things, although that wasn’t my main reason for using it, such as qualifying objects and remove redundant code in stored procedures. It does come with a bit of a price tag, but there are some bundles Redgate do that may make justification of its purchase expectable.

You can find some of Redgate’s products already incorporated in the Microsoft stack: SQL 2008 has SQL prompt (2008 maybe not as clever); SQL Compare and SQL Data Compare can be found in team foundation server 2008 Database edition GDR. Note SQL Refactor is not the same as GDR’s refactor.

All in all, SQL Refactor is a clever little utility.

And as if by Magic

2009-01-24T16:45:00.005Z

Seems my curiosity to see (well watch) the new roadmap for BI has been answered, see The BI Blog

Kind of sounds like a reporting server with no mention of real analytics, apart from Excel which I don't think counts. Maybe the news of all of this will reignite the market for an analytics tool for analysis services. The return of Panorama Novaview perhaps, who happen to be running a webinar on An Alternative BI Solution to Microsoft PerformancePoint

Chris Webb also raised some good points on Excel in his blog More thoughts on the death of PPS Planning

PerformancePoint gets a re-design

2009-01-22T20:46:00.004Z

I'm little unsure on Microsoft's thinking behind this one with regards to Patrick Hustling's blog on PerformancePoint turning into a service and planning being axed. It almost as disappointing as what has become of the ProClarity toolset.

I’m a little surprised they have decided to improve the monitoring, my experience with monitoring has not been the greatest and I always let it off because it was version 1. Yet the planning aspect seemed a better product and this is the one to go.

I suppose there is a big drive from Microsoft to deliver BI through SharePoint. Also recent articles have implied that recent trends in BI, not just Microsoft, are moving towards a web based application.

Be interesting to see Microsoft's new roadmap and where PPServices now fits in.

Merge Join task not always matching

2009-01-19T20:43:00.006Z

I’m not a big fan of the merge join task, mainly because you have to sort things and that comes with a performance cost. And to make things worse I stumbled on an issue with SQL 2005 where the merge join task doesn’t always perform as expected.

The issue occurs when you want to merge two data sources, but don’t want to use the SSIS sort task. Instead of using the sort task, you change the data sources to use a sql command, generate some SQL with an order by clause in it. Then in the advance editor of the source, set the IsSorted property to True.

Then you have to set the SortKeyPosition to match your order by clause in the SQL statement. Do this for both data source and then use them as inputs to the merge join task. As below.

Problematic approach

This in theory should work, although for some reason, which I have yet to discover doesn’t. For some reason the merge join doesn’t always match. Replacing the sorts with sort tasks seems to fix the problem, what this method does differently to using a order by clause, I’m not sure.

Working , with performance issues

Yet, if you are involving large volumes of data I would consider removing all of this with a simple lookup statement. Hopefully the problem will make itself apparent to me and I’ll update this blog with the reason.

Adatis Recieve Not just one MVP

2009-01-05T18:37:00.004Z

Although Tim would prefer to keep this award quiet and not let it go to his head! May I be one the first to congratulate both Tim Kent and Sacha Tomey on receiving MVPs.

Both have contributed significantly here in the UK to promote Microsoft and help build what I would consider to be the leading consultancy firm in the UK for PPS. Last year their consultancy firm became gold members and has since doubled in size.

I’m sure we will be hearing a lot more from them in the future.

Congratulations again.

A little break but I am back

2009-01-05T18:11:00.002Z

Well Christmas has passed, I'm a little fatter (some may still think I am s skinny rake but ho hum). Thought I better try and keep on top of my blogs, so better efforts this year.

Below is just a heads up on future topics, although most have been done to death by various sources.

Compare the different methods currently implemented in SSIS to do updates for dimensions, various methods are out there, going to try and establish the better for 2005 and if I get my hands on a server 2008.
My favourite, Team Foundation Server, although looking at the database professional aspect of it.
Deploying BIDS and PPS across environments, a commonly asked question with no real answer. It does seem to lend itself to various possibilities and some more dubious than others.
Fuzzy Lookup performance, a task not highly used and less often talked about

Hopefully in the summations of everyone else’s blogging I uncover some interesting BI subjects of interest. I may even talk about service packs, although missed out on the handful that have just gone out :)

And a Happy New Year

Marco & Alberto's Methodology Comparison

2008-10-01T20:57:00.003+01:00

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

2008-09-17T20:45:00.004+01:00

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

2008-08-27T21:21:00.004+01:00

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

2008-08-06T21:25:00.002+01:00

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

2008-08-05T21:16:00.005+01:00

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

2008-07-30T19:38:00.000+01:00

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