Dimension design

I have a cube, with a handset dimension. The facts I am measuring are counting the subscribers, their revenue and their usage. In the handset dimension, I have an attribute "score" which actually scores the features of the handsets. Now I would also like to measure the average handset score. Do I have to add a sum aggregated measure which sums up the handset score and create a calculated measure to divide this score measure by the subscriber count? Or is there another way to have the same result?

Thank you in advance

Joos

I think Sum/Count for this case.

SSAS 2005 also has Aggregation function AverageOfChildren, that specifies average of leaf descendants in time. Read about that one here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

Vidas Matelis

|||Could you explain the granulairy of the fact table - is it a periodic snapshot, or is there a record per handset owned by a subscriber? And when computing the average handset score for a given selection of data, is the average across all handsets in thst selection, or across all distinct handset types selected - could you illustrate with an example?|||

I have 2 fact tables. The first one is used to count subscribers and to measure their revenue. The second fact tabel measures the usage of subscribers.

In each fact table, I do have a record per subscriber/handset combination. Each handset has a score. This score is an attribute of the handset dimension. Of course, I do have other dimensions, but this does not matter for this problem.

For a given selection of data, I should sum up the scores of all corresponding handsets, and divide it by the nr of subscribers. To get a real average, I will not sum up the scores of all distinct handsets.

So for example:

Subscriber 1, handset A, score 5

Subscriber 2, handset B, score 4

Subscriber 3, handset A, score 5

This should result in an average score of (5+4+5)/3

Thanks

Joos

|||

Assuming that there is a [SubscriberCount] measure from the 1st fact table, and that there are [Handset] and [Score] attributes in the [Handset] dimension, where [Score] has a numeric MemberValue defined (like 4, 5 .. above):

Sum([Handset].[Handset].[Handset],

[Measures].[SubscriberCount] * [Handset].[Score].MemberValue)

/ [Measures].[SubscriberCount]

Or, for better performance, you could add the Handset Score to the 1st fact table using a Named Query, and create a "sum" measure on it like [HandsetScore]. Then, as Vidas suggested, you could use Sum/Count, like:

[Measures].[HandsetScore] / [Measures].[SubscriberCount]