I'm on the move

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

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

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

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

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

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

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