A really quick note about how to write a MDX query to get Top N products (think dimension!) by revenue (Think measure!). Using this query as a starting point, you should be able to write queries that meet your similar business requirements:
Here’s the query structure:
[code gator=”false” language=”language"sql"”]
SELECT
{[Measures].[Revenue]} ON COLUMNS,
{TopCount([Product].[Product Name],20,[Measures].[Revenue])} ON ROWS
FROM
[Cube]
[/code]
Note the use of Top Count & it’s query syntax. Also note that you can specify other number instead of 20. I hope this gives you a good starting point.