This is the third article in the four part series about designing SSAS Tabular models. In this article you will learn how to manage and secure a deployed model on the server.
Part 1 - Getting Started with SSAS Tabular
Part 2 - Easy DAX: Getting Started with Data Analysis Expressions
Part 3 - Tabular Model Administration
Part 4 - Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions
Starting Your Modeling Career – with Analysis Services Tabular Models
Paul Turley, SolidQ Mentor & SQL Server MVP
Part 3 - Tabular Model Administration & Security
It’s common knowledge that SSAS Tabular and Power Pivot use the same in-memory engine and support the same essential features. Tabular supports a number of features which take it from desktop and small group scale to a truly enterprise class business solution. We explore two important enterprise features in the following exercise.
The first thing to understand is that a Tabular solution sits on the SQL Server Analysis Services foundation, which is a 14-year-old mature technology. When a project is deployed, an SSAS database is created. In SQL Server Management Studio (SSMS), you may notice the lack of objects that may be familiar to SSAS multidimensional users; like cubes and dimensions. The database simply contains tables (some of which may be “measure group” type tables and others serve as dimensions.)
To follow-along, use the solution you completed in Part 2 or download the Article 2 Completed Project.
Managing the Workspace Database
When opening the Model.BIM file in the SSDT model designer, a user-specific workspace database is automatically deployed to the server specified in the model properties. As a general rule, I don’t recommend the workspace database be on a production server. It should be on an instance where you have administrative rights.
Just a word about model stability, recovery and maintenance… Tabular SSAS is a very good technology, architecturally speaking. The model designer is OK with room to improve. On a scale of 0 to 10, I give it a 5 with high hopes of future improvements. It has quirks; on occasion, stuff breaks and you should have a disaster mitigation plan. Just this week, working on a Tabular project that has been going swimmingly for months, I made a column name change to a source table and the model designer went all goofy and started reporting senseless errors. I couldn’t open the Model.BIM file. After a brief attempt to undo my breaking change, I just restored the project, re-applied changes and was back in business. Make frequent backup copies of your project folder which can be accomplished using copy and paste in Windows Explorer. If you ever have trouble opening the model, try deleting the .Settings file and then re-enter the model properties in the designer.
Before moving to the next step, open the completed project from the previous article and then open the model in the designer. Run SSMS then connect to your SSAS Tabular instance. Expand the Databases node; you will see the workspace database has the project name followed by your user name and a big long globally unique identifier. When you process objects and view data in the designer, you are actually running queries against this database.
Deploying the Model
In the SSDT project, right-click the project name in the Solution Explorer and choose Properties. Make sure that the Server is set to use the same SSAS instance name as the workspace database is for the model (LocalHost if using SSAS Tabular locally) and set the Processing Option to Do Not Process. This changes the Deploy action for the project to only update the server with metadata changes and not to process data changes automatically.
Figure 1 – Deployment Options in the Project Properties
Click OK to save these changes to the project properties and then right-click the project and choose Deploy. The model is redeployed and the Deploy dialog should display Success.
Why Partition Tables?
In large multidimensional models it is common to create several partitions to improve query performance. In a cube, having multiple partitions allows the SSAS query engine to spawn parallel threads to read data from disk and reduce IO bottlenecks. In Tabular, complex partitioning schemes are completely unnecessary because the query engine never retrieves data from disk. Having multiple partitions on large and frequently-changing tables may reduce processing time and help simplify data loading. For example, if you have five years of fact data that never changes and need to add new records every day, it would be very inefficient to reprocess the entire table and reload billions of records every night. Having a daily partition for new records loaded every night may take only seconds to update.
Create a Partition
For this exercise, keep in mind that the Adventure Works sample data only includes records from 2005 through 2008. In production you might have weekly, daily or event up-to-the-minute transactional data to add. Your partitioning approach should be tailored to your needs but keep it simple.
In the model designer, choose the Internet Sales table and then select Partitions… from the TABLE menu. Every table includes one partition by default. Select Internet Sales in the partition list and change the Partition Name to “Internet Sales - Before 2008”. Click the SQL button on the right, just above the grid, add a new line to the end of the query and type: WHERE YEAR(OrderDate) < 2008. Click the Validate button and make sure no error is reported.
Click the Copy button and rename the new partition: “Internet Sales – 2008 and After”. Change the WHERE clause in the SQL Statement to: WHERE YEAR(OrderDate) >= 2008.
Figure 2 – A new partition
The asterisk preceding the new partition names indicates that changes need to be saved. Just click the OK button to apply the changes and close the Partition Manager. Right-click the project in the Solution Explorer and deploy the model again.
In a production scenario, you may have three or four partitions for larger tables that segregate records relative to the current date. Refer to the BI Semantic Model security guide, referenced at the end of the article, for more partition filter examples.
Process Partitions on the Server
Our goal is to prepare processing scripts to hand-off to our database administrator. Open SQL Server Management Studio (SSMS) and connect to the tabular instance. Find the newly-deployed database and expand the Tables node. Right-click on the Internet Sales table and choose Partitions…
Click the Process icon and in the Process Partition(s) dialog, check the box next to the Internet Sales – Before 2008 partition. Select Process Full from the Mode drop-down list and click the Script drop-down list button in the toolbar. Use the Save As dialog to give the script file an appropriate name including the object name and action, such as “Internet Sales – Before 2008 - ProcessFull.xmla”. Repeat the process for the other partition in this table.
To process the remaining tables, right-click on one table and choose Process Table(s). In the next dialog, check every table except the Internet Sales table, set the processing mode to Process Full and then use the Script drop-down button to save the script to a file; perhaps: “All tables except Internet Sales – ProcessFull.xmla”. Open all three of these files in SSMS using File > Open > File... You can select all three files at once.
Note that the processing instructions are brief and easy to understand. These files can be combined and consolidated so that when you hand them off to the admin, the conversation is as easy as “please run this one at the end of the day”, then “run this one at the end of the week” and “run this one at the end of the month”. SQL Server Agent can be used to schedule these tasks.
Designing & Implementing Security
There are two general levels of security in Analysis Services; Tabular uses the same fundamental mechanisms as multidimensional. Roles are defined with a set of permissions. Then users or Windows groups are assigned to each role which enables those permissions for groups of users. This is the first level of security which is necessary for any user to access data in the model. The second security level provides row-level filtering on any table. This is commonly used to return only data that a user has permission to view. In role-based security, any number of roles may be defined to confine security access and user-specific filtering logic for a group of users. Role and user-specific filter logic is implemented with DAX script and can be data-driven.
Implement Data-Driven User Filters
First we will need a table added to associate users with attribute values for filtering. Let’s say that we have regional sales managers who should only see sales information for their regions. I will create a reference table to store users and their regions. Using SSMS, I run the following query:
USE AdventureWorksDW2012
GO
CREATE TABLE UserSalesTerritoryRegion
(
UserName nvarchar(100) NOT NULL,
Region nvarchar(50) NOT NULL
)
GO
INSERT INTO UserSalesTerritoryRegion (UserName, Region)
VALUES ('PAUL-DEV1\Paul', 'Northwest'),
('PAUL-DEV1\Paul', 'Southwest'),
('PAUL-DEV1\Marti', 'United Kingdom')
Before you run this script, you’ll need to modify the machine name or domain and user name so it works in your environment. If your domain is BIGCORP and your user name is Fred, use ‘BIGCORP\Fred’ instead of ‘PAUL-DEV1\Paul’.
There is an important catch here – and read this carefully. The user name that you use to test this role filter cannot be in the SSAS server Administrators role. If they are, filtering won’t do anything because administrators see all. To manage the Administrators role membership, right-click the SSAS instance in SSMS, choose Properties and go to the Security page.
If you remove a user from the Administrators role, make sure you are able to login and connect as another user who is in the Administrators role or you’ll can lock yourself out of the server. I know that I can also log into my machine as the user named Administrator who is in the SSAS Administrators role so I’ve removed ‘Paul’ from the SSAS Administrators role for testing purposes.
Add the Row Filter Expression
In the model designer, open the data source for AdventureWorksDW2012 and use the Open button to add the UserSalesTerritoryRegion table to the model. Don’t make any changes in the Table Import Wizard and then click Finish.
This table doesn’t have a single key so we won’t create any relationships. Using the Roles icon on the toolbar, open the Role Manager dialog. Add a new role named Sales Managers and change the permissions to Read. On the Members page, add the user referenced in the UserSalesTerritoryRegion table or a Windows group to which they belong. In my case, I am adding myself, “Paul”.
Now, back on the Row Filters page, place the cursor in the second column of the Sales Territory row and enter the following DAX Filter script:
=[Region]=LOOKUPVALUE( UserSalesTerritoryRegion[Region], UserSalesTerritoryRegion[Region], [Region], UserSalesTerritoryRegion[UserName], USERNAME() )
Every time a query runs, each record in the Sales Territory table will be evaluated using this expression to decide if it should be included or excluded from the filtered results. At first the “=[Region]=…” pattern looks a little strange, but it is a Boolean expression that tests to see if the expression on the right side of the second equal sign returns a value matching the current Region. Here is a quick translation of this script (reading right-to-left):
Get the current user name and match it to records in the UserSalesTerritoryRegion table. Filter those records where the Region column in the UserSalesTerritoryRegion matches the Region field in the Sales Territory table row being evaluated. If these match, return the Region field value from the UserSalesTerritoryRegion table to be matched to the ‘Sales Territory’[Region] and, therefore, include this row in the results.
Let’s see it work. The good news is that you don’t have to log out and log back in to test this in the context of the user. Open up Excel, connect a PivotTable to the workspace model, and then provide the test user’s user name and password. In the PivotTable, add Region from the Sales Territory table to Rows and a measure from Reseller Sales to the Values. Figure 3 shows only the Northwest and Southwest sales regions because the data-driven filter is applied for this user.
Figure 3 – Filtered results in an Excel PivotTable
No matter what reporting or query tool this user is using or how they query the model, they will only see data related to these Regions.
Save your work and use this project to begin Part 4, “Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions”.
Resources
Download sample project for this article
Partitions (SSAS Tabular)
https://technet.microsoft.com/en-us/library/hh230976(v=sql.120).aspx
Securing the Tabular BI Semantic Model
https://msdn.microsoft.com/en-us/library/jj127437.aspx
