Concepts and their names
Microsoft have announced that the name Vertipaq is changing to xVelocity In-Memory Analytics Engine at the same time as SQL Server 2012 launched, on 7 March 2012. Vertipaq was the name of the in-memory tabular compression and analysis engine of PowerPivot, introduced a few years ago, with SQL Server 2008 R2. Let us help you get some clarity with regards to the name changes, and please consider also reading this blog post by the SSAS team.
The new name, xVelocity, will refer to a number of other performance-related technologies of SQL Server, becoming a family of related features, in the future. For now, however, xVelocity is already a small but formidable family, as it includes both xVelocity for Data Warehousing, that is Columnstore Indexes technology (explained more here), and xVelocity for Business Intelligence, which is the name of the engine of tabular modelling in Analysis Services (SSAS) and the new PowerPivot for SQL Server 2012.
In all of those cases, you can see how a common innovation, columnar compression, scanning, and aggregation, is shared between the three implementations. At the same time, however, there are significant differences between them. For example, xVelocity in PowerPivot requires the entire model to reside in the available memory (if we disregards built-in OS paging). xVelocity for SSAS Tabular Mode introduces explicit memory paging, meaning that larger tables can be handled by it, even if they do not fit in memory at once. xVelocity seems like a cool name for a common concept across those three technology products (relational engine indexes, SSAS, and PowerPivot), as it groups them logically while respecting their architectural and implementation differences. This is important, as sometimes the same, or a similar result, could be obtained by using them interchangably.
For example, you may want to start letting SSAS Tabular do all the processing using its own in-memory engine, but at some stage you might want to offload some processing to a back-end warehouse with Columnstore Indexes (xVelocity for DW) enabled. In doing so, you would switch to DirectQuery in your tabular solution settings, or you could let the user decide, or even use a hybrid of those approaches. Indeed, this seems to be one of the key uses for the DirectQuery technique.
To make it even simpler, you can think of xVelocity family of technologies as a conceptual tree:
- xVelocity for Data Warehousing, that is column store index in the relational SQL Server database engine,
- xVelocity for Business Intelligence, available as:
- xVelocity In-memory Analytics Engine in SSAS 2012 Tabular Mode (used for enterprise-grade, scalable BI), and,
- xVelocity In-memory Analytics Engine (previously called Vertipaq) in PowerPivot (used for self-service and team-oriented BI scenarios), available in PowerPivot for Excel and SharePoint Server.
Incidentally, the name BI Semantic Model is also conceptual in nature, as there is no actual product so named—though the closest to a thing that BISM gets to is the new BISM Connection File for SharePoint. The real-world product happens to be SQL Server Analysis Services 2012 and it runs your BISMs, if you like, or permits you to create a BISM from a combination of the choices it offers with regards to the modelling approach (tabular or multidimensional), query logic (DAX or MDX), and data access technologies (xVelocity, MOLAP, ROLAP, DirectQuery). So, is a PowerPivot a BISM? No, it is a product, like SSAS, that can host a tabular model, which is part of what makes a BISM.
So a better way to say things is that PowerPivot, like SSAS, can host a BISM, though in the case of PowerPivot, it is one that restricts the logic and data access options to just DAX and xVelocity, while with SSAS you have those options open at your disposal. By the way, we explain BISM in detail in this online training course.
Happy St Patrick’s Day everyone!
Comments
hxy0135 · 19 March 2012
Hi,
We have always wanted to offer BI self-service capability via PowerPivot to our sales force. But only have to give it up just because we cannot find a way to secure the data.
Our business rule requires that a sales person is allowed to only see their own data. In Excel PowerPivot workbook, when a user retrieve data from SQL server or OLAP cube, is there a way to pass a current user’s credentials to the server, so the server can filter data based on the user credential and send back the data that is only allowed to be seen by the user?
Thank you,
Hua
Rafal Lukawiecki · 19 March 2012
Hi Hua, what you are trying to accomplish, that is to provide role-based security in a tabular model, cannot be done too easily and securely just by using PowerPivot for Excel, but it is very easy to do, and secure, using the new concept of Roles in SSAS 2012 Tabular Mode.
In short, you would need to create a new tabular project using your existing PowerPivot workbook as the source, add a role, such as US Managers, set it its rights to, perhaps, Read, and then add a DAX expression that evaluates to True on those rows of the tables that you wish to protect by making them available to US Managers only, for instance: =[Country]=”United States”. Finally, you add an Active Directory group that includes all US Managers to the just created role. I show this, step-by-step, in an upcoming online video, which is part of our new, four-part, online training, that will be uploaded in about two weeks—if you subscribe to our newsletter, you will get an email as soon as that video has been published.
hxy0135 · 19 March 2012
Thank you for your quick reply and the information.
When we use PowerPivot workbook as a datasource, the security takes effect only when user accesses the workbook in SharePoint, not when they use Excel. Am I right?
Thank you again,
Hua
Rafal Lukawiecki · 20 March 2012
There are two points in time when a security check, authorization, is made in your scenario, if I understood it properly. First of all, SharePoint checks if the user is in a SharePoint role that allows the workbook to be displayed. Secondly, it is done whenever the workbook is refreshed from any data sources from which it was built. So, if you use PowerPivot as a data source for another PowerPivot workbook, those two points will require a successful authorization. However, if the user never refreshes the workbook, nor is it refreshed on its own schedule, the only time authorization is checked is on the SharePoint file open. SQL Server 2012 Analysis Services in Tabular Mode permits a much more fine-grained, row-level, rule-based (DAX), role-oriented security, more suitable for your scenario, I think.
hxy0135 · 20 March 2012
Thank you very much, Rafal.