Office Solution Accelerator Excel Add-In for Analysis Services

1. Office Solution AcceleratorExcel Add-In for Analysis Services Nick Barclay BI Consultant Tuesday, 22nd June 2004

2. 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

3. 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)

4. Architecture Microsoft Excel Report Builder UI Reporting Add-in Metadata Manager Reporting Add-in Manager • Report layout • Report interactions • Formula management • Query creation and exporting Query Manager Retrieves and shares cube information Consolidates and executes queries Cube Metadata Manager • Analysis Services cube(s) • Source and Destination (writeback) OLAP Cube OLAP Cube

5. DEMO: General Solution Overview

6. 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

7. DEMO: Structured Reports

8. 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()

9. 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

10. 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

11. DEMO: Free-Form Reports

12. 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

13. 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

14. 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

15. 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 • nick_barclay@pa.com.au

16. Questions