Just another Blog

My Technical, Professional and at times Personal Blog

Showing posts with label Analysis Services. Show all posts

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.

image

image

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

image

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.

image

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.

image

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

image

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.

image

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

image

View KPIs in Excel:

image

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

image

image

PowerPivot Model is imported in Analysis Services as a Database.

image

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

image

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

image

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

image

pick the table to import:

image

After the table is imported in BIDS:

image

Create multiple measure by one click:

image

Create Models in BIDS:

image

Easily Create Hierarchies:

image

Product Hierarchy in PowerPivot:

image

Create perspectives by one click:

image

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

image

Work with Perspectives in BIDS:

image

View the Model in PowerPivot:

image

Security:

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

image

image

Deploy Models: Models can be deployed to Analysis Services.

image

New Analysis Services database is created:

image

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.

image

How do you connect to the BISM?

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

image

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.

image

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

image

image

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.

image

BISM Architecture:

image

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:

image

image

Data Centric View in BISM:

image

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

image

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.

image

Note that the good old DSV still exists:

image

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

image

The same Model can be edited within PowerPivot:

image

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.

image

Partitions can now be defined directly within the Model:

image

New Trace events added for Vertipaq:

image

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

SQL Server Management Studio shows cube being edited:

image

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

image

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.

image

PowerShell commands for Analysis Services:

image

Some other key features:

image

image

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:

image

Google+ Followers