Usage Monitoring

Usage Monitoring

A secure DW/BI system will have usage monitoring in place. In an increasingly regulated world, its extremely valuable to know who is connected to the system and what theyre doing.

In Chapter 15 we talk about how to set up usage monitoring on Analysis Services, Reporting Services, and the relational engine. For some organizations its sufficient simply to collect logons : whos accessing the database and when? Other organizations need to know exactly who is accessing which information.

Reporting Services monitors usage by default. In addition, Reporting Services provides an option to copy the usage logs from the Reporting Services catalog into a separate database. This option provides a target relational database, Integration Services packages to move the data, and a starter set of reports . This database should suffice for the majority of usage reporting requirements from Reporting Services.

Usage monitoring provides other valuable benefits. Its a valuable tool for performance tuning. Spending a bit of time analyzing how business users are accessing data is very valuable for understanding how to improve your DW/ BI system.

If you set up a usage monitoring systemas we strongly recommendyou should inform your business users of what youre doing, why, and how the information will be used.

Protecting Privacy

A DW/BI system with information about consumer behavior has great potential for improving your products, customer service, sales process, and user experience. But it also has great potential for abuse. In many countries , online customers must be able to opt out from providing any personal information or having that information stored permanently. Whether or not its a regulatory requirement, its the right thing to do.

Before you go crazy with your new DW/BI system, mining the data to uncover customer patterns and develop targeted customer lists, you should develop a customer privacy policy. Make sure every business user of your system knows the policies for using the data and communicating with customers.

Protect your customers by ensuring that mailing lists can be generated only by a designated List Manager, who keeps track of the lists that have been created, and when each customer has been contacted. This information should be used as input to subsequent list creation to avoid inundating customers with unwelcome offers. CRM and contact management software offers features to track campaigns , promotional offers, and mailings . Set up the DW/BI system to create lists of customer IDs, and then hand that list off to the contact management system.

The safest approach is to leave personally identifying information out of the DW/BI system completely, or stored in an encrypted format.


The goal of this chapter is to highlight the most important security issues for a DW/BI system, and to help you figure out where and when to secure data. We couldnt possibly cover all the security features of SQL Server, nor all the details of how to implement those features. We hope merely to have provided you with the tools to develop a security plan and security test plan.

One of the most important steps you can take for a secure DW/BI system is to identify which DW/BI team member is in charge of security. That security manager will drive the development and implementation of the security plan.

The easiest pieces of the security plan have to do with physical security and operating system security. Theres lots of information available about how to secure servers and the Windows operating system. You just have to commit to doing it.

The harder question, and the one to which we devoted most of this chapter, has to do with securing the data. As we described, securing predefined reports in Reporting Services is easy, painless, and effective. Securing data for ad hoc analysis is a harder problem. Youll definitely find it easier, and the user experience much better, to define security rules in Analysis Services than in the relational database. Analysis Services security features make this a fairly straightforward task for a wide range of requirements. And because security rules are defined in MDX, even very complicated scenarios are feasible .

Finally, we struggled with how to set up the relational database security to support direct ad hoc access. Its possiblepeople have been doing it for years but its hardly as easy or satisfactory as wed like.

The SQL Server documentation in Books Online has a strong emphasis on security features. Its as if Microsoft executive management made security a priority. But the security documentation is scattered across the different components , and it isnt always easy to find the information you need. You might be able to cut corners in some aspects of system development, especially if your data volumes are small, but everyone needs to be careful about security.