Defining Row-Level Security in SSAS Tabular database

I work in HR IT where security is always the primary consideration when it comes to data. Recently I was watching a replay of the TechEd’12 presentation by Julie Strauss on “Developing and Managing a BI Semantic Model in Microsoft SQL Server 2012 Analysis Services”. I want to highlight the row level security bit that Julie demonstrated for my own future reference.

Create a table with the user-id and the dimensional values you want to secure. In the below example, she is securing SalesTerritoryCountry dimension values. Basically, its just a separate table used for looking up.

Next, create the below lookupvalue formula and assign it to the table as a DAX filter.

The above formula translates to: Go to the Security table>SalesTerritoryCountry column, lookup the username with the username used to logon with and return SalesTerritoryCountry. That is how row-level security works. You only show the rows that the end users are authorized to see.

In addition, you can also secure the Security table itself from being exposed to the end user. This is simply done by hiding it from client tools.

In order to test this, you can use the ‘Analyze in Excel’ feature in Visual Studio (SQL Server Data Tools):

Use ‘Other Windows User’ to test the security by emulating the other user (s)

Obviously, there is more to security than just row-level security. If you have successfully implemented  other strategies for securing your data in SSAS, leave me a note below.

 

What is new in SQL Server 2012 Analysis Services?

Highlights:

1. The new release is based on the vision to reach a broader user base
2. SSAS brings together the existing  multi-dimensional model with the new tabular model pioneered by PowerPivot under a single unified platform – the BI Semantic Model
3. Data Mining Component remains the same
4. The BI Semantic Model promises one model for all end user experiences
   1. End Users/Personal BI – PowerPivot for Excel
   2. Team BI – PowerPivot for SharePoint
   3. Coporate BI – Analysis Services
5. What is The BI Semantic Model? It is just another term for a Data Model hosted within Analysis Services
6. There are three approaches in developing a BI Semantic Model
   1. PowerPivot Workbook
      1. Design Type: Tabular
      2. Tool: Excel 2010
      3. Biz Logic: DAX
      4. Data Access-Cache Passthrough: In Memory
      5. Deployment: SharePoint Library/Analysis Services/PowerPivot
   2. Tabular Project
      1. Think of this as PowerPivot for the IT Pro. End user hands off the PowerPivot workbook to IT department.
      2. Design Type: Tabular
      3. Tool: SQL Server Data Tool
      4. Biz Logic: DAX
      5. Data Access-Cache Passthrough: In Memory Direct Query
      6. Deployment: Analysis Services Tabular Mode
   3. Multidimensional Project
      1. Design Type: Multidimensional
      2. Deployment: SQL Server Data Tools
      3. Biz Logic: MDX
      4. Data Access-Cache Passthrough: MOLAP/ROLAP
      5. Deployment: Analysis Services Multidimenional
   4. Front End Clients
      1. PowerView (uses DAX to query the BI Semantic Model)
      2. 3rd party apps
      3. Reporting Services
      4. Excel
      5. PowerPivot
      6. SharePoint Insights (PerformancePoint Services)
   5. Note that Tabular Models understand both DAX and MDX
   6. DAX is new SQL Server 2012 and only works against tabular models
   7. The advantage of a tabular model is that we can now work with:
      1. Files
      2. OData Feeds
      3. Cloud Services
      4. Relational Databases etc
   8. BI Sematic Model supports Fine-grained security row and cell level
   9. The following templates are available through SQL Server Data tools:
      1. Analysis Services Multidimensional and Data Mining Project
      2. Import from Server (Multidimensional and Data Mining)
      3. Analysis Services Tabular Project
      4. Import from PowerPivot
      5. Import from Server (Tabular)
   10. Multidimensional Model New Features
       1. 300+ improvements
       2. New Developer tool: SQL Server Data tools (Visual Studio 2010)
       3. Removal of 4G string limit for attributes
       4. New events for monitoring locks and attributes
       5. Support for XEvents for low overhead eventing support
       6. PowerShell support
   11. Installation and Configuration
       1. All Analysis Services instances are installed with SQL Server Installer
          1. Server modes: Multidimensional and Tabular – both can be installed on the same server
       2. The PowerPivot Configuration tool is used to configure/repair a PowerPivot instance
   12. New PowerShell cmdlets for AMO for command-line connectivity, navigation and discovery of SSAS databases
   13. Existing apps – every  UDM  becomes (can be upgraded to) a BI Semantic Model

