Tag Archives: Microsoft Office

Unpivot data using Microsoft Data Explorer

I have been really impressed with Microsoft Data Explorer the minute I had a chance to play with it as I was able to turn formatted Excel reports into legitimate data sources available for analysis. Fairly recently, an update to the tool delivered an uber critical feature that makes a huge impact on the world of self-service BI. I am talking about the Unpivot.

Let’s look at this table for example (shamelessly borrowed from this Wikipedia page)

Here we see historical and predicted population for several regions of the world. This table serves great purpose at conveying the information, however, if I wanted to do any sort of analysis across all of the time periods, I would have a problem doing it as the data is stored across eight columns instead of one, like this

region year population
Africa

1750

106

Africa

1800

107

Etc…

The new Unpivot feature of the Microsoft Data Explorer allows us to do just that, collapse multiple columns of data into one. (Another common example would be a financial statement where you have financial metrics going down in rows and the amounts going across the columns, one number for each month)

Let’s go through a few steps to get a feel for how it works.

First, let’s fire up Data Explorer and click on the   button on the ribbon. That will bring up find page window where we can paste the URL of our table (http://schools-wikipedia.org/wp/w/World_population.htm)

Click OK and the new data explorer query window comes up that looks like this:

Click on the first table on the Navigator pane and you should get something that looks like this

As you can see, we have our regions in rows and our data in a column for each year analyzed.

Now, let’s unpivot this data, in order to do that, highlight all the year columns and right click on a header of any highlighted column and select Unpivot menu option

The result should like this:

Now you can right click the Attribute column and rename it to say Year and you will have yourself a perfectly consumable data set

You can click Done and then add the data to your PowerPivot model or a pivot table. I think that for anybody who gets data as a pivoted report, this feature will save countless hours trying to massage the data into something a little bit more consumable.

How to think strategic about Business Intelligence

Many people reach out to me asking for advice in helping them evaluate various BI platforms for their companies. In my mind I am obviously the wrong guy to ask as I am not shy to admit my fairly heavy bias towards Microsoft.  However, over the course of the last twenty years, I have been exposed to just about every major BI player on the market and I think that my bias is well justified as it is actually based on a very pragmatic and rational set of criteria.

Having said that, I find it very interesting, however, that a lot of companies go about selecting their Business Intelligence platform in a very non-strategic, irrational and tactical way. I, therefore, decided to compile a quick list of things that I think would be important to consider as these evaluations take place.

Hardware evolution

The changes that are taking place on the hardware front are very important. Majority of the top enterprise BI vendors will say that some sort of a BI appliance (Hana, Exadata, and PDW just to name a few) is absolutely necessary to catch up with the ever increasing data volumes. I happen to think that as a general rule this argument is completely ridiculous. As a rule of thumb, hardware is commoditized on a daily basis. A $20k server today will likely have 32+ cores and up to a TB of RAM. Few years ago, similar hardware would require a $1M investment. Today I routinely load hundreds of millions of rows of data in a PowerPivot model that runs on my laptop allowing me to do the kind of analysis that required something like Teradata + Microstrategy just a few years ago. My recommendation would be to exhaust the commodity hardware options before an appliance is even considered.

Self Service Vs. Enterprise

Much of my time is spent fighting philosophical battles with various IT organizations around this topic. IT wants to be in control and own the content creation (they want to build all dashboards and reports themselves). At the same time, I have yet to see an IT shop that did not have a huge backlog of reports and an upset customer (business) who is starving for data and reporting tools.  I therefore recommend reframing the role of IT in the business intelligence strategy of a company this way: IT will own data, security, governance and plumbing required to wire up all the pieces of the BI architecture, business will own all (well, most…) of the content creation. IT would be required to have someone on staff who can assist business customers if they get stuck but whatever BI tool an organization is looking at, it should be self-service focused first and foremost.

Platform consideration

Over the last few years the BI landscape has changed very significantly. One of the major changes was the movement away from the “best in breed” and towards the “platform buy”. Generally speaking, this makes sense. A platform buy will sacrifice on some features and usability for the potential gains in interoperability. In reality, however, out of the four big vendors, IBM, Oracle, SAP and Microsoft, only one can honestly say that all of its offerings are wired together natively.

Bill of materials

Many people don’t think about this aspect of the decision, but I think it is very important to consider how many different products and installs will have to be stood up in order to get the BI platform implemented. All but one vendor will typically have a separate tool (or most likely several different tools that came from different acquisitions) for different aspects of BI. So an ETL, Dashboarding, Reporting, Analysis, Master Data, Data Quality, Predictive Analysis, Data Warehousing, Semantic Layer, Self Service tools, Data Exploration, Drill-though, Report bursting, Knowledge management, Social, and many other elements of business intelligence platform will often require a different infrastructure investment, install management and most importantly a separate license to obtain.

Conclusion

This is obviously not a complete list of things to consider for someone who is trying to evaluate a BI platform but I think it is a good start. When I am involved in an evaluation process like this, my number one goal is to spend as much of the budget $$$ as possible on content – dashboards, reports, analytical views – something that my end users will get to use on a day-to-day basis as opposed to adding more hardware to the data centers or software on a balance sheet.

PowerView Feature Comparisons

PowerView experience will be delivered across several Microsoft products:

  1. Excel (fat client)
  2. Excel Services (SharePoint)
  3. SharePoint – SQL Edition
  4. Office 365
  5. PowerPoint

I get a lot of questions as to what features are supported across all these deployment scenarios. Luckily Microsoft shared a slide that I will attempt to reproduce here that explains what features work where and how.

Feature

Excel

Excel Services

SharePoint/SQL

Office 365

All visualizations

Yes

Yes

Yes

Yes

Power View Authoring

Yes

No

Yes

No

Cross Visualization Interactivity

Yes

Yes

Yes

Yes

External Images

Yes, anonymous

Yes, anonymous

Yes

Yes

Workbook or model size

Max Workbook Size

AS Limits

AS Limits

10MB

Export to PowerPoint

No

No

Yes

No

Reorder Power View views

No

No

Yes

No

Connecting to multidimensional UDM

No

No

Yes

No

Offline Mode

Yes

No

No

No

Multiple External Models

Yes

Yes

No

Yes

Report Zoom

No

Browser Zoom

Browser Zoom

Browser Zoom

Reading and Presentation mode

No

No

Yes

No

Windows RT support

No

No

No

No

Windows 8 Pro support

Yes

Yes

Yes

Yes

There are several interesting points to make here.

  1. Microsoft thinks of “Export to PowerPoint” as a feature as opposed to thinking of PowerPoint as another environment for Power View like Excel even though the PowerPoint does support the authoring capability as it is outlined here
  2. There seems to be some fragmentation in several important features such as ability to support multidimensional models and multiple external models
  3. No Windows RT support is very annoying (given the fact that RT is happily supporting Flash but not Microsoft own Silverlight). I understand (although do not agree with) that Microsoft had to scale down the RT functionality in order to please its hardware partners; however, I do not understand why I do not even have an option to BUY the missing functionality such as Silverlight, Outlook, etc. (or rather the entire Office Professional) for Windows RT. As I mentioned before, I love the Surface from the hardware perspective, however, I believe that I should be able to have an option to upgrade my user experience to the full featured product for additional $$$ if I have to

How to connect Excel PowerView to Analysis Services cube

There are several UI gotchas related to building PowerView visualizations that I wanted to address with this post. First, PowerView is not available in the Ribbon out of the box, in order to enable it, one needs to go to File->Options->Add-ins. Then find Manage Add-Ins drop down box, select “COM Ad-ins” and click Go. That brings a window up that allows us to check PowerView box to enable it.

 

 

 

Once enabled, the PowerView button is now available

 

 

 

 

Now that PowerView is enabled, what can we do with it? Well, if the workbook happens to have a PowerPivot model in it, clicking the PowerView icon will create a new worksheet with the PowerView experience in it against that PowerPivot model. If the workbook does not have a PowerPivot model in it, you will be staring at a blank PowerView worksheet with no data to explore. This is where things get confusing, since there is no way to specify connection to an existing Analysis Services cube from here.

In order to connect to the Analysis Services model (I still call them cube even in Tabular mode), one should click on Data->Existing connections. That brings up a list of existing connections, (click on Data->From Other Sources->From Analysis Services to create one), pick a connection to your tabular model and click Open. That will bring up a screen that will look like this:

 

 

 

 

 

 

 

 

Now you can select Power View report and off you go!

PowerView in Excel 2013

I have been playing around with the newly released Office 2013.  I cannot seem to find that many tangible Business Intelligence features in it, other than the PowerView feature that is now available in Excel.  This feature has been flaky at best; I am getting all kinds of errors trying to build some visualizations against an embedded PowerPivot data source.  By the way, PowerPivot is now bundled with Excel, no need to download it separately, but Microsoft did not make it easy to enable it.  To enable, go to Options->Manage Add-ins->COM Add-ins and find PowerPivot on the list.

Anyway, back to PowerView.  To create a PowerView visualization (not sure if I should call it a dashboard or a report), one should click on the PowerView icon on the Insert menu of the Office Ribbon. That adds a new worksheet with the familiar PowerView designer in it.  The designer has a few new features that I am very excited about:

  • Ability to modify background (use a picture or color)
  • Ability to insert pictures
  • Ability to create Maps (this is huge!)
  • Cosmetic changes that actually make this tool a lot more user-friendly
  • Ability to Drill up/down on the Matrix control (!!!)
  • Ability to change the font and size of the font for the entire visualization or its individual components

Issues: not very stable in this build… essentially, I now save my work after every change I make, but that will most likely be addressed as the product gets stabilized for the RTM.

Big Issues: PowerView does not support Analysis Services in Multidimensional mode, so I am starting to get really concerned about the future of this very robust technology… Oh, yeah, and it also still requires Silverlight….

How to get SharePoint 2013 and Office 2013

It seems like the demand for the two products is simply insane, lots of links do not work.

I have found a couple of links that have been pretty reliable for me so I will share them here:

  1. Download SharePoint 2013 here
  2. Download Office 2013 (aka Office 365 Preview) here
  3. Download Project Professional 2013 here
  4. Download Visio Professional 2013 here

The offie link is a little flaky, I assume due to millions of over excited downloaders.

Office 15 unveiled by Steve Ballmer… Available for preview…

Steve Ballmer just unveiled the new version of Microsoft office available for preview here..  The two most salient points of his presentation are touch support and windows 8 integration. 

The new version of office appears a lot friendlier for content creation on a tablet.  SkyDrive also becomes a more prominent feature that allows content creation and consumption across multiple computers (office and home).

No Business Intelligence features have been highlighted during the presentation.  I have been trying to install the Preview edition on my Windows 8 tablet (only Windows 7 and 8 devices are supported to run the Preview) but apparently you cannot have both Office 2010 and the Office 15 Preview installed on the same machine, and as I just found out, removing office 2010 from the tablet is not an easy experience.  Stay tuned as I am re-imaging my tablet….

Bubble Charts in Excel and Excel Services

Creating Bubble Charts is one of the biggest challenges in Excel and Excel Services with respect to data visualization.  Microsoft made it very intuitive in PowerView and very not intuitive in Excel.  Frankly, the way this functionality is implemented in Excel makes it practically unusable.  Rumor has it that this area is getting a lot of love and attention in the new release of Microsoft Office (commonly known now as Office 15) but for now we do need some help with building this in the Excel Services supported way.

Microsoft has posted an example of how to do bubble charts in excel so that it will be suitable for Excel Services.  You can find a link to the excel file here. It took me a while to figure out how the file is set up. So to get a quick jump-start on it, take a look at all of the name ranges defined and also click the bubble chart and then on Chart Tools->Design-> Select Data button on the ribbon and then Edit on the Pop up Window to see how those named ranges are being used in the chart.

Not easy and user-friendly, but it works and will provide a much-needed relieve until the new features come out with the new release of Microsoft Office and SharePoint.

Improving performance of Excel reports against Analysis Services

I will start this post with a bit of a rant. There are two types of people in the Business Intelligence world:

  1. Those who do not like Excel
  2. Those who do

Most of the right-click-hating low tech users belong to the first category (they are also usually the same people who like bubble gum and shiny things).  I like bubble gum as much as the next guy, but along with those of us who are viewing business intelligence as something more than pretty gauges and dials, I consider Excel as the ultimate tool for just about every facet of BI.

Over the last two releases, Microsoft has added a lot of very powerful features to Excel to make it easier for a user to explore and analyze data in Analysis Services cubes.  One can connect Excel to Analysis Services by clicking on Data->From Other Sources->From Analysis Services.  That creates a connection that can then be used as a source of not only pivot tables and charts, but also a number of Cube…() functions that have been added to Excel in 2007 in order to make Excel even more robust and sophisticated reporting and analytical tool.

These functions break down the limits of pivot charts and pivot tables in terms of what’s possible with respect to the look and feel of the report.  There are however some gotchas that one needs to be mindful of and also some opportunities to improve the performance of the Excel reports that are sourcing their data from Analysis Services.  Here is a good article that suggests some ideas to optimize the performance.

« Older Entries