SQL Server Performance

Urgent:can't Order cube after NON EMPTY is used

Discussion in 'Analysis Services/Data Warehousing' started by marialua, Aug 11, 2004.

  1. marialua New Member

    Hello

    I'm having quite a frustrating problem. My MDX statement
    returns data via a Matrix report (reporting services) that
    shows me monthly hits to a website on a user-by-user
    basis. It is displayed in Alphabetical order based on the
    users name.

    Sometimes hundreds of users can be returned (a 12 page
    report). It's the case that many of these users haven't
    made any hits to the site so they just have empty rows
    against them. I have used NON EMPTY to get rid of these
    empty rows like so:

    SELECT { Measures.members } on Columns ,
    NON EMPTY { Crossjoin( [Time].[Calendar].[2004].[May]:
    [Time].[Calendar].[2004].[July], [Business].[User].
    [Business Entity Id].[1583].Children ) } on Rows
    FROM UsageStats_Phase1

    This suppresses the empty rows, but messes up the
    ordering. People who have hits in May are ordered
    alphabetically, and people who don't are left out. then
    people who have hits in June are ordered alphabetically
    and everyone else is left out, etc.

    so the entire report isn't listed alphabetically. Only on
    a month by month basis, so unless you know that, it looks
    like people are missing from the report. Until you scroll
    down to the correct month for which they have hits.

    The other problem is that the months (columns) aren't
    showing up in order either. So instead of May, June,
    July... it's now something like June, May, July, etc...

    Does anyone know how I can reorder my results after
    suppressing the empty rows? any help would be very much
    appreciated!!

    Thanks in advance
    Maria
  2. stevefromOZ New Member

    Hi Maria,

    While you have users nested within the months, i don't think you'll be able to get the ordering the way you want it. I'm not sure how you would envisage this working, because if only users with a name starting with 'B' were active in May and then the 'A' users were active in June, how can the 'A' users get ordered before the 'B' users, as this would then break them out of the month in which they were active. An alternative is to nest the months within the users, so the query becomes

    SELECT { Measures.members } on Columns ,
    NON EMPTY {{[Business].[User].
    [Business Entity Id].[1583].Children} * {[Time].[Calendar].[2004].[May]:
    [Time].[Calendar].[2004].[July]}} on Rows
    FROM UsageStats_Phase1

    Another alternative is to not have the month on an axis at all (obviously this depends on the reports' user requirements), but you could create a tuple from the months and then use this as a filter. This would make the query something like

    WITH MEMBER [Time].[MyFilter] AS '[Time].[Calendar].[2004].[May] + Time].[Calendar].[2004].[June] +
    [Time].[Calendar].[2004].[July]'

    SELECT { Measures.members } on Columns ,
    NON EMPTY {[Business].[User].[Business Entity Id].[1583].Children} on Rows
    FROM UsageStats_Phase1
    WHERE
    [Time[.[MyFilter]

    The ordering of your months (of every level within every dimension really) is determined by the 'Order By' column in the properties page for the level (when editing cubes in Analysis Manager). You could use the ORDER function in your query, but this will probably require you to specify a measure to order the results by.

    Hope this helps.

    Steve

    HTH,

    Steve
  3. marialua New Member

    Hi Steve,<br /><br />Thanks so much for your reply and help. You solved my problem in one fell swoop!!! [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />The first suggestion you came up with worked! i.e. <br />SELECT { Measures.members } on Columns ,<br />NON EMPTY {{[Business].[User].<br />[Business Entity Id].[1583].Children} * {[Time].[Calendar].[2004].[May]:<br />[Time].[Calendar].[2004].[July]}} on Rows<br />FROM UsageStats_Phase1<br /><br />I Just copied and pasted from your mail and voila!!<br /><br />Thanks again!<br /><br />Maria (saved bacon) Bermudes<br />[8D]

Share This Page