AS Introduction and OLAP Security Agenda

1
AS Introduction and OLAP Security - Agenda

• Analysis Services (SSAS) and Microsoft BI stack
• Analysis Services feature areas
• Whats new in SQL Server 2008 SSAS
• Analysis Services Security Architecture
• Custom Data Security static and dynamic
• Demos using Adventure Works OLAP cube

2
(No Transcript)
3
Analysis Services

• Unified Dimensional Model
• Integrating relational and OLAP views
• Pro-active caching
• Bringing the best of MOLAP to ROLAP
• Advanced Business Intelligence
• KPIs, MDX scripts, translations, currency
• Web services
• Native XML/A

4
What is Analysis Services
How do users
Spreadsheets

• Get to heterogeneous data?
• Combine it understand it?
• Easily navigate through it?
• Consume it as business metrics?
• Explore it interactively?
• Get consistent information?
• Gain unique insight into it?
• Gain competitive advantage?
• Align around common goals?
• Do it all without disturbing the operational
  systems?

Datamart
Datamart
BI Front Ends
SQL Server
DW
DW
Teradata
Oracle DB2
Ad-Hoc Reports
LOB
Production Reports
LOB
Dashboards
5
What is Analysis Services
Spreadsheets
Analysis Services
Datamart
Datamart
BI Front Ends
SQL Server
DW
DW
Teradata
UDM
Ad-Hoc Reports
Oracle DB2
XML/A or ODBO
LOB
Production Reports
LOB
Dashboards
6
Common Bet on the UDM
Excel Visio Excel Server Sharepoint Project
Server RS Report Designer RS Report
Builder Biztalk BSM 2005 MBS Performance Point VS
Burton Business Objects Cognos SPSS Proclarity P
anorama Outlooksoft GEAC MIS AG 100s other
vendors
Spreadsheets
Analysis Services
Datamart
SQL Server
Datamart
BI Front Ends
DW
DW
Teradata
UDM
Ad-Hoc Reports
XML/A or ODBO
LOB
Production Reports
Oracle DB2
LOB
Dashboards
7
Analysis Services Key Benefits

• One Version of the Truth
• Performance
• Advanced analytics

8
Analysis Services Key Benefits

• One Version of the Truth
• Performance
• Advanced analytics

• One way to get to all enterprise data sources
• Common, user friendly, business terminology
• Central repository of sanctioned enterprise
  business logic
• One set of key business metrics and goals
• Consistent, easy, user experience with the data
• One version of business information, available in
  every client tool or application

UDM
9
The UDM Key Features

• DSV - Access and combine heterogeneous data
  sources
• One Click Cube wizard auto build a cube from
  a relational schema
• Complex schemas - multiple fact tables, new
  dimension relationships, many-to-many dimensions
• Attribute based dimensions - full wealth of
  stored data, reporting
• Translations - native experience in any language
• Perspectives custom views of the business data
  model
• Advanced BI time intelligence, financial
  intelligence, semi-additive measures
• MDX Scripts new language, debugger
• KPIs central scorecard repository
• Actions new reporting action, multiple
  drill-through action
• XML/A access one version of truth in hundreds
  of BI tools

10
Many-to-Many Dimensions

• Many-to-Many dimensions
• Provide immense value in modeling real world
• Handle many interesting scenarios
• Can cause performance problems
• As bridge table grows
• As cascading of M2M relationships grows
• Many-to-Many Design Patterns
• Many-to-many dimensional modeling paper
• Optimization techniques
• Create aggregations to support M2M model
• Partition measure groups to support M2M model
• Matrix optimization technique

11
Analysis Services Key Benefits

• One Version of the Truth
• Performance
• Advanced analytics

12
Proactive Caching
MOLAP cache
New Version
RDBMS
UDM
MDX
Analysis Services
13
Proactive Caching
New Version
RDBMS
UDM
MDX
Analysis Services
14
Whats New for SSAS 2008 Performance?

• Sparse block (subspace) query improvements
• More performance information
• Real-time design warnings
• Write-back partition
• Improved wizards
• CREATE CACHE
• Scale out read only servers
• YTD and PeriodsToDate use optimised path
• Fast and scalable backups
• Dynamic sets
• Compression (SQLS)

15
New BIDS Attribute Relation Designer

• Tools Enhancement
• Attribute Relationships in a time dimension

16
Analysis Services 2008 DMVs

• Concept is similar to SQL Server Relational DMVs
• Dynamic table
• Query results are produced on request
• Power of relational access
• Using SQL Syntax
• Syntax Select from system.NameOfTheDiscover
• Access through regular relational techniques,
  objects, APIs
• SQL Linked server, ADO.net , sqlcmd, poweshell
• Lot of system information available
• Every Discover\Schema Rowset is accessible
  through DMV
• All of the schema rowsets (MDSCHEMA_KPIS,
  MDSCHEMA_CUBES ..)
• Over 20 system DMVs ( DISCOVER_PERFORMANCE_COUNTER
  S, DISCOVER_LOCKS, )
