2013 – A Year In Review

2 01 2014

It is in our nature as humans to look back in order to understand where we have been.

Warning – some of this blog contains stuff about my family… In case you only want the technical stuff.

Family Fun

This past year has been very interesting for me personally and professionally. In the past year, my youngest, Mikayla, has entered Junior High officially taking our family out of elementary schools. Mikalya joined me at the SQL Saturday event in Omaha. At the same time, my oldest, Kristyna, is now a senior at Burnsville Senior High School. Both of my boys, Alex a junior and Andrew a freshman, are both taller than me and staying active. Alex joined us at the Minnesota SQL Saturday and did a lot of volunteering. Andrew probably had the best event of all as he joined me at SQL Saturday in Fargo. There he got to see Bill Gates in person. I am proud of all of them, they are great kids. This was also the year I celebrated 20 years with the woman I love, Sheila. Without her support, I would not have been able to get this far in my career as well. Yep, it has been a busy year personally. Soon there will be lots of college, marriage, and maybe even grandkids. Wow, I must be getting old.

Magenic and the Server Development Practice

2013 is my first full year as a Practice Lead at Magenic. I started out as the Practice Lead for our Business Intelligence and Data Practice. In August, my role expanded to include SharePoint, Biztalk, and TFS. This allows us to focus server technologies at Magenic. Along  the way, I have had to learn a lot about VMs (still a work in progress). I really enjoy working with the pros across the company that we have. We some very talented BI, SharePoint and BizTalk consultants including a few virtual TSPs in SQL Server, Business Intelligence, and BizTalk.

During this past year, I have traveled around the country to consult, to speak, and to meet customers. I have had the privilege of speaking at multiple SQL Saturdays, Modern Apps Live, SQL Live, and Code Mastery events. It has been fun. I almost made it to all of our offices including the locations we opened this year. I made it to Minneapolis, Chicago, Atlanta, Charlotte, Boston, New York City, and San Francisco. Still need to get out to Los Angelos and Manila.

image

While it has been hard at times, the travel experience has been good overall. I try to keep my speaking engagements up to date, maybe I will see some of you next year.

This year I also authored outside of the blog. Chuck Whittemore (The Insight Analyst)The Changing World of Business Intelligence: Leading with Microsoft Excel - Custom Software Development White Paper and I coauthored a white paper on Leading with Excel: The Changing World of Business Intelligence. This was a fun project where we bring together Microsoft Excel and Microsoft BI in a real world way. We continue to successfully work this strategy with our customers and it was the impetus for my Excel BI Tips blog post series. I SQL Server Analysis Services 2012 Cube Development Cookbookalso had the privilege to coauthor a book that is just being released: SQL Server Analysis Services 2012 Cube Development Cookbook by Packt Publishing. This the third book I have worked on and it has been a while since was last published so this was a good experience for me. I still don’t know if I would take an entire project on, but maybe someday.

This year wraps up with me becoming a virtual TSP with Microsoft to further support their efforts with SQL Server and Business Intelligence in the marketplace.

One other thing that has been interesting for me is that with the release of Power Pivot and SQL Server Analysis Services Tabular Model, I am seeing a huge shift in how I work with and sell BI. I have always worked with cubes, but now I see the in-memory space as a more compelling and leading edge solution that will continue to change what my career will look like. While I had a lot of fun being a cube and MDX wizard, the ability to deliver results to business users in a timely fashion with great visualizations is actually more fun. The more things change …

Happy New Year!

I hope you and your family had much to look back and celebrate this year. I thank God for the blessings of a great company to work for and an awesome family to be with.





Exploring Excel 2013 for BI Tip #13: Connecting Slicers in Pivot Tables Sourced from a Power Pivot Model

17 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

