By: Shaili Modi

Analysis Services in SharePoint integrated mode is an Analysis Services server instance that supports in-memory storage of compressed data that is calculated in real time, in response to queries for PowerPivot data.

When installed through PowerPivot for SharePoint, an Analysis Services server instance loads and unloads PowerPivot data in the farm, and responds to queries for PowerPivot data from the local PowerPivot System Service. Within a SharePoint farm, an Analysis Services server instance accepts authorized requests only from the local PowerPivot System Service that is installed with it on the same computer.

Analysis Services loads PowerPivot data as raw, un-aggregated data that is restructured dynamically in response to user interaction with data visualization objects in a workbook. The data is highly compressed to allow for fast performance on the server. A load event is triggered by an on demand request for query processing or by a schedule that defines a data refresh operation. In both cases, the local PowerPivot System Service retrieves a read-only copy of the .xlsx file and issues a load request to Analysis Services. Analysis Services opens the workbook, extracts the PowerPivot data, and loads it into memory.

This is basically called as ‘SSAS Cube’. The Cube is nothing but combination of Dimension and Measures, where dimension is the master data like Employee, Geography etc. and measures is any number whose aggregation makes sense in reporting or anything which you can quantify.

Excel Pivot tables are a great way to view and analyze cube data in an ad-hoc nature and also satisfy simple reporting requirements.

Here is a quick overview to insert and format a report:
1. You can easily add a pivot table / pivot chart to your Excel sheet from the ribbon

2. You can then choose your SSAS cube data connection and specify the location of the pivot table

3. Then convert to cube formula for formatting the reports. We can convert the pivot table to formulas using the OLAP Tools ribbon in Excel. This is a great way to get started on a report with cube functions since it gets you well on your way.

When you create reports using pivot tables, you are locked in to the capabilities that pivot tables offer. However, you can use cube functions to have much more freedom. This is because each cell in excel can contain its own formula, and you can lay these cells out any way you’d like. The formulas can be self-contained, or reference other cells. Since cube functions are used just like any other Excel function, we get all the same flexibility with cube functions as we do with standard Excel functions.