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.





PASS Summit 2012 Wrap Up

13 11 2012

Wow, what a week. Once again, PASS put on a great event that provided much in the way of events and training for the SQL Server community. If you followed my countdown you know some of what I love about PASS. Last year I blogged everyday but I did not do that this week. So, what was different for me? Well, for one I volunteered much more this year than last and I was privileged to speak twice. I spent more time meeting new people and catching up with friends and that was great as well. Enjoy my wrap up from my week.

Tuesday – Leadership Meetings, Welcome Reception, and some Karaoke

Before the event officially kicked off, I joined community leaders from around the world for a series of leadership meetings. First we had a meeting on SQL Saturdays which was an opportunity to see the immense growth of these free training events around the U.S. and throughout the world. What a great opportunity for SQL Server professionals to improve their skills and for those passionate about the community to improve their abilities by leading these events. Many ideas were shared among the team including a panel on how to effectively run a SQL Saturday on a tight budget.

Once that was completed, the Regional Mentors enjoyed a lunch together and an opportunity to share what we do to support the user groups in our regions. I particularly enjoyed the fact that I was able to spend some time with Regional Mentors from Germany, Holland, and Portugal. This highlighted further the international scope and reach of PASS. This was followed by the Chapter Leaders meeting. That meeting was held as a series of round tables that the chapter leaders could move through. I was working at the table focused on leadership with Ryan Adams ( B | T ) from the North Texas SQL Server User Group – NTSSUG. We had a number of good conversations around building leadership teams for user groups and what is needed to have an effectively led user group. Check out the NTSSUG site for the by-laws sample we discussed multiple times.

All of these meetings were followed up with the Welcome Reception, which I made a small portion of as I was trying to drop my backpack at my hotel and work my way back there. After the reception, I headed out to Bush Gardens with a number of others. During that time, Jes Borland ( T ) managed to get a microphone in my hand and I had my first round of karaoke. Yes, I actually did sing and had fun doing it. All-in-all, it was a good time had by all.

Wednesday – SQL Around the World, Microsoft Announcements, Tabular Models, and Magenic Team Dinner

This was the true kick off to the event. For many, they looked at the key note as the kick off. Before that even began, I was working in the Community Zone encouraging people to participate in the SQL Around the World community activity. It was a great game. You needed to find 10 people from 10 different countries and find out something interesting about them or their country. I found a dancer and someone who had ridden a cheetah as a kid. I also surprised someone from the Czech Republic when she mentioned her home town only to have me let her know I had been to her home town many years ago. It was a fun conversation. If you did this and have other cool stories let me know. It was amazing as well over 50 different countries were represented at PASS.

Next, Ted Kummert had the first keynote session of the day. His keynote was filled with announcements concerning SQL Server including the following:

  • Hekaton: the project code name for a new in-memory OLTP engine
  • The Columnstore Index will be updateable
  • Next version of PDW will be out in H1 2013
  • Polybase: allows you to query across multiple types of data sources such as SQL Server and Hadoop with T-SQL
  • DAX Queries will be able to query SSAS Cubes

He also highlighted some recent announcements related to the SQL Server stack:

  • Microsoft HDInsight Server CTP: Hadoop for Windows Server
  • Windows Azure HDInsight Service Preview: Hadoop for Azure
  • Power View and PowerPivot fully implemented in Excel 2013

After the keynote, I hit a session on BigData and Hive which was put on by SQL CAT and very informative. My big takeaway was to use EXTERNAL tables not INTERNAL tables when working with Hive. I then went to do final prep for my Tabular Model session. In this session, “Building a Tabular Model Database”, I present on what tabular and in-memory is, and then proceed to open up a Visual Studio project and create a database. I think it went well and the attendees seemed to enjoy the upbeat nature for an end of day session. The night wrapped up with dinner with the Magenic team (7 of us). Good chance to grow relationships across offices from around the country.

Thursday – Community Zone and DAX as a Query Language

Thursday was a fairly low key day for me. Once again I spent time in the Community Zone. I had the opportunity to talk with a few people on creating a user group in their area. As always, I like to see people interested in growing their local community.

I also attended Alberto Ferrari’s session on DAX. I think the biggest surprise to me was that you can now query DAX directly from SSMS. I am not sure that I am convinced that it is a full query language yet, but it is definitely closer. The key to it all is the EVALUATE expression which allows you to create the DAX query ironically in the MDX window. Here is just a taste of DAX as a query:

EVALUATE
    ‘DimCurrency’
ORDER BY

    ‘DimCurrency’[CurrencyAlternateKey]

What I found interesting is that you can create columns, build measures, and perform many other operations against the tabular model using DAX. In the end, it will not increase the memory used as storage as it is all calculated. Look for some more on this in later blog posts as I delve more into the in-memory storage and usage when working with DAX.

Friday – More Community Zone, HDInsight, Paul White, and Window Functions

Last day. I spent more time in the Zone. I really did enjoy my time there as I continued to meet more people. I was even present when a contract was completed for the Shanghai user group. Very cool indeed. I then attended a session on HDInsight by Mike Flasco from Microsoft. This is very cool stuff as you can create simple Hadoop cluster on your desktop to test the technology. Microsoft and Hortonworks have done a great job of bringing Hadoop data into the Microsoft stack.

On my way to present my final session of the day and the conference, I stopped in for the second half of Allen White’s ( B ) optimization presentation. In a word (or two), mind-blowing! Wow, who knew that the optimizer did all those things? I was highly impressed and think he should look at a precon on the subject next year. Unlike some three hour presentations, he could have went longer as he was not stretching his content out. Nice work Paul. So, I got to follow that with a presentation on Window Functions in T-SQL. For the second time, I had the last slot of the last day. I think this presentation went well even though we were all worn out from a content-filled week. It was fun to try some ideas from the audience in the demos. That always makes for a more interesting demo. I will be doing a follow up post on what I learned from some of the attendees on the subject as well, proving once again this is a user community event. We all have something to contribute! (If you attended this session, you will find links to the blogs on the subject here.)

What’s Next?

Coming in April is the new Business Analytics conference in Chicago followed by the PASS Summit in Charlotte, North Carolina. Of course, your local user groups will continue to meet with regional SQL Saturdays sprinkled throughout the year as well. How will you participate and contribute in 2013? We look forward to seeing you all again, soon.





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.





Why I am excited about SQL Server 2012 (Part 2)

28 03 2012

Earlier this month I published a blog entry on this same sumagenic-custom-soltionsbject.  In honor of the local Minneapolis launch event, I decided to expand the list.  You can find five more reasons I am excited out on Magenic’s blog.

Here is the link and enjoy SQL Server 2012.

http://magenic.com/Blog/WhyIAmExcitedaboutSQLServer2012Part2.aspx





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 729 other followers

%d bloggers like this: