Exploring Excel 2013 for BI Tip #3: Flash Fill

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.

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?

The 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 work, 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

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.

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.

The 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.  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

I am working as the data architect and developer on a modern app 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.

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.

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.

Exploring Excel 2013 for BI Tip #2: Show Details

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!

Show Details

So, you have connected Excel to your SSAS cube.  You really wish you could easily drill to details using Excel.   You can do this in Excel 2013 by double clicking the cell or right clicking the cell to get the shortcut menu which includes the option as well.

It is really cool that you can just double-click the cell and get there directly.  By default, it returns the first thousand rows of detail from the underlying cube data.

You can change this in the connection options for your SSAS connection.

One “gotcha” on this is that It doesn’t work on a calculated cell, but it works fine on standard measures.  If you use a lot of calculated members, you will get the error in both cases.  So if this option is important for the users of a specific calculation, you may need to get it into cube as a standard, not calculated, measure.

Exploring Excel 2013 for BI Tip #1: Quick Explore

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!

Quick Explore

So, you have connected Excel to your SSAS cube.  You really wish you could cross drill easily in the product.  If you have used PerformancePoint Services you know the process.  Right click on the bar or cell and then choose the dimension to drill to using the information you clicked on as a starting point.  You can now do this in Excel 2013 using Quick Explore.  Here’s how to do it.

1. Click on a cell and hover over it.

2. Click the Quick Explore button a.k.a. magnifying glass and lightning bolt.  That will pop up the following dialog box.

In this box, you can see that the cell has focus on “Boston” based on context and is drilling into the Geography hierarchy of the Client dimension based on the selected filters and slice of data we are focused on.

3. Click the Drill To target.  Excel will create a new view of the Pivot Table with the Drill to on the row as shown here with the filter still in place.

4. Going back…  One of those frustrating things with this is how do I go back to my original view.  Ironically, you use Undo.  This will reset the view back to the previous.  So, if you choose to drill into this data again and again, you have to Undo that many operations to get back to the starting point.  Of course, can click the arrow by the arrow to see your “bread crumb” and pick where you want to go back to as well.

Until the next tip…

Join Me at Modern Apps Live! Las Vegas

If you are familiar at all with Visual Studio Live! then you should check out this new conference.  At the Vegas conference, I am participating in this unique three-day event which is a series of sessions that build on each other as the conference progresses called Modern Apps Live! The goal is to do a end-to-end application build during which the presenters build the application as well as focus on Application Lifecycle Management, best practices, tools, and a variety of technologies.

I will be presenting on the database design and business intelligence components of the solution and would love to see you there.  To make this even sweeter, if you register with the following code, MVSPK3, you will qualify for one of the following offers:

• The 5 day all-access Best Value Conference Package for just $1,595 – a savings of $500.00 off the standard price of $2,095! (*Includes pre and post conference events.)

OR:

• The 3 day Modern Apps Live! Conference for just $1,295 – a savings of $400.00 off the standard price of $1,695

This is for new registrants only.  Make sure to use the code by February 27, 2013, to take advantage of the full discount.

Also keep in mind that you will be able to take advantage of the Visual Studio Live! sessions as well as this is a cohosted conference.

I hope to see many of you there!

Exploring Excel 2013 as Microsoft’s Business Intelligence Client

A 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 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 those 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.