For a new DW/BI project, or a substantial makeover of an existing system, the Microsoft business intelligence toolset provides a compelling single-vendor platform. Each of the components of the SQL Server BI product set is sufficiently high performance, scalable, easy to use and maintain, and functional enough to meet the majority of business intelligence scenarios.
Microsofts BI technologies also play well with others, and can be integrated into an existing data warehouse system. There are a relatively small number of features that require you to commit fully to the Microsoft platform. As we described in this chapter, its easy to combine non-Microsoft data warehousing technologies with SQL Server components. There is no reason to avoid using Microsoft products in a heterogeneous system, if for technical, economic, or political reasons you prefer BI products from a mix of companies.
A heterogeneous DW/BI architecture is particularly compelling in the short to medium term , especially for existing data warehouses that are in less than perfect health. Some of Microsofts technologies, especially the ability to build OLAP databases from non-dimensional relational sources, can greatly improve the immediate usability and business user acceptance of an existing system.
Chapter 12: Security
Balancing safety and opportunity
Security is another one of those black holes of the DW/BI system. It seems straightforward at first glance, but often ends up being more complicated, and using more resources, than originally planned.
As you may recall, Microsoft was hit hard in 2002-2004 by security breaches and gaffes. We dont need to replay those events. But if youre considering using Microsoft technology to manage your DW/BI systemand if youve reached Chapter 12 you probably areyou should understand that these events had a huge impact on Microsoft and SQL Server. We can, and often do, poke fun at Microsoft. But theyre more serious than ever about security. If youre also serious about security, and take the necessary steps to educate yourself, keep up-to-date on security bulletins and software updates, and design your system to minimize your attack surface, youll be in a good position to run a safe system. Microsoft throws so much information and so many security options at you that the greatest risk may be that youll give up out of frustration and confusion. We hope this chapter helps by highlighting the most important issues for a DW/BI system.
You can minimize the cost and risk of implementing security byyes!writing a security plan. That plan should have a section for securing the environment, including the hardware and operating system; a plan for securing the operations and administration of the system; and a plan for securing data. No security plan is complete without a discussion of how to test the security. Designing and implementing tests for whether the right people have access to the right data can be as hard as any other task in developing and operating the DW/BI system.
In this chapter, we talk about the major components of DW/BI system security. These are the components that should be included in your security plan. The easy part is securing the physical environment and operating systems. A serious corporate environment will lock down the physical servers and systems. Turn on only those services and features that are necessary to run your system.
The tasks of defining and implementing security spans the Lifecycle diagram that weve shown in previous chapters. During business requirements gathering, document the real business needs for security. Its important to get senior managements view, but you also need to talk to analysts and other potential users about the kinds of information they need to do their jobs effectively. You may need to push back to senior management, helping them to understand the costs of minimizing access to data. Designing and implementing security occurs in all the boxes of the Architecture, Data, and Applications tracks of the Lifecycle. And the cost of deploying and maintaining security never goes away.
After youve slammed the security doors shut, you need to start re-opening them to allow users into the system. A DW/BI system is valuable only if people can access it. The more information thats broadly available, the more valuable your system will be. Information is an asset. If you keep it in the equivalent of a Swiss bank account with zero interest, or even a savings account at 2 percent, youre doing your organization a disservice. Careful stewardship of data requires that you protect the information thats truly confidential and broadly publish the rest. Some organizations executive teams and culture are diametrically opposed to open access, but its worth arguing and pushing. Lets hope your executive sponsor can carry this battle forward.
You need to ensure that only authorized users can access the DW/BI system, and limit everyones view of data as appropriate. There are as many ways to do this as there are possible configurations for your DW/BI system. This is especially true if youre using non-Microsoft software in your system. The bulk of this chapter is devoted to discussing the most common configurations. The SQL Server documentation in Books Online does a good job of discussing security for Reporting Services, the relational database engine, and Analysis Services. Figuring out how these components work together is harder, so thats where weve focused our attention.
After reading this chapter, you should be able to answer the following questions:
How do you secure the hardware and operating systems for all the servers in your DW/BI system?
What kinds of security will you need for different kinds of user access, from running reports to ad hoc query and analysis?
How should you implement and test security features in the various components of your DW/BI system?
How can you monitor usage and protect the privacy of your customers?