PASS Attack!

11 10 2013

It’s Friday. Tomorrow I present two sessions at SQL Saturday #238 – Minnesota. Then I take off for PASS Summit 2013 on Sunday. I then wrap up the week at SQL Saturday #237 – Charlotte BI Edition.

I hope to see a variety of you at these different events.

If you come to SQL Saturday 238, you will find me in two sessions to kick off the day followed by time at the Magenic or PASS booths. I am presenting on Power BI and Tabular Models. If all goes well, my son will be joining me. He is a bit taller …

Next up is this years Summit. While I am not speaking at this event, I will be participating in the Community Zone as well as many of the volunteer meetings on Tuesday. If you are a user group leader, SQL Saturday leader, or other volunteer, be sure to connect with other volunteers to share ideas and generally grow the SQL Server community.

Finally, I will be at the SQL Saturday event in Charlotte. At this event, I will be presenting on Using Power Pivot to Drive Quality into ETL Processes. I am excited about this session as we used this method on a project with great success. This is just another great way to use Power Pivot to improve your development processes. You will likely be able to find me at the PASS booth during some of the day as well.

I hope PASS Attacks are deadly. But it should be fun and memorable. I look forward to seeing you all there.





Adding Top 10 Charts to Power View Which Honor Filters

19 09 2013

Before I get too far in the weeds, I have to recognize Rob Collie, his blog (http://www.powerpivotpro.com) and his book DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX. His book has been a great resource as I work through fun with DAX. I used information from his post on Dynamic TopN Reports via Slicers, Part 2 to solve my problem which I will describe next.  Thanks for the info Rob!


The problem I was trying to solve was creating a top 10 chart in Power View that showed the top 10 in context of what was filtered in the Power View report. If you click a pie slice in a chart, filter the top 10 at the same time. Also have it work with slicers. I was working with Power View and a couple other BI tools to see if this was possible. All tools are able to handle creating a calculation that will show the top 10 for a set of criteria, however, when the data was dynamically filtered on the page, only top 10 values that from the original measure that also worked in the filter worked.  So, now my top 10 charts would have fewer than 10 results.

I will walk through the process so you can see both ways and how I got to the eventual solution.

Both solutions use the RANKX function in Power Pivot. So let’s start their. I am using my Poll solution from the My Vote application built for Modern Apps Live. We have a Poll Response Count calculated measure:

Poll Response Count:=COUNTA([PollResponseID])

Using that as the starting measure we add the ability to get the top 10 responses by Poll. On the Poll table, which is joined to the Poll Response table by Poll ID, I added the following calculation to give rank values to the Polls.

Poll Response Rank:=RANKX(ALL(‘Poll’),[Poll Response Count])

This applies a rank value in ascending order to the Polls by Response Count.  Let’s apply this to a Power View report. In my report, I will be adding a Slicer on Category Name (all Polls belong to a Category), a bar chart with Response Count by Poll Question, a Pie Chart with Response Count by Category, and a table which shows the raw rank value for each question. Here is what that looks like:

image

In order to get the top responses in the bar chart we need to add Poll Response Rank to the Chart Filter (Not the view filter) for the bar chart. As you can see below, we pick the responses less than or equal to ten.

image

This applies the top 10 filter only to the chart which is what we want. However, when I filter by Off-Topic only two values remain in the chart. When you look at the table values ranked less than 10 overall will not be included in the chart. In some cases this is exactly the functionality you want. Here is what the filtered view looks like, only two of the 4 show up in the bar chart.

image

What if I wanted the slicer to also apply to the bar chart. We need to use a slightly different formula.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count])

By changing the chart filter to the new rank value you will see we get the filter applied based on the selections in the sheet.

image

How Does It Work?

The key is what I learned from Rob’s writings and samples. The ALL() function removes external context when applying a function. So ALL in this case will return the full set of Polls and then apply the RANKX function. So, it is returning the rank for all polls. ALLSELECTED() applies the RANKX function to the currently selected values. So any operation within a Power View sheet which changes the filter context, such as a slicer, will reset the Polls to only the set of Polls which have been selected and then apply the ranking.

If you do not use ALL or ALLSELECTED, the context is the row and will always return 1. Which is not helpful.

Can We Reverse the Order?

If you need to show the bottom ranking, you can add to the RANKX function. RANKX syntax:

RANKX(<table>,<expression>[,<value> [,<order>] [, <ties>]]])

Value, Order, and Ties are optional parameters. The one that matters is the Order parameter. The Order can be 0 or 1 and it defaults to 0. 0 ranks the values in descending order. To get the bottom result, you would use 1 here. The following code shows how we would rank the bottom polls.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count],,1)

For the optional parameters, you only need to leave a placeholder if you do not need it. Why create a separate value for reverse order? Because Power View can easily handle less than 10, but not the last 10. By making the change above and not changing our standard rank you can see that the data is now filtering for the bottom ten based on how the ranking value is assigned.

image

I hope this helps you create even more effective visualizations with just a bit more DAX under your belt.





Exploring Excel 2013 as Microsoft’s Business Intelligence Client

4 02 2013

