# Medians, Modes, Percentiles and OLAP

I'm newbie trying to wrap my head around OLAP, and I have a few questions.

• Question 1: Can an OLAP cube store medians, modes, percentiles?
• Question 2: Can an user-written MDX query return a summary of row-level data? (ex: % transactions > \$100). Or must the cube designer add this to the cube?
• Question 3: Do any OLAP products now provide mechanisms for accessing row level data? Which?

Our IT Department are looking for feedback on what sort of issues we're having with a particular MS Analsis Services ROLAP cube. We don't have access to the relational database behind it and need to perform calculations that aren't currently available as measures in the cube.

Let me see if I have this right.

1. A cube can provide statistics for counts, means, proportions, standard deviations.
2. If a particular statistic hasn't been catered for in a measure provided by the cube designer, can we write an MDX query to get it? Or do they need to change the cube in order to pre-calculate it from the row level data?
3. A cube cannot provide statistics likes medians, modes or percentiles, beacuse these statistics don't aggregrate properly.

I'm reading Leland Wilkinson's The Grammar of Graphics and in his chapter on Data Mining and OLAP, he says

These [cube operations] work well with statistics like counts, means, proportions, and standard deviations. Simple aggregations over subclasses can be computed by operating on sums, sums of squares, and other terms that are combined in linear functions to produce basic summary statistics.

They do not work properly with statistics like the median, mode and percentiles because the aggregate of these statistics is not the statistic of their aggregates. The median of medians is not the median of the aggregate, for example.

A more sophisticated ROLAP model has emerged recently, however. It is possible, through several technologies, to give statistical algorithms access to raw data through the relational model in real time. This approach is more promising than the rigid aggregations offered by structures such as data cubes.

In the most elegant form of this architecture, applications can request remote connections to provide information about their data-handling methods and take suitable action depending on the returned information. In this form, component architecture can achieve the real promise of distributed computing: design and execution that are independent of site, operating system, or language.

That was written circa 2005. Is anyone aware of products employing this methodology to allow for row-level data access?

• No takers? Any suggestions on how to improve the question to elicit an answer? Oct 12, 2011 at 11:29