BookmarkSubscribeRSS Feed
metalray
Calcite | Level 5
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.
4 REPLIES 4
AngelaHall
SAS Employee
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
metalray
Calcite | Level 5
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.
AngelaHall
SAS Employee
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
metalray
Calcite | Level 5
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 : )

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2304 views
  • 0 likes
  • 2 in conversation