Getting Started with Microsoft Data Explorer

Posted on March 5, 2013 by devinknight

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from http://www.microsoft.com/en-us/download/details.aspx?id=36803.

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

image

Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.

image

Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.

image

Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx.

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

  1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
  2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.image
  3. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.image
  4. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.image
  5. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
  6. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.image
  7. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.image
  8. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.image
  9. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
  10. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:image

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

Filed under: Excel | Tagged: , , , | 4 Comments »

Excel 2013 Inquire Add-in

Posted on February 11, 2013 by devinknight

What is Inquire

The Inquire Add-in for Excel 2013 helps you analyze and review your workbooks for things like relationships, invalid formulas, and to compare .xlsx files for differences.  Let’s take some time to review how to enable the add-in and then how to use it.

Enabling the Add-in

If you are using Office Professional Plus, the Inquire add-in comes installed with Excel you simply need to enable it.

The Inquire add-in is already installed with Excel 2013 you simply need to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

image

Check off Inquire from the Add-Ins available list then click OK.

image

Once you have enabled the add-in the INQUIRE tab will appear in the increasingly crowded Office ribbon.

image

Let’s take a look at what this new add-in has given us.

What does it do?

Report

Workbook Analysis

The Workbook Analysis tool is used for analyzing details of a workbook for reporting on the things like the workbook structure, formulas, cells, ranges, and warnings.

image

One odd thing here that a lot of people notice a workbook structure property called “Very hidden sheets”.  This is simple another way of hiding sheets through VBA.  See here for more details on how to do a Very hidden sheet.  http://j-walk.com/ss/excel/usertips/tip036.htm

Diagram

Workbook Relationship

The Workbook Relationship tool shows links between multiple workbooks that you may have.  This can be especially helpful when you have dozens of cell references to an external workbook.  Trying to untangle that mess can be very difficult and this tool visualizes those dependencies.

image

Worksheet Relationship

The Worksheet Relationship tool works exactly the same as the Workbook Relationship but now visualizing dependencies at the sheet level.

image

Cell Relationship

Again the Cell Relationship tool is very similar but it only shows relationships of cells that you have highlighted prior to click on the command.  These are often dependencies are often displayed because a cell is referenced in a formula.

image

Compare

Compare Files

The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.

Any differences that the compare tool finds color coded by content type in a grid view.  Here’s an example output:

image

Miscellaneous

Clean Excess Cell Formatting

If you’ve developed an Excel workbook that’s gotten out hand with how large it is and how much logic is built in use the Clean Excess Cell Formatting tool to remove extra formatting on cells that you may not be aware of.

This can actually help increase the performance and file size of workbook that have become bloated with this kind of leftover formatting.  You can test it out by putting a background color on a couple irrelevant cells and then run the tool.

Workbook Passwords

If you are using the Inquire add-in for analyzing workbooks that are password protected you must add the workbook password to the Password Manager.

image

Help

This is the standard Excel Help that will walk you through what the add-in does.

I’ve found the Inquire add-in has a couple nice features that i can definitely see myself using in our increasingly Excel centric world we live in.

Filed under: Excel | Tagged: | 3 Comments »

Excel 2013 – Trouble Installing Silverlight for Power View

Posted on September 25, 2012 by devinknight

Last week during my 24 Hours of PASS session on Choosing a Reporting Platform I fielded a question that appeared multiple times during the Q&A so I thought it would be worth post.

When using Power View in Excel 2013 the first thing you may notice is a screen like the below screenshot prompting you to download and install Silverlight.

7-16-2012 9-34-29 PM

The question that many people in the session had was no matter how many times they installed Silverlight they couldn’t make it past this screen prompting to install Silverlight.

Turns out this is an easy fix.  Just make sure when you download and install Silverlight that it is done from Internet Explorer.  Many people use other web browsers these days but remember you’re working in a Microsoft tool and it would only make sense that you would download Silverlight using IE.  After installing using Internet Explorer hit the Reload button and you should be good to go!

Filed under: Excel, Power View | 9 Comments »

OLAP PivotTable Extensions Update

Posted on July 31, 2012 by devinknight

OLAP PivotTable Extensions is a really cool free add-in for Excel that’s been around for quite a while now.  It allows you to have some exposure to what’s going on under the covers (Among many other features) while you browse a cube using an Excel PivotTable. This weekend there has been an updated version and you should check out the features and download it now!

http://olappivottableextend.codeplex.com

The new features in this release are:

  • Support for Excel 2013
  • Upgrading to the AS2012 version of ADOMD.NET to now support connections to .bism files. (This upgrade required adding the .NET Framework v3.5 SP1 as a prerequisite. Use a prior version of OLAP PivotTable Extensions if this prerequisite is a problem for your organization.)

Filed under: Excel, SSAS | Tagged: , , | 1 Comment »

Office 2013 Preview – Enabling PowerPivot in Excel

Posted on July 17, 2012 by devinknight

With the Office 2013 announcements today I was excited to download and get my hands on the tools.  Because this is still a customer preview we’re bound to find things here and there that aren’t functioning as expected when experimenting with the Office tools.

The things I had on my list to test first were Power View and PowerPivot from Excel (Yes, Power View is now available in Excel).  In this post I’ll focus on helping you get started with PowerPivot.

With Excel 2013 you no longer have to download PowerPivot as a separate add-in but you may find when exploring the PowerPivot tab that all the available buttons in the Office Ribbon are greyed out.  The problem is that while the add-in maybe installed it is not enabled.  To fix this click File->Options as shown below.

image

Go to the Add-Ins page.

image

Change the Manage property to COM Add-ins then select Go

