Wednesday, March 7, 2012

How to implement shared member

We are doing a proof of concept to replace Essbase with SSAS. One of the tasks is to create a hierarchy a same member in two places. I know Analysis Services has not supported this feature in the past. Is there anything new in 2005 that would facilitate this?

Example:

Fee Type Dim

All Fee Types

FT1

FT2

FT3

FT4

FT5

FT6

Conversion Fee Types

FT2

FT4

Extension Fee Types

FT5

FT6

Thanks to some good posted hints from Richard Tkachuk, created a work around. So SSAS can't do true shared members well to my understanding neither can Hyerion Essbase in aggregate storeage mode. But what can be done is to create more members in the dimension that have no matching facts. Then write a few calc scripts to associate the measure data with the appropriate real member. Richards example was for a parent child heirarchy. Mine's a little simpler as I only needed a couple of attribute hierarchies. Here's some what I did to help any else that may need it. I'm also not the most experienced MDX coder so there may be a more efficient way to do this.

Changed Dimension Table: added a columns for an associated member, an attribute hierarcy, and a flag in dicating shared member.

CREATE TABLE [dbo].[FEE_TYP](
[FEE_TYP_CD] [char](3) NOT NULL,
[FEE_TYP_DESC] [varchar](62) NULL,
[FEE_TYP_ASSOCIATED] [char](2) NULL, -- Member Key for associated member
[FEE_TYP_SM_FLG] [char](1) NULL, -- Shared Member Flag
[FEE_TYP_HIERARCHY] [varchar](30) NULL) -- Attribute Hierarchy

Sample rows.

insert FEE_TYP values ('96', 'Conversion Refinance /', NULL, NULL, 'All Fee Typ')
insert FEE_TYP values ('A96', '96 - Conversion Refinance /', '96', 'Y', 'All Loan Cnvrt')
insert FEE_TYP values ('B96', '96 - Conversion Refinance /', '96', 'Y', 'Cnvrt Refi')
insert FEE_TYP values ('C96', '96 - Conversion Refinance /', '96', 'Y', 'RT Cnvrt Fee Chng')
insert FEE_TYP values ('D96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Fee Chng')
insert FEE_TYP values ('E96', '96 - Conversion Refinance /', '96', 'Y', 'TA Acq Move')

Created a hierarchy called All Curr Fee Types

Sample calc:

scope ({[FEE TYP].[All Curr Fee Types].members},[FEE TYP].[FEE TYP SM FLG].&Yes)
this = (StrToMember("[FEE TYP].[FEE TYP].&[" + [FEE TYP].[FEE TYP].currentmember.properties("FEE TYP ASSOCIATED") + "]"))
end scope
scope ({[FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Loan Cnvrt].children))
end scope
-- repeat for other members of the attribute heirarchy
scope ({[FEE TYP].[All Curr Fee Types].[ALL]})
this = (sum([FEE TYP].[All Curr Fee Types].[FEE TYP Group].&[All Fee Typ].children))
end scope

No comments:

Post a Comment