Microsoft Business Intelligence Tools for Excel Analysts

Chapter 13: Browsing Analysis Services OLAP Cubes with Excel

In This Chapter

• Understanding Analysis Services OLAP cubes
• Connecting to an OLAP data source
• Creating offline cubes
• Using cube functions
• Adding calculations to your OLAP PivotTables

Since the release of SQL Server OLAP 7.0, Microsoft Analysis Services has proven to be an analytics workhorse that has taken and maintained the lead position in the market for OLAP (online analytical processing) databases. Many of the leading analytics and data visualization tools (including Excel) provide native connectivity with Analysis Services, providing interaction with a very rich and responsive data store via end-user data tools. Most importantly, there is a significant number of Analysis Services installations across businesses that are handling reporting, analytics, budgeting, and planning on a day-to-day basis.

But with all its capabilities, Analysis Services is complex and can present you with a significant learning curve. Though it serves as a back-end data platform for dashboards and PivotTables, Analysis Services is not well understood by many end users, remaining a platform and set of tools best understood by database developers. Even though these tools are mostly used by database developers, you can benefit from learning how they work.

This chapter provides a solid conceptual understanding of what Analysis Services is capable of. This knowledge not only enhances your abilities when it comes to the ongoing, daily analytics ...