Traditional web-based reporting with SAS BI tools

A duplicate member caption was found in

Reply
Regular Contributor
Posts: 207

A duplicate member caption was found in

Hello,
while building my cube I got the error
"ERROR: A duplicate member caption was found in "SUBCATEGORY" level for value "testmember". I used my sql tool to query the source table but there is nothing like testmember in the columns "SUBCATEGORY" *
what does this error really mean?
Of course there are duplicates in the column but each row is unique since its builds a hierarchy.

*of course, "testmember" is replace here by the real name.
SAS Employee
Posts: 238

Re: A duplicate member caption was found in

Essentially the error msg means that the 'testmember' value appears differently in the SUBCATEGORY level of your hierarchy. The next msg in your log should say "It may be that the same caption exists with different variants of upper and lower case characters, or with a different number of leading blanks. The MDX language is case tolerant so having one or more captions that differ only in the case or number of leading blanks makes the captions ambiguous."

SAS OLAP Cube build process is very sensitive. Meaning that if you have a dimension with members in different cases (such as 'U.S.A.' and 'U.s.a') or with extra spaces/special characters (such as 'My Movie Title ' and 'My Movie Title') then you will receive that error message. It basically is blocking you from creating separate levels for something that is likely the same.

I have seen similar when dealing with special characters in the data table (such as carriage returns and leading/trailing spaces). Clean data is imperative to OLAP cube building. You might not pick this discrepancy up in a sql query - b/c sql doesnt take into account spaces and special characters like SAS OLAP does.

I would suggest including a step in your source data creation to change anything that looks like 'testmember' to 'testmember' and rerunning the OLAP cube.

~ Angela
http://sas-bi.blogspot.com
Regular Contributor
Posts: 207

Re: A duplicate member caption was found in

Hi Angela,
Thanks for your reply. I still dont understand why a SAS OLAP cube cant be built.
Executing the following SQL on my Oracle staging database:

select distinct
MEASUREKEY,
SUPERCATEGORY,
CATEGORY,
SUBCATEGORY,
MEASURENAME,
SOURCE
from mytable

results in 743 rows. the total rows are 743. that means each row is a unique top to bottom hierarchy.
I dont understand why I get an error telling me their are duplicates.

You said "Essentially the error msg means that the 'testmember' value appears differently in the SUBCATEGORY level of your hierarchy"
Well, if it appears differently in the column subcategory than it is a different parent of MEASURENAME, correct?

the full error reads:
-----------------------------
ERROR: A duplicate member caption was found in "SUBCATEGORY" level for value "growthxxxxxxxxxxxxxxxxxx
xxxx
".
It may be that the same caption exists with different variants of upper and lower case characters, o
r with a different number of
leading blanks. The MDX language is case tolerant so having one or more captions that differ only i
n the case or number of leading
blanks makes the captions ambiguous.
ERROR: Hierarchy "DIM_KPI" could not be initialized.
ERROR: Cube "xxxxx" cannot be created.
---------------------------------------

There are not that many spaces in any of the rows under columns SUBCATGORY. A statement like this
select measurekey,subcategory from T_measures where subcategory LIKE '% %' results in not a single row returend. So I can not make out to what member name the error refers.
SAS Employee
Posts: 238

Re: A duplicate member caption was found in

From the error message: "growthxxxxxxxxxxxxxxxxxx
xxxx
".

It appears that the values in the SUBCATEGORY measure has trailing blanks or even special characters. You will need to clean the SUBCATEGORY variable prior to the OLAP cube step. I would suggest doing something like the following to review SUBCATEGORY values for special characters - this code replaces spaces with * and then prints out all the test values:

data test;
set orastaging.table;
new=tranwrd(subcategory, " ", "*");
run;
proc sql;
create table dist_new as
select distinct new from work.test;
run;
proc print; run;

~ Angela
Regular Contributor
Posts: 207

Re: A duplicate member caption was found in

Hi Angela,
Thanks for your suggestion. I solved the case now.

When I said
"Well, if it appears differently in the column subcategory than it is a different parent of MEASURENAME, correct?"
I was wrong. the member caption has to be unique regardless of upper and lower case characters.
I just did not understand the error : )
Post a Question
Discussion Stats
  • 4 replies
  • 1004 views
  • 0 likes
  • 2 in conversation