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.

AJAX Cross domain with PHP and PL/SQL using JSONP

Currently, if you try to use a regular ajax call cross domain there is a same origin policy that will not allow you to do it. One way around this is using JSONP (JavaScript Object Notation with Padding). The name makes no sense and trying to think what the padding means is useless. But this will allow cross domain communication and that is the important part. On my P drive I made a PHP page like this:

<?php
header("content-type: text/javascript");

    if(isset($_GET['name']) && isset($_GET['callback']))
    {
        $obj->name = $_GET['name'];
        $obj->message = "Hello " . $obj->name;

        echo $_GET['callback']. '(' . json_encode($obj) . ');';
    }
?>


The JSON is $obj, and the encoding will format it to a Javascript object notation to allow accessing $obj name and message.

Next, the PL/SQL page:

create or replace procedure ajax_test is
begin
  htp.prn('
<!DOCTYPE html>
<html lang="en">
  <head>
    <title>JQuery JSONP</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script>
    $(document).ready(function(){

        $("#useJSONP").click(function(){
            $.ajax({
                url: "http://www.wou.edu/~braken/test_stuff.php",
                data: {name: "Chad"},
                dataType: "jsonp",
                jsonp: "callback",
                jsonpCallback: "jsonpCallback",
                success: function(){
                    alert("success");
                }
            });
        });

    });

    function jsonpCallback(data){
        $("#jsonpResult").text(data.message);
    }
    </script>
  </head>

  <body>
    <input type="button" id="useJSONP" value="Use JSONP"></input><br /><br />
    <span id="jsonpResult"></span>
  </body>
</html>
  ');
end ajax_test;



With this, as soon as you hit the button, it will call the http page and send it the data "Chad" then the PHP page will get that name and make a $obj->name and message, and then will encode it in JSON. Then it will call the function named in jsonpCallback in the ajax call which will take the data and display it. Notice, the message, since it was encoded in JSON, can be accessed as data.message which is the $obj from the PHP page.

Stuff and things

I've noticed that Soukup is taking on a lot of stuff with Student and FIS/HR. I really need to get to the point where he can worry about just Student. I hope it doesn't take a long time for this to happen.

This week we have been working on the leave accrual. Mike is taking Wendler's old script and rewriting it. We were also talking about putting it into job submission that way we no longer have to worry about it. We can teach them how to do it then we can wash our hands clean from making mistakes.

Second week

I spent some time this week using grails for the first time. I like it. It does a good job cutting out things that are tedious to code. I can make a class/entity and just write "def scaffold = class/entity" and all the add, update, delete functionality is automatically made. Validation is made for you as well. If you declare an int in the class, and try to insert a letter, it will show an error message by that box.

Today, I met with Michele and she went over the Procurement Card processes and listed out the where I would come into play with any issues. We talked a little about faculty load and when she has more time we are going to talk more about where Huber was with the project.

First Week

This week has been nothing but learning. Dorothy came over to help me with navigating through the Banner forms. The first day, I felt I didn't know where to start so that was really helpful. Michele also came over and explained a few things for me and actually has some training set up for me :) It's nice that I'm not wandering in the dark.

A lot of the other time has been Soukup showing me how to use SecureCRT, things to look for before running a Pro*C process, how to look at Banner Forms and log in to PL/SQL and view the tables that relate to the forms. Huber left a lot of documentation that I have been reading through. He had a file, "Banner new technology", which shows what Banner 9 will be using (like Groovy, Grails, Hibernate), and RESTful web services. The first on my list to start learning in that is Groovy and Grails. As a student programmer I spent time on RESTful Web Services with Amazon and Facebook. For instance you can use google translate restful api  to get the translation from one language to another for a particular word/phrase all with the url, and it will get you the result that you wish and so you can display it to the page without sending the person to google. It is a stateless alternative to SOAP(Simple Object Access Protocol). I used Netbeans IDE to learn anout REST because they have a good guide here

Today, I talked to Soukup about job submissions and going over some readme files and he pointed out how it would be good to mod a perl file for job submission. Next week I am going to try to get my feet wet with that.