The Changing World of BI, A New White Paper for Magenic

In the ever changing landscape that is Business Intelligence (or is that Business Analytics?), a fellow business analyst from Magenic, Chuck Whittemore (B), and I authored a white paper based on our experiences over the past months.

What I think makes our work unique, a BI architect and a business analyst came together to show our worlds colliding in the age of modern BI tools.  While the goal has always been to bring the data to the users and let them work with it as creatively as possible, the tools to do this were IT focused.  What we see now is that with the advent of in-memory, client-side BI tools, users are now able to get to this on their own.  Microsoft has invested heavily in Excel to make it a first-class BI tool.  Our paper discusses this disruptive nature of the new tools and how Excel is being pushed to the next level.  After all, Excel is everywhere already.

Enjoy the read and I welcome your feedback.

The Changing World of Business Intelligence: Leading with Microsoft Excel

Exploring Excel 2013 for BI Tip #6: GeoFlow–The Latest Excel Visualization

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!

Introducing GeoFlow

Today, Microsoft announced a new add-in for Excel 2013 called GeoFlow.  While it is still a preview, I have to admit this is very cool.  More than that it is super simple to use.  At this point, you are familiar with my sample data set that was created for Modern Apps Live Las Vegas.  The app is called MyVote and it is analysis of polls and related submissions.

Getting GeoFlow

You can download GeoFlow and view the requirements at Microsoft’s download center:  http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx.

Building Your First Tour and Scene

On the insert tab, click the Map button and it will open a blank Tour canvas like the following:

Once you have created a tour it looks like the following:

In this view, you need to use the New Tour button.  Existing Tours are shown into the dialog box.

In my case, the data comes from an underlying PowerPivot data source.  This makes the next design options very easy.  Before you leave this page, you should play with the map.  It is easy to zoom and move around.

About Tours and Scenes

The Tour is a container for Scenes.  This is similar in construction to PowerPoint.  As you add scenes, you can play them with transitions.  Keep in mind that GeoFlow visualizations are meant to be “in motion”.  Scenes focus on data for a period of time.  Tours put these scenes together to tell a story.  Time to build a story about MyVote.

Your First Layer

The first step is to pick the geography.  I had zip codes stored in a Postal Code column.  When I selected it, I was given the option to choose the type of geography data. There are a number of geography data types supported.  After selecting this, the data starts to show up on the map where ever data is present.

Once you have your Geography selected you pick your Chart Type, Height, Category, and Time.   As you add each element look at the map to see how your data is becoming visualized.  Even during the design you can see the 3D visualization work.

The following screens illustrate what happens in each of these phases:

1. Pick CategoryName for Category.

2. Pick PollSubmissionID as Height.  Set the aggregation as count.

3. Pick PollSubmissionDate as Time.

Once you have done this play the visualization to see the data move over time.

You have created your first scene.

Creating a Tour

You already have a tour, but it only has one scene and does not look like a tour.  Any good tour has more than one stop.  Let’s add a scene by clicking the Add Scene button.

Clicking this creates a copy of your selected scene, which is the one we created.  This matters as you add more scenes and may want to use specific existing scenes as a starting point.

In order to see something change, we are going to change the category from category to age range.  This will show a different view of the data that we can see.

Quick note on the task panel. After creating your new scene you are on the Settings tab.  There are three tabs on the task panel.  The first is the layers view, the next is layer details, and the third one is the settings tab.

Now you can play the tour to see how it looks by clicking Play Tour in the ribbon.  Click the “back” arrow on the lower left of the screen to return to the design surface.

Some Scene Modifications I Did

Here are some ideas to add more “flare” to your visualizations.

• Try different chart types.  Heat maps and bubbles are supported as well.
• Layer visualizations.  You can add a layer into your scene which will allow you to have different chart types with different geography to enhance your visualization.
• Change scene timing.  By clicking the gear on the view, you can change the pace of the scene.
• Zoom in to sections of the map.  You can focus on a specific area and add a new scene to your tour to focus on a different area.  Then you can see more details for the area in question and move to the next one as a part of the tour.
• Change your theme.  Try out the different themes.  This will affect map types and chart coloring.
• Label your layers.  When you label the layer it changes the legend header.
• Resize the legend.  You can resize your legend to better fit the data.
• Change the effect.  On the settings tab for the scene you can change the Effect.  I liked flyovers, but many options are available.  You can also change the speed and duration here.  These changes affect the tour play.

Here is my Tour:

More Information

Here are some links for more information.  Enjoy this tool. I know I did.

GeoFlow Download Details Page: Requirements, Web Download

http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx

GeoFlow Facebook Page:

https://www.facebook.com/GeoFlowForExcel

GeoFlow Community Forum: Post Questions to be answered by team and community

http://tiny.cc/f63vuw

Announcement at the PASS Business Analytics Conference

http://blogs.technet.com/b/dataplatforminsider/archive/2013/04/11/day-2-pass-business-analytics-conference-new-3d-mapping-analytics-tool-for-excel.aspx

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

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.

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.

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.

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.

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.

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.

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.

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.

Excel 2013 Will Impact BI Users

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