image

Check Microsoft Office PowerPivot for Excel 2013 then click OK.  You may also notice PowerPivot for Excel as an option here if you had a previous version of PowerPivot installed.

image

You should now be able to start working with PowerPivot.  Hope this helps getting you started!

Filed under: Excel, PowerPivot | Tagged: , | 1 Comment »

Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

Posted on May 2, 2012 by devinknight

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

  • Ad Hoc Reporting
  • Content Sharing
  • User Flexibility
  • Developer Flexibility
  • Time to Develop

image

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

https://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

Filed under: Excel, PerformancePoint, Power View, PowerPivot, SSRS | Tagged: | 10 Comments »

Choosing the Right Microsoft Reporting Technology Part 2: Excel

Posted on March 6, 2012 by devinknight

This post is a continuation of a series started to help you understand each of the Microsoft presentation layer tools purpose and when you should choose each for your reporting solution.  The decision to pick the appropriate tool is difficult but so very important as I detailed in part one of this series.  As I continue the discussion here again are the different tools I will detail:

    • Reporting Services
    • Excel (From the perspective of building PivotTables without PowerPivot)
    • PowerPivot
    • PerformancePoint
    • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

In part one of the series you learned about Reporting Services and the role it plays mainly as a static reporting tool.  Here in part two we will discuss the polar opposite, which is Excel.

Excel

What it is

As you can see in my list I’ve made sure to differentiate Excel and PowerPivot as two separate tools even though PowerPivot is part of (free add in) Excel.  So to be clear when I say Excel I’m referring to Excel PivotTables off of an Analysis Services data source.  Of course any business user will tell you that PivotTables are possible without an OLAP data source, but to keep this relevant to Business Intelligence I will focus on Analysis Services as a data source.  With that focus in mind let’s discuss the tool.

Excel is customarily thought of as an ad hoc reporting tool that is great for getting a quick answer from your data source.  This is where quick analysis can be done without having to wait days for a report developer to define specs, write a source query and then finally build the report.  The report below for example was literally built in less than a minute.

image

Usually when I teach Analysis Services to a group my first question is, “How many of you have dozens of reports waiting for you or someone else to build when you get back to work?”  Predictably about half the room raises their hand and I go on to explain that part of the goal when building an OLAP database is to take much of the reporting burden off of you and move that to your more savvy end users that are comfortable in Excel.  This is possible because after you have built the Analysis Services layer Excel can consume it in a way that give users the ability to drag and drop fields into a PivotTable with very little training required.  This was one of the earliest forms of Self services BI.  So now many of those reports that are waiting in your queue can likely easily be created by the users themselves.  Of course this doesn’t solve all reporting needs for users but it can definitely relieve some stress.

Because Excel is most end users comfort zone you will find they need little training.  In fact admittedly I have learned a thing or two from end users about reporting in Excel.  Your diving into the end user’s bread and butter and that is why this is such a popular option for reporting.  Be wary though because end users love it so much they may start going beyond Excel’s intended purpose as a reporting platform.

What it isn’t

While Excel is great for cranking out reports quickly many think it lacks some customization features.  Now I happen to think this is partially a myth because we’re IT folks and don’t play in Excel all day.  Like I said earlier I’ve had too many experience where an end user has taught me something I didn’t think was possible in Excel.  Even though I identify it as partial myth I would still argue that tools like Reporting Services are far more customizable with with reports they produce.

Excel also is not necessarily the right tool for static reports.  As mentioned earlier it’s purpose is for ad hoc reporting, but if you use it for more permanent reports it’s not the end of the world.  You may be just miss out on the benefit of other reporting tools.  For example, let’s say your sales team looks at a weekly report of their previous weeks sales compared to last year.  If this report was in excel they could either open the file each week or view it in SharePoint with Excel Services, but the better solution may be to have it emailed directly to the sales team members using a Reporting Services report and subscriptions.  Having it in Reporting Services also opens the doors for features like report caching if performance becomes a problem.

Who Uses it

No long explanation needed here because this tool is for end users as previously detailed.

A couple caveats I’ll mention here are:

  • Excel is a far superior tool for testing KPIs than the built in Analysis Services KPI browser.
  • In SQL Server 2012 Excel becomes the primary way that developers test tabular BISM designs.

How is it consumed

The two typical ways of consuming Excel PivotTable reports are opening the .xls or .xlsx file itself or SharePoint with Excel Services.  Ideally your company would go the Excel Services route otherwise an unorganized chaos of workbooks being emailed or placed on shared drives can become a mess.

Excel Services allows you to place the workbook in a web facing version of Excel.  It does not require that Excel is installed on the users machine because they can view the Excel workbook directly through their web browser.  Any content deployed to Excel Services is managed through SharePoint content databases.

Limitations

One major limitation some find is depending on the version of Excel you are running that row limits exist.  I would argue that you are not using the Excel PivotTables for their intended purpose if you get anywhere near those limits.

I have also found that occasionally Excel can write some rather poor MDX against a cube with the drag and drop interface and unfortunately you do not have much control over it.  There is a nice free tool available called OLAP PivotTable Extensions which allow you to right-click on a cell and it provides the MDX that led to the results.  This can be pretty helpful when you’re first learning MDX (the query language for Multidimensional Analysis Services).

Summary

As we go through this series remember these high level characteristics about Excel:

  • Most end user preference
  • For ad hoc report development
  • Can be used for static reports but may not be best fit
  • Excel Services with SharePoint best way to store and share reports

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PowerPivot.  To read any of the other parts to this series follow the links below.

Filed under: Excel | Tagged: , | 4 Comments »