Welcome to Instant SQL Server Analysis Services 2012 Cube Security.

The launch of Microsoft SQL Server 2012 brings a new edition called Business Intelligence (BI). This edition joins the SQL Server family, which can be classified as a milestone release from Microsoft Corporation.

In the current real-world scenario, the underlying hardware such as disk speed, memory, and processing power have no limits to completely guarantee acceptable levels of performance from a database or SQL Server instance. Similarly, the features from the 2012 version are also high-end to keep up the performance, scalability, and availability of the data platform.

Since the inception of SQL Server 2008, security has become the paramount feature, which is implemented by default. This kind of authorization is intended to support the organizations that nonexistence of skilled DBAs, to securely deploy the SQL Server features. The compliance and regulations within the financial industry have elevated Microsoft's responsibility to provide a baseline functionality to deliver the true scale of security and manageability features.

Then, coming to the data access layer, the highlight within the security method is Transparent Data Encryption (TDE) to protect data and Extensible Key Management (EKM) to enable accessibility to the protected data in the form of a key password. The Windows operating system (and not only in the SQL Server arena) has also contributed authentication features, such as Kerberos Authentication that will accomplish robust authentication methods to access the data platform. SQL Server 2012 continues with similar levels of security features: secure by design, secure by default, and secure in deployment.

In practice, SQL Server Analysis Services (SSAS) is just a part of the Business Intelligence (BI) tool that provides next-generation data warehousing capabilities. To conform to a complete SQL Server BI platform, services such as core-database engine, SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS) are essential.

BI solutions comprise key findings that detail data and trend analysis that require data integration, cleansing, and transformation. Right on time with the current social networking plugins such as Facebook, Twitter, LinkedIn, and MySpace, they add more fuel to the fire that leads to the data explosion.

Similarly, the need of data usage from mainframe computer to the mobile devices, it is the data flow that keeps the business running. The primary concern with such vast amounts of data flow across multiple systems, the BI helps business to make strategic decisions at the right time. This is where security becomes a prime concern when the data leaves the corporate network and it must be presented to the right owner without compromise or any data leaks.

With regard to trustworthy computing concepts, it is over 10 years now since security features were developed and enhanced from version to version. Here are a few of the best security features (my favorite) that are built-in within SQL Server as compared to other RDBMS products:

This chapter covers the recipes that are a foundation to security and its importance within SSAS on Online Analytical Process (OLAP) databases, which are essential for an organization to disperse the data effectively without compromising the confidentiality of data.

Security in particular for an Analysis Services platform is an important issue that needs to be properly administered and managed. As this book relates to the cube, it is an important subject that needs to be highlighted. A cube contains data for a particular dataset or a whole organization. Specific to the subset of data as a DBA, you need to ensure that such data can only be accessed by relevant privileged people in the organization.

Nevertheless, security is paramount for a data platform; it can be fixed in the initial stages of design that will certainly avoid any breach of data access layers. So the recipes from this chapter consist of the required ingredients to construct and administer OLAP security management:

By the time this book was written, SQL Server 2012 Service Pack 1 was released, so the majority of the SSAS features information that are referred here are applicable for RTM + Service Pack 1 releases.

The introduction of Business Intelligence Semantic Model (BISM) facilitates and supports a wide range of reporting and analytics while blending the two Analysis Services modeling features: Multidimensional and Tabular.

Specific to the Business Intelligence arena we have a new feature called tabular modeling. This is a new addition for the Business Analysts that can build up a BI semantic model using the relational tables from the database engine rather than using the dimensions from Analysis Services.

This BI edition contains a majority of Enterprise edition's data management features, such as Master Data Services (MDS), Data Quality Services (DQS), Power Pivot, and Manageability. As the name suggests, the majority of the features for Corporate BI are also in-the-box such as CUBE analytics, Reporting related features, Multidimensional BI Semantic model, Self-service Business Intelligence, Alerting, and Power Pivot for SharePoint Server.

As a Database Administrator and Data Architect the data security is a prime concern and this book spotlights the key concepts on securing the CUBE. So we need to understand the SQL Server security model and how to effectively implement the same on the data platform. Simply, security does not mean stopping at delegating access control on server-scope to column-level or defining a strong password for a SQL user.

The Administration and Management to data platform relies upon Manageability enhancements, so it is essential to contain appropriate tools to manage the SQL Server data platform.

SQL Server Management Studio (SSMS) is the key resource in Manageability aspects that has been introduced in version 2005 and became popular since version 2008 with greater enhancements. It's not just that the SSMS is being used by DBAs & Developers; with SQL Server 2012 the manageability enhancement augments the developer's working experience with the product.

Similarly, just to leverage policy-based management, resource governor or managing SQL Server scheduled jobs, SSMS can be used to debug the code using Transact-SQL (TSQL) debugger, IntelliSense enhancements that suggest string matches based on selective words. Also, the newest addition within SSMS is the Insert Snippet menu that allows developers to insert a template as a reference when writing TSQL statements.

Business Intelligence Development Studio (BIDS) is the one tool that delivers the BI development capabilities for Analysis Services, Reporting Services, and Integration Services, which is versions 2008 and 2008 R2.

Combining together the SSMS and BIDS provides the tools for data platform administration, security, and all required management functions.

The BIDS is a unique tool to perform views, charts, and prediction-analysis in addition to the free-form query editor to build ad hoc Data Mining Extensions (DMX) queries to manage the data mining models in Analysis Services.