For San Diego’s Microsoft Business Intelligence Security Planners,
A Mid-Level Overview of the Analysis Services OLAP Security Model:

Context:  As we continue to explore improved business intelligence data architecture, with our overall mantra of "...fewer, faster, more sophisticated cubes providing more simultaneous users with answers to more questions..." (...gasp!), we need to consider role-based security.  It's all good news, and here is the story...

In general:  Role-based security explicitly grants or denies Windows Users or AD groups access to AS Objects.  AS Role members can be either AD Groups or Windows Users.   AS Security granularity can be just as granular and just as multi-dimensional as the AS cube itself, such that a specific restriction does not prevent full drill-down access to other needed information slices.  For a BI front-end integrated with SharePoint, SharePoint Security might simply grant/deny a Windows AD group the visible rendering of a dashboard page, tab within a dashboard page, or a specific report.  This is true in San Diego, as elsewhere (shameless SEO plug).

Analysis Services' Server Role:  AS has just one Server Role, called ‘Administrator’.  Members of this role can see/do virtually everything with that AS server instance.

Analysis Services Database Roles:  Created by aforementioned AS Server Role, ‘Administrator’, they can receive admin (eg. ‘Create’,‘Process’) or user permissions such as read cube, read dimension, read cell (most granular), ‘read/write’ (rarely needed) or read data source (needed if end-user needs drill-through-action from cube to a more granular DW RDBMS).  Frequently, multiple AS Database User Roles are created to assigned fine-grained security to AD Groups / Users.  Lastly, if multiple cubes are deployed into production within a single AS Database (the container of the cube and related metada/MDX), the AS Server Role (Admin) has option to set AS Database Roles at either DB or (if security differs among these cubes) at cube level.

Note on Role Additivity:   For denies vs. grants on same level/granularity of a dimension hierarchy, ‘grant’ wins over ‘deny’.  However, when conflicting grant vs. deny exist on different granularities, finer grained wins whether an explicit ‘deny’ or ‘grant’.  So, if required, Windows users with varied membership in a collection of AD Groups (example: ‘All SVPs’, ‘All Directors’ ‘Sales Dept’, ‘Operations Dept’), which are then assigned as members to various AS DB Roles, can be used in conjunction with AS’s aforementioned ‘additive’ grant vs. deny capability on different levels of dimensional granularity.  A mid-level explanation of this follows…

Analysis Services OLAP Security Levels:
Highest Level – Dimension Security:  An AS User DB Role can be explicitly granted/denied access to one or more dimensions in their entirety.  A user denied access to the entire ‘Staff’ dimension, for example, would only be able to see that dimensions default value, which is normally ‘All’.  This means that he will see all detailed cube data, except that he could not slice (filter), drill-down (group by or sort) by staff (employees, teams, managers, directors, etc).

Mid-Level - Dimension Data Security:  As AS User DB Role may be granted access to the Staff Dimension per se, but denied access to peer-level details outside of his/her team, seeing only aggregates either laterally (other teams) and higher up (management). 
* (Perhaps obviously,) an AS User DB Role may have this level of security configured independently across multiple dimensions.
* This level of security does NOT deny any specific facts/measures, only the dimensional criteria by which they are sliced.

Examples: One AS User DB Role has been…
Granted Unrestricted Permissions:  Therefore, San Diego users see…
Sales Territory:                    North America       Europe          Asia                   Total
Reseller Sales Revenue:                $75,000     $15,000    $25,000             $115,000
Reseller Order Count:                           350              50           100                     500

__________________________________

Denied Dimension Permissions on ‘Sales Territory’ Dimension:  Therefore, San Diego users see…
Sales Territory:                North America         Europe          Asia                      Total
Reseller Sales Revenue:                      -                    -                -                 $115,000
Reseller Order Count:                          -                    -                -                         500

__________________________________

Denied Dimension Data Permissions on ‘Europe’ and ‘Asia’:  Therefore, San Diego users see…
Sales Territory:                   North America     Europe          Asia                      Total
Reseller Sales Revenue:              $75,000                -               -                 $115,000
Reseller Order Count:                         350                -               -                         500

__________________________________

Fine Grained Level - Cell Security:  An AS User DB Role can be granted (or denied) permission to view one cube measure (one fact table column), but not another.  For example, in a ‘Student Retention’ cube, one AS User DB Role may be granted read permission on the ‘StudentInAttendance’ measure, but neither the ‘StudentBillable’ or ‘StudentAtRisk’ measures.
* If no ‘Dimension Data Security’ restrictions exist for this AS User DB Role, then the above scenario applies to all dimensions as they slice the measures.

Denied Cell Permissions on ‘Reseller Sales Revenue’ measure:  Therefore, San Diego users see…
Sales Territory:                        North America      Europe      Asia          Total
Reseller Sales Revenue:                               -                -            -                 - 
Reseller Order Count:                              350              50       100             500

__________________________________

Finest Grained level – Combined Cell Security and Dimension Data Security:  Combining these two approaches, an AS User DB Role can be granted or denied access to any combination of measures, dimensions (dimension tables), dimension attributes (dim table columns), and dimension members (rows/records in any dim table columns).

Combined Cell Security and Dimension Data Security:  Therefore, San Diego users see (extreme example)…
Sales Territory:                    North America     Europe      Asia                   Total
Reseller Sales Revenue:           $75,000                   -            -              $115,000
Reseller Order Count:                          -                 50            -                      500
__________________________________

As a departing thought... keep in mind that none of the above permission restrictions prevent an AS User DB Role from performing fine-grain-detailed drill-down on other needed dimensions (eg. Product, Customer, Organization, Geography, Date, etc.) or other unrestricted measures (facts). 
When other such dimensions are added as rows or columns (cross-joined) in the above examples, the resulting values will provide all details, except wherein the above permissions were explicitly controlled.  The takeaway here may be that BI teams can build and support fewer, more sophisticated OLAP cubes with a highly granular security architecture, rather than re-building otherwise-identical cubes simply in order to differentiate fine-grained security.  Some would even call it slick!

Daniel Upton
Business Intelligence in San Diego