Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2013/05/04/new-release---dax-studio-1.2-with-excel-2013-support.aspx

It’s been a long while coming, but the next release of DAX Studio is out. If you already have DAX Studio running in Excel 2010 you will not see anything new in this release, this release is all about getting Excel 2013 support and there has been quite a bit or work gone into the back-end to deal with some major changes to the way PowerPivot works inside Excel. We’ve also been doing some re-architecting with a view to possibly being able to support a standalone version that will run outside of Excel.

A big thanks to Greg Galloway for his assistance with the code for supporting PowerPivot in Excel 2013.

You can get the latest DAX Studio release from here https://daxstudio.codeplex.com/releases/

Below is a copy of the release notes

This version adds support for installing the addin into Excel 2013 including support for connecting to PowerPivot models.
Note: the Query Table feature is not supported against Excel 2013 PowerPivot models. This feature still works in Excel 2010 or when connected to a Tabular Server, but it was never officially supported by Microsoft and has proved to be unstable in Excel 2013 and has been disabled as it can cause occasional model corruption.
However Excel 2013 does support this method of doing the same thing manually. And we are trying to see if we can hook into this mechanism to re-enable this functionality in a future release.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2013/02/02/ssas-clearing-the-mdx-script-for-a-session-in-ssms.aspx

Sometimes when troubleshooting performance issues you may want to comment out the MDX Script in your cube in order to quickly isolate whether the MDX Script is a significant contributor to the issue. So if you can reproduce your issue in your dev environment you can open up your project in BIDS / SSDT, comment out the script and re-deploy. But what happens if you can't reproduce the issue against dev. You may not have the same server specs, or the same data volumes or you  may even have design changes in dev that have not yet been deployed that would cloud the issue.

There is a solution to this using the connection technique from my previous post. If you are a server or database administrator you can use the CLEAR CALCULATIONS command. This command effectively clears the MDX script for your current session. So what I usually do is to run the following:

CLEAR CALCULATIONS;

GO

CALCULATE;

As noted by joschko in the comments on the previous post you can use the GO keyword between statements to run multiple statements at a time in SSMS. Personally I don't tend to use this with SCOPE statements as "GO" is not an MDX keyword, it's only understood by SSMS, so you can't copy and paste it into your MDX Script and I like to keep the SCOPEs that I'm testing in a form that I can directly paste into the cube script.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2012/12/19/ssas-executing-mdx-scope-statements-using-ssms.aspx

So usually when I want to test an MDX expression in SSMS I'll write a query with a "WITH MEMBER…" clause to create a query scope calculated measure. But sometimes you may want to test a scoped assignment before putting it in your cube script. The following steps show you how to do this.

1. Click on the button to open an new MDX window, enter your server name and then click on the "Options >>" button

2. You must then specify the database that you are using

3. Then under the "Additional Connection Properties" tab you need to add a "Cube=" parameter with the name of the cube that you wish to apply the scope to.

So we can now write a normal MDX query against our cube to test that everything is working correctly.

Then we can execute our SCOPE statements. Note that SSAS will only let you execute one statement at a time, so I had to execute each of the 3 lines below separately. The following scope simply overrides the value of the Sales Amount measure to always return a value of 1. While this is not something that you would do in a real world scenario, it makes it very easy to see if our scope has been applied. (note: a statement can span multiple lines, but each statement will be terminated by a semi-colon)

After running the 3 separate statements one at a time we can then run our original query again to see the affects of our scope assignment.

Note that this is a session based assignment which will only be visible to queries run from the same session. As soon as you close this session the temporary assignment will disappear If you open another MDX window or connect using Excel you will not see the affects of the temporary scope statement unless you use the "SessionID" parameter on the connection string (or in the "Additional Connection Parameters" for SSMS).

You can find the SessionID by running "SELECT * FROM $SYSTEM.DISCOVER_SESSIONS" and getting the SESSION_ID column

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2012/03/21/bids-helper-1.6-beta-release-now-with-sql-2012-support.aspx

The beta for BIDS Helper 1.6 was just released. We have not updated the version notification just yet as we would like to get some feedback on people's experiences with the SQL 2012 version. So if you are using SQL 2012, go grab it and let us know how you go (you can post a comment on this blog post or on the BIDS Helper site itself). This is the first release that supports SQL 2012 and consequently also the first release that runs in Visual Studio 2010. A big thanks to Greg Galloway for doing the bulk of the work on this release.

Please note that if you are doing an xcopy deploy that you will need to unblock the files you download or you will get a cryptic error message. This appears to be caused by a security update to either Visual Studio or the .Net framework – the xcopy deploy instructions have been updated to show you how to do this.

Below are the notes from the release page.

======

This beta release is the first to support SQL Server 2012 (in addition to SQL Server 2005, 2008, and 2008 R2). Since it is marked as a beta release, we are looking for bug reports in the next few months as you use BIDS Helper on real projects.
In addition to getting all existing BIDS Helper functionality working appropriately in SQL Server 2012 (SSDT), the following features are new...

• Analysis Services Tabular
  • Smart Diff
  • Tabular Actions Editor
  • Tabular HideMemberIf
  • Tabular Pre-Build

• The Unused Datasets feature for Reporting Services now accounts for new features in Reporting Services 2008 R2 like Lookups and new features in Reporting Services 2012.
• SSIS: emit an informational message when a variable has an expression defined and EvaluateAsExpression = False
• SSAS: roles reports points to wrong server
• SSIS - Variable Copy / Move broken in v1.5
• "Unused DataSets Report" not showing up in Context menu on VS2005 if Solution Folders used
• SSAS Tabular: Create a UI for managing actions
• SSAS Tabular: Smart Diff improvements for new schema and Tabular models
• SSIS: Copy/Move Variable Erroring due to custom Control Flow item Icon
• SSIS Performance Visualization Index out of range
• fixing bugs in AggManager when aggregation design IDs don't match names

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.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/10/11/sql-pass-summit-amp-mvp-deepdives-volume-2.aspx

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.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/10/10/book-review---mdx-with-microsoft-sql-server-2008-r2.aspx

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.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/09/23/im-an-analysis-services-maestro.aspx

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)

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

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/06/08/bids-helper-version-1.5-released.aspx

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

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/03/17/mdx-needs-a-function-or-macro-syntax.aspx

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 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)

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2011/01/12/a-new-blogger-on-the-ssas-team.aspx

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)

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2010/11/19/do-dax-and-mdx-need-a-safe-divide-operator.aspx

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.

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.

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2010/11/16/new-technical-article-on-the-columnstore-vertipaq-indexes-in-denali.aspx

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.