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 #7: Retaining Pivot Table Size After Data Refreshes

23 05 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!

Retaining the Pivot Table Size after a Data Refresh

Let’s start with the fact that these tips are a result of my work with Excel to support BI tasks.  This turns out to be one of those things that power users of Excel probably already know.  I had to find this out and thus I am sharing it here.

The Problem

When I was working on the dashboard for Modern Apps Live! I had some nicely formatted tables.  However, I had two pivot tables stacked as seen in the picture below on the right.

Summary Dashboard

Whenever I did a data refresh the columns would automatically resize to the “one inch” width.  Making the Poll Questions in the second table unreadable.

The Solution

I went looking for how to fix this.  I searched for solutions online and I poked around to see if I could fix this problem.  After all, it was very important to be able to refresh the data on the dashboard.

I was able to locate the solution here. As it turns out, you need to unselect the Autofit column widths on update property on the PivotTable.  You can get to those options in a couple of ways in Excel 2013.  First you can right click on the table to get the short cut menu and select PivotTable Options.

image

Or you can select Options on the far left of the ANALYZE tab on the PIVOTTABLE TOOLS ribbon.

image

Once you are in PivotTable Options, you go to the Layout & Format tab and deselect the Autofit option as mentioned above.

image

Voila! Problem resolved, now my dashboard does not need the columns resized in the pivot tables each time it is refreshed.





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





Exploring Excel 2013 for BI Tip #2: Show Details

13 02 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!

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.

image

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.

image

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

image

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

8 02 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!

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.

image

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

image

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.

image

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.

image

Until the next tip…





Join Me at Modern Apps Live! Las Vegas

5 02 2013

ModernAppsLive

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

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.





Are You Signed Up for 24 Hours of PASS–Business Analytics?

29 01 2013

If you have not signed up for the 24 Hours of PASS-Business Analytics you should be.  This is a great chance to hear 12 speakers (they will be repeated in the following 12 hours).  Topics are varied from Big Data to Strategy to Collaboration.  Most importantly you24 Hours of PASS Business Analytics can’t beat the price to hear speakers like Denny Lee, Peter Meyers, and Stacia Misner to name a few.

I get the privilege of moderating two of the sessions: Session 8:  What Is Big Data? by Mark Whitehorn and Session 10: Visualizing Data with Power View by Sean Boon.

Finally, I heard Marc Reguera talk about how Microsoft Finance uses Power View at a different event.  If you want to see Power View put into practical use by a business user, I highly recommend you check out his session.  I think it is the final piece of the puzzle to join the technology with the business.

I hope you all take the opportunity to join us for this compelling and free event preview to the PASS Business Analytics Conference in Chicago on April 10-12, 2013.








Follow

Get every new post delivered to your Inbox.

Join 694 other followers

%d bloggers like this: