MDX Query Issue

Question

MDX Query Issue

cmbrooks08

SSC Eights!

Points: 861
More actions
September 17, 2010 at 10:16 am

#240509

This MDX query runs fine-
SELECT
KPIValue("XXX") ON COLUMNS,
(
[Facility Attributes].[Latitude].Children,
[Facility Attributes].[Longitude].Children,
[Facility Attributes].[Building Name].Children
) ON ROWS
FROM
[Cube]
The issue we have is that some of the Lat and Longs are null, and we want to omit those from the result set. We tried a 'Non Empty', but we have too many arguments - does anybody know a work around to that?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

You might try setting up a member for Lat and one for Lon and then put the non empty there, and then use the member(s) in your query

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

cmbrooks08 (9/17/2010)
This MDX query runs fine-
SELECT
KPIValue("XXX") ON COLUMNS,
(
[Facility Attributes].[Latitude].Children,
[Facility Attributes].[Longitude].Children,
[Facility Attributes].[Building Name].Children
) ON ROWS
FROM
[Cube]
The issue we have is that some of the Lat and Longs are null, and we want to omit those from the result set. We tried a 'Non Empty', but we have too many arguments - does anybody know a work around to that?

I would suggest the following:

Step 1: During the ETL phase convert all Nulls to a default integer say -1

Step 2: During OLAP analysis phase filter cell records on the basis of [Value]>-1

Please let me know incase of any further explanation

Raunak J

cmbrooks08 SSC Eights! Points: 861 More actions · Answer 3

Thanks everybody!

Basically, we ended up filtering the records into an array and eliminated the null records. It seemed to work great!