• You can even get to the dimension members
• select from system.MDSCHEMA_MEMBERS
• where DIMENSION_UNIQUE_NAME 'ACCOUNT'

17
What Are The Main DMVs
Connection1
Discover_Connections
Connection2
Discover_Sessions
Session
Session
Discover_Commands
Session Scope
Session Scope
Discover_Command_Objects
Database
Discover_Object_Activity
Dim1
Cube
Discover_Object_Memory_Usage
Dim1
MG1
Part1
Part2
18
Scale-Out Scalable Shared Databases
19
Scalable Backup

• Need
• Estimated 20 of cubes are greater then 50GB
• BI is mission critical to many business
• Needs fast and reliable backup I need a fast
  means of moving /shipping cubes from one server
  to another
• Problem
• Analysis Services 2005 backup scales well up to
  20GB cubes. Beyond 20GB seeing significant
  performance degradation on backup operation
• Note 20GB of AS cubes represents 80GB
  relational data
• Today's workaround File copy of data folder
• Solution
• Out of the box performance that is comparable to
  the speed of file copy

20
Backup Performance - AS 2008
21
Analysis Services Security architecture

• Secure By Default standard SQL Server policy
• Integrated Windows Security (authentication)
• Service runs with least privileges required
• Managed code in object models, stored procedures
• Custom data security for dimensions and cell data
• Based on Unified Dimensional Model (UDM)
• Administrative security at server and database
  levels
• Encryption of communications, passwords ..
• Security implemented at server not at client

22
Combining Roles

• Roles grant access to data
• If a user has permissions to see dimension
  members or data because of membership in a role,
  such members and data are visible independent of
  users membership in other roles
• Users are allowed to state roles they want to
  apply during a session
• Roles connection string property
• Select from roles they belong to

Role1
Role2
Combining Role1 and Role2
23
Defining Security

• Two ways to define Security
• Cell level
• Dimension member

24
Cell Security

• Use Cell Security when user can see all dimension
  members, but not all data for each member
• Sales Managers can see profit for some products,
  but only Gross Sales for others
• Cannot see profit if value less than 1000.00,
  otherwise visible
• Cell security is defined with MDX expressions
  that resolve to true or false
• Can see the cell if expression resolves true not
  viewable if expression resolves to false

25
Types of Cell Permissions

• Three different types of cell permissions
• Read access to target cells determined solely
  from the expression
• ReadContingent must also have access to cells
  used in the derivation of the target cell.
• Read/Write

Read cell permission resolves to true
ReadContingent cell permission resolves to true
26
Cell Security Other Considerations

• Administer Cell Security with free-form MDX
  requires some MDX knowledge
• Secured Cell Value connection string property can
  be used to change how secured cells appear

27
Dimension Security Terms

• AllowedSet
• List of members role permitted to see
• DeniedSet
• List of members role not permitted to see
• ApplyDenied
• defines scope of members denied
• DefaultMember
• defines the default attribute member for role

28
Visual Totals

• Visual Totals controls whether aggregated
  values are based on viewable members or all
  members
• If role can see USA, but not Canada or Mexico,
  what value should be shown for all customers?
• If only visible members are included in the
  total, VisualTotals is on.
• If all members are included in the total,
  VisualTotals is off.
• Visual Totals prevents users from inferring
  secured data

29
Dynamic Dimension Security

• Occasionally, security must be tailored to the
  individual user
• Data Driven
• Or completely arbitrary
• Examples
• Each Project Manager can see projects they have
  billed time or to which their employees have
  billed time
• Each Sales Representatives to see their products
  and only their products
• Each of my users can see an arbitrary subset of
  sales regions that I define.
• Keep administration of security roles to a
  minimum
• Let the data drive the rules
• Avoid creating a role per user

30
Dimension and Cell Security - Summary

• Use Cell Security when
• Users can not see some data for specific
  dimension members
• Use Dimension Security when
• Users can not see any data for some dimension
  members
• Use Dynamic Security when
• Security depends on the data itself

31
Demos of Dimension and Cell Security

• Regular dimension
• Parent-child dimension
• Visual Totals
• Read Cell Security
• Read Contingent Cell Security

32
Summary

• Analysis Services plays a central role in BI
  Stack
• SQL 2005 new architecture based on UDM
• SQL 2008 design, scale and manage better
• Security secure by default, with SQL platform
• User Data Security customize by dimension / cell
• Dynamic Security customize by fact table or sproc

33
Analysis Services Security Resources

• Microsoft Books Online (BOL), videos, webcasts
• Configuring Security (Analysis Services)
• Dimension Security in SQL Server Analysis
  Services
• Deploying, Managing and Securing Analysis
  Services
• Analysis Services Books
• Microsoft SQL Server 2008 AS Unleashed (Part 8)
• Expert Cube Development with Microsoft SQL Server
  2008 Analysis Services (to be released)
• Other blog entries and articles
• Default members, MDX Scripts, Security, KPIs and
  Perspectives (Mosha's blog on cube
  initialization)
• Protect UDM with Dimension Data Security (SQL Mag)