Hello,

In my fact table 3 columns namely TimeStamp, Temperature,
Location_Area
It has 4 records as follows (CSV Format):
---------------------------------------
TimeStamp Temp Location
---------------------------------------
2004-04-20 12:00:00.000, NULL, 1
2004-04-20 12:00:00.000, NULL, 2
2004-04-20 12:00:00.005, 5, 1
2004-04-20 12:00:00.005, 10, 2
---------------------------------------
where for location 1 is USA and 2 is UK. There is a lookup table for
location (Star schema).

I have created a cube with 2 dimensions:
1. A Time dimension (Year-Month-Day-Hour-Minute) based on Timestamp
column.
2. Location Dimension based on the Location column from fact table.

I have created 2 measures - Min_Temp and Max_Temp; which represent the
MIN and MAX temperature based on the Temperature column from fact
table.

The problem I am facing is that my Min_Temp for 12:00 is always NULL.
I was expecting MIN(NULL,5)...to be 5. The MAX(NULL,5) is coming to be
5, as expected. So is there a way I can ignore the NULLs while
calculating Min_Temp?

I tried building a calculated measure for Min_Temp, but it doesn't
work.
Here is the MDX query I tried:
Iif(([Measures].[Min_Temp] is NULL) or ([Measures].[Min_Temp]<= 0) ,
[Measures].[Max_Temp], [Measures].[Min_Temp])

Could someone tell me how I can ignore the NULLs while calculating a
Minimum Measure?

Thanks.

This is a well-known issue when dealing with null data:

http://support.microsoft.com/default...b;en-us;244650
...
Question: How does OLAP Services read NULL measure data in a fact table?

Answer: If there is no record in the fact table for a certain
combination of dimension members, (such as [1997].[Quarter1].[January],
[USA].[Texas].[Dallas] ) then, on cube processing, OLAP Services treats
the corresponding cell in the cube as empty. However, when there are
records in the fact table that have one or more null measure columns,
OLAP Services assigns a 0 value to the corresponding cells in the cube.

This behavior may not always be desirable, particularly, when the user
has to make a distinction in the cube's measure between a fact table
NULL and zero values. If such a distinction must be made, then the user
has to make sure that the fact table (or tables) do not have records
with empty measure columns. One solution is to break these empty measure
columns into different fact tables. The steps necessary to implement
this solution are:

Create multiple fact tables. One for each measure that needs a
distinction to be made between a NULL and a zero value.

Create a cube based on each fact table.

Create a virtual cube that includes all the base cubes to view all the
measures together.
In your case, presumably the fact records with NULL need to be excluded.
A simple solution would be to configure a SQL
"Source Table Filter" expression for the cube, like:

"FactTable"."Temp" is not null


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!