• banner

    “The PCA crew is leagues above
    the competition” - Pepsico

  • "a prior vendor failed to develop
    the application, which (Practical) delivered." - Franklin Park

  • "the application (is) easier to use, with a
    much simpler, more intuitive use interface" - Royal Administration Services

  • "Sometimes it's the things you don't ask for
    that are important" - Royal Administration Services

  • "PCA turned our raw data and algorithms into easy to use
    and understand features and reports." - CareScout

  • "The quality and professionalism ... has been
    so outstanding that we want to send you a letter of thanks." - USALCO

  • "PCA people and applications stand out by
    far exceeding our expectations." - USALCO

  • "The resulting application greatly increas(ed)...
    our business effectiveness and efficiency." - The Advest Group

  • "PCA completes projects
    efficiently and effectively" - Pepsico

Excel and Analysis Services

Practical Computer Applications (PCA) uses Excel 2010 as a front-end tools for SQL Server Analysis Services (SSAS) for a number of purposes. We use it as a prototyping tool to help define requirements as well as an interface for users to view, analyze and report on enormous amounts of data. If you have or are considering a SSAS database and would like to get a better view of your data, work with the experts in data visualization by contacting PCA today. Or read more about our SSAS Services.

PCA uses Excel’s multiple methods to access, explore, drill down and visualize SSAS data including PivotTables, PivotCharts, Cube UI Controls, Slicers, Cube Functions, PowerPivot Add-ins, and Excel WebApps. In addition Excel has the native ability to connect to SSAS Cubes, to save named SSAS connections as separate objects (embedded or as a file) and read from it any dimensions, measures, and hierarchies. 

Technical Description: Two standard methods in Excel to present SSAS data are PivotTable and PivotCharts and both those methods will invoke special Cube UI control, which has four listboxes to present SSAS cubes dimensions, measures and hierarchies in four different roles: rows, columns, values and filters. Portions of a PivotTable can be converted to a set of Cube Functions while a part of the PivotTable can be optionally kept unconverted using Report Filters and reused by Cube Functions.

Another method is use of seven embedded Cube Functions, which are normal Excel functions, providing a SSAS value(s) to a cell or range of cells. Each of these functions have multiple parameters.

Slicers are very convenient way to filter data inside PivotTables and PivotCharts, they can be synchronized with each other and with all or some of PivotTables/Charts and enable interactive drill down and exploration of SSAS Data within Excel

One of the most powerful Excel tools is a PowerPivot Plug-in which is an excellent in-memory columnar database and data engine. PowerPivot creates the instances of SSAS cubes locally and also enables Excel users to clone the remote SSAS cube to local multidimensional cube and save it as an embedded part of Excel spreadsheet into XLSX file(s). PowerPivot introduces new expression language – DAX, which is also implemented as part of SSAS for SQL Server 2012. Version 2 of PowerPivot enables Excel users to create SSAS cubes locally and then publish/deploy them into SSAS of SQL Server 2012. PowerPivot can automatically refresh a dataset and reuse published workbook applications/spreadsheets as data sources.

Thanks to Office SDK, most Excel spreadsheets can be embedded into desktop or internet or smart client application. PCA has a lot of experience with this approach, including fast data transfer between such application(s) and SSAS Cubes.

Excel WebApp functionality enable users to deploy their spreadsheet applications into web (specifically Office Live and/or SkyDrive) together with PivotTables, PivotCharts, and slicers.

Read more about PCAs SSAS Services.