My 2012 US Tour with #SQLFamily

My Pacific North West US tour earlier this month officially concluded on 11 Nov 2012. I am very honoured and grateful that I met new friends, caught up with old friends and delivered 4 presentations too. The greatest thing about this trip is the community and networking, especially at SQL Saturday 172 in Portland, PASS Summit 2012 in Seattle, and SQL Saturday 166 in Olympia.  The sessions delivered in these two SQL Saturday events were superb and attendees were so eager in learning.

Last year, I was just a book-worm (or a session worm?) where I went to every single session slot, as I had two very specific aims; i.e. learning SSAS performance tuning and finding out more about SQL Server 2012. I achieved what I wanted and was really happy to find a gateway to learning. I met lots of new people during the sessions too.

This year, I decided to take a different approach. Throughout 2012, I have talked to people from other parts of the world via twitter, LinkedIn and emails. So, it was important during PASS Summit 2012 for me to meet them, and to thank them in person for sharing their thoughts and opinions. The result is, throughout my US trip, I met more people, from different parts of Australia, United States (outside of Seattle), United Kingdom, Germany, Portugal, Spain, Mexico, Denmark, Slovenia, Philippines, Netherland, Norway, Brazil, New Zealand, to name a few.

One thing that I realise is that we are a family, #SQLFamily; we are diverse in knowledge, expertise and other aspects of life, but we are one that we share passion in knowledge sharing and learning. So, I am proud to be part of #SQLFamily and that every time I am around my #SQLFamily, I feel home.

As an Alumni Mentor for the First Timers group at PASS Summit 2012, I sent a few tips. One of them was that everyone’s experience for PASS Summit would be unique. You are entitled to have different experience from your peers. The most important thing is that you are having a good time and learning. I had a blast! So, I’d like to thank the sponsors, organisers, volunteers, speakers and #SQLFamily in making PASS Summit 2012 happen.  The feedback that I have received from other speakers, my First Timers group and attendees have been nothing but positive. So, once again, many thanks.

How do you rate your PASS Summit 2012 experience? Share your thoughts here and let PASS know!

Last but not least, I would also like to extend my thank you to SQL Saturday 172 and SQL Saturday 166 organisers, sponsors, volunteers and attendees.


Highlights from PASS Summit 2012

WIT Luncheon – PASS Summit 2012 by Sarah Strate

PASS 2012 Day 1 – Interviews and Keynote

PASS 2012 Day 2 – Keynote and Session Recording (Query Tuning Mastery with Adam Machanic)

PASS 2012 Day 3 – Interviews and Session recording (Working with Claims and SQL Server BI Technologies with Adam Saxton) 

PASS Global Community Video

PASS 2012 Keynote and Mobile BI Announcement by Marco Russo

PASS 2012 Announcements by James Serra




WIT Luncheon at PASS Summit 2012


Moderator for today’s luncheon is Wendy Pastrick, and the luncheon is spoonsored by SQLSentry, Inc.

Panelist Bios are available from

  • Stefanie Higgins
  • Denise McInerney
  • Kevin Kline
  • Jen Stirrup
  • Kendra Little


Ballroom 6AB is filled with 720 people attending WIT luncheon for Q&A sessions with the panelists on “Where have been and where are we going?”

12:10 PM

WIT envolvement in education changes throughout time. Number of PASS Events

In 2011 we have 24 HOP organised specificlaly for female speakers. This has encouraged more women to participate and to speak at various PASS events. Wome in all computing application has declined to 25% in US. Women leave the Technology industry twice than men.

Companies like Microsoft, Google, Twitter have been investing in WIT, such as in form of training and process.

DigiGirls at Microsoft is a great example of initiative for WIT.

Let’s start thinking “what can inspire or have inspired women to contribute more in the Computing / Technology field?”


12:17 PM

At PASS, funding / sponsoring is not like those in IBM or larger companies. But we at PASS have great attitude, community. As a community, we need to say “Come in, sit next to me, let me introduce you to my friend”. At PASS we want to be the organisation that says more than just “Welcome”.

