It's all about the answers!

Ask a question

How to use manual steps to create data warehouse (Oracle)


Daniel W (13710) | asked Aug 23 '13, 10:53 a.m.
retagged Aug 26 '13, 5:19 p.m. by Amy Laird (16514)
In our environment, there will NOT be DBA permission allowed. Is there any manual steps to follow to create the data warehouse? At least the guidelines like what User/schema need to be created.
We have RRC, QM, CCM installed along with RTC.

3 answers



permanent link
Francesco Chiossi (5.7k1719) | answered Aug 23 '13, 11:14 a.m.
Hello Daniel,

this article could be a good start:
More Control over the Oracle Data Warehouse Setup

In version 4.0.3 you can also generate the DW creation scripts that you can review:
Repository tools command to generate data warehouse creation scripts

Best Regards
Francesco

permanent link
Sandy Grewal (1.6k1123) | answered Aug 26 '13, 3:44 p.m.
JAZZ DEVELOPER
Daniel,
If you are a version before 403 there are no manual steps available automatically in the product.
You will need to contact IBM Support and ask for scripts for your database vendor and we can provide them.

Sandy

permanent link
Daniel W (13710) | answered Aug 23 '13, 11:21 a.m.
We are actually using 4.0.
So the 4.0.3 probably doesn't apply to us.
I did read over the first article without actually put it into run.

Which it came down to this script. Is this script sufficient enough to create the data warehouse?
We have all those RIDW, RIODS....etc created.
Script:
CREATE USER <etl db user> PROFILE DEFAULT 
IDENTIFIED BY <password> DEFAULT TABLESPACE USERS
ACCOUNT UNLOCK;

ALTER USER <etl db user> TEMPORARY TABLESPACE RIS_TEMP;
ALTER USER <etl db user> QUOTA UNLIMITED ON VNF_32K;
ALTER USER <etl db user> QUOTA UNLIMITED ON VNF_IDX;
ALTER USER <etl db user> QUOTA UNLIMITED ON USERS;
ALTER USER <etl db user> QUOTA UNLIMITED ON VSTR_32K;
ALTER USER <etl db user> QUOTA UNLIMITED ON VSTR_IDX;

GRANT CREATE SESSION TO <etl db user>;
GRANT CONNECT TO <etl db user>;
GRANT ANALYZE ANY TO <etl db user>;

BEGIN
FOR TABNAME IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER IN ('RIODS','RIDW','CONFIG','RIASSET','RISCHK','RICALM'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT,UPDATE,DELETE,INSERT ON ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" TO <etl db user>';
END LOOP;
END;
/

BEGIN
FOR VNAME IN (SELECT OWNER,VIEW_NAME FROM ALL_VIEWS WHERE OWNER IN ('RIDW','RICALM')) 
LOOP 
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || VNAME.OWNER || '."' || VNAME.VIEW_NAME || '" TO <etl db user>';
END LOOP;
END;
/

Your answer


Register or to post your answer.