MDX – Handling Division by zero or null

Posted on September 29, 2010 by marcel

 Sometimes it is necessary to implement divisions using MDX. And what happens when the divisor is a zero or a null? What to do? Well many bloggers and Mosha  have already handled that. Although in special cases you are not able to use this straightaway. An example:

You want to calculate some values, using MDX that is generated automatically, depending on other values. Furthermore you have formulas that use division, and those are created dynamically. Something like ((A+B)*0.5/(C+D)*0.5)*0.5 whereas {A,B,C,D} are values in cube.

So what to do? At first it’s necessary to know what the result of a division by zero or null is. Well this is easy to test.  Listing 1 shows the results from a mdx query that use the calculation, whereas x > 0.

Calculation Results How to handle it
0 / NULL 1.#INF [Measures].[Formula] > 1
NULL / NULL NULL [Measures].[Formula] = 0
0 / 0 -1.#IND [Measures].[Formula] = 0
NULL / 0 NULL [Measures].[Formula] = 0
X / NULL 1.#INF [Measures].[Formula] > 1
NULL / X NULL [Measures].[Formula] = 0
X / 0 1.#INF [Measures].[Formula] > 1
0 / X 0 [Measures].[Formula] = 0
? -1.#INF [Measures].[Formula] < -1

 

You see that sometimes the results are like ±1.#INF  (infinity) or ±1.#IND (not defined). Once you are able to handle the formula as a member you may check if this is in the range -1 <= 0 <= 1. An MDX Statement could be something like:

iif([Measures].[Formula] < -1 or [Measures].[Formula] > 1 or [Measures].[Formula] = 0, null ,[Measures].[Formula]))

You may want to test it with:

WITH

MEMBER [Measures].[Formula] as 1/0

MEMBER [MEMBER_NAME] AS

(

iif([Measures].[Formula] < 1 or [Measures].[Formula] > 1 or

[Measures].[Formula] = 0, null ,[Measures].[Formula])

)

select {[MEMBER_NAME],[Measures].[Formula]} on 0 from [CUBE_NAME]

Posted in MDX, SSAS | Leave a comment

SSIS and the limit of 8000 chars in a string variable

Posted on September 26, 2010 by Daniel

Another limitation of SSIS, which drives me nuts is the fact, that a string variable cannot be filled with more than 8000 chars via an Execute SQL Task for example. After doing some research on this topic I found a very helpful blog post in which a workaround is mentioned and that happily works fine for me.

My scenario: I have a dataflow, in which i pull data from four different excel sheets, which all have the same basic structure, but differ in the number of columns. So I build my corresponding SQL on the DB and would like to pass this VARCHAR (which definetly is larger than 8000) to a string varibale to use it as statement in the data flow. So, this task then fails with the following error message “The type of the value being assigned to variable “User::xxx” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.” My first workaround was to cast the value given back from the query as varchar(60000) which results in the following error message “The size (16000) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).” So no luck with this either….

I then found this blog entry http://blog.boxedbits.com/archives/6 (Langstons’s answer) which led to a working solution although it is more a workaround.

In the first step I call a scalar valued function which returns a loooong string in just onw row and one column. I set the Result Set to “Full result set” and pass that set to a variable of type object. This object variable is then used in a for each loop container choosing the Enumerator “Foreach ADO Enumerator” and taking the object variable as source variable. Inside the variable mapping I map the first column of the result set to my string variable and voila! You now have the looong string inside the variable which I can work with now in my data flow. To be honest I have no idea why it works that way neither has Langston. Still glad he pointed me to that solution ;)

Cheers Langston

Posted in SQL Server, SSIS, Uncategorized | Leave a comment