NewsArticles Minimize
28

The new SSAS engine (only currently available to PowerPivot, either as part of Excel or Sharepoint 2010, and shipped along with SQL Server 2008 R2) has a completely different architecture than the traditional disk based storage engine.

As a columnar database,  its driving principle is that the variation of data across 1 column is usually low (for example, if a column stores “gender” it only needs to store two values for the entire column). This in contrast to the traditional storage in which values are stored for each row, no matter how much data variance there is (as you can see - when storing columns with high degree of variance, the columnar approach may lose its advantage).   

Due to the reduced need to store discrete values (along with other data pattern algorithms), high levels of compression are possible which allows the Excel version of Vertipaq to run in-memory, all based on RAM storage. The huge advantage here over traditional disk based SSAS storage is data retrieval and calculations happen at a much faster rate, as disk I/O processing is omitted. 

Given that current personal computers have much higher RAM specifications, it is finally possible to hold in-memory an entire database table representing millions of rows, thanks to the Vertipaq's architecture. 

Another characteristic of  PowerPivot's Vertipaq engine is that it is an in-process memory storage.  That means it is bound to Excel, as they are both the same file.  The advantages are it allows portability, as the file IS the database; however, the disadvantage is that any process failure compromises the whole system.


Vertipaq, in its PowerPivot for Sharepoint version, requires a dedicated SQL Server Analysis Server instance which only responds to the PowerPivot system, and as its Excel counterpart, does not store the data in pre-aggregated mode but instead computes aggregations on the fly. Other server functions like data unloading (removing data from cache at specified intervals, if users do not make requests) are also featured in this type of configuration.


Some of the features that were currently left out for version 1.0 of PowerPivot (like many-to-many relationships and dimension hierarchies) are available to the underlying engine, as it really is in fact, a repackaged SSAS instance.  Those features will eventually be exposed, in order to make the Vertipaq/PowerPivot system even more robust.


At this point, it is not possible to access PowerPivot OLAP cubes via a programmatic interface (though you can access OLAP cube members via Excel cube functions).  Let’s hope the next version will include a way to interact with it (and with Vertipaq) via managed code.

Posted in: Blog

Post Comment

Only registered users may post comments.

LatestArticles Minimize
Computed column 'mydate' in table 'test2' Cannot be Persisted because the Column is Non-deterministic. by Wayne Snyder
How to create a calculated column which converts a string to a date and persist it. The problem is that the calculated column must be deterministic.

SSAS Process Full from SSIS with Parameter for DatabaseName by Wayne Snyder
We have recently had a need to process tabular models programmatically, but we needed it to be easily done by an end user – self service is the key – right? To solve this problem we have a...

Using Linked Tables in PowerPivot by Wayne Snyder
PowerPivot allows you to create a table in an Excel worksheet, and import it into your PowerPivot model. You can import data from Excel spreadsheets, but this differs in that the Excel data we wish to...

Errors Creating and Maintaining Relationships with PowerPivot Tables by Wayne Snyder
In this article you will learn how to create relationships in PowerPivot. More Importantly, you will learn about many-to-many relationships. Since PowerPivot only supports 1-to-many relationships, you...

Importing Flat Files into PowerPivot– Overcoming Problems by Wayne Snyder
You may run into issues when trying to import flat files into PowerPivot. This article documents some of the issues, and includes workarounds when available. As I bump into new problems, I will try to...

Importing Flat Files Into PowerPivot by Wayne Snyder
Bringing flat files into your Powerpivot model is easy. We will go through the process step by step, so that you can begin mashing up data to get your job done more quickly, more effectively, and with...

Changing Granularity of Leaf Level Calculations in SSAS Tabular by Jason Thomas

Finding Nearest Stores using SSRS Map Reports by Jason Thomas

Heat Maps for SSRS using Map Control by Jason Thomas

Parameters for Analysis Services Reporting: Introduction, Pt. 3 by William Pearson
BI Architect Bill Pearson continues an extended examination of parameterization within Analysis Services reports. In this, Part 3 of the article, we continue to get hands-on practice cr...


Advertisement Minimize

Advertisement Minimize

Copyright 2004-2013 MSBICentral.com Terms Of Use Privacy Statement