# Weird results from an MDX formula. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

# Weird results from an MDX formula.

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].&, [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
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].&,
[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].&:[Reg Rc Yr Term].[Dim Year Term Fact].&},
crossjoin ( {[Dim Status Level 1].[Dim Status Level].&,[Dim Status Level 1].[Dim Status Level].&},
{[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].&
The major question is what would cause the IIF to do the opposite of what I’m expecting, except in the first row?

Interesting, did you try debugging the code in step mode to see the values.

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.

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.

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].&<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].&:[Reg Rc Yr Term].[Dim Year Term Fact].&},crossjoin ( {[Dim Status Level 1].[Dim Status Level].&,[Dim Status Level 1].[Dim Status Level].&},
{[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].&
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].&<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

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |