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].&)
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