Managing the Solution

When moving the system from development into production, the Analysis Services database and reports can all be deployed in the usual ways, but the WSS site adds a new wrinkle to the picture.


If you are going to install Reporting Services and WSS on the same server, you must follow the instructions for configuring Reporting Services with WSS in SQL Server Books Online because WSS has a tricky way of jumping in when any IIS applications are created.

When you are initially moving the Web portal from development to your production environment, you can use the SharePoint Migration Tool (smigrate) to migrate the site over to the new server. Basically, that involves running smigrate on the development server to create a backup file and then copying the file over to production and running smigrate with the r option to restore the site.

All the information about the WSS site is stored in a SQL Server database, much like Reporting Service's approach. When you install WSS on a server, by default it installs Microsoft SQL Server 2000 Desktop Engine (MSDE) to store the content information, but you can also use an existing database server, which is probably the best option because it will be easier to administer.


Access to the Analysis Services database and Reporting Services report is controlled as described in previous chapters, so we focus here on setting up the security for the WSS portal as well as using some of the advanced security features available in Analysis Services.

More Advanced Cube Security

As you saw in Chapter 5, you can control the dimensions and measures that a user can access by adding roles to the cube and defining which users and groups belong to a role. Doing so proves useful for large complex cubes such as the police service solution, because there are probably going to be some sensitive areas that only certain users should be able to access. For example, the Charge measure group relates to the Person dimension so that users can see which people were charged. The Person dimension includes some attributes such as Name and Date of Birth that are personal information, so these attributes would probably be disabled for most users by unchecking Allow Attribute on the Dimension Data tab for those attributes.

Dimension Data Security

Some scenarios require even more granular control over the data shown to the user, not just restricting the list of dimensions and measures. The Employee dimension contains information on all employees, including the chief of police and deputy chiefs, so this data would probably not be available to all users. On the Dimension Data tab of the role, you can select an attribute and define exactly what data a member of the role can see.

As shown in Figure 9-18, you can define the set of members that they can see in several different ways. You can explicitly list the members that they are allowed to see (allowed member set) or you can list those members that they should not see (denied member set). Both sets can contain either a list of members or an MDX statement that returns a set. In Figure 9-18, we have defined a denied set that removes all employee positions where the sequence is two or less (that is, deputy chief and chief of police).

Figure 9-18. Defining dimension data security

Choosing an allowed set versus a denied set has a major impact on how new dimension members are handled. If you select a list of product groups and create a role with those groups in an allowed set, any new product groups that are added will not be available to the users in this role because they are not explicitly allowed. On the other hand, if you select a list of product groups that the users should not see and create a role with a denied set, any new product groups will be visible because they are not explicitly denied. This could be a serious problem in some contexts so using an allowed set is generally recommended.

The Enable Visual Totals check box on the Dimension Data tab controls what the totals above this attribute will display. If visual totals are disabled (which is the default and best-performing option), the total for all positions will still contain the actual total, including the value for all the members that have been hidden, such as chief of police. This means that users might be able to infer the hidden values by subtracting the visible rows from the total. If you would like to exclude the hidden numbers from the total for all positions, you can turn on visual totals, which will calculate the total based only on those members that are visible to the user.

Testing the Roles

After you have defined the roles, you can test the results by opening the cube browser and clicking the Change User button on the left of the toolbar. Doing so enables you to select a specific role or combination of roles and then browse as if you are a member of those roles rather than an administrator.

One thing to remember when defining multiple roles in a cube is that if a user belongs to more than one role, either by directly being added to the role or by membership in Windows groups, the user's permissions are the sum of all the permissions they have across all the roles they belong to. For example, if one role disables access to a dimension but another role allows it, the user will end up with access to the dimension.

Securing the Portal Web Site

The reports that display within the Reporting Services web parts will use the standard report security, but you will need to use the WSS security settings to control access to the Web site itself and to define what actions a user can perform on the site. You can modify the user permissions by clicking the Site Settings button on the toolbar of a page in the Web site and then selecting Manage Users.

You can add specific users or Windows groups to the list and select which site groups they belong to. A site group controls what actions they can take on the Web site, such as just being able to add documents to existing libraries, or being able to create whole new document libraries. The four site groups that WSS includes by default (Reader, Contributor, Web Designer, and Administrator) can also be extended so that you can control exactly which rights are provided to users.


The most common type of change to the portal will be adding web parts with new reports or rearranging the page. The tricky part is that the SharePoint Migration Tool is really a one-time-only deal: After users have started adding documents and other information to the site, you can't redeploy the site from development into production without deleting all their information. Any future maintenance changes to the site will either need to be manually made to the production site or you could write a script that copies the documents back from the production system into development so that you can use the migration tool.

As the police service's requirements evolve, we will end up making some changes and additions to the Analysis Services database. Any reports and OWC views that have been built on top of the database will need to be retested, because some changes such as renaming an attribute may cause reports to give an error message. OWC is typically quite forgiving about changes and often just ignores any missing columns that you may have deleted or renamed, but you need to check that the view still makes sense.

One other area to watch out for is that any new objects that you add to the cube such as new attributes or measures are not automatically added to any of the perspectives that you have defined. So, if you add a new attribute to a dimension that is part of a perspective, you must remember to go and turn on that attribute in the Perspectives tab.


All the information on the scorecard site, including items such as the definition of the pages, security settings, and even the documents that users add to the team site, are stored in the WSS SQL Server databases. So this adds another task to the list for your database administrators: In addition to backing up the data warehouse and Reporting Services relational databases and the Analysis Services database, they will also need to back up the STS_Config relational database, which contains the site configuration information, and the STS_<<server_name>>_1 database, which contains the content.

Practical Business Intelligence with SQL Server 2005
Practical Business Intelligence with SQL Server 2005
ISBN: 0321356985
EAN: 2147483647
Year: 2007
Pages: 132 © 2008-2017.
If you may any questions please contact us: