Microsoft SQL Server 2012 Bible

Extending a BI Semantic Model with SQL Server Data Tools

BI Semantic Models developed with SQL Server Data Tools support all the features available in the PowerPivot Excel Add-in. The steps to add these features with SQL Server Data Tools are very similar to the steps already covered earlier in this chapter under the section titled “Extending a BI Semantic Model with PowerPivot.”

In addition, SQL Server Data Tools allows you to define security roles, a feature not available in the PowerPivot Excel add-in. The next sections describe the steps to add this functionality to a BI Semantic Model using the SQL Server Data Tools.

Creating Roles in SQL Server Data Tools

Roles provide the ability to limit access to Windows users by allowing specific rows that match the DAX . The permissions available include:

• None
• Read
• Read and Process
• Process
• Administrator

To create a new role in SQL Server Data Tools follow these steps:

1. Follow steps 1 to 12 of the section titled “Creating BI Semantic Models Using SQL Server Data Tools.”

2. Click on the Model menu option and select Roles. The Role Manager window opens.

3. Click on New to add a new role.

4. Type BicycleProductManagers in the new role's name.

5. Select the Read option in the Permissions column.

6. In the description textbox, type Read Access to Bicycle Category Product Managers.

7. In the Row Filters section, click inside the DAX filter textbox next to DimProductCategory and type the following formula:

=DimProductCategory[EnglishProductCategoryName] ...