At Magenic’s Code Mastery event in Boston, Stevo Smocilac and I were presenting on Power BI and Tabular Models. After our presentations, one of the attendees presented an issue to us about how he was unable to get a slicer to filter two separate pivot tables in his workbook. This will be the third tip on slicers as we investigate this issue to find a resolution. (Tip #4: Adding a Slicer and Tip #5: Cleaning Up Slicers)

To reproduce the issue, you will need to create a Power Pivot model with two related tables. In neither case will we have calculations. The primary table in my illustration is the Poll table with two fields PollID and PollQuestion. Create a flattened pivot table from the Power Pivot window as shown below.

image

Now that I have the Power Pivot connection and the pivot table ready to design in the workbook, I added the PollID and PollQuestion fields to the pivot table. Next, turn off subtotals for all fields. The next step is to add the slicer for the PollID.

Once all this is complete, we need to add another pivot table. In my case, I can use the PollOption table which has each of the valid options for each poll, essentially a clean one-to-many relationship that was built into the Power Pivot model. I added the next pivot table to the worksheet using the following steps:

1. From the Insert tab on the ribbon, select Pivot Table

image

2. In the dialog that opens, select “Use an external data source” and click “Choose Connection.”

image

3. Select the Power Pivot model as your source and click Open. Then select OK on the next dialog. This will result in another pivot table which shares the Power Pivot model as the source.

image

Once you have the new pivot table add the columns from the related table. In my worksheet I selected the PollID and OptionText from the Poll Option table. The final step to reproduce the issue is to relate the existing slicer to the the new pivot table. This is done by right-clicking the slicer box and choosing the Report Connections option. From here you can add the new pivot table to the Slicer connections.

Now that this is set up, when you click a slicer option, it filters the first table, but not the second as shown here:

image

As you can see the first table filtered correctly, but not the second.

Solving the Issue

It turns out the solution is fairly straightforward, but not something you would normally think of. If you add an aggregation to the related table, Poll Option, then the slicer works just fine.

image

It does not seem to matter what is aggregated, but an aggregation is required. While I was unable to find any reason why, my best guess is that this is related to the fact that pivot tables still communicate with Power Pivot models using MDX. I have seen this happen when working with multidimensional models where you needed a value in order to get the slicer or filter to work correctly, even in MDX itself.

While this solves the problem, we are now left with a column we did not want on the pivot table. It would appear that the only option we have to make the column not appear is to hide the column. However you will need to tread carefully here as it hides the column for all tables below as well.





Exploring Excel 2013 for BI Tip #12: Icon Sets

3 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Icon Sets

So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.

image_thumb1_thumb

Icons in Brief

Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.

image

Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).

Creating a KPI with an Icon Set

When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.

image

This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.

Using shapes as well as colors is particularly helpful if you have color blind users.

In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.

image

As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.

image

This button will show the following selections for applying formatting rules:

  • Selected Cells (currently selected)
  • All cells showing “Submission Count” values
  • All cells showing “Submission Count” values for “PollQuestion”

Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:

image

As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.

image

You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.

You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.





Traveling, Talking, and a PASS Attack Have Happened

1 12 2013

Well, this fall has been crazy. As you may have noted from a couple of my previous posts, link to speaking and pass attack, I have been busy this fall. With multiple speaking engagements and additional travel for work, I have been on the road more than I have been home. Rather than handling each event independently, I will give some synopsis here.

SQL Saturday #235 – New York City 2013

This was my first trip to New York City which is kind of ironic since I was born in Avoca, New York and lived there for five years. I spoke on Building BI Solutions with Excel 2013. During the presentation, we had an energetic conversation about pushing more of the BI work to business users. My take is that those users know how to interpret the data better than the technology staff. Ironically, they are doing it already thanks to the prolific “Export to Excel” functionality available in most BI tools. The event was a lot of fun to participate in.

SQL Saturday #238 – Minnesota 2013

This was the first SQL Saturday in Minnesota that I did not have a lot of other responsibilities beyond speaking. I felt a little guilty, so I brought my sixteen year old son, Alex, and he volunteered in my place. Turns out he did awesome and had a great time as well. I spoke in back to back sessions to open up the day. The first session, Beyond Power Pivot – Building Tabular Model Solutions, covered the development of tabular model databases using SQL Server Data Tools and the “enterprise“ features only available in Analysis Services. The next session was “Power to the User: a Look at Power BI”. This session was a lot of fun and is a whirlwind demo of the Power BI features in Excel 2013. This was a huge event with around 400 attendees and 8 tracks. Kudos to the organizing committee for a job well done.

PASS Summit 2013

Summit this year was a good event. Due to a myriad of work related crisis, I was unable to enjoy the conference as much as I had hoped. We discussed the role of the user groups in SQL Saturdays during an organizer’s meeting. I hope that we begin to see the control of these events go back to user groups rather than treated as unrelated to user groups. As the event progressed, I was able to attend a few sessions. I am still asking for the single button install of SharePoint BI, but for now I will live with the 80 page install doc. One thing I like was having the vent in a warm location. That was definitely more enjoyable than a cold, damp Seattle fall. I still look forward to the next Summit and hope to see many of you there next year.

SQL Saturday #237 – Charlotte BI Edition 2013

Immediately following Summit was this SQL Saturday. There were a lot of big time speakers that came from the Summit and they still let me talk. This session was on using Power Pivot and Power Query to test ETL processes built with SSIS. The best part was when my computer died during the demo. When I got it restarted, an error message came up on SSIS and a number of people in the room gasped. We all knew that I would not be able to fix that right away, some moved on with half of the demo working. It all turned out okay as I had screenshots in the deck to show the changes. Overall, it was a good event and gave me a chance to see some peers one more time.

CodeMastery – Atlanta

This was the first of two CodeMastery events within a week of each other. This was a lot of fun for me as I was the keynote speaker and had the opportunity to speak about the Changing World of Business Intelligence. Having been trained as a preacher, this was the closest to that type of speaking I have done in some time. We had a good, interactive audience for this event. I also presented on Power BI at this event, once again introducing a lot of people to what Microsoft is doing with BI these days.

CodeMastery – Minneapolis

Back home in Minneapolis, I participated in this CodeMastery as a speaker as well. At this event, I once again discussed the power of Power BI and Microsoft’s strategy. We had about 80 at this midweek event which was great.

Live 360 – Orlando

At Live! 360, I had the opportunity to speak at two of the 360 events – Modern Apps Live! and SQL Server Live! This event was a lot of fun to participate in. I had the opportunity to speak five times between the two events. The part that made all of this interesting was losing my harddrive that weekend. I had to rebuild demos and recover presentations all week long. Through it all I was able to get all but one presentation’s demo up and running. Funny thing though, I tweaked the demo, and it worked partly during the presentation. Nice job by 1105 to put this together. I look forward to being at the Vegas event in March.

CodeMastery – Boston

This event was also supposed to have happened by now, however, it was rescheduled to next week. If you are in the Boston area at the time, register for the event and join us for some free training. Hopefully, I will see you there.

Thanksgiving Day Survivor

Kid Pic PumpkinsAs I wrap up this post, I have to say a big “Thank You” to my family who has had to put up with a lot of travel over the past few weeks. Without their love and support this would have been a really rough time.

Turkey Image Source: http://bit.ly/1bBFIF4





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 for BI Tip #11: Color Scales

28 08 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Color Scales

In the last tip we covered using conditional formatting with Data Bars. Next up is Color Scales.

What Are Color Scales?

Color Scales are a conditional formatting feature that can be applied to cells in Excel.  Color scales “color” the cell based on the data that the formatting is applied to.  The color scale feature works with pivot tables and standard cells in Excel.  Our focus will be on using color scale with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image_thumb1

image

First starting with a single column, we can use use Quick Analysis or the Ribbon to apply our Color Scale rules. While the data bar used size to highlight the differences in the data, color scales will, obviously, use color. The most common color scale to use is to use Red for lower numbers to green for higher with yellow or white in the middle. Let’s apply that to our column of data.

image

This is particularly effective when working with percentages or other values where red can mean “bad”.  Keep in mind that you can change the order of the color scaling so that a higher number can be red as well.  You can also use two color scales when that is all that is needed.  I have used two color scales in cases where only two values are present such as a Boolean value.

When setting up the formatting, you may notice a “box” in the corner.  This can be used to tell Excel how to apply the formatting.

image

This works great when you pick the first cell in question then use this short cut to set the formatting based on how you want to apply the changes. The  only rule type that applies in this scenario is the “Format all cells based on their values” which options are shown in the shortcut.

While a very simple visualization technique it can be to highlight differences in a compelling way.

Next tip will cover Icon Sets. See you then.








Follow

Get every new post delivered to your Inbox.

Join 694 other followers

%d bloggers like this: