April 2010 - Posts


I recently got asked by our network guys about botlenecks in the WAN that used for mirroring to our DR I site. They asked me to turn off encryption of Database Mirroring so that the riverbed software  they were using could optimise the packets sent over the WAN.

I was a bit sceptical at first about the security risks, but it seems the riverbed software has its own form of obfuscation making the packets difficult to read.

After reading an article by rusanu I realised that it could be done with minimal downtime and potential reducing network traffic by 5-10% on its own.

After turning off encryption I was pleasantly suprised to see that overall network traffic for mirroring dropped by a whopping 75%!





I was searching recently for a code formatter for T-Sql and I came accross this nice little utility that I wanted to share: http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

I've been dealing with a lot of legacy code latley and there is nothing I find more infuriating than unformatted code. This tool seems to work quite well. Just one click and it formats everything nicely. There is also a free web version.


Posted by blakmk
Filed under: , ,

Well it this weeks  T-SQL Tuesday #005  topic seems quite fitting. Having spent the past few weeks creating reports and dashboards in SSRS and SSAS 2008, I was frustrated by how difficult it is to use custom datasets to generate parameter drill downs. It also seems Reporting Services can be quite unforgiving when it comes to renaming things like datasets, so I want to share a couple of techniques that I found useful.

One of the things I regularly do is to add parameters to the querys. However doing this causes Reporting Services to generate a hidden dataset and parameter name for you. One of the things I like to do is tweak these hidden datasets removing the ‘ALL’ level which is a tip I picked up from Devin Knight in his blog:

There are some rules i’ve developed for myself since working with SSRS and MDX, they may not be the best or only way but they work for me.

Rule 1 – Never trust the automatically generated hidden datasets

Or even ANY, automatically generated MDX queries for that matter.... I’ve previously blogged about this here.  

If you examine the MDX generated in the hidden dataset you will see that it generates the MDX in the context of the originiating query by building a subcube, this mean it may NOT be appropriate to use this in a subsequent query which has a different context. Make sure you always understand what is going on.

Often when i’m developing a dashboard or a report there are several parameter oriented datasets that I like to manually create. It can be that I have different datasets using the same dimension but in a different context. One example of this, is that I often use a dataset for last month and a dataset for the last 6 months. Both use the same date hierarchy.

However Reporting Services seems not to be too smart when it comes to generating unique datasets when working with and renaming parameters and datasets. Very often I have come across this error when it comes to refactoring parameter names and default datasets.

"an item with the same key has already been added"

The only way I’ve found to reliably avoid this is to obey to rule 2.

Rule 2 – Follow this sequence when it comes to working with Parameters and DataSets:

1.    Create Lookup and Default Datasets in advance

2.    Create parameters (set the datasets for available and default values)

3.    Go into query and tick parameter check box

4.    On dataset properties screen, select the parameter defined earlier from the parameter value defined earlier.

Rule 3 – Dont tear your hair out when you have just renamed objects and your report doesn’t build

Just use XML notepad on the original report file. I found I gained a good understanding of the structure of the underlying XML document just by using XML notepad. From this you can do a search and find references of the missing object. You can also just do a wholesale search and replace (after taking a backup copy of course ;-)

So I hope the above help to save the sanity of anyone who regularly works with SSRS and MDX.



Posted by blakmk | 2 comment(s)
Filed under: , , ,