SSAS Security Model with Maintenance Plan Secure Enable

SSAS Security Model with Maintenance Plan Secure Enable Maintain Sustain

Problems, Solutions and Data Oh MY! • End users need data • Presentation layer data needs to be secured/segregated • Valuable Technology Talent has limited time and excessive work load • MS has created coupled technologies that enable the end-to-end solution

Get ‘er DONE! • Keep it Simple Stupid (yet Comprehensive). – Identify exactly who needs to see what • Make use of the Existing Security Processes. – Not your job (or problem) • Stand on the Shoulders of Giants – AD Amins are your best friend – Existing Business Process can enable the big win • Listen, Listen – Build the right solution – Listen to the reporting needs • ENABLE the Business with Technology – DO NOT create extra work that will not further the business

Who needs what? • Use existing groups in Active Directory to define roles in the Cube – AD Admins can identify and enable this process • Existing groups • Naming conventions – Identify the partitions in reporting needs • VP, Directors, Managers, Business Units, Locations – Identify the patterns in the underlying data • Country, State, Division, Territory

Dissect the data • Identify a pattern in the underlying data as it relates to the business and security. – The data will inherently be organized into patterns with relationships – The business reporting needs will be driven by the very essence of the data: • Who did what, where, when, how – Evaluate the underlying data for design patterns for security roles – Model the security design on business reporting model

Create the Correct Solution • Enable them that pays the bills – Setup the highest pay grades personally • Interview the business to understand the reporting needs • Listen to the big brains in IT • Listen to the big brains in AD • Listen to the big brains in Business • “Recommend” an implementation….

Who Dat Gonna Do Da Work? • Integrated Security and Service Accounts enable the comprehensive framework – Drive report development to leverage the Share. Point Data Sources • SSAS roles determine the type of data available depending on the role passed through to the Cube • SSIS is the tool kit to create and maintain the cube roles

Data Sources are the root of the solution! – Service Accounts Solution • High level data access • Dashboards • Enterprise level totals – Integrated Security Solution • Pass through the credentials of the current user • Selective reports developed to harness the cube roles security

Data Sources are your friend

Identify the “Role” Security will play in the cube • What needs to be secured • Where can security design patterns be implemented • 90 -10 – Make that 90% count – Make that 10% fit

Roles in the cube restrict/allow access to data

Create Cube Roles • Define cube roles as they will relate to the business

The What

The Who

reuse Re. Use re. Use RE-USE • Create an initial Role to use as a template • SSMS enables duplication of a role • Script the duplication – Create the XMLA script to a new query window • XMLA script can be broken apart and implemented in a SSIS script task • Using SSIS enables C#, SQL, XMLA and connections to EVERYTHING

XMLA Scripts to create Role

XMLA Role Script

Use the Data!

SQL fills a Data. Set • Use the Data. Set filled by the SQL task • A For. Each Loop breaks out each item in the dataset • The script task takes advantage of the variables set by the For. Each Loop • Parameters and variables and Strings – Create a custom XMLA script to execute for each country role

SSIS enables the Framework

SSIS Script Task is the Key

SSIS implementation • C# Script – Validate AD Group – Parameterize valuable data – Accommodate complex/elegant data – Build custom XMLA Script

SSIS XMLA task “do WORK”

Maintenance • Create Tool Set to maintain security in cube quickly and effciently. • The same framework can be used to quickly create and delete all the roles in the cube(s) • Nightly and/or weekly runs of this frameset can help maintain a comprehensive security model