To answer your questions in order:
- The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube. The ability to embed this sort of computation is one of the main unique selling points of OLAP technology.
- If you have a dimension that can identify individual rows (which could be a degenerate or 'fact' dimension derived from an identifier on the fact table) then you can do a query based on individual rows. However, OLAP works in terms of dimensions and aggregates, so you would need to have a dimension capable of identifying individual rows (with an aggregate composed of one value).
- Any OLAP tool can do what's described in (2), plus they generally support a mechanism known as 'drill-through' where the cube will return a bordereaux of the transactional data underlying a given slice that you drill through into.
If you want to do calculations that aren't directly available in the cube script, many OLAP tools such as the late, lamented ProClarity will allow you to formulate queries involving custom MDX based calculations. Unless the cube doesn't have the information you need to do the actual calculations, the custom MDX calculations should be able to support any computation you need.
Although OLAP queries are traditionally associated with statistical queries in aggregate, if you have a dimension that allows drill down to detail you need it is certainly possible to formulate queries that will calculate medians, percentiles or histogram queries from which modes can be inferred or computed.
For example, this has an example of a pareto analysis query, which is based on rankings.
Many cube products can operate in a hybrid or relational OLAP mode where they do not persist the data themselves but query it from an underlying database. In addition, pure ROLAP tools such as Business Objects, Report Builder or Discoverer can query from an underlying database and do work row by row. However, they tend to lack the sophistication of dedicated OLAP products, and they don't have much in the way of statistical analysis capability out of the box.
Another option would be to use a union to get parallel periods for the current and previous member. Then extract the first item out of this, which will always be the current member (except in the case of Feb 29 where it should fall back to the previous member).
That is, effectively coalesce with the previous member parallel period as per below:
union
(
PARALLELPERIOD(
[Date].[Fiscal Detail].[Fiscal Year],1,
[Date].[Fiscal Detail].currentmember)
*(will be null if Feb 29)*
,
PARALLELPERIOD(
[Date].[Fiscal Detail].[Fiscal Year],1,
[Date].[Fiscal Detail].currentmember.lag(1))
*(will be the prior year Feb 28 when above is Feb 29 )*
).item(0) *(get the first member out of the union set)*
Best Answer
Answers to your questions, in order:
First point: I'm not sure what you're getting at here. A measure can only be something that will display in a single cell. You can aggregate stuff up to create a measure. You can also have multiple fact tables (called measure groups) in a cube. If you slice by common attributes they measure groups will slice by those attributes. If you slice by something that one of the measure groups does not have in common, the values will be repeated for each slice of the other group.
Yes. You can order by attributes or values of measures.
Probably OK. I can't see any harm in that. Can't see a lot of point in it either, but if you really only have one drill down path there's no reason that why that can't be implemented in a cube.
Yes, you can do distributed partitions. Never built a system with remote paritions personally, but there's a technet article here..
SQL Server will do distributed partitioned views over partitions on multiple separate instances with Enterprise Edition. There is a shared-nothing version as well, but you can only buy that in a hardware bundle from an OEM. It doesn't seem to be getting a lot of traction in the market.