Disclaimer: I am still learning and dont consider myself as a expert, so if you are following this to setup your own Warehouse, you are on your own. So, please dont do this directly for Production setup. Comments/suggestions on the steps I followed are most welcome.
In this post, I would like to share my experience of setting up a Data Warehouse (henceforth will be mentioned as DW) with Clarity DB on SQL Server as Source Data along with some additional data loaded from excel files and Oracle DB as the DW.
Setting up a DW is almost similar to setting up any database with some differences (which I would mention). This is what I have observed. If you feel there is some other differences as well, please share.
So, here are the steps I followed:
1. Setup Temp folder.
Most often, in a new environment, Temp folder related to your login will point to the default location. Its desirable (and maybe advisable?) to set it to "C:\Temp" or similar directory structure since some installations dont prefer having space in the Temp directory path.
2. If you are using a DHCP enabled IP address (if you dont have static IP), then please install Microsoft Loopback adapter and assign it to a static IP.
I setup the adapter following steps outlined in "http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/df1dbf9f-58fa-4ac1-b019-8f86863371b7/". There could be other blogs that show you how to do it. I set the IP to "10.10.10.10" and Subnet Mask to "255.255.255.0".
3. Setup Java, create JAVA_HOME variable and include in PATH. Ensure no space in Java installation directory.
I installed jdk1.6.33 and installed it on "C:\Java" folder. Created JAVA_HOME variable as "C:\Java\jdk1.6.0_33" and set included it in PATH as "%JAVA_HOME%\bin;".
4. Install Oracle DB with "Software Only" option
Setup only Oracle software since we would use "DBCA" to create DB with different set of parameters tuned for DW. Try setting up Oracle on a different drive or different partition.
5. Create DW database using DBCA
While creating, ensure to select the DB template as "Data Warehouse", and I have selected UTF8 as Character encoding with "STAR_TRANSFORMATION_ENABLED" and "QWERTY_REWRITE_ENABLED" set to "TRUE". Also, ensure to set "NLS_DATE_FORMAT", "NLS_TIMESTAMP_FORMAT" and "NLS_TIMESTAMP_TZ_FORMAT". These parameters prove a lot useful when you try loading date information from Clarity into DW.
There are some other useful parameters that could be set while creating the database. Please refer to the article "http://docs.oracle.com/cd/E11882_01/olap.112/e17123.pdf", especially page 122.
Ensure to provide adequate memory allocated to the DB and try setting all accounts with same password (at lease easier for me to remember).
6. Setup ORACLE_UNQNAME environment variable
Setup ORACLE_UNQNAME environment variable and set it to the DB name you chose in Step 5.
7. Unsecure DB Console
Unsecure DB Console if you want to access "Enterprise Manager". When I tried accessing EM, I was getting strange characters and not seeing login page. When I unsecured the console, was able to login to EM.
Run the following command to unsecure: "emctl unsecure dbconsole"
Note: Depending on security policy you have in place, you may or may not do this step.
8. Setup OLAP tablespaces.
OLAP prefer different kind of tablespaces than what comes default with the DB. You could create these tablespaces by following the steps outlined in the link "http://docs.oracle.com/cd/E11882_01/olap.112/e17123.pdf".
Caution: When I created the UNDO tablespace and set EM to the new UNDO tablespace and restarted the DB, the instance wont come up and so had to revert back the changes. But, other tablespaces and settings has not created any problems so far. If you know what was preventing the DB from coming up, please do share.
Rest of the steps are applicable if you are using "ORACLE DATA INTEGRATOR (ODI)" as your ELT tool and "OBIEE" as your BI tool.
9. Create a new schema for ODI to store temporary tables.
10. Run RCU to setup repository for ODI and OBIEE
10.1 On Screen 2, provide your DB hostname, port (default 1521), Service Name (you can use netca to find your Service Name), Username and Password for sys/system account.
10.2 On Screen 3, choose "Oracle Data Integrator" and "Oracle Business Intelligence" from existing option. You dont need to change the default Schemas, but do note down them as you would need them while setting up ODI and OBIEE.
10.3 On Screen 4, provide the passwords for the Schemas for ODI and OBIEE. You could use same password like I did or provide different for each schema. Ensure to remember them as you would need them later.
10.4 On Screen 5, you setup ODI related parameters. For options 1, 4, 5 and 6, you can choose the default values which are 001, D, 001 and WORKREP. Provide Supervisor and Work Repository passwords.
Proceed with rest of the steps to setup the Repositories in the DW.
11. Install ODI by running the command "setup.exe -jreLoc C:\Java\jdk1.6.0_33" from Disk1 folder.
Change "C:\Java\jdk1.6.0_33" to location where Java is installed in your environment.
Will update rest of the steps later....
No comments:
Post a Comment