1 / 16

Office Solution Accelerator Excel Add-In for Analysis Services

Office Solution Accelerator Excel Add-In for Analysis Services. Nick Barclay BI Consultant Tuesday, 22 nd 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

cybil
Download Presentation

Office Solution Accelerator Excel Add-In for Analysis Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related