Excel 2013 LogoA little over six months ago, I wrote an article on Magenic.com about Excel 2013’s Impact for BI Users.  This coincided with the Office 2013 Preview release.  I discussed the addition of Power View and PowerPivot into the product.   Last week, I followed that article up with another on the fact that Excel appears to be Microsoft’s BI client platform now and into the future.

It appears that I am not alone in this assessment.  Michael Koploy of Software Advice, a company that reviews BI software, did a Q&A session with Rob Collie, a.k.a. PowerPivotPro ( T | B ), where Rob came to some similar conclusions.  He notes that “the powerpivotprotoday[1]inclusion of PowerPivot in 2013 is a major inflection point” for users to implement BI in Excel more fully.  I really like his take on the relationship between BI specialists and IT pros and the end users of Excel.  Microsoft has been pushing a concept of BI for the masses and ironically today’s Excel is the helping this happen.  Check out the rest of the Q&A about whether Excel is the next great BI tool for more insights from Rob.

As I start to deliver more solutions with customers using Excel, SharePoint and soon Office365, I am going to kick off a new series here on my blog – Excel 2013 BI Tips.  My goal is to document XLTipsthose tips, tricks and discoveries from my journey with the new Excel.  Some of them may be seem common, but I just don’t want to forget so I will be noting them as well.  Join me for this interesting journey through Excel and BI.





Are You Signed Up for 24 Hours of PASS–Business Analytics?

29 01 2013

If you have not signed up for the 24 Hours of PASS-Business Analytics you should be.  This is a great chance to hear 12 speakers (they will be repeated in the following 12 hours).  Topics are varied from Big Data to Strategy to Collaboration.  Most importantly you24 Hours of PASS Business Analytics can’t beat the price to hear speakers like Denny Lee, Peter Meyers, and Stacia Misner to name a few.

I get the privilege of moderating two of the sessions: Session 8:  What Is Big Data? by Mark Whitehorn and Session 10: Visualizing Data with Power View by Sean Boon.

Finally, I heard Marc Reguera talk about how Microsoft Finance uses Power View at a different event.  If you want to see Power View put into practical use by a business user, I highly recommend you check out his session.  I think it is the final piece of the puzzle to join the technology with the business.

I hope you all take the opportunity to join us for this compelling and free event preview to the PASS Business Analytics Conference in Chicago on April 10-12, 2013.





PASS Summit 2012 Wrap Up

13 11 2012

Wow, what a week. Once again, PASS put on a great event that provided much in the way of events and training for the SQL Server community. If you followed my countdown you know some of what I love about PASS. Last year I blogged everyday but I did not do that this week. So, what was different for me? Well, for one I volunteered much more this year than last and I was privileged to speak twice. I spent more time meeting new people and catching up with friends and that was great as well. Enjoy my wrap up from my week.

Tuesday – Leadership Meetings, Welcome Reception, and some Karaoke

Before the event officially kicked off, I joined community leaders from around the world for a series of leadership meetings. First we had a meeting on SQL Saturdays which was an opportunity to see the immense growth of these free training events around the U.S. and throughout the world. What a great opportunity for SQL Server professionals to improve their skills and for those passionate about the community to improve their abilities by leading these events. Many ideas were shared among the team including a panel on how to effectively run a SQL Saturday on a tight budget.

Once that was completed, the Regional Mentors enjoyed a lunch together and an opportunity to share what we do to support the user groups in our regions. I particularly enjoyed the fact that I was able to spend some time with Regional Mentors from Germany, Holland, and Portugal. This highlighted further the international scope and reach of PASS. This was followed by the Chapter Leaders meeting. That meeting was held as a series of round tables that the chapter leaders could move through. I was working at the table focused on leadership with Ryan Adams ( B | T ) from the North Texas SQL Server User Group – NTSSUG. We had a number of good conversations around building leadership teams for user groups and what is needed to have an effectively led user group. Check out the NTSSUG site for the by-laws sample we discussed multiple times.

All of these meetings were followed up with the Welcome Reception, which I made a small portion of as I was trying to drop my backpack at my hotel and work my way back there. After the reception, I headed out to Bush Gardens with a number of others. During that time, Jes Borland ( T ) managed to get a microphone in my hand and I had my first round of karaoke. Yes, I actually did sing and had fun doing it. All-in-all, it was a good time had by all.

Wednesday – SQL Around the World, Microsoft Announcements, Tabular Models, and Magenic Team Dinner

This was the true kick off to the event. For many, they looked at the key note as the kick off. Before that even began, I was working in the Community Zone encouraging people to participate in the SQL Around the World community activity. It was a great game. You needed to find 10 people from 10 different countries and find out something interesting about them or their country. I found a dancer and someone who had ridden a cheetah as a kid. I also surprised someone from the Czech Republic when she mentioned her home town only to have me let her know I had been to her home town many years ago. It was a fun conversation. If you did this and have other cool stories let me know. It was amazing as well over 50 different countries were represented at PASS.

