Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model

19 06 2014

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!

From Power Pivot to SSAS Tabular

As companies move through the cycle of building Excel based solutions for business intelligence and analytics, they eventually end up with a SQL Server Analysis Services Tabular Model. The tabular model comes into play when you need more data in your model or want to support more granular security.

Up to this point, users have been happily using Power Pivot models in Excel to build their analysis solutions. However, once the model is deployed to tabular some functionality or interaction with the model changes in significant ways.

To summarize this point, power users or data modelers will create Power Pivot models in Excel. These models may or may not be deployed SharePoint, but they need to take them to the next level. You can migrate a Power Pivot model to tabular with ease by using the import option in SQL Server Data Tools.

image

Interacting with Power Pivot

I started by creating a simple Power Pivot model using Adventure Works DW data based on the Internet Sales fact table. I am using seven tables in my model as shown here.

image

I am not going to add any calculated measures to the model because Power Pivot allows me to use the data as it sets. Next we create a pivot table based on this model. I dropped the Fiscal Year onto rows and added OrderQuantity and ExtendedAmount to the values region. When OrderQuantity and ExtendedAmount are added to the pivot table, Excel defaults to a sum calculation when working with the data. Basically Excel creates the calculation for you based on what it knows about the data.

The point here is that I have data that can be used as values without doing any additional work with the model. I saved the workbook, closed Excel and moved on to the next step.

Interacting with Tabular

First we need to convert the Power Pivot model to a tabular model. Which is done by importing the model we just saved in SQL Server Data Tools. Once we have the project open, we need to deploy the model to a SSAS tabular instance so we can connect to it with Excel.

image

Now that it has been deployed to SSAS we can reopen our workbook and add a connection to the tabular model. In the field list we notice three differences now that the model is tabular.

1. The SUM symbol (sigma) is used to highlight values or measures that can be calculated.

2. The values we created in the Power Pivot model show up here.

3. In the Values section, “_No measures defined” is shown.

image

When working with multidimensional models, the Values section are represented the same. That makes sense as the connection that Excel is using is based on MDX not DAX. This significantly changes the user experience.

Let’s add a new measure to our Power Pivot model and try to do the same in the tabular model. We can still drop the DiscountAmount into the values section in our pivot table based on Power Pivot. However, when we try to do the same on tabular we get an error saying that we cannot add it to that area of the report.

image

In order for us to use DiscountAmount as a measure we will need to create an OLAP measure (See Excel Tip #8 for details) to use it in this Excel workbook or we will need to add it as a calculated measure in tabular and redeploy for it to be available.

What’s Happening

Because Excel treats a tabular model the same as a multidimensional model in SSAS you will need to add calculated measures for all measures you want to use as values in pivot tables in Excel. Multidimensional models are highly structured using the dimension and measure group techniques. While tabular “feels” like Power Pivot, to be used by Excel it needs to appear structured like multidimensional cubes.

Making this more interesting is that Excel uses MDX to communicate with tabular models, not DAX. As a result, we are able to use the OLAP tools in the PivotTable Tools ribbon.

image

This option is not available when working with Power Pivot models in Excel.

Impact to Users

Overall the impact to users, in particular power users and report builders, is that they have less “freedom” to design when using a tabular model. If they want to add more calculations, they need to be familiar with MDX. Furthermore, if they want the calculations to be generally available they need to work with IT to deploy updated models.

Hopefully we will see DAX supported interaction with SSAS in the future, but for the moment you need to understand how tabular and Power Pivot differ when using pivot tables in Excel.





Exploring Excel 2013 for BI Tip #14: Sparklines and Pivot Tables

7 01 2014

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!

Sparklines and Dashboards

There are a lot of visualization possibilities with Excel. When creating dashboards, sparklines are a good visualization of what happened over a data series. My goal was to add sparklines to a pivot table so it could be added to a dashboard. After many failed attempts, I was able to get the following to work.

On the INSERT tab, you will find the Sparklines options. In my pivot table I am going to add Line and Column Sparkline visualizations using the MyVote submission counts.

image

Here are the steps that I used to add this visualization to my pivot table.

First, I created a pivot table with Submission Count as the measure, the rows were the Poll Categories, and the columns are the quarters of the year. Here is what the original data looks like.

image

In this case, I kept the Grand Totals for both columns and rows turned on. I am going to use these areas as the targets for the sparklines. I am going to use lines for trends over time on the Grand Total column. Then I am going to use the column visualization to show the category distribution on Grand Total row.

Adding the Line Sparkline

To add the line sparkline, select all of the data cells (no grand totals). Next, select the Line Sparkline option. This will open the Create Sparklines dialog. In the dialog, you can see the Data Range is already populated with the highlighted cells. The Location Range is empty as shown below.

image

Next, you select the columns in the Grand Total column, and that cell range will be added to the Location Range field. This will put the sparklines in those columns and they will match the data trend. For clarity, the final step would be to change the column name to “Trend” and change the font color to white so the text is not seen. Here is the result.

image

Adding the Column Sparkline

Next up, we will add the Column Sparkline. Highlight the same cells as before. Once the cells have been highlighted, select the Column Sparkline option. Select the Grand Total row for the location. This will show the distribution within the quarter for the categories. Changing the font to white does not hide the value in this case. I actually reduced the font size to 1 to make it nearly invisible. (There is no transparent font available.) Here is the result.

image

I also added lower right corner by selecting the Grand Total column cells as the data and that cell as the location to get a consistent look at distribution. One other note, the Grand Total row is called “Trend” as well because they have to have the same name. But, overall, this was the look I was working toward.

Limitations and Nuances with Sparklines

Now for the stuff that doesn’t work as you would like. Sparklines are technically not part of the pivot table. As a result, the table needs to be static in shape. This means rows and columns need to stay the same in count and position.

I am going to add a category slicer to my example. When I select the Entertainment category, all of the sparklines are “stranded” in space. Quarter 2 disappears because it has no data and as a result the trendlines are no longer in the table. This is also true for the columns as four categories are eliminated by the filter. Worse yet, if you look at the filter, you will notice we have no poll submissions in the News category. When that is added the sparklines will end up in the last data row as opposed to the Grand Total sections.

image

Sparklines are a nice tool to have, but you need to understand what is the best way to use them in the context of what you are doing.

Reference and Credit

I ran across this during my search for how sparklines work in pivot tables: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-you-insert-a-sparkline-into-a-pivot-table/e072570d-b367-41f1-b2d6-2dbe939db311.  As I note with the limitations to my solution, the forum post above calls out some alternatives which allow for more dynamic approaches, but they also involve coding. Furthermore, the comment from Andrew Lavinsky (MVP) confirmed that this was possible and that it is supported in SharePoint Excel Services.





Exploring Excel 2013 for BI Tip #12: Icon Sets

3 12 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!

Using Icon Sets

So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.

image_thumb1_thumb

Icons in Brief

Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.

image

Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).

