July 20th, 2011 by Stewart Bryson
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 18.104.22.168 of Analytic Workspace Manager (AWM), where indexing of all attributes is a binary choice. However, in 22.214.171.124 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.