Based on experience, younger women needs nurturing and encouragement to see why IT is awesome, and not succumbed to peer pressure.


12:25 PM

International speaker, Jen Stirrup, brings her views and findings of WIT. It seems that not only Women are more disengaged in IT but more so in data.

SQL Saturdays in Europe have managed to attract female attendants. It became apparent that both men and women are interested in WIT and diversity issues.


12:35 PM

Kendra Little shared her experience from working as just an employee to an enterprenuer, via PASS. Speaking at various events, helps growing mentoring skills and evetually develops consulting / consultancy sense essential in becoming an enterpreneur.


12:40 PM

Focus groups with women who dropped out classes, help in understanding why it happens and how we can counteract that.  Money may not be the only incentive for IT career. Most women are interested in the work/life balance.

PASS WIT website ( contains resources for you to start up WIT community near you!


12:50 PM

Work life balance is quite important to women. Track how you spend your time – get some data – analyse it and make some proposal to do things more effectively.  Web cams and time tracking are ways to show that you are working hard and effectively. Retaining employees by offereing work/life blanace is important.

A great way of proposing more flexibility is “Why don’t we try it for a month, if I don’t work more effectively, we’ll revert back to how it was before”.


12:57 PM

There are organisations that can help you get started in participating in WIT in your area. and some examples in US.


1:00 PM

A comment from an attendee “Women who are in IT seem to be more analytical and less emotional than those who aren’t”. Not eveyone is in IT, it’s best to give opportunities / facilitates opportunities for women to try it out first.


1:15 PM

Culture greatlly affects Women involvement in IT, and people in some countries are being funellled into specific areas of study or work.  PASS Global Growth and WIT can help in reaching out those women in other countries with small participation of Women in IT.

“You can be what you want to be and great at what you do” is a great encouragement for young women to get into IT.


1:25 PM

Closing note: send a tweet, blog about it and mention it to some one to start conversation on  ”Where have been and where are we going?”



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:


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



Upgrading from SQL Server 2012 Evaluation Edition

This article provides a step-by-step instruction on how to upgrade a SQL Server 2012 RTM Evaluation Edition server to a BI edition. Similar steps can be used to upgrade to any other licensed editions, Developer/Standard/Enterprise.

Long story short, changing the edition from the Evaluation edition to another one does not require building and migrating to a new server with the intended edition. As the Evaluation edition is a 180-day trial edition of Enterprise edition, there can be some issues when changing the edition to a non-Enterprise edition due to incompatible features used. This article assumes that the features used in the sample SQL Server instance with the Evaluation edition are non-Enterprise only features.


  1. A SQL Server 2012 RTM instance in Evaluation mode to be upgraded.
  2. A Developer/Standard/BI/Enterprise License Key or the ISO file of the SQL Server 2012 RTM installation that contains the license key.


In the sample below, the SQL Server instance is running on Evaluation mode, and it is to be upgraded to the BI edition. None of the Enterprise only features are installed.

1. Run the SQL Server 2012 RTM ISO file.

2. Choose Maintenance > Edition Upgrade.

3. Click Next on Setup Support Rules.

4. Follow the Upgrade the Edition for SQL Server 2012, which starts with Setup Support Rules.

5. On the next screen, choose Enter the product key and type in the product key of the new license. This is usually pre-filled for Developer edition; or if the licensed ISO file is used.

6. Accept the License Terms on the next screen.

7. Select the instance to be upgraded to the new edition.

8. The installation will then continue with running Edition Upgrade Rules. This is where feature compatibility is being checked.

9. Click Upgrade on the next screen.

10. Once the upgrade is successful, click Close.

11. Verify that the existing SQL Server instance has been upgraded to the correct edition by checking via SQL Server Management Studio. For more information on how to check the edition, see my blog post here.


Wrap Up

This post provides a step-by-step instruction on how to upgrade a SQL Server 2012 RTM instance with Evaluation edition to a different edition. It is a relatively easy process when only the compatible features are used. To check when the Evaluation edition expire, please see my earlier post here.

If you have had some curly experience in upgrading, please feel free to leave a comment. Thank you!


Further Reading

Upgrade to a Different Edition of SQL Server 2012 (Setup) on Technet

Retrieving SQL Server 2012 Evaluation Period Expiry Date



Sydney SQL User Group Presentations

Below is a list of slide decks from my past presentations at Sydney SQL User Group. I plan to update this post within a few days of the presentation.

Hope to see you at the next User Group meeting. Remember, it’s held every first Wednesday at lunch time and every second Tuesday evening of the month at Sydney Mechanics’ School Arts.


11 Sep 2012 – Direct Query vs Vertipaq in BISM

7 Mar 2012 – High Volume Data Processing Techniques Without Driving Your DBA Crazy

13 Sep 2011 – Important Trace Flags Every DBA Should Know


PASS Summit 2012 Lightning Talk Session

Firstly, I’d like to thank everyone who voted for my “TRY CATCH in a Nutshell” Lightning Talk session for PASS Summit 2012. I am very honoured and thrilled to be selected.

I’d like to congratulate all the selected speakers and I’m very excited to meet you all at the Summit.

Lastly, I hope you’d be able to join SQL Family in PASS Summit this year. It’s not too late to register!


PASS Summit 2012 - Nov 6th - 9th 2012


24 Hours of PASS Summit 2012 Preview

How awesome is it to be able to watch 24 out of the 190+ upcoming PASS Summit 2012 sessions from your own home?

Very awesome! I’m excited about the 24 Hours of PASS Summit Preview.  It’s 24 consecutive hours of great sessions for SQL Server Professionals delivered by outstanding speakers in the SQL Community around the world, and best of all FREE!

I am also thrilled to be a moderator for Session 22, with Peter Myers on Big Data Analytics with PowerPivot and Power View.

The tracks in 24 Hours of PASS event include

This event starts on 20 September 2012 at 12:00 GMT. So, be sure you check the schedule in your time zone.

Below is a list that I’ve created for Australia Easterm Standard Time (AEST). Also see more info here:


** 20 Sep 2012 GMT **

Session 01 (BIA) – Starts at 12:00 GMT (Thu, 20 Sep 2012 at 22:00 AEST)
Choosing the Right Reporting Platform 
Presenter: Brian Knight, Devin Knight

Session 02 (DBA) – Starts at 13:00 GMT (Thu, 20 Sep 2012 at 23:00 AEST)
Best Practices for Upgrading to SQL Server 2012 
Presenter:Robert Davis

Session 03 (AppDev) – Starts at 14:00 GMT (Fri, 21 Sep 2012 at Midnight AEST)
Three Ways to Identify Slow Running Queries 
Presenter: Grant Fritchey

Session 04 (AppDev) – Starts at 15:00 GMT (Fri, 21 Sep 2012 at 01:00 AEST)
Fasten Your Seatbelt – Troubleshooting the Most Difficult SQL Server Problems 
Presenter: Klaus Aschenbrenner

Session 05 (CLD) – Starts at 16:00 GMT (Fri, 21 Sep 2012 at 02:00 AEST)
SQL Server Private Cloud != Azure 
Presenter: Allan Hirt, Ben DeBow

Session 06 (AppDev) – Starts at 17:00 GMT (Fri, 21 Sep 2012 at 03:00 AEST)
What are the Largest SQL Server Projects in the World? 
Presenter: Kevin Cox

Session 07 (AppDev) – Starts at 18:00 GMT (Fri, 21 Sep 2012 at 04:00 AEST)
Practical Demos of Text Mining and Data Mining using SQL Server 2012 
Presenter: Mark Tabladillo

Session 08 (DBA) – Starts at 19:00 GMT (Fri, 21 Sep 2012 at 05:00 AEST)
PowerShell 101 for the SQL Server DBA 
Presenter: Allen White

Session 09 (BID) – Starts at 20:00 GMT (Fri, 21 Sep 2012 at 06:00 AEST)
Mobile Business Intelligence 
Presenter: Jen Underwood

Session 10 (BID) – Starts at 21:00 GMT (Fri, 21 Sep 2012 at 07:00 AEST)
Slow MDX Queries: The Case of the Empty Tuples 
Presenter: Stacia Misner

Session 11 (DBA) – Starts at 22:00 GMT (Fri, 21 Sep 2012 at 08:00 AEST)
Using SQL Server 2012 Always On 
Presenter: Denny Cherry

Session 12 (PD) – Starts at 23:00 GMT (Fri, 21 Sep 2012 at 09:00 AEST)
Leadership – Winning Influence in IT Teams 
Presenter: Kevin Kline

** 21 Sep 2012 GMT **

Session 13 (BIA) – Starts at 00:00 GMT (Fri, 21 Sep 2012 at 10:00 AEST)
BI Architecture With SQL 2012 & SharePoint 2010 
Presenter: Rod Colledge

Session 14 (DBA) – Starts at 01:00 GMT (Fri, 21 Sep 2012 at 11:00 AEST)
DBCC, Statistics, and You 
Presenter: Erin Stellato

Session 15 (BIA) – Starts at 02:00 GMT (Fri, 21 Sep 2012 at Noon AEST)
SSIS Design Patterns for Fun and Profit 
Presenter: Jessica Moss, Michelle Ufford

Session 16 (AppDev) – Starts at 03:00 GMT (Fri, 21 Sep 2012 at 13:00 AEST)
Characteristics of a Great Relational Database 
Presenter: Louis Davidson

Session 17 (BIA) – Starts at 04:00 GMT (Fri, 21 Sep 2012 at 14:00 AEST)
What’s All the Buzz about Hadoop and Hive? 
Presenter: Cindy Gross

Session 18 (AppDev) – Starts at 05:00 GMT (Fri, 21 Sep 2012 at 15:00 AEST)
Taking SQL Server Into the Beyond Relational Realm 
Presenter: Michael Rys

Session 19 (BIA) – Starts at 06:00 GMT (Fri, 21 Sep 2012 at 16:00 AEST)
Agile Data Warehousing with SQL Server 2012 
Presenter: Davide Mauri

Session 20 (AppDev) – Starts at 07:00 GMT (Fri, 21 Sep 2012 at 17:00 AEST)
Digging Into the Plan Cache 
Presenter: Jason Strate

Session 21 (BIA) – Starts at 08:00 GMT (Fri, 21 Sep 2012 at 18:00 AEST)
Introduction to Microsoft’s Big Data Platform and Hadoop Primer 
Presenter: Denny Lee

Session 22 (BID) – Starts at 09:00 GMT (Fri, 21 Sep 2012 at 19:00 AEST)
Big Data Analytics with PowerPivot and Power View 
Presenter: Peter Myers

Session 23 (CLD) – Starts at 10:00 GMT (Fri, 21 Sep 2012 at 20:00 AEST)
Best Practices and Lessons Learned Using SSIS for Large Scale Azure Data Movement 
Presenter: Steven Howard

Session 24 (PD) – Starts at 11:00 GMT (Fri, 21 Sep 2012 at 21:00 AEST)
Mentoring for Professional Development 
Presenter: Andy Warren


Also, check out what the PASS Director, Rob Farley has to say about the upcoming 24 Hours of PASS 

See you there!


PASS Summit 2012 - Nov 6th - 9th 2012

Power View Map in Excel 2013 Preview

With Office 2013 preview launched in mid July 2012, there have been ubiquitous posts on the new Power View feature in Excel 2013. This is a very welcomed feature and I would sense that it will win Business adoption as an easy to use and “hopefully” affordable Self Service BI.  Here’s my thought on a fairly early version of Power View feature in Excel 2013 Preview.

Power View

Since the first time Power View introduced in SQL Server 2012 and made available in SharePoint 2010, I’ve always been fan of it. With most tools if you have analytical mind and are great with visualization / design, most reports you’ll create will be useful and used – and in my opinion – Power View is definitely one of these tools.

Using the Australia Broadband Guarantee data set provided from, I am able to quickly and painlessly create an interesting Power View Map in Excel 2013 that I can analyze the investment the Australian government has made, at country level and down to specific Suburbs / Postcodes. The map feature is currently not available in SQL Server 2012 RTM with SharePoint 2010. I do hope that it will be introduced as a Service Pack 1 to SQL Server 2012.

Sample Data and Map

The Australian Broadband Guarantee (ABG) was an Australian Government initiative designed to help residential and small business premises access high-quality broadband services regardless of where they were located. The program targeted premises unable to access commercial metro-comparable services, particularly those living in remote parts of Australia

More information on the project:

The dataset I’m using has a very straight forward data structure. My motive here is to see which areas have the funding gone to? Does the amount of funding yield many connections? Is there any isolated area that gets funding?

Zoomed at the Nation level:

Australia Broadband Guarantee Map - Nation Level


Zoomed at a State level, with funding > $1 million filter: (hovering on one of the bubble gives me a descriptive tooltip)

Australia Broadband Guarantee in NSW


I can zoomed in further to the actual postcode as the granularity of the location is postcode, as shown below:

Australia Broadband Funding in Aarons Pass NSW


Advantages and Disadvantages of Using PowerView Map

Although it’s still at its early stage as Office 2013 is only released in a Preview version, I consider Power View Map feature has the following advantages and disadvantages.

Advantages of PowerView in Office 2013 preview as at the time this post is written:

1. It integrates with Excel. So it allows for rapid prototyping and development.

2. No knowledge of Power Pivot is required. Power View can be created based on a table on a worksheet and it automatically creates Power Pivot data based on the table. Knowing Power Pivot and being able to configure relationship and data categories, will help for performance (so it seems based on my testing).

3. The map feature recognises longitude and latitude pairs which are easily attainable. It can also use information such as Suburb and State.

4. It uses Bing Map as the map layer, so there is no need to acquire ESRI shape files or converting to Spatial data type.

5. Performance of the Power View map feature is amazing. When plotting over 2000 locations, I could barely notice the wait time.

6. Most importantly, the ability to interactively zoom in/out the map – from world to road level – is fantastic. This is something that is not available in Report Builder or SSRS yet.


Disadvantages of PowerView in Office 2013 preview as of the time this post is written:

1. No support for Spatial Data type. So there is no user defined zone that can be layered on top of the map.

2. Location, Longitude and Latitude values are all needed. Location could be PostCode, City/Suburb or Country.

3. The Location category is US Centric. This meaning that if State is used as the Power View Map Location, and there is a State with the same name or abbreviation in US, it would point to US. As an example, “WA” is Western Australia in Australia (, but it is also Washington in US (; which is over 9,000 miles away or over 14,000 kilometers.

4. Unable to connect directly to the Analysis Services; i.e. it is required to import the data into Power Pivot first or Excel table, then create a Power View map based on the imported data.

There are a few areas that still don’t work properly. The application crashed a few times on me when converting the initial pre-built table created by Power View to a map, as well as when the column detects text (larger size) instead of numbers for aggregation. I think in time, these issues will be ironed out.

Wrap Up

Office 2013 seems to offer a number of nice features, especially for self-service BI solutions using Excel 2013. I have no doubt that small businesses will start adopting Excel 2013 more and more in the near future, for data analytics using the new readily available Power View feature in Excel.

Further Reading

Excel 2013 Preview with PowerPivot and Power View by Kasper De Jonge

Microsoft Business Intelligence in Excel 2013, SharePoint 2013 and SQL Server 2012 SP1 by Microsoft BI Team

Building a Simple BI Solution in Excel 2013, Part 1 by Chris Webb

Shark Week Special: Mapping Shark Attacks (Drillable Maps and Hyperlinks) by Sean Boon