MS SQL Server OLAP - How to get NON EMPTY behavior when dim properties are aliased as m

Author

How to get NON EMPTY behavior when dim properties are aliased as m

Raghu

2006-03-27, 8:28 pm

Hi,

Let’s say we had the following information:

Employee Manager Dollar Sales
-------------------------------------------
Alice Alice null
Bob Alice null
Cindy Bob 200
Charlie Bob 250
Doug Bob null

Query 1:
I first had the following query -

SELECT NON EMPTY { [Measures].[Dollar Sales] } ON COLUMNS,
NON EMPTY { (DESCENDANTS([Employee Dim].[Employee hier].[Level 02])) } ON ROWS
FROM [Customer Sales Cube]

Output 1:
Employee Name Dollar Sales
-----------------------------------
Alice 450
Bob 450
Cindy 200
Charlie 250

Query 2:
Now I wanted the number of descendants at every node, total number of levels
in the hierarchy (Employee is a parent child hierarchy) and the level number
of every item. So I changed the query like this:

WITH

MEMBER [Measures].& #91;EmployeeHierLeve
ls] as '[Employee Dim].[Employee
hier].LEVELS.COUNT'

MEMBER [Measures].& #91;EmployeeHierLeve
lNum] as '[Employee Dim].[Employee
hier].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].& #91;NumOfDescendants
] as 'Count([Employee Dim].[Employee
hier].CURRENTMEMBER.CHILDREN)'

SELECT
NON EMPTY { [Measures].& #91;EmployeeHierLeve
ls],
[Measures].& #91;EmployeeHierLeve
lNum], [Measures].& #91;NumOfDescendants
],
[Measures].[Dollar Sales] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Employee Dim].[Employee hier].[Level 02])) } ON ROWS
FROM [Customer Sales Cube]

Output 2:
EmpName EmpHierLevels EmpLevelNum NumOfDesc Dollar Sales
Alice 3 1 5
450
Alice 3 2 0
null (DataMember)
Bob 3 2 4
450
Bob 3 3 0
null (Data Member)
Cindy 3 3 0
200
Charlie 3 3 0
250
Doug 3 3 0
null

Query 3:

I only wanted these additional properties for records that were qualifying
in Query #1. But the side effects of this query are:

a. I am seeing data for DataMembers.
b. I am seeing data for employees for whom the dollar sales were null before.

To resolve null DataMember problem (a) I modified the Query like this:

WITH

MEMBER [Measures].& #91;EmployeeHierLeve
ls] as '[Employee Dim].[Employee
hier].LEVELS.COUNT'

MEMBER [Measures].& #91;EmployeeHierLeve
lNum] as '[Employee Dim].[Employee
hier].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].& #91;NumOfDescendants
] as 'Count([Employee Dim].[Employee
hier].CURRENTMEMBER.CHILDREN)'

SELECT

NON EMPTY { [Measures].& #91;EmployeeHierLeve
ls],
[Measures].& #91;EmployeeHierLeve
lNum], [Measures].& #91;NumOfDescendants
],
[Measures].[Dollar Sales] } ON COLUMNS,

NON EMPTY {
(FILTER(
DESCENDANTS([Employee Dim].[Employee hier].[Level 02]),
NOT [Employee Dim].[Employee hier].CurrentMember is [Employee
Dim].[Employee hier].Parent.DataMember
)
)
} ON ROWS

FROM [Customer Sales Cube]

Output 3:

EmpName EmpHierLevels EmpLevelNum NumOfDesc Dollar Sales
Alice 3 1 5
450
Bob 3 2 4
450
Cindy 3 3 0
200
Charlie 3 3 0
250
Doug 3 3 0
null

How can I resolve problem b (i.e I don't want to see Doug's entry above)?
Any help is greatly appreciated.

Thanks
Raghu

Deepak Puri

2006-03-27, 8:28 pm

Hi Raghu,

One way to filter out rows wih no data is to set the Dimension Property
measures to null when there is no data:
[color=darkred]
WITH

MEMBER [Measures].& #91;EmployeeHierLeve
ls] as
'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
[Employee Dim].[Employee hier].LEVELS.COUNT)'

MEMBER [Measures].& #91;EmployeeHierLeve
lNum] as
'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
[Employee Dim].[Employee hier].CURRENTMEMBER.LEVEL.ORDINAL)'

MEMBER [Measures].& #91;NumOfDescendants
] as
'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
Count([Employee Dim].[Employee hier].CHILDREN))'

SELECT
NON EMPTY { [Measures].& #91;EmployeeHierLeve
ls],
[Measures].& #91;EmployeeHierLeve
lNum], [Measures].& #91;NumOfDescendants
],
[Measures].[Dollar Sales] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Employee Dim].[Employee hier].[Level 02])) }
ON ROWS
FROM [Customer Sales Cube][color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links