CODEWITHSUNDEEP
ssasmdxssas-2008
GoodHeartedOne
GoodHeartedOne

Reputation: 95

aggregate on a dimension name

I'm trying to aggregate the result of my query based on the names of members in one dimension. The following will return an error but this is as close as it gets to what I think:

with member [Staff].[Staff Hierarchy].x as 
aggregate ([Staff].[Staff Hierarchy].currentmember.name)

select [Measures].[a measure] on 0,
[Staff].[Staff Hierarchy].x on 1 
from 
(
select [Staff].[Staff Hierarchy].allmembers on 0
from [the cube]
)

Note: I'm basically trying to group by the names as we have multiple records in fact table for one person. Since we are using parent-child hierarchy, I cannot use the name of the staff as an attribute of my dimension.

More details: I'd like to do something similar to this but instead of "cases" I need the employee names. This would be very similar to group by.

Upvotes: 1

Views: 628

Answers (2)

Frank Goortani
Frank Goortani

Reputation: 1435

The OPENQUERY must be the last resort as it has serious performance issues. The alternative We've found is as follows:

MEMBER [Staff].[Staff Hierarchy].[Employee number 1] AS
Aggregate ( [Staff].[Staff Hierarchy].&[101], [Staff].[Staff Hierarchy].&[102] )

to automate creating members for all employees, We wrote a query like this: 

Declare @mdx varchar(max)
Declare @thisStaffName varchar(500)
declare @thisStaffMember varchar(500)
declare @ThisStaffBusinessKey int
Declare @ThisStaffSurrogateKey int
Declare @TheSet varchar(max)='set CustomEmployeeSet as {[Staff].[Staff Hierarchy].['

set @mdx = 'with member [Staff].[Staff Hierarchy].[' 
Declare  staff_Cursor Cursor for 
select distinct  top 10 BusinessKey from DIM_Staff order by BusinessKey

open staff_cursor

Fetch Next from staff_Cursor into @ThisStaffBusinessKey

while @@FETCH_STATUS = 0
BEGIN
    print 'APN: ' + cast(@ThisStaffBusinessKey as varchar(200))
    select  @thisStaffName = STAFF_HIER_LEVEL_CD + '-' + FIRST_NM + ' ' +LAST_NM + '-' + CAST(BusinessKey AS VARCHAR(200))
    from DIM_Staff 
    where BusinessKey=@ThisStaffBusinessKey 

    set @TheSet =@TheSet + @thisStaffName + '],[Staff].[Staff Hierarchy].['
    set @mdx = @mdx + @thisStaffName + '] as aggregate('

    declare This_Staff_Cursor Cursor for 
    select STAFF_HIST_ROW_ID from DIM_Staff
    where BusinessKey=@ThisStaffBusinessKey


    open This_Staff_Cursor

    Fetch Next from This_Staff_Cursor into @ThisStaffSurrogateKey

    while @@fetch_status =0
    begin
        print 'SHRI: ' + CAST(@ThisStaffSurrogateKey AS VARCHAR(200))
        set @mdx = @mdx + '[Staff].[Staff Hierarchy].&[' + CAST(@ThisStaffSurrogateKey AS VARCHAR(200))
        Fetch Next from This_Staff_Cursor into @ThisStaffSurrogateKey

            SET @mdx = @mdx + '],' 
    END
    set @mdx = left(@mdx, len(@mdx)-1)
    set @mdx = @mdx + ') member [Staff].[Staff Hierarchy].['
    close This_Staff_Cursor
    DEALLOCATE This_Staff_Cursor
    Fetch Next from staff_Cursor into @ThisStaffBusinessKey
END
set @mdx = left(@mdx, len(@mdx)-34)
set @TheSet = left(@TheSet, len(@TheSet)-28)
set @TheSet = @TheSet + '}'
set @mdx = @mdx + ' '+@TheSet
close staff_cursor
deallocate staff_cursor
SELECT @mdx + 'select [the measure] on 0, CustomEmployeeSet on 1 from [the cube] '

Upvotes: 1

whytheq
whytheq

Reputation: 35557

A measure should be aggregated already in the cube. In our cubes a script like the following would create "grouped" results per staff member

WITH MEMBER [Measures].[memberName] AS
'[Staff].[Staff Hierarchy].CurrentMember.name'
SELECT 
    {[Measures].[a measure], [Measures].[memberName]} ON COLUMNS,
    [Staff].[Staff Hierarchy].members ON ROWS  //<< is there not a level? [Staff].[Staff Hierarchy].[a Level].members ON ROWS
FROM [the cube]

Upvotes: 0

Related Questions