Analytic Workspace Manager

From: John Miller <mr_john_miller_at_yahoo.com>
Date: 14 Jun 2004 11:28:26 -0700
Message-ID: <c5dd3eb6.0406141028.53285e13_at_posting.google.com>

I've installed the 10g client and am able to get the awm working. Is 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 Received on Mon Jun 14 2004 - 20:28:26 CEST