I'm currently in Seattle enjoying the start of some of the activities around the SQL PASS Summit.

One of the activities that I'm looking forward to is the book signing session for the MVP Deepdives Volume 2 at lunchtime on Wednesday. I was fortunate to be one of the 60 or so authors this time around with a chapter on using Powershell to manipulate Analysis Services databases.

All of the proceeds from this book go to support Operation Smile. You'll find the book's website here: http://www.manning.com/delaney/.

But if you are at PASS you should be able to pick up a copy from the bookstore and drop by on Wednesday to get it signed by the authors.

Full Disclosure: I was one of the technical reviewers on this book.

I think my friend Tomislav did a great job on this book and it would make a valuable addition to the bookshelf of anyone that is working with MDX.

I really enjoyed reading this and there were even a couple of interesting techniques that I have added to my toolkit. As far as I know there are not any other MDX books on the market quite like this one. It's more aimed at the intermediate level of MDX user and assumes that you have some concept of things like members, tuples and sets. You will probably get the most out of it if you have had at least a little experience writing your own MDX expressions.

The fact that it's written in a cook book style makes it very practical. You can scan down the table of contents if you want and just pick out individual recipes. Or you can read it from cover to cover as the recipes start simple and gradually increase in complexity. The individual recipes stand alone, but they also have links to other related recipes. Although the book makes frequent references to SSAS 2008 R2 the vast majority of the recipes will work just fine with the 2005 and 2008 versions (and for Denali when it's released).

Finally I was very surprised to find that the image on the front cover is one of my home town Melbourne, Australia. So if you live in Melbourne and are into MDX you definitely should buy yourself a copy of this book.

A number of people have spotted this announcement on the SQLCAT blog already:
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2011/09/19/announcing-the-first-wave-of-ssas-maestros.aspx, but if you have not seen it yet, I’m very proud to say that I’ve been made an Analysis Services Maestro!

If you have not heard about the Maestro program you can find out more details about it here; as Chris has said, it’s basically something like an MCM for Analysis Services. The course itself was a fair bit of work and went into a lot of the Analysis Services internals, along with worksheets for the labs during the course we had to sit a written exam and submit a 5000 word case study.

Congratulations also go to the other ten awardees: Ulf, Alberto, Greg, Chris, Dirk, Mick, Vidas, Marco, Harlan and John. (Interestingly the 3 founding members of the BIDS Helper project are all in this first batch of Maestros)

Today the BIDS Helper team released a new version the details below are taken from the download page for the new release. Those of you with BIDS Helper already installed should get a new version notification when you next launch BIDS. The major feature in this release is the Biml Package Generator, John has been doing some great work documenting the capabilities of this new feature here  (make sure you also check out the samples and tutorials of this powerful feature).

Enjoy!

======

New Features

• Duplicate Role feature for SSAS
• Biml Package Generator feature for SSIS

• Fixes issue with Printer Friendly Dimension Usage not working from the cube right-click menu
• Integrated new SSIS Expression Editor Control (http://expressioneditor.codeplex.com - v1.0.3.0)
• SSIS variable move dialog includes improved validation as well as UI enhancements
• SSIS Expression List now supports variables, constraints and nested objects, as well as UI enhancements
• New Enable/Disable features dialog, includes grouped features with descriptions and help button links
• Fixed issue SSIS Variables Window Extensions - not all variables are highlighted when package configurations impact them

I was having an interesting discussion with a few people about the impact of named sets on performance (the same discussion noted by Chris Webb here: http://cwebbbi.wordpress.com/2011/03/16/referencing-named-sets-in-calculations). And apparently the core of the performance issue comes down to the way named sets are materialized within the SSAS engine. Which lead me to the thought that what we really need is a syntax for declaring a non-materialized set or to take this even further a way of declaring an MDX expression as function or macro so that it can be re-used in multiple places. Because sometimes you do want the set materialised, such as when you use an ordered set for calculating rankings. But a lot of the time we just want to make our MDX modular and want to avoid having to repeat the same code over and over.

I did some searches on connect and could not find any similar suggestions so I posted one here: https://connect.microsoft.com/SQLServer/feedback/details/651646/mdx-macro-or-function-syntax

Although apparently I did not search quite hard enough as Chris Webb made a similar suggestion some time ago, although he also included a request for true MDX stored procedures (not the .Net style stored procs that we have at the moment): https://connect.microsoft.com/SQLServer/feedback/details/473694/create-parameterised-queries-and-functions-on-the-server

Chris also pointed out this post that he did last year http://cwebbbi.wordpress.com/2010/09/13/iccube/ where he pointed out that the icCube product already has this sort of functionality.

So if you think either or both of these suggestions is a good idea then I would encourage you to click on the links and vote for them.

I just noticed that Jeffrey Wang from the Analysis Services team has started blogging. He has put up a great first post on “Execution Plans and Plan Hints for MDX IIF Function and CASE Statement”. Check it out here http://mdxdax.blogspot.com. If you want to subscribe you can get an RSS feed at  http://mdxdax.blogspot.com/rss.xml (I don't know why Blogspot does not make the rss easier to find)

I’ve been teaching an MDX course for the last few days as well as reading Marco and Alberto’s excellent PowerPivot book on the train and it struck me that every time I do a division in both languages I seem to be using the following pattern in order to avoid returning an error to the user

MDX: IIF( <denominator> = 0, NULL, <numerator> / <denominator> )  

DAX: IF( <denominator> = 0, BLANK(), <numerator> / <denominator> )

I know that languages like C++ and C# don’t test for this automatically as it’s an extra operation that is not always required. So I've sort of been pre-conditioned into thinking this is normal. But particularly in DAX, which is supposed to be as simple and user friendly as possible, I was thinking that the divide operator should do this automatically or we should have something like a SafeDivide() function or maybe a different operator.

If you want the calculation to behave differently when the denominator is 0 or blank then you could still specifically test for that, but at the moment I can’t think of a compelling reason where I would want to show the user a “divide by 0” error.

Chris Webb has had a suggestion up on connect for a while to have this feature added to MDX here:

https://connect.microsoft.com/SQLServer/feedback/details/448127/mdx-needs-a-special-division-by-zero-operator

And Marco just added one for DAX here:

https://connect.microsoft.com/SQLServer/feedback/details/622995/dax-needs-a-special-division-by-zero-operator

Go and vote if you think this would be a good thing to have.

You may have already heard some information about the fact that Vertipaq indexes that are coming in the next version of SQL Server code named "Denali"  following the announcements last week at PASS. If you want some more detailed information there is a new technical article available for download:  Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0 which goes into more depth about how this will all work.

Wow. There were a stack of announcements made at the key note. So I thought I would try to jot then down while I'm just waiting for my first technical session a Deep Dive on PowerPivot to start.

• The Parallel Data Warehouse Edition of SQL Server 2008 R2 has been released to manufacturing, there was a demo with a query over an 800 Billion record table that was returning in 19 seconds on a 40 node parallel cluster.
• Yahoo came up and talked about an SSAS cube of theirs, it's 12TB in size and they load 1.2TB every day with an average query speed of less than 10 seconds. The cube is loaded continuously during the day and they are looking to expand it to a 40TB cube in the near future.
• As most of you may have heard the PowerPivot Vertipaq column store is moving into SSAS in Denali (the next release of SQL Server) – what you may not have heard is that it is also moving into the relational engine. Amir Netz did a demo showing standard SQL queries over a 2 billion row table in SQL Server that simply had a Vertipaq covering index created over it returning in seconds. This looks like being a game changer for real time BI and ROLAP. [Update: apparently Vertipaq indexes are not updated in real-time]
• Project Crescent – a new interactive report building environment – keep your eyes open for information on this. You're going to have to look for videos – I can't do it justice in a bulleted list. Think of report builder on steroids with elements of Data Analyzer.

A few months ago Richard Lees did a blog post showing how you can calculate a LastNonEmpty measure using recursion. In Richard's example he used what I call "explicit recursion" in that he explicitly referenced the measure in it's own expression.

An example of this sort of calculation against Adventure Works would look like the following.

WITH
MEMBER Measures.LastNonEmptyExplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember, Measures.[LastNonEmptyExplicit])
            ,Measures.[Internet Sales Amount])
SELECT
  {[Measures].[Internet Sales Amount]
    ,[Measures].[LastNonEmptyExplicit] } ON COLUMNS,
  TAIL([Date].[Calendar].[Month].&[2003]&[7].Children,15)
ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].[Gloves]

If you run this you will see results like the following, I have included the raw measure so that you can see that way the calculation carries the last non-empty value forward. I have drawn in some arrows so you can see how the calculation would evaluate for the July 23 figure.

However as the title of this post would suggest, there is another variation on the syntax for doing recursion which I call "implicit recursion" and it looks like the following

WITH
MEMBER Measures.LastNonEmptyImplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember)
            ,Measures.[Internet Sales Amount])
SELECT
  {[Measures].[Internet Sales Amount]
    ,[Measures].[LastNonEmptyImplicit] } ON COLUMNS,
  TAIL([Date].[Calendar].[Month].&[2003]&[7].Children,15)
ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].[Gloves]

Note that the output is identical.

The only difference between these two examples is that the second does not explicitly reference itself, but because there is a .PrevMember call on the calendar date hierarchy and no other measure is reference the use of the current measure is implied. This is very subtle and I have seen it done accidentally before which lead to a lot of confusion.

Effectively "implicit recursion" is evaluated the same as if you had entered the following.

WITH
MEMBER Measures.LastNonEmptyImplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember, Measures.CurrentMember)
            ,Measures.[Internet Sales Amount])

And because at the point where the expression is evaluated the "CurrentMember" on the measures dimension is the calculation itself we end up with a recursion.