The Below Presentation is part of SQL Server 2012 Training Kit: SQL11UPD06-DECK-01

Next Generation Tools for Microsoft SQL Server Analysis Services in Denali

Great presentation by Julie Strauss at Microsoft Tech-ed’11. Download slides from here. I have captured the key learning here.

KPI designer inside PowerPivot:

KPI designer lets you create KPIs in a PowerPivot Model and lets you reuse it.

Document KPIs: these will appear as annotations in the PowerPivot field list.

View KPIs in Excel:

You can restore this PowerPivot Excel workbook directly in Analysis Services. This will import everything we built within PowerPivot: model, tables, data, KPIs etc

PowerPivot Model is imported in Analysis Services as a Database.

Business Intelligence Development Studio (BIDS) now available Visual Studio 2010:

When you create a new Semantic Model in Visual Studio, the system creates a new workspace database in Analysis Services:

You have the same table import wizard in BIDS that you have in PowerPivot:

pick the table to import:

After the table is imported in BIDS:

Create multiple measure by one click:

Create Models in BIDS:

Easily Create Hierarchies:

Product Hierarchy in PowerPivot:

Create perspectives by one click:

Create copies of perspectives easily and check the measures and dimensions you don’t need:

Work with Perspectives in BIDS:

View the Model in PowerPivot:

Security:

Apply row level security using the Role Manager. Use DAX filters to return true or false.

Deploy Models: Models can be deployed to Analysis Services.

New Analysis Services database is created:

Analysis Services and PowerPivot New Features in SQL Server Denali

Great introduction to the new features/concepts of Analysis Services/PowerPivot in SQL Server Denali by T K Anand and Ashvini Sharma. I have captured the key learning for my future reference.

BI Semantic Model (BISM)

Earlier releases of analysis services (2005, 2008) supported dimensional modeling (UDM). Going forward, analysis services can work on tabular relational models as well – it’s a hybrid technology. Essentially you can create one model for all end user experiences. This model can be used by PowerPivot for Excel, PowerPivot for SharePoint or Analysis Services.

How do you connect to the BISM?

This is the same as it is in Excel/Analysis Services 2005/2008.

Within SharePoint, you can have files that are pointers to various data sources – connection information is stored for reuse.You can create crescent reports directly from it. Alternatively users can use this file to work the data in Excel or PerformancePoint.

Create, View and Analyze in Crescent: Crescent reports can be launched directly from PowerPivot Gallery.

So, what happens to existing Analysis Services databases with the introduction of BISM? Every UDM becomes a BISM in Denali.BISM encompasses all the features that the UDM offers today.

BISM Architecture:

Data can be sourced pretty much with what is our there: Oracle, SQL, Teradata, LOB – Line of Business Applications, Excel, Textfiles, OData Feeds – Netflix or Facebook, Cloud Services such as SQL Azure.

Modeling can now be done using Visual Studio 2010:

Data Centric View in BISM:

Model Extensions: You can create calculated columns or hide columns from end users all within the model.

Model View in BISM: This is your data model. These tables can be exposed directly to the end users to run direct ad-hoc queries.

Note that the good old DSV still exists:

Models are tabular in nature, but when opened in Excel, we will see a Multi-dimensional view and sends MDX queries to the Model!

The same Model can be edited within PowerPivot:

PowerPivot gives end users the chance to build the application whereas Analysis Services is targeted for the IT department. It is the same model that powers the two.

Fine Grained Security control is supported within the Model.

Partitions can now be defined directly within the Model:

New Trace events added for Vertipaq:

Excel sends MDX queries, PowerPivot and Crescent sends DAX queries to the Model.

SQL Server Management Studio shows cube being edited:

SQL Server Management Studio shows Tables from the Analysis Server database:

PowerShell is extended within Denali to Analysis Services. PowerShell is a object-oriented shell that extends to .NET objects. As you browse the objects you are able to invoke commands on the objects.

PowerShell commands for Analysis Services:

Some other key features:

Comparison Table for the Data Model within BISM:

Tabular	Multidimensional
Easier to build, faster time to solution, end users can build this	sophisticated, higher learning curve and suited for IT department
Advanced concepts not available ex: parent-child, many-to-many join	Advanced concepts baked into the model
Easy to model the solution on a raw database	Ideal for OLAP apps

Analysis Services Architecture: