Office Solution AcceleratorExcel Add-In for Analysis Services
Nick Barclay
BI ConsultantTuesday, 22nd June 2004
So what is it?
► An Excel Add-In− Enables OLAP data to be viewed and manipulated
in Excel
− Does not use Excel Pivot Table APIs
− Provides functionality for two types of reports− Structured
− Free-Form
− Gives Excel access to server-side AS features− Actions, formatting, drillthrough, member properties,
writeback
So what is it? (contd.)
► An Office Solution Accelerator− Continues to increase Office application
productivity
− Makes data more accessible to the desktop
− Like all OSA’s – it’s free (under your current Office license)
Architecture
OLAPCube
• Analysis Services cube(s)• Source and Destination
(writeback)OLAPCube
• Formula management
• Query creation and exporting
Retrieves and shares cube information
Consolidates and executes queries
•Report layout•Report interactions
Microsoft Excel
Report Builder UI Reporting
Add-inMetadataManager
Reporting Add-in
MetadataManager
Reporting Add-in Manager
Reporting Add-in Manager
CubeMetadataManager
QueryManager
DEMO: General Solution Overview
Structured Reports
Share many similarities with Pivot Tables
► Strict organisation into rows, columns, pages & data
► Like PT’s one contiguous object
► One cube per report
DEMO: Structured Reports
Structured Reports - Advantages
► Drill Up/Down, Expand/Contract, Isolate/Eliminate
► Custom filter expressions− Top/Bottom count/sum/pct
− Multi-levelled logic <, >, <=, >=, <>, between
► Pivotability
► Report MetaData functions− STRUCTUREDREPORTGETXML(),
STRUCTUREDREPORTGETMDX()
Structured Reports - Limitations
► Can’t hold formatting well
► Single data source at a time
► Can’t insert rows / columns− No non-OLAP data inside report object
► Unable to sort within report
Free-Form
► Put data anywhere, from any cube
► Add extra Excel-based information
► Great for medium to high level data
► Use human readable (and writable) functions to retrieve cube data
DEMO: Free-Form Reports
Free-Form Reports - Advantages
► Multiple OLAP sources (with shared dimensions)− Nest shared dimensions from any cube
► Drop/move anything, anywhere
► 3 OLAP-based Excel functions− CUBECELLMEMBER()
− CUBECELLPROPERTY()
− CUBECELLVALUE()
► Expand / Collapse dimension members
Free-Form Reports - Limitations
► Can’t use report refresh to add new dimension members− Must re-query (collapse & expand)
− ‘Hard-coded’ cell formulas
► Page filters can be a little difficult at times
In summary
► Now able to access more server-side OLAP functionality than previously
► Very much a Power User tool
► Hopefully may become standard Excel feature…?
► Don’t forget about Pivot Tables!!
Another good step to further empower Office users and bring BI to the masses
References
► Office Solution Accelerator Home− www.microsoft.com/office/solutions/accelerators/
default.mspx► OLAP Tools
− Word OLAP Scribe www.microsoft.com/downloads − Excel OLAP Scribe www.tomchester.net
► OLAP Books− Analysis Services Step by Step – MS Press− Fast Track to MDX - Springer− MDX Solutions - Wiley
Questions