Creating a KPI with an Icon Set

When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.

image

This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.

Using shapes as well as colors is particularly helpful if you have color blind users.

In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.

image

As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.

image

This button will show the following selections for applying formatting rules:

  • Selected Cells (currently selected)
  • All cells showing “Submission Count” values
  • All cells showing “Submission Count” values for “PollQuestion”

Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:

image

As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.

image

You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.

You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.





Adding Top 10 Charts to Power View Which Honor Filters

19 09 2013

Before I get too far in the weeds, I have to recognize Rob Collie, his blog (http://www.powerpivotpro.com) and his book DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX. His book has been a great resource as I work through fun with DAX. I used information from his post on Dynamic TopN Reports via Slicers, Part 2 to solve my problem which I will describe next.  Thanks for the info Rob!


The problem I was trying to solve was creating a top 10 chart in Power View that showed the top 10 in context of what was filtered in the Power View report. If you click a pie slice in a chart, filter the top 10 at the same time. Also have it work with slicers. I was working with Power View and a couple other BI tools to see if this was possible. All tools are able to handle creating a calculation that will show the top 10 for a set of criteria, however, when the data was dynamically filtered on the page, only top 10 values that from the original measure that also worked in the filter worked.  So, now my top 10 charts would have fewer than 10 results.

I will walk through the process so you can see both ways and how I got to the eventual solution.

Both solutions use the RANKX function in Power Pivot. So let’s start their. I am using my Poll solution from the My Vote application built for Modern Apps Live. We have a Poll Response Count calculated measure:

Poll Response Count:=COUNTA([PollResponseID])

Using that as the starting measure we add the ability to get the top 10 responses by Poll. On the Poll table, which is joined to the Poll Response table by Poll ID, I added the following calculation to give rank values to the Polls.

Poll Response Rank:=RANKX(ALL(‘Poll’),[Poll Response Count])

This applies a rank value in ascending order to the Polls by Response Count.  Let’s apply this to a Power View report. In my report, I will be adding a Slicer on Category Name (all Polls belong to a Category), a bar chart with Response Count by Poll Question, a Pie Chart with Response Count by Category, and a table which shows the raw rank value for each question. Here is what that looks like:

image

In order to get the top responses in the bar chart we need to add Poll Response Rank to the Chart Filter (Not the view filter) for the bar chart. As you can see below, we pick the responses less than or equal to ten.

image

This applies the top 10 filter only to the chart which is what we want. However, when I filter by Off-Topic only two values remain in the chart. When you look at the table values ranked less than 10 overall will not be included in the chart. In some cases this is exactly the functionality you want. Here is what the filtered view looks like, only two of the 4 show up in the bar chart.

image

What if I wanted the slicer to also apply to the bar chart. We need to use a slightly different formula.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count])

