1 / 33

AS Introduction and OLAP Security - Agenda

AS Introduction and OLAP Security - Agenda. Analysis Services (SSAS) and Microsoft BI stack Analysis Services feature areas What’s new in SQL Server 2008 SSAS Analysis Services Security Architecture Custom Data Security – static and dynamic Demos using Adventure Works OLAP cube.

sibley
Download Presentation

AS Introduction and OLAP Security - Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. AS Introduction and OLAP Security - Agenda • Analysis Services (SSAS) and Microsoft BI stack • Analysis Services feature areas • What’s new in SQL Server 2008 SSAS • Analysis Services Security Architecture • Custom Data Security – static and dynamic • Demos using Adventure Works OLAP cube

  2. 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

  3. What is Analysis Services • 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? How do users: Spreadsheets Datamart Datamart BI Front Ends SQL Server DW DW Teradata Oracle DB2 Ad-Hoc Reports LOB Production Reports LOB Dashboards

  4. Cache 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

  5. Cache 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 Panorama Outlooksoft GEAC MIS AG 100’s 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

  6. Analysis Services – Key Benefits • One Version of the Truth • Performance • Advanced analytics

  7. 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

  8. 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

  9. 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”

  10. Enable ad-hoc, dynamic, exploratory experience with the data • Isolate back-end sources from exhaustive users queries • Automatically synchronize =Proactive Caching Analysis Services – Key Benefits • One Version of the Truth • Performance • Advanced analytics

  11. Update Data SQL Events Proactive Caching MOLAP cache New Version RDBMS UDM MDX Analysis Services

  12. MOLAP cache Data SQL Proactive Caching New Version RDBMS UDM MDX Analysis Services

  13. What’s 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)

  14. New BIDS Attribute Relation Designer • Tools Enhancement • Attribute Relationships in a time dimension

  15. Analysis Services 2008 DMVs • Concept is similar to SQL Server Relational DMV’s • 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_COUNTERS, DISCOVER_LOCKS, … ) • You can even get to the dimension members select * from $system.MDSCHEMA_MEMBERS where [DIMENSION_UNIQUE_NAME] = '[ACCOUNT]'

  16. What Are The Main DMVs Connection1 Connection2 Discover_Connections 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

  17. Scale-Out Scalable Shared Databases Virtual IP . . . Analysis Server Analysis Server Analysis Server SAN storage

  18. 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

  19. Backup Performance - AS 2008

  20. 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

  21. 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 user’s 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

  22. Cell-level security Employees Total salary Sq Ft East 50 N/A 16,500 Dimension Member security Central 75 $7,600K 20,625 Employees Sq Ft West 35 N/A 10,500 East 50 16,500 Central 75 20,625 West 35 10,500 Defining Security • Two ways to define Security: • Cell level • Dimension member

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. Demos of Dimension and Cell Security • Regular dimension • Parent-child dimension • Visual Totals • Read Cell Security • Read Contingent Cell Security

  31. 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

  32. Analysis Services SecurityResources • 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)

More Related