SSAS: distribution of measures over percentage

Question

Hi there,

I am running a SSAS cube that stores facts of HTTP requests. The is a column "Time Taken" that stores the milliseconds a particular HTTP request took.

Like...

RequestID     Time Taken
--------------------------
1             0
2             10
3             20
4             20
5             2000

I want to provide a report through Excel that shows the distribution of those timings by percentage of requests. A statement like "90% of all requests took less than 20millisecond".

Analysis:

100%          <2000
80%           <20
60%           <20
40%           <10
20%           <=0

I am pretty much lost what would be the right approach to design aggregations, calculations etc. to offer this analysis through Excel.

Any ideas?

Thanks, Alex

Answer 1

Providing a report "through" Excel is a bit ambiguous as Excel manages it's own data and does it's own calculations. For data-manipulation and report-generation I far prefer Excel to SQL Server, which is after all truly an end-user product whereas SQL Server is more for management of data-stores.

Could you clarify whether your intention is to:

• manipulate the data in SQL Server into data for reports and then send that data to Excel, or...

• send the data to Excel so that it can then be manipulated into generated reports