SQL Server Performance

Weird results from an MDX formula.

Discussion in 'SQL Server 2008 Analysis Services' started by IrishPirate, Apr 27, 2011.

  1. IrishPirate New Member

    New problem. I'm building a cube to calculate a percentage of full-time students. For undergrads, we divide the total credit hours by 30, grad students are divided by 24. The data is aggregated by campus, enrollment year and student level, and is added to the measures section (it's a sum based on a compound key). Next I create an MDX under the calculations using a simple IIF(). The function follows:
    iif( [Dim Student Level 1].[Dim Student Level].&[1], [Measures].[Reg Credit Hrs]/30, [Measures].[Reg Credit Hrs]/24 )
    Two problems arise: for some reason the logic is being reversed - all values in the undergrad column (level = 1) are firing the FALSE expression (divide by 24), while the opposite occurs for the second column; also, the very first row contains no results for undergrads, but the grad data is firing the correct expression.
    Has anyone had an experience like this?
    <<<<<Output and Code >>>>>>>
    2007 2007 2007 2007
    UnderGraduates Graduates
    Cr Hrs Avg Hrs Cr Hrs Avg Hrs
    BSU .00 .00 33.00 1.38
    FSU (null) (null) (null) (null)
    SU (null) (null) (null) (null)
    TU 564.00 23.50 333.00 11.10
    UMB 1,376.00 57.33 1,248.00 41.60
    UMBC 1,822.00 75.92 60.00 2.00
    UMCP 6,220.00 259.17 2,067.00 68.90
    UMES 562.50 23.44 (null) (null)
    UMUC 3,086.00 128.58 414.00 13.80
    UoB 222.00 9.25 234.00 7.80

    with
    member [Measures].[Avg Hours] as
    'iif([Dim Status Level 1].[Dim Status Level].&[1],
    [Measures].[Reg Credit Hrs]/30, //IF UNDERGRAD Divide by 30
    [Measures].[Reg Credit Hrs]/24)'//IF GRADUATE Divide by 24
    ,SOLVE_ORDER = 1, FORMAT_STRING = '#.##' //FOR SOME REASON THIS IIF IS WORKING IN REVERSE EXCEPT FOR THE FIRST ROW.
    select
    crossjoin ( //GROUP BY YEARS, THEN BY STUDENT LEVELS - UNDERGRADS = 1, GRADS = 2
    {[Reg Rc Yr Term].[Dim Year Term Fact].&[2007]:[Reg Rc Yr Term].[Dim Year Term Fact].&[2011]},
    crossjoin ( {[Dim Status Level 1].[Dim Status Level].&[1],[Dim Status Level 1].[Dim Status Level].&[2]},
    {[Measures].[Reg Credit Hrs],[Measures].[Avg Hours]})) on columns, //SUM CREDIT HOURS BY CAMPUS, YEAR AND STUDENT LEVEL

    {[Dim Fice 1].[DUSM Abrv].&[BSU]:[Dim Fice 1].[DUSM Abrv].&[UoB]} on rows //LIST ROWS BY CAMPUS ABBREVIATION
    from [Inst Rsch 7] //Namr of cube
    where [Dim Reg Center 2].[Dim Reg Center].&[1]
    The major question is what would cause the IIF to do the opposite of what I'm expecting, except in the first row?
  2. satya Moderator

    Interesting, did you try debugging the code in step mode to see the values.
  3. IrishPirate New Member

    Thanks for looking at my question. I haven't used the debugger in BIDS. I'm looking at it right now and will see what it yields. I've also updated my original post to include a copy of the output - I've reduced it for one year to keep it simple - as well as the MDX Statement which created the list. The problem is consistent between BIDS and the Server Manager using Analysis Services, so I'm sure it's something I'm overlooking.
    Thanks again for reply, I really appreciate it.
  4. satya Moderator

    Certainly your question is unique here, I'm not MDX expert but wanted to check through the ANSI SQL process to see if its possible or not.
    Anyways let me do some reasearch to see if its possible, this is interesting.
  5. IrishPirate New Member

    After a great deal of trial and error I stumbled into a solution...similar to a bear finding honey, lots of pain.
    I'm copying the code in my final answer:with
    member [Measures].[thingy] as [Dim Status Level 1].[Dim Status Level]'member
    [Measures].[Avg Hours] as'iif( [Measures].[Reg Credit Hrs] = null,"NULL",iif([Dim Status Level 1].[Dim Status Level].[Dim Status Level].&[1]<2,[Measures].[Reg Credit Hrs]/30,[Measures].[Reg Credit Hrs]/24))',
    SOLVE_ORDER = 10, FORMAT_STRING = '#.##'select
    crossjoin ( {[Reg Rc Yr Term].[Dim Year Term Fact].&[2007]:[Reg Rc Yr Term].[Dim Year Term Fact].&[2011]},crossjoin ( {[Dim Status Level 1].[Dim Status Level].&[1],[Dim Status Level 1].[Dim Status Level].&[2]},
    {[Measures].[Reg Credit Hrs],[Measures].[Avg Hours]}))
    on columns,{[Dim Fice 1].[DUSM Abrv].&[BSU]:[Dim Fice 1].[DUSM Abrv].&[UoB]} on rows
    from
    [Inst Rsch 7]where
    [Dim Reg Center 2].[Dim Reg Center].&[1]
    The messy part is the highlighted iif statement. Notice the first test for credit hours = null. The books all seem to indicate IS NULL is the correct method, but my = NULL gets it right. When I tried IS NULL I get incorrect values for the BSU Row. Also, the second test of iif([Dim Status Level 1].[Dim Status Level].[Dim Status Level].&[1]<2 originally didn't contain <2. In the various books I've used, they indicate that choosing a single member - in this case a status level that equals 1 - doesn't require an equals test. Also, if I use = 1, incorrect values are created, but the <2 fires the TRUE events and I get the correct answer. This has been quite an exercise. I also wonder how many times others have had similar results.
    Thanks for your help - hopefully I could contribute something useful.
    Terry

Share This Page