By changing the chart filter to the new rank value you will see we get the filter applied based on the selections in the sheet.

image

How Does It Work?

The key is what I learned from Rob’s writings and samples. The ALL() function removes external context when applying a function. So ALL in this case will return the full set of Polls and then apply the RANKX function. So, it is returning the rank for all polls. ALLSELECTED() applies the RANKX function to the currently selected values. So any operation within a Power View sheet which changes the filter context, such as a slicer, will reset the Polls to only the set of Polls which have been selected and then apply the ranking.

If you do not use ALL or ALLSELECTED, the context is the row and will always return 1. Which is not helpful.

Can We Reverse the Order?

If you need to show the bottom ranking, you can add to the RANKX function. RANKX syntax:

RANKX(<table>,<expression>[,<value> [,<order>] [, <ties>]]])

Value, Order, and Ties are optional parameters. The one that matters is the Order parameter. The Order can be 0 or 1 and it defaults to 0. 0 ranks the values in descending order. To get the bottom result, you would use 1 here. The following code shows how we would rank the bottom polls.

Poll Response Filtered Rank:=RANKX(ALLSELECTED(‘Poll’),[Poll Response Count],,1)

For the optional parameters, you only need to leave a placeholder if you do not need it. Why create a separate value for reverse order? Because Power View can easily handle less than 10, but not the last 10. By making the change above and not changing our standard rank you can see that the data is now filtering for the bottom ten based on how the ranking value is assigned.

image

I hope this helps you create even more effective visualizations with just a bit more DAX under your belt.





Exploring Excel 2013 for BI Tip #11: Color Scales

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

Using Color Scales

In the last tip we covered using conditional formatting with Data Bars. Next up is Color Scales.

What Are Color Scales?

Color Scales are a conditional formatting feature that can be applied to cells in Excel.  Color scales “color” the cell based on the data that the formatting is applied to.  The color scale feature works with pivot tables and standard cells in Excel.  Our focus will be on using color scale with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image_thumb1

image

First starting with a single column, we can use use Quick Analysis or the Ribbon to apply our Color Scale rules. While the data bar used size to highlight the differences in the data, color scales will, obviously, use color. The most common color scale to use is to use Red for lower numbers to green for higher with yellow or white in the middle. Let’s apply that to our column of data.

image

This is particularly effective when working with percentages or other values where red can mean “bad”.  Keep in mind that you can change the order of the color scaling so that a higher number can be red as well.  You can also use two color scales when that is all that is needed.  I have used two color scales in cases where only two values are present such as a Boolean value.

When setting up the formatting, you may notice a “box” in the corner.  This can be used to tell Excel how to apply the formatting.

image

This works great when you pick the first cell in question then use this short cut to set the formatting based on how you want to apply the changes. The  only rule type that applies in this scenario is the “Format all cells based on their values” which options are shown in the shortcut.

While a very simple visualization technique it can be to highlight differences in a compelling way.

Next tip will cover Icon Sets. See you then.





Exploring Excel 2013 for BI Tip #10: The Data Bar

30 07 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!

Using the Data Bar

This feature has been a part of Excel for a long time.  However, as with any tool, some of the oldies are really good.  As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years.  So as part of this series, I will also highlight some important visualizations that have been around.

What is the Data Bar?

The data bar is a conditional formatting feature that can be applied to cells in Excel.  Data bars “fill” the cell proportionally based on the data that the formatting is applied to.  Data bars work with pivot table and standard data in Excel.  Our focus will be on using the data bar with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image

image

As you can see Data Bars are one option under Conditional Formatting.  Look for future tips to come on some of the other Conditional formatting options.

Adding Data Bars

The following sample is from the MyVote data generated from the Modern Apps Live! project.  In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.

image

To add data bars highlight the area to add the bars and choose a format.

image

Advanced Settings

By clicking More Rules … you will be able to apply advanced options.

image

In my opinion, the most important setting to use here is at the top.  When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.

However, this option will also highlight the any total columns (in our case Grand Total was included).  If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”.   You will find that this is the most common option to select when using data bars for data visualization.

Modifying Existing Data Bars

Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down.  This will open a dialog box which has the formatting rules for the selected pivot table.  There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook.  From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.

Data bars are a simple, but effective data visualization when you need to highlight the variance between values.  With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.





Exploring Excel 2013 for BI Tip #9: Adding Calculated Members

25 06 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 Calculated Members to the Excel 2013 Workbook

