Data from applications (security, licenses, etc.) is replicated and stored into a data warehouse. Currently, an Oracle database is a prerequisite for the data warehouse database, but other databases are in the process of being certified.


1) Requirements


-- TABLESPACE

 CREATE TABLESPACE "NOMASX1" DATAFILE 

 SIZE 5G

 AUTOEXTEND ON NEXT 104857600 MAXSIZE 10G

 NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192

 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 

 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

  

-- USER

CREATE USER "NOMASX1" IDENTIFIED BY  ...   

DEFAULT TABLESPACE "NOMASX1"

TEMPORARY TABLESPACE "TEMP";

          

-- QUOTAS

ALTER USER NOMASX1 QUOTA UNLIMITED ON NOMASX1;

ALTER USER NOMASX1  QUOTA UNLIMITED ON NOMASX1;


-- ROLES

GRANT "GATHER_SYSTEM_STATISTICS" TO NOMASX1 ;

GRANT "CONNECT" TO NOMASX1 ;

GRANT "RESOURCE" TO NOMASX1  ;

ALTER USER NOMASX1  DEFAULT ROLE "CONNECT","RESOURCE";


-- SYSTEM PRIVILEGES

GRANT CREATE ANY INDEX TO NOMASX1 ;

GRANT ALTER ANY TABLE TO NOMASX1 ;

GRANT SELECT ANY TABLE TO  NOMASX1;

GRANT DELETE ANY TABLE TO NOMASX1 ;

GRANT DROP ANY TABLE TO NOMASX1 ;

GRANT ANALYZE ANY TO NOMASX1 ;

GRANT CREATE ANY INDEXTYPE TO NOMASX1 ;

GRANT DROP ANY INDEX TO NOMASX1 ;

GRANT UPDATE ANY TABLE TO NOMASX1 ;

GRANT INSERT ANY TABLE TO NOMASX1 ;

GRANT UNLIMITED TABLESPACE TO NOMASX1 ;

GRANT CREATE ANY TABLE TO NOMASX1  ;


-- JDEDWARDS SPECIFIC FUNCTION FOR DATE CONVERSIONS

create or replace FUNCTION  NOMASX1.JULIAN2GREGORIAN ("JDATE" IN NUMBER) RETURN DATE    IS GDATE date;  

BEGIN                                                                            

if (JDATE <>0) then                                                             

select (to_date((select ('01/01/'|| (select trunc(((JDATE+1900000)/1000),0) from dual)) from dual),    

'dd/mm/yyyy')+(select mod((JDATE)+1900000,1000) from dual) )               

into GDATE                                                                 

from dual;                                                     

return GDATE-1;                                                  

else                                                           

return null;                                                     

end if;                                                          

END;


2) Setting up the data warehouse


An installation wizard is provided and available when installing client workstations.



The creation of tables and configuration data feeding is automatic via the wizard



NOMASX-1 is ready !!!