Oracle OLAP 11gR2 and Single-line Indexed Attributes

Just a quick post today to demonstrate an issue I ran into with an Oracle OLAP 11gR2 dimension today. I was maintaining the dimension when I encountered the following error:

I found Oracle support document 1258925.1, which has a handle on the problem. It describes a deficiency OLAP has with indexing attributes that contain newline characters. However... the note references version 11.2.0.1 of Analytic Workspace Manager (AWM), where indexing of all attributes is a binary choice. However, in 11.2.0.2 of AWM, we can choose to index on an attribute-by-attribute basis, as seen in the attribute details pane:

So here's what I know: I have an indexed attribute with newline characters. ETL processing and data quality notwithstanding (that seems like a strange thing to survive an ETL process), I need to find out which attribute to un-index. Problem is... there is nothing in the maintenance logs that tell me which attribute is the problem. (By the way: if this information exists and you know where it is, then please comment and I'll update the blog post.)

So I wrote this little piece of PL/SQL that did the trick for me, and I wanted to share it. CHR(10) is the construct we use in PL/SQL to denote a newline character, so I construct a query against each column to determine whether it contains any:

SQL> DECLARE
  2     l_table      VARCHAR2(30)    := 'DIM_FUND';
  3     l_results    NUMBER;
  4  BEGIN
  5     FOR x IN ( select 'select count(*) from '
  6                       ||l_table
  7                       ||' where regexp_like('
  8                       ||column_name
  9                       ||', chr(10))' stmt,
 10                      column_name
 11                  from dba_tab_columns
 12                 where table_name=l_table
 13              )
 14     LOOP
 15  --      dbms_output.put_line( x.stmt );
 16
 17        EXECUTE IMMEDIATE x.stmt
 18        INTO l_results;
 19
 20        IF l_results > 0
 21        THEN
 22           dbms_output.put_line
 23           ( x.column_name
 24             ||': '
 25             || l_results
 26           );
 27
 28        END IF;
 29
 30     END LOOP;
 31
 32  END;
 33  /
FUND_FIN_MANAGER_TITLE: 3

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.82
SQL>

This did the trick! I un-indexed the attribute in AWM and the dimension maintenance procedure was successful.