In my last tip (#8), I discussed using calculated measures.  In this tip, I will talk about creating a calculated member.  The primary difference is that a member becomes a part of a dimension and can be used as a filter, column header, row header, or even a slicer.

In my simple example, I want to created an aggregated set of categories used in my polls called “Cool Cats” which contained the Fun, Entertainment, and Sports categories and puts my new member in the Poll Category attribute hierarchy.  I will use this to see how many submissions there were in these categories.

As before, you can create and manage calculated members from the ANALYZE tab in the PIVOTTABLE TOOLS ribbon.  Use the OLAP Tools menu and select the MDX Calculated Member option.  If you have already created the member, use the Manage Calculations option to edit existing members and measures.

image

The New Calculated Member dialog is different from the dialog used to edit the member.  Let’s start with the create dialog as noted below.

image

(1) Assign your measure a name.  It will have to be unique in the context of the the Parent Hierarchy.

(2) Assign the Parent Hierarchy and Parent Member.  This establishes where you plan to locate the new member.  As you can see you can choose any hierarchy you use in your cube.  The Parent Member property lets you choose that first level within the hierarchy.  In my case, I am choosing the Poll Category attribute hierarchy and the All member as the parent.

(3) Create your member with MDX.  Because you are creating a member, it is important that your MDX resolves to a member.  As a side note I used a set initially which passed the Test MDX operation, but displayed as #VALUE in Excel.

(4) Test MDX will allow you verify you have no syntax errors in your member creation.  However, as I noted in step 3 it is not flawless, so you may still have issues even though it is valid syntax.

A couple of important concepts. In my situation, the Cool Cats member stopped at the top level.  If I put this in the Category hierarchy which has multiple levels, Cool Cats would have no children as it is a stand alone member.  However, when applied at a filter level it will filter results properly. The image below shows the filter in use.

image

Now when I pull the Poll Category hierarchy into the pivot table you will see that Cool Cats is a peer member and has the valid value.  By default Excel will not calculate the Grand Total with those members twice.

image

You will need to be prepared to properly call this out for your users so the understand how calculated members operate in this scenario.

A common use case for creating calculated members is to create date members that aren’t easily created or tested.  This will allow you to work out the member and how it affects the user experience.





Exploring Excel 2013 for BI Tip #8: Adding Calculated Measures

20 06 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 Calculated Measures to the Excel 2013 Workbook

If you have worked with SQL Server Analysis Services in the past you already know what calculated measures are.  More importantly, you know how to update the MDXScript without requiring a cube refresh.  (If you are unaware of this, check out the BIDS Helper project on CodePlex.)

A calculated measure uses existing measures and MDX to provide additional, shared calculations in a cube.  However, there are many times that the ability to create a calculated measure in Excel would be great.  In Excel 2013, this is now possible.

Once you have connected to a cube using a pivot table, you can add calculated measures using the OLAP Tools menu on the ANALYZE tab.

image

When you select the MDX Calculated Measure item, it will open an MDX dialog designer in which you can create a measure.  (MDX Calculated Members are will be in the next tip.)

image

Before we create our measure, let’s talk about the ancillary parts such as the name, folder and measure group.  You will want to give your measure a name.  It needs to be unique within the work you are doing and unique from other measures in the cube or you will get an error.

image

The folder and measure group are really optional.  It really depends on how you want display the new measures in the Excel Fields window.  I would recommend that folders are used when large volumes of measures are being used.  It is a great way to organize the measures into consumable, related groups for your users.

image

When you designate the measure group, the measure and folder will be put in the same group as the measure group.  This is appropriate when the measure is related exclusively to the measure group, conceptually if not technically. I usually will only do this if all of the measures come from the same measure group (technically related) or if the user understands that the measure “should” be a part of the measure group even if it is dependent on measures outside of the current measure group (conceptually).

Next, you create the measure.  The Fields and Items tab contains the measures and dimensions available while the Functions tab has the MDX functions you can use.  Use the Test MDX button to verify syntax prior to saving the measure.

The really nice part is that this measure is now contained within the workbook.  It does not get published back to the server.  However, if the measure becomes popular, you can use the MDX from this measure to create a new measure on the server.  It will be business verified before being published.  By using Excel to create calculated measures, you also prevent a glut of single use measures from being created on the server.

Finally, to manage created measures, use the Manage Calculations option on the OLAP Tools menu.  It will open a dialog with all of the calculated measures and calculated members created with this data connection in the workbook.  In my scenario, I used the MyVote Cube connection to create the measure.  Basically, the pivot table is associated with a connection and that is the defacto filter for this list.

image

Use Excel to test MDX simply.  This will allow you to create measures, verify data, then deploy working code.  It is a great addition to the product.

Next up… Calculated Members.





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








Follow

Get every new post delivered to your Inbox.

Join 730 other followers

%d bloggers like this: