Exploring Excel 2013 for BI Tip #5: Cleaning Up Slicers

29 03 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!

Cleaning Up Slicers

In this tip, a follow up from Tip 4 on adding slicers, I wanted to highlight a couple of ways to clean up your slicers to make them more user friendly.

The focus here is around Slicer Settings which can be opened from the short cut menu on the slicer or in the SLICER TOOLS ribbon option as shown below.

image

This will open the Slicer Settings dialog.  The image below shows the setting for my Age Range slicer with the slicer.  The settings you see are the default settings.

image

So how can you change this to be better for the user.  You may need to change the Caption to reflect something better for the users.  You can also remove this if the content in the slicer is self-explanatory.

Next, check your sorting.  If you have the data sorted correctly, use it.  However, you can also do typical alpha sorting. Be aware, that you may have issues with data types like dates or numbers if they are not ready for this type of sort.

The 3 check boxes are the most relevant.  The two selected by default should be used in general.  The next set of images show the visualization of the slicer based on these settings.  In this scenario, we only have data for the 30-39 option.

No options are selected:

image

This gives no visual cues to what data is available or not.  This is the least preferred as there is no clarity to the user.

image

The image above dims the options that have no data by selecting the “Visually indicate items with no data”.  This works great in a short list and you want to display options with no data.  Basically, knowing that no data exists is important to the user.

When you have more options, you can add the option to “Show items with no data last” which will move items with data to the top.  With longer lists this helps provide relevant data at the top of the list.

image

The final option will hide items with no data.  This is clearly preferred for potentially long lists.  My favorite usage for this option is to use this with dates.  This will allow me to source only dates with data for filtering use.  Here is what our list looks like with the “Hide items with no data” option selected.

image

This option is really cool as slicers are interactive and will remove options as other slicers are selected.  I tend to use this a lot as it also saves real estate on the sheet when designing dashboards in Excel.





Exploring Excel 2013 for BI Tip #4: Adding a Slicer

28 03 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!

Adding a Slicer

Slicers are not new functionality in Excel as they were in Excel 2010 as well.  However, they are even more highlighted in Excel 2013.  You can add a slicer from the INSERT tab on the Ribbon in Excel as shown in the image below.

image

When you click this button, you get a data source dialog.  It will show you the available connections in your workbook as well as other connections you have used or are available.  Now in my case, I am using a PowerPivot data source.  In order to use that you need to create your slicer from the PivotTable tools as shown below by right-clicking the field to use as a Slicer.

image

Once you click this, it will create a Slicer on the sheet you are working in with all of the available, unique values from the field.

image

Connecting Your Slicers to Data

Now you have created your first slicer.  However, I would be cruel not to wrap this up by showing you how to connect your slicer to related data objects in Excel.

There are two ways to get to the Report Connections option.  First, in the short cut menu (right-click) on the slicer you will get the option in that menu as noted below.

image

The second way is to bring focus to the Slicer and you will get a new menu option on the ribbon called Slicer Tools – Options.  The Report Connections button there will lead you to the same dialog to relate object.

image

You can also get to the short cut menu from the target data object such as a PivotTable.

The Report Connections dialog allows you to relate the slicer to data objects from the same data source.  It will display objects from all of the objects within your workbook.  This means that you can have slicers filter data across multiple sheets.  This is really cool.

image

In the next tip, I will talk about a new setting which, I believe, make slicers even more user friendly and usable to create a dashboard in Excel.





PowerPoint–My Dashboard and Report Design Tool

20 03 2013

At some point I think that I am becoming a Microsoft OfficeMALL13_Badge_See125x125 specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

Microsoft Powerpoint 2013 IconSo I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

image

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

image

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

image

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.





Exploring Excel 2013 for BI Tip #3: Flash Fill

15 03 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!

Flash Fill

Flash Fill is new functionality in Excel 2013 that allows you to manipulate data and fill a column based on actions you took.  I like to think of it as somewhere between programming a macro and using the black cross to pull down formulas.  I am going to illustrate a couple of ways to use this then you will have to take it from there.

First, you will need a set of data that this makes sense with.  So let’s start with something obvious.  You have a column of data that has a customer’s name formatted as Firstname Lastname.  But you really need to format them as Last name, First name. 

Here is the dataset we will use (it is the Indiana Hoosier players from the 80-81 season who were drafted).

Year Round Pick Player NBA_Club Key
1981 1 2 Isaiah Thomas Detroit Pistons 1
1981 1 18 Ray Tolbert New Jersey Nets 2
1981 5 115 Glen Grunwald Boston Celtics 3
1981 8 180 Steve Risley Phoenix Suns 4
1982 10 225 Landon Turner Boston Celtics 5
1983 1 22 Randy Wittman Washington Bullets 6
1983 2 40 Jim Thomas Indiana Pacers 7
1983 2 41 Ted Kitchel Milwaukee Bucks 8
1983 4 78 Steve Bouchie Detroit Pistons 9
1983 7 141 Tony Brown Indiana Pacers 10

So in our first illustration, we will take the Player column and separate and reorder the name – Last name, First, name.

The Flash Fill function is located on the DATA tab as shown below.

image

Follow the next steps carefully to reproduce what I am doing.

  1. Highlight the Isaiah Thomas last name and copy it to an empty cell.
  2. Add a comma.
  3. Copy his first name over.  This should have a column where you see, Thomas, Isaiah.
  4. Highlight the cell with the new value and click Flash Fill.

You are done.  You now have a column that is formatted the way you wanted it to be.  Cool, huh?

imageThe next set of data I am going to work with is the Hoosiers 2011-2012 season.  The data includes a Record column which has the conference record part of the way through the set.  What I want is to have separate columns for the overall and conference records.

This time I am going to select the cell on row 15 which has both records in it.  The first column I will create will have the overall record.  This is done by copying only the conference record over and flashfilling the column.  Works great.  The first row will have no data because the dash does not fit the pattern.  The next column, I only copy the section in the parenthesis.  However, this did not work.  For the rows without a set of data in parenthesis, it copied that anyway.  Let’s clear the column and try one other way.

After many attempts that did not imagework, then next operation needed more patterns to follow.  For rows 13 and 14, neither of which has a conference record, I placed an empty set of parenthesis (), Then for the conference record, I copied the conference record in parenthesis over.  My results look like the table to the right.  As you can see, it is pattern matching and sometimes it does not get you the results you would expect.  However, it is a great tool and will get you most if not all of the way to the result you need with very little effort.  Enjoy.





Logging into Azure and Office365 with Different Accounts

14 03 2013

I have been doing some work on the Modern Apps Live! content that required me to use both an Office365 account and an Azure account.  My Azure work is currently associated with my Microsoft Account (formerly known as my Live ID).  On the other hand, I am working with a Microsoft Office365 account which I have been unable to tie to my current account at this point.

While I was working with Office365, I was trying to open my Azure account to get some storage info that I wanted to use.  It told me I was already signed in with a different user ID and it did not have access to my Azure portal as noted in the image below.

image

Obviously I did not want to sign out, so I started looking for options.  The IT Director at Magenic, Dave Meier, mentioned he was having the same issue with multiple Microsoft accounts. So, we determined this was somehow related to IE so I installed Google Chrome.  By using Chrome I was able to work around the issue.  However, Dave came across the following article regarding this change in behavior starting in IE 8 http://blogs.msdn.com/b/askie/archive/2009/05/08/session-management-within-internet-explorer-8-0.aspx.  Turns out they changed how they manage sessions in that version.

imageThe article brings up a couple of options to work around.  One is using Alt-F-I to change the session.  Kind of annoying as I actually use my mouse (some of you keyboard junkies will like that solution).  So I used the shortcut option and created a shortcut for IE on my Windows 8 desktop which uses the switch –noframemerging.  I added this to my Taskbar, and voila, I have an easy way to open a new session browser which ignores my other session.  Keep in mind that you are essentially running two different sessions in IE which means that you will have two sets of credentials active.  Be sure to keep track of which is which.

Also, as a quick sanity test, Google Chrome works the same way.image  Even when I open a different Chrome instance, it keeps my session state. So, it appears the IE shortcut is a nice way to handle this issue.  Although, it is nice to know I can use a Chrome instance and and IE instance if that is my preferred method.

Add a comment if you try this in FireFox and want to chime in.  I really only want to use one browser, so Chrome is still one more than I want.

UPDATE:  You can also use InPrivate or Incognito modes accomplish the same thing.  Thanks to Rocky Lhotka ( T | B ) for pointing this out.





Steps to Preload Data into Tables with SSDT

6 03 2013

I am working as the data architect and developer on a modern appMALL13_Badge_See125x125 build with a the team from Modern Apps Live! in Vegas.  The goal of the project is to provide guidance to build modern applications and use this application as a reference.  While the conference is focused on the why of the build, we have learned some interesting things about how as well.  This is one of those how items.

In this post, I needed to preload some data into the database.  I wanted to include this process in the database project I had created.  However, I quickly found out that this was not a straightforward as I thought it would be.  Here are the steps I followed and any of the gotchas along the way.

1. Create Scripts for the Load Queries.

I started out with scripts that included a DELETE statement followed by an INSERT statement.  However, this created problems when data existed, particularly when the table is a list table used as a foreign key.

Next, I tried MERGE.  This worked great.  This gives me a way to handle new records that are required for the lookup or any changes made to existing data.  Here is the script I used:

merge dbo.MVCategory as target 
using ( 
        select 1, 'Fun' 
        union 
        select 2, 'Technology' 
        union 
        select 3, 'Entertainment' 
        union 
        select 4, 'News' 
        union 
        select 5, 'Sports' 
        union 
        select 6, 'Off-Topic' 
    ) as source (CategoryID, CategoryName) 
    on target.CategoryID = source.CategoryID 
when matched then 
    update set target.CategoryName = source.CategoryName 
when not matched then 
    insert (CategoryID, CategoryName) values (source.CategoryID, source.CategoryName) 
;

After going through this process on my own, I also found the same recommendation from the SSDT team at Microsoft as noted here: http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

2. Add the Scripts to Your Project

This step is pretty straight forward.  You can either create the script files and add them to your project or you can create them within your project as script files.

3. Change the Build Action to None

This was one of the key pieces I missed.  After I added the scripts to the project and then ran a build, it was broke the build.  Each of these files which were merge scripts reported an error during the build.  It turns out this is called out in the article I reference above as well.  SSDT (SQL Server Data Tools) is designed to build database objects not manipulate data.  One other area of grief caused by this is that you can break the build in the solution if your project is part of a bigger solution such as mine.  As a result, you will get grief from the other developers, you can trust me on this one.

The image below shows where to set the Build Action property to NONE.  This will exclude these files from the build in this format.

image

4. Add a PostDeployment Script to Your Project

If you do not already have a PostDeployment Script, you need to do this at this point.  This is a specific type of script task that can be found in the Add menu.

image

5. Add SQLCMD Statements to the PostDeployment Script

The final part of the process is to add SQLCMD statement to the PostDeployment script to execute the files you have created.  As noted in the help in the template, you can execute the scripts by calling a single SQLCMD statement for each script.

:r .\PreLoadMVCategory.sql

The :r {filename} syntax will expand the script for execution during a publish call or DACPAC creation.

I hope you find this useful as well.  This is a common task required in creating solutions.








Follow

Get every new post delivered to your Inbox.

Join 731 other followers

%d bloggers like this: