DAX Studio Review

One of the enjoyable and privilege components of my work is discovering new things and learning new things, especially in becoming more productive. DAX Studio is a great example of this.

DAX Studio is a result of great hard work and dedication from BI experts; Paul te Braak, Marco Russo, Darren Gosbell and Angry Koala. The product can be downloaded from Codeplex. The version in this review is 1.0.1.

This article is a review of DAX Studio based on my experience at work. It does not encompass all usage scenarios under the sun for DAX, however it should provide some insights on the pros and cons of the product.

DAX Studio is an Add-In

DAX Studio is an add-in for Excel 2010 (I believe the product would also work for Excel 2013 as well, but I am yet to test this). After installing DAX Studio, you will see that the button becomes available on the Add-In tab

DAX Studio Add-In Button

Upon clicking on the DAX Studio, a dialog box would appear, requesting for connection to either an existing PowerPivot (default) or a tabular model instance.

Connect To detects no PowerPivot model

 

It is quite interesting that when we connect to the PowerPivot model linked to the workbook, the connection will say “Microsoft_SQLServer_AnalysisServ…”. Unfortunately the name is cut off from the drop down box and it’s not resizeable. Similarly it is not easy to pick a tabular database from the drop down list when they have long name with the same first few characters as shown below.

 

Perspective is selectable from the dropdown list on the right near where the Server Name drop down box.

Similar to SSMS, the following information information is shown on the bottom left of DAX Studio window:

  • Server Name ($Embeded$ when connecting to a Power Pivot model)
  • SPID
  • Version

 

DAX Studio Shines

DAX Studio allows you to browse through the objects that you have defined on the model. The calculated measures are shown under the table that it is created, not grouped separately like in SSMS under “measures”. In the version that I am evaluating, the calculated measures are listed based on the creation time/date. This could be a good thing for some users. Unfortunately, in my case, I am implementing over a hundred of calculated measures (it’s for a specific Accounting report, hence lots of measures) within my model, making it harder to find the measure I want sometimes. But at the same time, when I’m still debugging the newest calculated measure, it’s really good as I can go to the bottom of the list and pick the last calculated measure that I want to test.

I also find that the ability to see the hidden fields or tables in DAX Studio is very powerful. Quite often, my query requires those hidden column or tables as part of calculation. In addition, it makes life so much easier in debugging what the hidden columns that were used as part of the calculation.

Based on my experience when using DAX Studio, there are two major aspects that I find very useful.

1. Performance of the DAX Studio when being used for querying

More often than not, I have a few pivot tables within one workbook. When I only need to inspect / debut a calculated measure, I wouldn’t want to refresh the whole pivot tables. So, with DAX Studio, I am able to perform a smaller set of unit testing independently from the rest of my workbook. I find that it’s a lot quicker for me to refresh the EVALUATE statement in DAX Studio than for the PowerPivot to refresh the data.

2. Analysing performance of queries

As part of unit testing, I am often is concerned with how long my query runs. I can go to the DMV tab and perform drag and drop on useful DMVs like:

select * from $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS

In addition to being able to see the different types of performance counter, DAX Studio also returns a message about errors and how long the query takes.

 

Let’s take a look at the Output generated by DAX Studio. The actions that can run on DAX Studio are:

  1. Output query to Excel as a Table
  2. Output query to Excel as a static data
  3. Output query to Grid dialog box
  4. Evaluate only (no output)
  5. Clear Cache

 

There are also options on creating new sheet or overwriting existing sheet when Output query to Excel is chosen.

There is support for line numbers, saving the query and – most of all – syntax highlighting.

Where to from here?

As a BI Consultant working with Tabular Model and PowerPivot solutions on daily basis, I think DAX Studio is a superb companion. It helps in increasing developers productivity.

I think the product can be improved further. Here are some small areas of improvement which would be helpful (I’m talking based on my experience – if you share my view on areas of improvement, please vote or comment on the codeplex comment link).

1. More support for SSMS/VS short cut keys, e.g. changing case to upper case or lower case, Ctrl+K Ctrl+C to comment things

2. Keeping the column format as specified in the model. Even though the calculated measure is formatted as shown below, the output is not retaining the format.

Internet Total Sales format is Currency

 

 

The workaround for this is to use the “FORMAT” function in the DAX query, but this is not ideal.

3. Ability to sort the calculated measures – at the moment it’s in the order they’re created, which might not be the best.

4. Providing a warning dialog box – “You have unsaved query. Are you sure you wish to exit DAX Studio?”. I have been caught a few times accidentally closing DAX Studio instead of the Grid dialog box and lost some changes :(

5. Displaying DAX query plan

6. Adding integration to source control

7. Resizeable Database Name Dropdown Box.

Wrap Up

I have been using DAX Studio for over a month now and am very delighted that such product exists. This really helps in the development life cycle of Tabular Model and PowerPivot solutions. Is it overkill, given SSMS is available? No, querying PowerPivot via SSMS is not an option (not that I am aware of). Would there be more support for the product? Yes, I believe so. I would highly recommend downloading, using the product and providing feedback. The project is built and coordinated by BI experts in the world who are passionate and actively involved in implementing Tabular Model and PowerPivot solutions.

DAX Studio is a great tool for developing DAX query, unit testing and querying performance statistics. It is easy to use and can definitely help.

If you have any comments and feedback on DAX Studio, please post them on the Codeplex project so it can be tracked. I’m also happy to hear thoughts around the product or a similar one.

Further Reading

Using DAX studio to create PowerPivot measures by Javier Gullien

Introduction to DAX Studio by Paul Te Braak

DAX Studio 1.0 beta available #dax #tabular #powerpivot by Marco Russo

Gantt Reworked with ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE by David Churchward for PowerPivot Pro

 

SQL Server Virtual Presentations

23 Oct 2012 Ready, Steady, Catch! hosted at SQL Lunch

SQLLunch.com Session Recording - available soon

Slide Deck

 

1 Nov 2012 Power View for Geographical Analysis hosted at PASS DW/BI VC

Coming soon

 

 

 

PASS Summit 2012 Tips

Having such a fantastic experience last year at PASS Summit 2011, I cannot resist to volunteer as an Alumni Mentor for the First Timers program this year.

I have quite a number of First Timers in my group and I have been sending them with tips fairly regularly. I plan to do so until the PASS Summit 2012 starts on Nov 6, 2012. Some of these tips I have covered in my previous First Timers article. This year, I’m planning to put a bit more details and update it regularly prior to the Summit.  I think these tips should be useful for First Timers, and would be a good checklist for Alumnis as well.

So here are what I have shared so far in reverse chronological order. If you have any other tips, please feel free to share them too!

Tip #9: Updates from last week and last reminders

The PASS Summit 2012 mobile guide is now available.

On October 25th, Rick Heiges – a former PASS Board Member – discussed “What to Expect at PASS Summit 2012”. The slide deck is available for download.

Also, for every session, you are able to voice your feedback via a survey. I believe this year it will be done electronically. Please fill in the form with your feedback of the session. The feedback process is important for the speakers, PASS and the community. Good quality feedback will help speakers to better themselves and PASS to know how good the speakers/sessions are, which will help for future presentations for the community.

A few reminders:

1. Don’t forget your business cards
2. Attend First Timers orientation (important)
3. Have a plan on which sessions to go
4. Come in early for the Keynote sessions
5. Come in early to register on site at the Washington State Convention Center
6. Say “Hi” to fellow First Timers and “Hi” to alumnis, speakers, volunteers, sponsors and SQL CAT team.
7. Be safe and healthy; have a good learning time and most importantly have fun!

We really are not far away from PASS Summit at all. Some of us will begin their overseas or interstate travel this week for PASS Summit (and may be SQL Saturday #172 in Portland) – like myself. If you are, travel safe and see you in Seattle.

 

Tip #8: PASS Summit 2012 Webcast and SQL Saturdays

[Posted on 20 Oct 2012]

A few days ago, Denny Cherry shared very interesting information for First Timers about PASS Summit 2012. In the session titled SQL PASS 2012 First Timers Webcast, he discussed the following areas:

  • Transport/accommodation.
  • Out and about in Seattle.
  • Getting around the conference center. The layout is quite different from last year’s Summit. It looks like we have expanded the area to cater for more people.
  • Things to do in Seattle including networking events and SQL Karaoke.
  • The meaning behind the Session “Levels” – 100, 200, 300, 400, 500 mean, so you can appropriate choose the sessions for you.
  • The type of Sessions – PreCon, Regular, Spotlight, Half-Day and Lightning Talk.
  • Networking and meeting new people.
  • Registration schedule.

If you missed out, it is worth watching the session recording.

At this stage, the PASS Summit organizers are busy with allocating sessions to the appropriate rooms. Some sessions may be moved to another location depending on the demand. So make sure that you use the Schedule Builder to choose the sessions that you would like to go to. Seats are allocated on first come first served basis. Most of the time, there will be enough seats. In the case that the room is too full or you miss out on some sessions, you can pre-purchase the sessions now, or after the event.

I would also like to remind you that there is going to be another upcoming webcast What to Expect at PASS Summit 2012 – presented by Rick Heiges.

I hope by now you would already have the following things organized:

  1. Transport / Accommodation
  2. Use the schedule builder to put together a list of sessions to go to
  3. Check out the networking events
  4. Have your business cards ready

It is wise to visit the Registration booth at the Convention Center, to get your pass for the Summit and your swag bag on or before early Tuesday, 6 Nov 2012 at the Convention Center. They usually have enough Staff to serve a large crowd at the Registration booth, but it’s better to get them early then missing out on your First Timers Orientation session. Registration opens from Sunday evening, 4 Nov 2012. See the At-a-Glance page for more information on the schedule and what you could expect at the Summit.

By the way, SQL Saturday #172 in Portland (about an hour by plane) is held on Saturday, 3 Nov 2012, which is free. There is also SQL Saturday #166 in Olympia (about 90 minutes drive from Seattle) which is held on the Saturday after, 10 Nov 2012, and is also free. So, why not check them out if you’re in the area! There will be lots of people attending PASS Summit who are presenting at these SQL Saturdays, such as myself. It may be worthwhile connecting with others who may be heading to these SQL Saturdays too.

 

Tip #7: Have you read the Summit Bulletin?

[Posted on 12 Oct 2012]

By now you should have received the Summit Bulletin email from the PASS Headquarters, which contains a plethora of information on the exciting things that are coming up at the Summit.

I attended the Keynote sessions from PASS Summit 2011 and really enjoyed it. They talked about SQL Server 2012 last year and what’s new in the SQL / Azure world. We had both Microsoft SQL Server Executives Ted Kummert and Quentin Clark too. So, I’m happy that we have them this year again and am looking forward to hearing what they have to say in on the Keynote Addresses this year.

Did you know that PASS Summit attendees are entitled to 50% discount? There will be free Practice Test and e-Learning. If you’ve been thinking about getting your first Microsoft SQL Server certification or upgrading your existing ones to SQL Server 2012, now is a good time. You might be home with a certification or two!

Luncheons are ideal for networking and meeting new people. So I hope to see many of First Timers in my group during the Luncheons. Don’t be shy, say Hi to your fellow First Timers,/Alumni Mentors, make new friends and catch up with your old friends too 

In case you miss out the email notification from PASS, the 24 Hours of PASS Summit 2012 Preview session recordings are available from here.

 

Tip #6 – First Timers Events

[Posted on 8 Oct 2012]

As you may already know, PASS website contains a wealth of information on the Summit and is updated frequently.

Next week we will kick off the first couple of First Timers events:

(Subject to Change)

Wednesday, October 17, 2012

20:00 GMT Webcast: SQLPASS 2012 First-Timers – presented by Denny Cherry

Thursday, October 25, 2012

16:00 GMT Webcast: What to Expect at PASS Summit 2012 – presented by Rick Heiges

I would also like to remind you of the Professional Networking Events on Nov 6th afternoon. Please ensure that you send the RSVP on time to secure your spot. If you haven’t received an invitation, please let the organizers know.

Tuesday, November 6, 2012

1:00pm – 4:45pm Professional Development and Networking Workshop Session #1* (Room 4C 3&4)

2:00pm – 2:45pm Professional Development and Networking Workshop Session #2* (Room 4C 3&4)

3:00pm – 3:45pm Professional Development and Networking Workshop Session #3* (Room 4C 3&4)

4:00pm – 4:45pm Professional Development and Networking Workshop Session #4* (Room 4C 3&4)

 

 

Tip #5 Maximising your First Timers Experience

[Posted on 4 Oct 2012]

One of the ways to get the most of the PASS Summit is to have at least one goal on what you want to achieve, even if it was just to “hang out” with SQL Family. Everyone is unique :)

I would recommend having a list of 3 goals – you don’t have to be “hardcore” about it, but keep them in mind when you are at the Summit. A few “goals” require planning, like which sessions to go. You may find that some sessions are conflicting with one another; or may be changed on the date. So for that reason, it would worth noting other sessions of interest that are in the same time slot.  Check out the Schedule Builder, where it’s an equivalent of “wish list” or “shopping cart”. Once you’ve picked the sessions you want to go to, you can have the list emailed to you or added to your calendar.

During the Summit, especially the Professional Network session(s) and the orientation, there will be a lot of opportunities to network. So, be sure you are ready and bring your business cards.

It may sound silly at first, but I’ve been asked for business cards numerous times during these events. It seems to be expected these days.

 

 

Tip #4 – Schedule

[Posted on 27 Sep 2012]

Great news! The PASS Summit 2012 schedule is out. You can check it out via the following links

Schedule in PDF format

Interactive Schedule via Pivot Viewer by LobsterPot Solutions

As it gets closer to the date, there’s going to be more tools available that will help you to add the sessions to your calendar. So, keep an eye out.

 

Tip #3 Transport and Accommodation

[Posted on 27 Sep 2012]

6 weeks away from PASS Summit seems to be long, but it really isn’t – especially if you are not local. If you are not based in US, like myself, it would be worthwhile organising your visa, flights and accommodation as early as possible.

Similarly if you are not based in Seattle, please organise your flights/transport as well as accommodation as soon as possible.

For travel and accommodation to PASS Summit, please consult the PASS Summit 2012 Travel & Accommodation page.

If you’re local, well, what can I say other than lucky you!

It would also be worthwhile to start planning on how you’d want to get to Washington State Convention Center, i.e. by car or public transport, etc.

 

Tip #2 – 24 Hours of PASS Summit 2012 Preview

[Posted on 20 Sep 2012]

In less than an hour, we will start the 24 Hours of PASS Summit 2012 Preview. It’s virtual, free and is a sneak peak of what you’re going to see at the Summit this year. Quite a few of these sessions are actually from the Pre-Con, so if you are still “umm-and-arr”-ing which Pre-Con sessions to go to, check them out at 24 Hours of PASS. If you haven’t registered, it’s not too late.

Stay connected by using #24HOP on twitter.

Update: Session recordings should be available within 2 to 3 weeks post event.

 

Tip #1 – Stay Connected

[Posted on 19 Sep 2012]

So, we are just 7 weeks away.

A great way of staying connected is via Twitter, with hashtag #sqlpass. For in person events, designed specifically for First Timerswhich you can have a look here: http://www.sqlpass.org/summit/2012/Connect/FirstTimers.aspx. This page will be updated with more information for First timers and events, so please check regularly.

Please mark your calendar to remind you of these events. I would encourage you to make yourself available in the afternoon of Nov 6th, 2012, especially for registration and attending the First Timers Orientation.

 

Other resources

PASS First Timers Guide and Schedule – 2012 edition by Jen McCown

Attending the Pass Summit, Pre-Summit Planning for First timers by  Pat Wright

How do you introduce yourself to people at SQLPass Summit?  by Jen Stirrup

Community Blog Roundup for PASS Summit 2011

 

PASS Summit 2012 - Nov 6th - 9th 2012