Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Blogs

Office Excel Add-in for Microsoft SQL Server Analysis Services – its free, its powerful and it could save you money!

  • Comments 4
  • Likes

Well I’ve been blogging for a 11 weeks or so and looking at my stats I can see the posts that attract the most attention are those that either advertise a freebie or offer a performance tip.  So, blatantly pandering to my ratings, here’s another freebie you should know about if you’re into Business Intelligence.  If you’re not into Business Intelligence (BI), I can only say you’re missing out on one of the most exciting applications of computer power.  (I’m building up to squeeze out the definitive blog on the wonders of BI – so keep watching this space).

 

I haven’t read all the surrounding blurb to this addin but suffice to say (in my unsubstantiated opinion) this addin has been developed by Microsoft in direct response to findings in the OLAP Report (www.olapreport.com).  The report concludes customers really warm up to Excel, as a BI (in particular SQL Server Analysis Services) front end, if they use an addin designed to augment Excel’s capabilities as a slicing, dicing, analysing front end.

 

Well, to be fair, that’s not the complete story.  The biggest competitors to MS Office are the previous versions of MS Office.  In response to this threat, Microsoft needs to make the more recent Office releases more attractive than the old ones.  To this end, Microsoft has released a number of these Office Accelerators, some of which only work with the more recent releases of Office and which consequently add to the value of upgrading; fair doos I think, don’t you?.

 

Enough of me, here’s a functional description of the product straight form the web site: http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx.  It’s from our lovely marketing people, they do their best; bless them.  If you can’t be bothered with the blah, blah, yawn you can cut straight to the download and check it out for yourself.  Believe me, there’s a large group of users who’ll find this very useful, they will laud you as someone who can deliver if you show it to them.  Do not let them discover it for themselves, you wont hear the last of it, believe me!  To help cut through the blah blah I've bolden the key bits.

 

About the Excel Add-in for Analysis Services

Using the Excel Add-in for SQL Server Analysis Services, your organization can utilize Microsoft Office Excel to access relevant information from multiple sources, perform in-depth analysis, and develop sophisticated reports that will improve vital business activities such as planning, budgeting, and forecasting.

Compatible with Microsoft Office Excel 2003 or Excel 2002, the Excel Add-in for Analysis Services can improve data analysis, shorten reporting cycles, and enhance your company's ability to respond to customers.

The Excel Add-in for SQL Server Analysis Services not only reduces the time and resources required to train users, but it also eliminates the need for organizations to support specialized reporting systems and tools. The key business benefits of Excel Add-in for Analysis Services include:

Real-time visibility into business trends

Increased speed and quality of decision-making

Streamlined data analysis that shortens reporting cycles and saves resources

Increased reporting flexibility through rich, highly customized, and refreshable reports

Access to relevant information from multiple data sources

Key Features

With the Excel Add-in for SQL Server Analysis Services, individual users can manage the reporting cycle from beginning to end and eliminate the need to cut and paste data from multiple systems. Key access, analyze, and authoring features let end users:

Create and maintain live data connections to multiple online analytical processing (OLAP) cubes, ensure data consistency and integrity, and combine data from multiple sources into a single report.

Conduct detailed analysis using native Excel capabilities, extending the richness of analysis through "what if" and drill through capabilities.

Personalize and easily author refresh report layouts, further reducing training costs and reliance on IT by leveraging Excel's popularity and ease of use.

The Excel add-in provides a comprehensive set of tools that makes it easy to link to disparate data sources, manage queries, build sophisticated reports in multiple layouts, perform write back, and much more. The key components of the Excel Add-in for Analysis Services include:

Cube Metadata Manager: Retrieves and shares OLAP cube information.

Query Manager: Consolidates and executes queries and returns the results—also performs write back to cubes.

Report Builder User Interface: Builds reports using this "task pane" interface.

Report Metadata Manager: Maintains report layout and enables report interactions like isolate, eliminate, expand/collapse, and drillup/drilldown.

Report Manager: Creates and manages formulas and presents query results in Excel cells.

Comments
  • I attended this Nov'04 Webcast by Hitachi Consulting on the Excel Add-In - useful for lazy people (like me) who prefer to be walked thru the material over reading it!

    http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032259398&CountryCode=US
    >>
    TechNet Webcast: Creating Free-Form and Structured Reports with the Office Excel Add-in for SQL Server Analysis Services
    >>

    - Deepak

  • great info!

  • The latest version of Microsoft Office has some really useful add-ins. The catch? You gotta run down to Office Max and buy the latest version.

  • This Add-in have big problem with the Drill Through in cube with mutiple partition, because the Olap Server return multiple Recordset for each partition and this addin only read the first recordset.