Rob Farley : sql, adssug

Infinite Drill-through in a single SSRS report

Rob Farley — Mon, 21 Dec 2009 10:18:15 GMT

Grant Paisley of Angry Koala and Report Surfer put me onto this a while back, and I have to admit I’m a bit of a fan. The idea comes from the fact the way that SQL Server Reporting Services (both 2005 and 2008) handles parameters with Analysis Services, and lets you make a report that drills through into itself, deeper and deeper into a hierarchy. Today I did a talk at the Adelaide SQL Server User Group, and mentioned this was possible (but didn’t have the time to demonstrate it properly).

If you make a parameterized query in an MDX query in SSRS, you use the STRTOMEMBER or STRTOSET function to handle this. But the MDX has no other indication of what dimension, hierarchy or level is being passed in. If you grab the children of whatever you’ve passed in, you can easily put this on the Rows axis and get one level down. Passing the UniqueName of whatever you’ve just provided back in as the next parameter, and you have infinite drill-through.

Look at the following MDX query:

WITH
MEMBER [Measures].[NextLevel] as StrToMember(@SomeDate).Hierarchy.CurrentMember.UniqueName
MEMBER [Measures].[NextLevel Name] as StrToMember(@SomeDate).Hierarchy.CurrentMember.Member_Name

SELECT
NON EMPTY { [Measures].[Internet Sales Amount], [Measures.NextLevel], [Measures].[NextLevel Name] } ON COLUMNS,
NON EMPTY { (StrToMember(@SomeDate).Children ) } ON ROWS

FROM [Adventure Works]

You see that I provide the UniqueName and Member_Name properties in known columns, so that I can easily reference them in my report. You’ll also notice that nowhere do I actually indicate which dimension I’m planning to drill down on, or to which hierarchy the @SomeDate parameter refers. I have suggested it’s a date, but only in name. At this point I also make sure that the Report Parameter is not restricted to values from a particular query, and I hide it from the user. I’m going to be passing in UniqueName values, which aren’t particular user-friendly.

If I start with [Date].[Fiscal].[Fiscal Year].&[2003], then my NextLevels will be [Date].[Fiscal].[Fiscal Semester].&[2003]&[1] and [Date].[Fiscal].[Fiscal Semester].&[2003]&[2]. This then continues down as far as I want it to go. I could always put a condition on my Action to pick up when there are no more levels, and potentially start down a different hierarchy. After all, I can always use a bunch of other parameters in the WHERE clause to slice the cube in other ways first, for placeholders. It really just comes down to MDX creativity to investigate different ways of drilling through the data.

Please bear in mind that other people may well have achieved the same sort of thing using a different query – I’m just posting what has worked for me. Hopefully by doing this, you can avoid making five drill-through reports just because your hierarchy has five levels. This might just remove 80% of your reporting effort!

Access’ DISTINCTROW keyword explained – it means WHERE EXISTS

Rob Farley — Thu, 11 Jun 2009 11:25:15 GMT

Steve Koop spoke recently at the Adelaide SQL Server User Group, talking about things which don’t convert particularly nicely when upsizing from Microsoft Access to SQL Server 2008. I think this is a really important thing for SQL people to know, as there seem to be many Access databases living in even the largest organisations.

One of the things he mentioned was DISTINCTROW. I’ve never really known what DISTINCTROW does, so I asked him. He sent me a link which explained the difference between DISTINCTROW and DISTINCT, and it described as “DISTINCTROW works on records, not just individual fields”. This might be good for some people, but I wanted to know a little more.

http://msdn.microsoft.com/en-us/library/aa140015.aspx says “The DISTINCTROW keyword is similar to the DISTINCT keyword except that it is based on entire rows, not just individual fields.” – but it also goes on to say a little more, confirming my suspicions. “It is useful only when based on multiple tables, and only when you select fields from some, but not all, of the tables.”

So DISTINCTROW is more about the JOIN type than anything else. It only applies if you are querying multiple tables, but not returning fields from all of them. So it’s a SEMI JOIN to the unused tables, which you write SQL Server using a WHERE EXISTS clause. It’s not really like DISTINCT at all – it’s about doing a Join without seeing the ‘duplication’ effect, clearly only feasible if you’re not returning columns from the other table.

If you’re not sure what a Semi Join is, then just think about the WHERE EXISTS clause, and it should become clear. If you look at the execution plan of a query in SQL Server that uses WHERE EXISTS, you’ll see that it’s doing a Semi Join. And if you’re looking at queries which use DISTINCTROW, consider changing them to WHERE EXISTS instead.

OUTPUT clause - knowing what goes in, and what you've accidentally taken out

Rob Farley — Sat, 04 Oct 2008 00:08:00 GMT

The OUTPUT clause has to be one of the best T-SQL features out there. It was new in SQL Server 2005, but it's still remarkably little known. I guess like many of the features that were introduced in SQL 2005, many people just make do with the way they did things before.

The basic point of the OUTPUT clause is to turn a data modification statement into one that returns data (although this data can be redirected to an existing table by simply adding "INTO tablename" after it), by providing a way of accessing the inserted and deleted tables that we have been using in triggers for years. If a DELETE statement is being issued, only the deleted table is available, and similarly inserted for an INSERT statement. UPDATE provides both - and the MERGE statement (new in SQL Server 2008) populates both (although it feels as if a FULL JOIN has been applied to them).

I'm sure it's easy to imagine the tremendous advantage to using it with UPDATE statements - auditing. It's very easy to push the changes to an audit table, without the need to create a trigger to do it for us. But I find that the biggest advantage is for those times when carelessness has got the better of us. Hopefully these times are rare (and the more experienced we get with databases the more we seem to respect the sanctity of the data), but if you always use "OUTPUT deleted.*" when deleting, or "OUTPUT deleted.*, inserted.*", then the odd time when you see more rows come back that you expected, you can have a plan associated with your "Oops" moment.

If you don't use the OUTPUT clause, you get a message that tells you how many rows were affected by your query. If you expect that number to be small, and it's actually quite large, you've probably done something wrong. "Oops" is probably an understatement. You may have started a transaction and be able to roll it back, but until such time as you get to that, you have locks which aren't being released. Regardless of whether or not you can roll it back, having something which shows you what you've just done can really help you out. You can copy the data presented into Excel, or Notepad, or whatever, and work out your problem. If you can't roll it back, then this may involve some sort of import process being quickly thrown together.

The benefit is far less obvious when inserting data - but in some ways, it's actually even more useful.