Next, Ted Kummert had the first keynote session of the day. His keynote was filled with announcements concerning SQL Server including the following:

  • Hekaton: the project code name for a new in-memory OLTP engine
  • The Columnstore Index will be updateable
  • Next version of PDW will be out in H1 2013
  • Polybase: allows you to query across multiple types of data sources such as SQL Server and Hadoop with T-SQL
  • DAX Queries will be able to query SSAS Cubes

He also highlighted some recent announcements related to the SQL Server stack:

  • Microsoft HDInsight Server CTP: Hadoop for Windows Server
  • Windows Azure HDInsight Service Preview: Hadoop for Azure
  • Power View and PowerPivot fully implemented in Excel 2013

After the keynote, I hit a session on BigData and Hive which was put on by SQL CAT and very informative. My big takeaway was to use EXTERNAL tables not INTERNAL tables when working with Hive. I then went to do final prep for my Tabular Model session. In this session, “Building a Tabular Model Database”, I present on what tabular and in-memory is, and then proceed to open up a Visual Studio project and create a database. I think it went well and the attendees seemed to enjoy the upbeat nature for an end of day session. The night wrapped up with dinner with the Magenic team (7 of us). Good chance to grow relationships across offices from around the country.

Thursday – Community Zone and DAX as a Query Language

Thursday was a fairly low key day for me. Once again I spent time in the Community Zone. I had the opportunity to talk with a few people on creating a user group in their area. As always, I like to see people interested in growing their local community.

I also attended Alberto Ferrari’s session on DAX. I think the biggest surprise to me was that you can now query DAX directly from SSMS. I am not sure that I am convinced that it is a full query language yet, but it is definitely closer. The key to it all is the EVALUATE expression which allows you to create the DAX query ironically in the MDX window. Here is just a taste of DAX as a query:

EVALUATE
    ‘DimCurrency’
ORDER BY

    ‘DimCurrency’[CurrencyAlternateKey]

What I found interesting is that you can create columns, build measures, and perform many other operations against the tabular model using DAX. In the end, it will not increase the memory used as storage as it is all calculated. Look for some more on this in later blog posts as I delve more into the in-memory storage and usage when working with DAX.

Friday – More Community Zone, HDInsight, Paul White, and Window Functions

Last day. I spent more time in the Zone. I really did enjoy my time there as I continued to meet more people. I was even present when a contract was completed for the Shanghai user group. Very cool indeed. I then attended a session on HDInsight by Mike Flasco from Microsoft. This is very cool stuff as you can create simple Hadoop cluster on your desktop to test the technology. Microsoft and Hortonworks have done a great job of bringing Hadoop data into the Microsoft stack.

On my way to present my final session of the day and the conference, I stopped in for the second half of Allen White’s ( B ) optimization presentation. In a word (or two), mind-blowing! Wow, who knew that the optimizer did all those things? I was highly impressed and think he should look at a precon on the subject next year. Unlike some three hour presentations, he could have went longer as he was not stretching his content out. Nice work Paul. So, I got to follow that with a presentation on Window Functions in T-SQL. For the second time, I had the last slot of the last day. I think this presentation went well even though we were all worn out from a content-filled week. It was fun to try some ideas from the audience in the demos. That always makes for a more interesting demo. I will be doing a follow up post on what I learned from some of the attendees on the subject as well, proving once again this is a user community event. We all have something to contribute! (If you attended this session, you will find links to the blogs on the subject here.)

What’s Next?

Coming in April is the new Business Analytics conference in Chicago followed by the PASS Summit in Charlotte, North Carolina. Of course, your local user groups will continue to meet with regional SQL Saturdays sprinkled throughout the year as well. How will you participate and contribute in 2013? We look forward to seeing you all again, soon.





Excel 2013 Will Impact BI Users

17 07 2012

Excel 2013 IconAs noted in that great text, The Hitchhiker’s Guide to the Galaxy, “Don’t panic!” Excel 2013 will make even more capabilities available to BI users.  This includes having xVelocity natively supported in the tool which makes much of the PowerPivot capabilities available in a spreadsheet which used to only be available in the PowerPivot window.  It also adds the ability to create Power View reports in Excel.

Check out more on my thoughts about this on Magenic’c blog.





PASS for Today (and Tomorrow)

20 03 2012

I justPASSMNLogo wanted to plug two events that are free SQL Server training.

First, Thomas LaRock (@sqlrockstar) and Jason Strate (@stratesql) are teaming up to bring you “Choose Your Own Adventure – Performance Tuning”. Join us at the Microsoft Technology Center in Edina, MN or online for this great adventure. This adventure starts at 3:00 PM CDT. More details can be found at http://minnesota.sqlpass.org.

Is that all?, you ask. No. Starting tonight at 0:00 GMT (7:00 PM CDT) is 24 Hours of PASS. Check out the awesome roster of speakers24HOP_Speaker including the likes of Denny Lee from SQLCAT, Marco Russo, and Dejan Sarka. These sessions run through the night and will be close captioned in 15 languages, making it a truly international event.  Oh, did I also mention, I will be speaking at this as well.

Take advantage of these free training opportunities by leaders within the SQL Community.  We all look forward to seeing you at both or either of these events.








Follow

Get every new post delivered to your Inbox.

Join 734 other followers

%d bloggers like this: