SSAS custom group by query

Question

My fact table looks like this

yesterday a
yesterday a
yesterday a
yesterday b
yesterday b
yesterday c
today     a
today     a
today     b
today     b
tommorow  a
tommorow  a
tommorow  c
tommorow  d

In the end I need an Excel report like this

               repetition count
               1     2     3
yesterday      1     1     1
today          0     2     0
tomorow        2     1     0

How to create a "repetion count" dimension in SSAS 2k5 ? Please keep in mind that my fact table is a liitle bit more complicated and I have more other dimension there.

My idea is to create a named query in DSV but I have some doubts if filtering will work correctly.

Answer 1

I think you would need to do a view with a GROUP BY and a count so that you load data like the following into your cube

yesterday a 3
yesterday b 2
yesterday c 1
today     a 2
today     b 2
tomorrow  a 2
tomorrow  c 1
tomorrow  d 1

Then you could use the count column as the key for a repetition count dimension and you would create a measure based on the row count aggregate type.