Denny Lee's Windows Live Space

• Reporting Services Scale-Out Architecture: The focus of this technical note is the Reporting Services scale-out architecture, which is referenced throughout this technical note series.

• Report Catalog Best Practices: Provides guidance and best practices on the report server catalogs—the underlying databases that provide metadata (parameters, snapshots, history, etc.) used by Reporting Services to provide your reports.

• SSRS Scale-Out Deployment Best Practices [link provided when published]: Provides guidance and best practices on deployment details for scaling out your Reporting Services environment including configurations and the use of File System snapshots.

• SSRS Performance Optimization Configurations [link provided when published]: Provides guidance and best practices on using specific Reporting Services features and configurations to optimize the performance of your Reporting Services environment.

• SSRS Troubleshooting Tips [link provided when published]: Provides various troubleshooting tips that are helpful to better understand issues that may occur within your Reporting Services environment

If you are to look at the table of values where you want to do a distinct count on the ID column

Within SQL, you will get a value of 4 in which there are four distinct values, 0, 1, 2, 3 and NULL is not counted.

But within Analysis Services, if you were to put a distinct count measure on top of the ID column, you would also get a value of 4 for four distinct values, but those distinct values are 1, 2, 3, and 0.  In this situation, NULL and 0 are the same value.  This is a little bit more apparent if your table had the values of

Within SQL, you will get a value of 3 (distinct values of 1, 2, and 3) but within AS, you will get a value of 4 (distinct values of 1, 2, 3, and NULL or 0).  There are various philosophical reasons why this is happening ranging from the fact that there are actually quite a few definitions for what NULL is (I really don't want to get into that) and within AS we're looking at things from a multi-dimensional point of view (i.e. from the dimension members) hence the exclusion of a NULL value implies exclusion of the rows.  Regardless of the philosophical point of view on this, one should just be aware of this when working with their distinct count measures.

Oh note, thanks to John Lam for reminding me that if you turn on the NullProcessing property it will preserve the NULL value - i.e. the first table will have 5 distinct values of 0, 1, 2, 3, and NULL.

Scalable shared databases
Scalable shared databases let you attach a read-only reporting database to multiple server instances over a storage area network (SAN). A reporting database is a read-only database that is built from one or more production databases that are used exclusively for reporting purposes. To be made into a scalable shared database, a reporting database must reside on one or more dedicated read-only volumes. The primary purpose of these read-only volumes is to host the reporting database or a coordinated set of reporting databases. These volumes are known as reporting volumes.

Benefits
Scalable shared databases offer the following benefits: • Provide workload scale-out of reporting databases by using commodity servers. A scalable shared database is a cost-effective way of making read-only data marts or data warehouses available to multiple server instances for reporting purposes, such as running queries or using SQL Server 2005 Reporting Services. 
• Provide workload isolation. Each server uses its own memory, CPU, and tempdb database. 
• Guarantee the same view of reporting data from all servers if all the server instances are configured identically. For example, all servers would use a single collation.

Note Optionally, you can update the reporting database on a second reporting volume. For more information, see the "Maximize the availability of a scalable shared database" section.

 

Restrictions
The following restrictions exist for a scalable shared database: • The database must be on a read-only volume.
• The data files can be accessed over an SAN. 
• Scalable shared databases are supported only on Microsoft Windows Server 2003 Service Pack 1 (SP1) or a later version of Windows Server 2003.

Abstract

Analysis and sharing of aggregate data (e.g., number of users whose favorite color is blue) is crucial to understanding patterns of the population being studied. The problem is that even summary data can expose the individuals who make up this information. Therefore, we are studying the use of privacy preserving data analysis techniques that will protect the individual and allow analysts to understand general trends. We believe that while there are unique perturbations of the data with these techniques, analysts will be able to use the data and protect the users at the same time. The case study involves applying these algorithms to MSN reporting data. One group focused on event usage (e.g., number of page views on Moneycentral.com) while another group focused on usage data (e.g., the number and frequency of users who use MSN Messenger). After four months of analysis, the first group did not like the effects of privacy preserving data analysis. In contract, the second group is prepared to use this type of reporting in production. Further discussions and inquiries revealed that the first group had perceived data trust issues that conflicted with use of the algorithms. But, the second group had no trust issues with the data and was very familiar with privacy issues, hence the successful outcome. Therefore, if users trust the data it is possible to use this privacy preserving algorithm to allow the analysis of data while protecting privacy.

Introduction

Based on an analysis of the 1990 US Census, 87% of the United States population is uniquely identifiable by the three attributes of zip code, date of birth, and gender [1]. Latanya Sweeney’s breakthrough research with the Governor William Weld case clearly depicts that the masking or hiding of publicly available information does not adequately protect the privacy of an individual. Sweeney was able to obtain masked medical data from the Group Insurance Commission of Massachusetts whom is responsible for state employees’ health insurance. The masked medical data contained only non-identifiable information such as ethnicity, visit date, diagnosis, procedure, medication, total charge, zip code, date of birth, and gender. For a cost of $20, Sweeney then obtained the publicly available Cambridge, MA voter list. Referring to the Venn diagram below, each circle graphically represents the two data sources and by itself reveals little information. The masked medical data on the left provides only medical information where only gender, date of birth, and zip code were identified. The voter list on the right provides personal information but limited only to voting information, address, date of birth, and gender. But recall that 87% of the US population is uniquely identifiable by only the three attributes of zip code, date of birth, and gender. In the case of the Massachusetts governor, there were only six people in Cambridge, MA whom had the same date of birth and only three of them were men. Of the men remaining, he was the only person to live in his particular zip code. Through these two pieces of information joined only by gender, date of birth, and zip code; Sweeney was able to identify and reveal the medical records of then Governor William Weld.

Figure 1: Unmasking of masked medical data

As one can see, this particular scenario stresses the inadequacies of ensuring privacy by masking medical data; it will require us to change our perceptions of what data privacy really entails. It takes only a small amount of information to reveal the individual underneath the data.

Many current privacy solutions involve the concept of masking the data such that personally identifiable information remains hidden. Regulatory bodies such as HIPAA provide very explicit instructions on what information can and cannot be revealed in different scenarios [2]. Businesses such as Microsoft® provide detailed statements (e.g., “Microsoft Online Privacy Statement”) detailing the personal information collected, uses of the information, and the choice to opt-out [3]. Academic institutions, such as Oregon Health & Science University, have requirements in addition to HIPAA standards staging the permitted uses and disclosures associated with protected health information [4]. Yet, as one can see from the William Weld case, the masking and explicit statements of use and disclosures may not necessarily insure privacy. There are many other masking solutions and/or perturbations of data as can be seen in J.C. Cannon and A. Cavoukian’s book “Privacy: What Developers and IT Professionals Should Know” [5]. But the potential weakness of these approaches is that the solutions may end up being too complicated to implement or that the modification of data will result in changing the meaning of the data. This latter point is especially important because of its affect on analysis and research. If statistical analysis ranging from mean/median to advanced data mining techniques result in inconsistent values, the results and conclusions based on these statistics may be incorrect.

How is it then possible to protect an individual’s privacy in a relatively simple manner while ensuring consistent statistical analysis for research purposes? This is the purpose of “Privacy Preserving Data Analysis” which is based on the work of Microsoft Research (MSR) researchers Cynthia Dwork and Frank McSherry (as well as S Chawla, K Talwar, A Blum, K Nissim, and A Smith) [6, 7, 8]. The basic premise of their research is that the addition of noise based on the exponential distribution to the data will be able to protect the individuals underneath the aggregate data. Recall that to attack aggregate data, one need only to ask enough questions to drill down to a specific individual with very distinct attributes. In the Governor Weld case, the first question would be the number of people who had his date of birth (answer is 6). The next question is number of those people who were male (answer is 3). The final question is the number of those people who lived in his 5-digit zip code (answer is 1). In this example, it required only three questions for the author, Sweeney, to drill down to Governor Weld and his medical records [6]. But, if we were to add exponential noise (some integer value between –∞ to +∞) to the consistently change the above values, it would be impossible to drill down to this one person. For example, if the noise values to be applied to these questions were that of (-2, +1, +6), then the results of the above questions would be:

When looking at the PPH-applied answers, the additional noise has made it impossible to drill down to the single individual, therefore the data is “privacy preserving”. In this particular example, the noise is too large but in the methods section we describe how to better calibrate the noise. Ultimately, the key is to add enough noise to the system so that the answer changes but the overall statistics do not. To prevent attacks to the noise algorithm itself, more noise needs to be added as more questions are asked. The background section of this paper provides more details on how this works. The efficacy of this methodology is not in question for this case study as this has already been amply researched and verified. Therefore, the purpose of this case study is to determine the applicability of this noise algorithm in an actual reporting environment.

Background

The privacy preserving data analysis concepts involve the addition of noise to the data in order to protect the individuals underneath the data. This case study involved the use of the privacy preserving histogram (PPH), which uses exponentially-distributed noise (the algorithm is better described in the methods section). These concepts are based on the research in “Practical Privacy: The SuLQ Framework” [7]. The next few background sections describe both the sanitization concept and the privacy mechanism theorem associated with it.

Sanitization Concept

To protect the individuals comprising the data, we will mask these individuals within the data by creating a sanitization point between the user interface and the data.

Figure 2: Sanitization Concept

The left green shape on the left represents the database while the right-most circle represents the answer or result set provided to the user interface. The middle section is the interactive sanitizer, defined К, where it introduces random noise to produce uncertainty, hence privacy. Note the magnitude of the noise is given by the theorem:

If many queries f₁, f₂, … are to be made, noise proportional to Σ­_iΔf_i suffices. For many sequences, we can often use less noise than Σ­_iΔf_i . Note that Δ Histogram = 1, independent of number of cells

The sanitizer requires the creation of a carefully detailed algorithm and will be discussed in more detail in the differential privacy section below. A safe answer on the amount of noise to apply is that of standard deviation equal to the total number of queries. If the number of queries is not known, then the standard deviation can be proportional to the square of the queries asked so far. As for the noise itself, it should be newly seeded each time a query is applied.

By doing this, this algorithm will be able to address all attacks. Consequently, for each person, the increase in probability of the individual being attacked (or anyone else for that matter) due to the contribution of their data is nominal. The example given is foiled for two reasons: a) the addition of noise will (formally) complicate the polynomial reconstruction and b) the number of queries is limited by the degree of privacy guaranteed, and N is generally going to be way too many queries.

Mathematically, at this sanitization point, the PPH will apply of a little bit of noise within each cell of the histogram. Descriptively, if one’s result set from the database is a report with a set of rows and columns, for each value within each row and column cell a small bit of error is added to the original number. Provided that the sanitization point can limit the number of questions being asked and/or add more noise as more questions are being asked, then the PPH can guarantee the privacy of the individuals that make up these aggregates.

Differential Privacy

Our privacy mechanism, К, gives ε-differential privacy for all transcripts t, all databases DB, and all data items (rows in the DB) Me, the ratio

Figure 3: Differential Privacy Formulae

The differential privacy theorem is a simple mathematical formula that describes the fact that nothing more can be learned about an individual when her information is in the DB (DB+Me) than when it is not in the DB (DB-Me). This is important from the context of joining one set of data that is in the DB (e.g., the masked medical data) with another set of data that is not in the DB (e.g., Cambridge, MA voter list). If there is no noticeable difference between f(DB-Me) and f(DB+Me) then there is no perceptible risk by joining the two data sets together. Therefore, to achieve this differential privacy, we will need to add scaled symmetric noise ∝ exp(-|x| ε/ Δf).