So my advice is to always use the "explicit" version when doing recursion. If you have a recursive measure in your MDX Script I would even go so far as to suggest putting a comment indicating that it is a recursive measure. Recursive measures are really powerful and are an extremely elegant coding technique, but they do have a performance impact, so you want to make sure that you are using them explicitly and that they are clearly identified.

Next week is the 2010 PASS conference. This will be my second PASS conference and I'm really looking forward to it. The content is great and I love the chance to meet up in person with all the really great people in the SQL community that come from all different parts of the world just to talk about SQL Server.

On Tuesday 9th for Lunch I am hosting a "Birds of a Feather" table on "SSAS Triple A – Administration, Automation and APIs". Between PowerSSAS, BIDSHelper and the ASSP projects on codeplex I have had a fair bit of experience in this area. So if you are interested in any of these topics or if you just want to drop by and have a chat you should hopefully be able to find me without too much trouble. And if you miss me there I will be on duty in the Ask Experts area after lunch for a few hours and then I'll be getting to as many of the BI sessions as I can.

I got asked a question a while ago on whether SSAS supports the transaction settings in SSIS. To answer that we really need to understand what happens when we set the TransactionOption property to Required on an SSIS container.

When you switch on transactions in SSIS it uses the Distributed Transaction Coordinator (DTC) to coordinate the transactions. The Distributed Transaction Coordinator is a Windows service that coordinates transactions across multiple processes and/or machines.

I did not expect that SSAS would support DTC transactions, but I could not find a source that stated this explicitly. I'll talk a bit more about why I did not expect this to be supported later. SSAS does use a transaction system internally to keep things consistent within itself, but that is different to DTC transactions.

So I setup a simple package to test this. I added a sequence container with the TransactionOption set to Required. Then I added two SSAS processing tasks with an empty sequence container that I could use in order to create a breakpoint between them.

Then I started the package and queried the DISCOVER_LOCKS DMV so that I could see the locks that were created during the processing.

If SSAS supported DTC transactions we should see these locks held for the life of the transaction. The locks are important as they will prevent other processes from reading/writing to the data that is not yet committed. You can see in the screen shot below that we have lots of locks with a LOCK_TYPE of 2 or 4 (which are processing read and write locks)

But when we get to the breakpoint in between the two processing operations you can in the screenshot below that all the processing locks have been released. The only locks that are visible are the LOCK_COMMIT_READ (type 8) locks that are held by the DMV query itself. (all queries take out this sort of lock to prevent a commit operation occurring during the middle of a query). At this point the updated version of the Products dimension is available for querying, even though our SSIS transaction has not committed.

Then when we proceed to the next processing step the locks for the next operation are taken out.

Then once everything has finished all the processing lock are again released.

I mentioned earlier that I did not expect SSAS to support distributed transactions and the reason for this is that If you inserted data into your data warehouse and processed your cube(s) in one transaction, a failure would mean that the whole transaction, including the insert into the data warehouse should be rolled back and I'm struggling to think of any time when this would be useful. It would make it really hard to trouble shoot issues, when the data that caused the issue has been removed.

So hopefully if you've made it this far you can see that SSAS does not support distributed transactions (which is what SSIS transactions are) and that this is a good thing.

If you do want to process your SSAS objects within an SSAS transaction the default behaviour is to do this, processing all objects sequentially in a single transaction. But you can change this if you like to process in parallel or to process each object within it's own transaction. Generally you will want to process everything in a single transaction as this will ensure that everything will remain in a consistent state.

Below is the processing options dialog from the SSIS "Analysis Services Processing" task showing the various transactional options.

TechEd 2010 Australia is approaching at a rapid pace and this year I have a speaking position. I will be talking on the topic of "PowerPivot: How, Why & When" doing some contrasting between PowerPivot and SSAS. And giving my opinions on where PowerPivot should be positioned in the Microsoft BI landscape. This will be happening at 3.30pm next Thursday.

Even if you don't manage to catch my talk you should be able to find me hanging around the SQL Server area in the exhibition hall.

Today I put out a new release for PowerSSAS. This release adds the following new cmdlets:

• backup-ASDatabase
• clear-ASCache
• get-ASConnection
• get-ASRole
• restore-ASDatabase

It also includes a Powershell v2 .psd1 module file and includes the option to just download a zip file which can be copied into a folder your modules folder. So you can now install PowerSSAS without needing to have admin privileges. There are instructions on installing PowerSSAS as a v2 module here.

I have also done some preliminary testing against SQL Server 2008 R2 and the 2008 files appear to work fine.

This release also fixes an issue with the dependencies that caused the 2008 version of PowerSSAS to look for the 2005 version of AMO.

You can get it from the downloads page of the codeplex site http://powerSSAS.codeplex.com

Next Tuesday, July 13 I will be speaking at the Sydney SQL User Group on "BI Power Tools". I am going to run through some of my favourite free BI tools including (but not limited to) some of the projects that I contribute to like BIDS Helper, the AS Stored Procedure project and PowerSSAS.

See here http://www.sqlserver.org.au/Events/RegisterMeeting.aspx?EventId=477 for more details and to register