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

19 06 2013

MagenicLogo2012x70tallIn 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

11 04 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!

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:

image

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

image

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 Layerimage

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.

image

image

image

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

You have created your first scene.

Creating a Tourimage

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.

imageQuick 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:

image

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

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.





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.





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.





PowerPivot and Market Research

16 02 2012

This past Tuesday, February 14, I was invited to discuss how technology can be used to help analyze data for the purposes of Market Research at Minnesota School of Business.  A friend of mine, Shane Smith, is teaching this class and invited me to show how the tools I work with every day can help his students analyze data for marketing.

The Setup

The class was analyzing the results from a survey they conducted at the school as a part of a market research project.  The survey had five questions with various types of answers such as Yes/No and multiple choice.

Shane wanted to show the class that using technology, they could glean more results from their survey.  This process would also help them understand the effectiveness of the survey they created.

The Data Prep

For my part, I wanted to introduce them to what I do – business intelligence solution design and implementation.  I also wanted to introduce them to the specific tools which they could use to analyze their results for the project.  In this case, we were using Excel 2010 and PowerPivot for Excel 2010 (SQL Server 2008 R2 version).  The final goal was to help them see that these tools can be used to analyze data in real world scenarios.

Shane provided me with the initial survey results in an Excel spreadsheet.  I took that data and flattened some of it out and turned some of it into data that math could be performed on.  For example, I took a multiple choice survey question with 5 possible values and turned it into 5 columns of data with 1 and 0 as possible values so additional calculations could be done on it.  We also created the equivalent of a dimension by taking one of the multiple choice questions and creating a new sheet with possible values and labels.

The Cool Stuff

I took the sheet we updated the data on and I walked the class through the process of bringing that data into PowerPivot.  This included building a relationship between the dimensional data and the primary survey results which were on two sheets in the source workbook. From there, we generated a couple of pivot table views and some charts.  Next, they came up with other ideas of what data to look at and how they wanted to see it presetned. I walked them through the process of prepping the data as necessary.  Then we created more visualizations and presentations of the data that helped them better analyze their results.

The Wrap Up

Kudos to the Microsoft team that put this toolset together.  These students, who are not technologists, gained confidence in the use  of PowerPivot for their project. They are also now set up to use it in the future for similar projects or even their jobs. Because of its ease of use, it was a great demo and training session on using tools to analyze data.

I had a lot fun sharing the technology that I work in regularly with users who would not have tried to do this on their own.  Tools like PowerPivot enable users to turn their data into usable information on their own.  While there will always be a need to build enterprise solutions, this fills a gap in the marketplace with a tool many are already familiar with.

I hope the students are able to continue to use the tool and find a place for it in their workplace in the future.  If you have not yet given PowerPivot a test run on your own, you can find it here: http://www.microsoft.com/en-us/bi/powerpivot.aspx.  We used the 2008 version, but feel free to give either a test run.  Keep in mind that you need Excel 2010 to use PowerPivot.  Best of luck to all of them and any of you who give it a test run.  Feel free to share what your experience with PowerPivot here in the comments.





PowerPivot for Excel Install Issues

2 02 2012

At one of my customers, we had a very frustrating experience while trying to get PowerPivot for Excel (SQL Server 2008 R2) installed on a number of developer machines.  All of the machines were 64-bit running Windows 7. We had Office 2010 32 bit installed in all cases.  The frustration occurred around the error message.

PowerPivot Error Message

We verified we had downloaded the correct version and according to the following Microsoft Support article, http://support.microsoft.com/kb/982765, we also confirmed all of our prerequisites were in place.  After going through this verification process, we had one of the developers completely reinstall MS Office and then install the add-in.  However, we were still unsuccessful.  So, we did what all good developers do and got our local desktop support guy, Steve Schuler, involved.  He was able to successfully work through the issue.

Here are the steps that Steve discovered which allowed us to successfully get the PowerPivot add-in installed.

  1. Save PowerPivot_for_Excel_x86.msi to a local folder.
  1. Right click on Command Prompt and choose Run as Administrator.
  1. Type msiexec/i c:\data\PowerPivot_for_Excel_x86.msi  into the Command window.
  1. Launch Excel and choose Accept when prompted to install PowerPivot.

If PowerPivot does not show in the ribbon, go to File-Options-Add-Ins and select Com Add-In’s from Manage drop-down list and choose Go.  Confirm that PowerPivot for Excel is selected.  If it is not selected, select it and then choose OK.

The root cause of the issue appears to be related to the security policies and administrative privileges on the machines. In our case, we are all local admins and should not have encountered the issue.  We had inconsistent experiences on many machines that were tested.  In some cases there was no issue and then in other cases we could only install with the steps above.

Thanks again to Steve for a job well done.








Follow

Get every new post delivered to your Inbox.

Join 734 other followers

%d bloggers like this: