Creating Customized MDX Named-Sets

Question

I have two dimensions called [Session Length] and [Date], and a measure called [Count - Logins] in my Cube. The [Session Length] dimension contains an attribute called [Session Length] whose members are integers from 0 to 240.

I like to write an MDX query which would aggregate [Count - Logins] over customized subsets of the [Session Length] dimension (i.e. I want to create a customized set based on the [Session Length] dimension and aggregate the count over individual members of this custom set). Here is the query I have come up with so far but unfortunately I have no clue how to move forward:

WITH SET [Description] AS {
         [SessionLength].[Session Length].&[0], //Glimpse
         [SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5], //Short
         [SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30], //Medium
         [SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90], //Long
         [SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240]} //Extended
     MEMBER [SessionLength].[Session Length].SessionDescription AS
            Aggregate([Description])
     SELECT
     { [Measures].[Count - Logins] }
     ON COLUMNS,
    NONEMPTY({[SessionLength].[Session Length].SessionDescription} * {[Date].[Date].[Date]}) ON ROWS
FROM MyCube

With the following sample result set:

    Session Length   |    Date    | Count - Logins
   -------------------------------------------------
  SessionDescription | 2014-02-01 | 22
  SessionDescription | 2014-02-01 | 17

As you can see the count is being aggregated over the whole set and not each member individually. Here is the result I'm hoping to produce:

    Session Length   |    Date    | Count - Logins
   -------------------------------------------------
  Glimpse            | 2014-02-01 | 3
  Short              | 2014-02-01 | 4
  Medium             | 2014-02-01 | 9
  Long               | 2014-02-01 | 5
  Extended           | 2014-02-01 | 1
  Glimpse            | 2014-02-02 | 2
  Short              | 2014-02-02 | 5
  Medium             | 2014-02-02 | 7
  Long               | 2014-02-02 | 2
  Extended           | 2014-02-02 | 1

Any help would be appreciated. I know this can be achieved by modifying the DSV but I don't want to alter the Cube's structure.

Answer 1

You must create separate SessionDescription members if you want to see separate entries on the rows e. g. like this:

WITH
     MEMBER [SessionLength].[Session Length].Glimpse AS
            Aggregate([SessionLength].[Session Length].&[0])
     MEMBER [SessionLength].[Session Length].Short AS
            Aggregate([SessionLength].[Session Length].&[1]:[SessionLength].[Session Length].&[5])
     MEMBER [SessionLength].[Session Length].Medium AS
            Aggregate([SessionLength].[Session Length].&[6]:[SessionLength].[Session Length].&[30])
     MEMBER [SessionLength].[Session Length].Long AS
            Aggregate([SessionLength].[Session Length].&[31]:[SessionLength].[Session Length].&[90])
     MEMBER [SessionLength].[Session Length].Extended AS
            Aggregate([SessionLength].[Session Length].&[90]:[SessionLength].[Session Length].&[240])
     SELECT
     { [Measures].[Count - Logins] }
     ON COLUMNS,
    NONEMPTY({
             [SessionLength].[Session Length].Glimpse,
             [SessionLength].[Session Length].Short,
             [SessionLength].[Session Length].Medium,
             [SessionLength].[Session Length].Long,
             [SessionLength].[Session Length].Extended
             }
             * {[Date].[Date].[Date]})
    ON ROWS
FROM MyCube

By the way, I left the 90 member in both, Long and Extended as it was in your original query. If you do not want to double-count these, you should remove them from one.