I'm having a bit of trouble creating a dimension with the MemberNamesUnique
and MemberKeysUnique property set to True for the dimension. For example I
use a test table as below. If I try and process a test dimension based on
the table with a single level for the test column and the member key for the
level set to dimid it fails when the length of the test column is >255 even
though the keys and names are clearly unique. If I set the level member key
to the test column it processes but there's only one member of the level
instead of 2. I've googled and searched the KB but can't seem to find
anything but it seems like a hard limit. Am I doing something wrong?
Basically I have a dimension of error messsages that are fairly long (but
unique) and can be exactly similar for the first 255 characters.

create table dim_test(dimid int,test varchar(1024))

-- ok
truncate table dim_test
insert dim_test select 1,replicate('a',254)+'b'
insert dim_test select 2,replicate('a',254)+'c'
select *,len(test) from dim_test

-- fail
truncate table dim_test
insert dim_test select 1,replicate('a',255)+'b'
insert dim_test select 2,replicate('a',255)+'c'
select *,len(test) from dim_test

Yes, we have run into it ourselves too - the length of the member property
cannot exceed 255 - it applies to names/keys as well. Should be fixed in
Yukon.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Jasper Smith" <jasper_smith9 (AT) hotmail (DOT) com> wrote

Thanks for the info, I assumed as much. Is there any workaround to this? The
scenario is I have a cube for SQL server job history and for one report
where users drilldown on a specific job that has failed I'd like to be able
to show which step it failed on and the error message (nvarchar(1024)). Can
I do this in AS or will I have to go to the underlying database to pick this
up ?

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Mosha Pasumansky [MS]" <moshap (AT) online (DOT) microsoft.com> wrote

Well I've found a workaround that seems to work okay. I've used the
messagekey in my fact table as a hidden measure and then actually run the
query in SQL and used a linked server to pass the MDX part to AS - something
like the query below. Not sure if this is the best way to do it or if you
have a better suggestion ? I'm using Reporting Services so it's not much
hassle having another data source.

select
"[Time].[Date].[MEMBER_CAPTION]",
"[Hour].[Time].[MEMBER_CAPTION]",
"[Measures].[FailedStep]",
m.message
from openquery
(JOBSOLAP, 'WITH SET [Failed Jobs] AS '' {Filter(
[Time].[Date].Members,[Measures].[Failed]>0)}''
select
Filter(CrossJoin({[Failed
Jobs]},{[Hour].[Time].Members}),[Measures].[Failed]>0) on rows,
{[Measures].[Failed],[Measures].[Messagekey],[Measures].[FailedStep]} on
columns
from Simple4
where ([Server Jobs2].[Jobname].&[1689])') t
join jobs_simple.dbo.dim_messages m
on t."[Measures].[Messagekey]" = m.messagekey


--
Cheers,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Mosha Pasumansky [MS]" <moshap (AT) online (DOT) microsoft.com> wrote

This looks like best workaround for AS2K.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================