Data Warehouse (Oracle warehouse builder)

I am going to go through some of the settings within Oracle Warehouse Builder. First, I go through the Runtime Parameters. If you right-click on a mapping and hit "configure" you can expand a list of options. One of the is Runtime Parameters.

Default Operating Mode 

Using Row Based will be slooooow. Why? Because you are using cursors, and so, it is like a for loop so, it is essentially

for a in <select> ...update or insert

This is good if you want to see the warnings/errors to know why set based is not loading.

Set based mode is basically putting your mapping into one ginormous sql statement. This is fast. Why is it fast? I am glad you asked. Because, as the name implies, you are using a set of data, not a row of data. Once the data loads, since its in one big chunck, it will appear in the target tables all at once. You won't use the added layer of PLSQL. The drawback is say you have a Foreign Key constraint violation. In row based it would roll back that record and continue. In set based it will roll the WHOLE thing back and no data would be loaded and you would be confused. You can combine these two with Set Based with row based as fail over. So,  if the set based fails, it will go into row based.

Faculty Load

Starting to get back into faculty workload. I have an understanding of the processes used for this but it is trickier when trying to see if you can accommodate all the business rules with what is there for faculty workload. I have 9 month contract set up and they are salaried, but when extracting them, it looks as if they are getting paid their annual salary for each term. Not sure if payroll will set that right, or if we run annual people through for one term, or if we set them up on a contract much like the term based ones.

10/15/12

The past couple weeks, I have been preparing for the FIS/HR upgrade that is coming in November. Job Submission was not working correctly, and we got it working like it does in production. I need to get together with Soukup before the upgrade to make sure I am aware of everything that I will need to do.

Bruce and I talked a bit and found a way to record the distance someone lives from our University using google maps api. It will  be interesting to get that measurement and see if it brings useful information. Not sure if Google will allow us to hit it that many times to get the latitude and longitude.

We have Eric's request about set with the data warehouse. Bruce is trying to adjust the report in Cognos to show the way that Eric would like. I need to go through and really polish the ETL for the whole data warehouse:

1. Add in some of the transformations to the ETL (Some of them were done on the fly for testing)
2. Redo student to enrollment, so it has the lowest level of detail?
3. Add a mapping in student/enrollment for the one that we deleted. (to not load 2 things in one mapping)
4. Possibly rename some things and have a better process for ETL development
       a. When to bind dimensions/cubes
       b. When to deploy, just to see if there is a faster way.
5. Build easier to get to data (if student/enrollment is redone)
6. Revisit the demographic for student.
7. Revisit the academics for student.

I have also been setting up some cron jobs on wouupg and getting up to speed with what needs to be done.

Data Warehouse

This week we have been working on answering Eric's question with the data warehouse. I feel like the planning made before hand was good but I can now see where it can  be polished a little more:

1) First thing is ALWAYS add time to research and understand how to answer the question in production   and how things are linked. When we planned this, we just thought of the work to make the dimensional model.

2) Time to Build and load ETL + time to link in Framework Manager + time to make reports + research time + testing time = total time. Keep in mind while testing, this might mean going back through the whole process (hopefully not, but it happens).

While doing this I was able to squeeze in fixing the leave in production. Banner figures leave a little different than how we used to figure leave so we are going to match  how Banner works with leave rather than the other way.

Busy week

This week has been busy. I have spent a bit of time trying to get FUPLOAD to a point where Eric and Michele can use it all on there own. There was a problem with DOS to Unix formatting, but I was able to solve it with some Visual Basic behind the spreadsheet. Apparently, changing Microsoft does not like to list out their file format codes, but changing the file format to 'xlTextMac' from 'xlText' solved the problem because Mac formatting is the same as Unix, which makes me happy :)

I am starting on more of the data warehouse. I am planning the ETL and smoothing out what each dimension should look like. It seems all the FOAPAL elements are hammered out. Just need to figure out how to place an entity as a vendor, student, staff, faculty and so on. Currently, I am thinking of grabbing pidms and looking them up and seeing what they are and place them according, so, I think I will make a function that takes a pidm and inserts into a temp table.

Restoring table to a certain time

I recently made a mistake while doing a non standard update in banner and had to call Jim Rouff to restore the table to a previous point in time. I have been reading some of  a book that Christina has and read about FLASHBACK. Now, if I make that same issue, I can resolve it myself. Example

FLASHBACK TABLE <table name> to timestamp systimestamp - interval '0 00:02:00' day to second;

This will restore the table to what it was 2 minutes ago, so you can see how the interval works and where seconds, hours, and (I think) days.

You may get an error/warning that the row movement is not enabled so then do this:

ALTER <table name> ENABLE ROW MOVEMENT;

then after the flashback you can disable row movement again with:

ALTER <table name> DISABLE ROW MOVEMENT;

No need to call Jim again :)

NOTE: If the structure of table was changed (like dropped column) then the FLASHBACK won't work. But, if you drop a table you can use:

FLASHBACK <table name> TO BEFORE DROP;

A dropped table is restored from the oracle recycling bin, so, if it is purged, it is lost. Restoring to a point in time is different. Oracle Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user executes an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

If you want to just select from a point in time you can do this:

select * from <table name>  as of timestamp systimestamp - interval '0 06:00:00' day to second;

That will select from the table as of 6 hours earlier.