Analytic Workspace Manager
John Miller
there a simple example that I can download to give this thing a try.
I couldn't find anything under the 10g client or server but I did find
a "sales history" set of scripts under 9i. I ran the scripts sql
scripts to create a relational star schema and to populate it. The
script runs fine. Next, ran Analytic Workspace Manager to translate
the relational olap to an mdd. The wizard is able to create the schema
but when it comes to loading the data I get the following error.
Preparing
Processing Defining Load for Dimension: CHANNELS_DIM
Processing Refreshing Dimension : CHANNELS_DIM
Error Message from Server = Problem loading source data for:
SH
CHANNELS_DIM
SQL cursor 'CG' is not open.
In SHAW2!___GET.DIM.VALS.1.L PROGRAM:
sql close cg
SqlException = ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "OLAPSYS.DBMS_AWM", line 1012
ORA-06512: at line 1
Error Code = 6510
Sql State = 65000
Error Message from Server = Problem loading source data for:
SH
CHANNELS_DIM
SQL cursor 'CG' is not open.
In SHAW2!___GET.DIM.VALS.1.L PROGRAM:
sql close cg
SqlException = ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "OLAPSYS.DBMS_AWM", line 1012
ORA-06512: at line 1
Error Code = 6510
Sql State = 65000
REFRESH_WIZARD.PAGE.IMPORT_FAILURE.COULD_NOT_REFRESH
REFRESH_WIZARD.PAGE.IMPORT_FAILURE.CUBES_NOT_REFRESHED
java.sql.SQLException: ORA-06510: PL/SQL: unhandled user-defined
exception
ORA-06512: at "OLAPSYS.DBMS_AWM", line 1012
ORA-06512: at line 1
Tidying Up
Poking around a bit within the gui tool I was able to find the program
and "fix" the problem. This is what the program looked like:
trap on ERR noprint
sql declare cg cursor for select -
to_char('') -
from -
(select distinct -
from . -
where IS NOT NULL -
order by -
ASC NULLS FIRST -
) T
sql open cg
if sqlcode ne 0
then goto ERR
sql fetch cg loop into -
:append SHAW.SHAW2!CHANNELS_DIM
if sqlcode ne 100 and sqlcode ne 0
then goto ERR
sql close cg
sql cleanup
return 0
ERR:
trap on NOSIG noprint
sql close cg
NOSIG:
sql cleanup
return 1
It looked like the column name and table name were not filled in and
so I changed them to this:
sql declare cg cursor for select distinct to_char(channel_id) from
sh.channels
sql open cg
if sqlcode ne 0
then goto ERR
sql fetch cg loop into -
:append SHAW.SHAW2!CHANNELS_DIM
if sqlcode ne 100 and sqlcode ne 0
then goto ERR
sql close cg
sql cleanup
return 0
ERR:
sql close cg
NOSIG:
sql cleanup
return 1
Next I opened a window called the OLAP Worksheet and I ran a program
to load each dimension. I dumped out the values for the dimensions by
doing a:
report <dimension name> ;
and the data seemed to be there.
Next there was another program called:
___GET.CUBE.DATA_SALES_CUBE_1
I ran that assuming that the program loads the cube. Question: does it
indeed load the cube?
Lastly, ran a simple query but it was continuing to run
forever...something must be wrong.
Questions:
1)Has anyone been able to load data from the awm wizard. If so, what
steps did you take to get it to work.
2)Is the above program correct for loading data? Note, there are no
nulls.
3)How do I load the cube?
4)When trying to create an aggregate plan through the wizard, no
levels are shown. How do you tell the awm what levels there are in
each of the dimensions.
5)Is there another oracle demo I can try out that is more fool-proof?
Thanks