We've had the @@IDENTITY and SCOPE_IDENTITY() functions available for some time, and they're widely used. But if multiple records are inserted, or if the targetted table doesn't contain an identity field, then they're actually not quite so great. Knowing which record is which is a question of re-querying the data and hoping you can tell. But if you "OUTPUT inserted.*", your application can immediately tell which row was inserted with which surrogate key (it's less of an issue if you use a natural primary key of course). With a large number of systems opting to use guids for PKs, defaulting to a value of newid(), it really helps to have an OUTPUT clause so that the guid doesn't have to be generating prior to performing the insert.

The biggest caveat with the OUTPUT clause is that it can be ruined by triggers. A trigger being called can prevent the inserted and deleted tables from being available at the end of the statement. I'd like to have a way of stating that the OUTPUT clause should still work, returning the state of the inserted and deleted tables prior to any triggers being called, but I suppose I can understand the logic behind the decision to disallow it.

This was one of the tips in my TechEd Australia presentation, which was re-delivered to user groups in Adelaide and Melbourne. The scripts and slides are available for download. This was one of the more popular tips, based on feedback.

SQL and Virtual Earth

Rob Farley — Thu, 01 Nov 2007 09:07:11 GMT

A while back I was talking to Bronwen Zande, of Brisbane-based SoulSolutions and GeekGirlBlogs.

Anyway, she and her partner John are big fans of Virtual Earth. As it's well publicised that SQL Server 2008 will have new spatial types, including fancy ways of integrating with Virtual Earth, I asked her if they were planning a tour of the Australian SQL Server User Groups to demonstrate how this could work. I figure this is something that few SQL Server people will do much investigation into, but may well be asked about by developers who are keen to find out what's possible.

Lo and behold, I guess things are happening, because they're on their way! In Adelaide, this meeting will be on January 10th, our usual time-slot on the second Thursday of the month, and there are other events happening around the country too. All this despite the fact that the latest publicly available version (July still) of SQL Server 2008 doesn't have the spatial types in it. Hopefully by then something will be available for us to check out the demos. Keep your eye on https://connect.microsoft.com/sql for news on that.

User Group meeting with Christine Bishop

Rob Farley — Fri, 12 Oct 2007 02:04:01 GMT

Yesterday we had Christine Bishop come to Adelaide. She's the Product Marketing Manager for SQL Server and BI for Microsoft Australia. That makes her handy to know if you're in the SQL space, and we had a nice crowd come along to the user group to meet her.

One thing that made this meeting different to the usual is that Christine isn't overly technical. She's not bad, but compared to the people in the crowd listening to her, she would've been in the less technical half. Normally we'd have some in depth technical session, showing people how to do particular things, but this time we looked more at the business side of things.

Feedback was good, and for those people looking for something a little more technical I demonstrated a quick point about SSIS and one about the use of row_number() to make sure that only the three most recent records for each category were stored in a table. I might post about these more another time.

My favourite thing about SQL Server 2008

Rob Farley — Mon, 20 Aug 2007 05:35:48 GMT

A few people have asked me what my favourite thing is in SQL Server 2008 ("Katmai"). But my favourite thing isn't actually a feature at all, it's a mindset that Microsoft are taking with it.

This mindset is "We will only put features into the CTPs once they are basically complete - including the documentation." And this makes me say "Wow!".

One of the problems with all beta software is the stuff that's in there that just isn't finished yet. You try to use some feature, and it doesn't work. Or worse, something dies because you tried it. It's these scenarios that stop people trying out betas, and seeing people using the previous version still nearly two years after release (It's now over 21 months since SQL 2005 was released).

Not the case with SQL Server 2008 though. Functionality may be limited, but hopefully new features should be both stable and documented when they appear. So now because this is the case, people should be able to port their systems over before release, confident that features won't be changing significantly between that time and RTM.

This week at my user-group I'm giving a talk on MERGE & TVPs. Two essentially different topics, but ones that compliment each other nicely. I think people will be leaving this meeting picturing places in their code where they want to refactor it to take advantage of these new features.

Darren Gosbell in Adelaide on July 12th

Rob Farley — Tue, 03 Jul 2007 08:03:02 GMT

What a great week for Adelaide having Australian SQL MVPs presenting! Code Camp SA on the weekend of July 7th and 8th features the Gregs (Linwood and Low), and BI expert Darren Gosbell will be coming to speak to the Adelaide SQL Server User Group the following Thursday. He'll be presenting about MDX, which is a topic that a few people have asked about in recent months. It should be great, and I only wish I could be there myself. I will put the event on the website just as soon as I have an abstract from him. Many thanks to his employer, James and Monroe, for sending him across for this.

If you're reading this and wondering how to find out more, go to the sqlserver.org.au site, register, and say you're from Adelaide. Then you'll be on my mailing list and will find out all about our upcoming events. Our meetings are on the second Thursday of the month (although in August we'll pick a different day, that doesn't clash with TechEd), in the building that used to be called the Santos Building.

Powershell script from my SQL presentation

Rob Farley — Tue, 16 Jan 2007 21:00:00 GMT

Last week I presented at my user-group about PowerShell and why every DBA should know this. The talk went for just over an hour, and as most of the audience hadn't used PowerShell at all, I started from the top and really pushed concepts like "You pipe objects not text". The script can be downloaded from here.

So then by the time I got around to talking about the fact that you can really easily hook into ADO and SMO, I think the audience were already caught on the idea that PowerShell really is very powerful and that anything you can do with SMO can be done really easily with PowerShell too. Most DBAs write scripts that use SMO to manage their SQL boxes. In the past they've used VBScript, but I think they should use PowerShell - it's got so much more going for it, and because it can replace cmd, there's almost no reason not to use it.

The talk didn't go into all the stuff you can do with SMO - that's a different presentation. This was a way of demonstrating that you can use PowerShell for SMO, as well as everything else you might want from a sysadmin perspective.

Adelaide SQL UG this week

Rob Farley — Mon, 11 Dec 2006 10:06:00 GMT

This Thursday Greg Linwood of Solid Quality Learning is coming to the Adelaide SQL Server User Group to talk present about Performance Tuning. It should be a great time. Greg does a lot for the SQL Community in Australia, and it will be really good to have him over again. If you're interested in coming, drop me a line, or check out the event page at sqlserver.org.au

Office 2007 RTM today, great time for a talk about it and SQL!

Rob Farley — Mon, 06 Nov 2006 23:31:00 GMT

Office 2007 has RTM'd today, and this includes Microsoft Office SharePoint Server 2007.

This is great timing, with Grant Paisley coming to Adelaide to give his talk about integrating BI and MOSS. This is a great talk, and if you're going to be in Adelaide, you should be there! Go to sqlserver.org.au, and find the link. And if you can't make it in Adelaide, go to his session in Canberra instead! I heard it at the SQL Code Camp, and it's definitely worth checking out.

The great thing about Office 2007 with BI stuff is that BI is all about making the data you have more useful. And Office 2007 is so much better integrated with other systems than previous versions, so it can become a great portal to getting your information out there.