Run Scripts in SQL Developer

I finally decided to save a script that cleans out a couple of tables for me.

Now I have a script, how do I run it in SQL Dev? In SQL*Plus, I would run it like @clean_tables. Two things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress.

I wanted to be able to run my script in a SQL Developer worksheet. How?

@clean_tables

Error starting at line 38 in command:
@clean_tables
Error report:
Unable to open file: "clean_tables.sql"

Twitter. Jeff Smith hangs out there, a lot. He supposedly has a real job as the Senior Assistant Principal Skinner Product Dude for SQL Developer at Oracle. Crazy title, I know. Back to Twitter.

@oraclenerd working directory would be directory of parent file

— Jeff Smith (@thatjeffsmith) February 22, 2013

Since he lives there (Twitter) (and I'm glad he does), I got an immediate response. Yay for Jeff.

Wait, what? Parent file? WTF are you talking about?

(I then remove the snark and try to put more details)

(oh, and I don't like that I can't just embed a single tweet...sorry, their fault, not mine)

@thatjeffsmith "@ test(.sql)" I just want to know where to put test.sql.

— oraclenerd (@oraclenerd) February 22, 2013

Two seconds later:

@oraclenerd i think this, Tools > Preferences > Database > Worksheet > Select default path to look for scripts. Please test :)

— Jeff Smith (@thatjeffsmith) February 22, 2013

Tested, and it works. Yay for me. Yay for Jeff.



In case it isn't obvious, I'm being sarcastic. Jeff is a fantastic advocate for SQL Developer. Yes, he gets paid to do it, but he goes above and beyond on a daily basis. Oracle is lucky to have him.

Fun with Date Math

(First off, sorry Mike, I'm hoping this will break my writer's block...)

On Friday I was asked to look at a report that wasn't returning all of the data. Sample:

Year/Month  Total Sales Total Sales (YAGO)
------------------------------------------
01/31/2013   $1,000,000           $900,000                
03/31/2013                        $950,000

For reference, YAGO is "Year Ago."

Notice anything funny there?

Yeah, February is missing. The (OBIEE) report has a filter on Jan, Feb and Mar of 2013. But it wasn't showing up. I confirmed via manual SQL (hah!) that there was (YAGO) data in there for February. Any ideas?

I immediately suspected one of two things:
- If the Date (month) dimension had a "year ago" column it was wrong.
- The join in OBIEE was doing it wrong.

I checked the date dimension first. It was fine. It didn't even have a YAGO column, so nothing to see there. I looked at the join between the date dimension and the fact table...

(YEAR ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" ) - 1 ) * 10000 
+  MONTH ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" )  * 100
+ CASE WHEN DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE") = 29 THEN 28 ELSE
DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE")  END 
= "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"

I want to tear my eyes out when I see stuff like that. I don't even want to know what it does. * 1000? * 100? Shoot me.

OK, so the MONTH_DIM_KEY is in the YYYYMMDD format. MONTHEND_DATE is a date data-type that corresponds to the last day of the month. For February 2013, it's 20130228, For February 2012, it should be 20120229. <<< Leap Year!!! I'm going to make a wild guess and say that the formula up there isn't working. How to test it though? That's logical SQL (OBIEE), it doesn't run in the database. I just ran the report and grabbed the SQL submitted to the database. This is what it looked like:

          AND ( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 +
          TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 +
          CASE
            WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
            THEN 28
            ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
          END = MONTH_DIM_KEY
  AND( MONTHEND_DATE IN( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ), TO_DATE(
  '2013-02-28', 'YYYY-MM-DD' ), TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) ) ) 

Eyes are burning again. This is also the "prettified" SQL after I hit Ctrl + F7 in SQL Developer. The very first thing I do with OBIEE generated SQL.

One part of that wouldn't be so bad, but it's three formulas adding up to some mysterious number (presumably the last day of the month, for the previous year, in YYYYMMDD format). So I moved all those formulas up into the SELECT part of the statement. Let's see what they are doing.

SELECT
  ( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 part_1,
  TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 part_2,
  CASE
    WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
    THEN 28
    ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
  END part_3
FROM my_month_dim
WHERE MONTHEND_DATE IN ( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ), 
                         TO_DATE( '2013-02-28', 'YYYY-MM-DD' ), 
                         TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) )

That resulted in this:

PART_1         PART_2         PART_3
20120000       100            31
20120000       200            28
20120000       300            31

So PART_3 is definitely incorrect. Am I going to bother to figure out why? I have some serious issues inside of my brain which simply do not allow me to do date math. I avoid it at all costs...instead choosing to use whatever the system provides me.

One of my favorites, especially when dealing with leap years, is ADD_MONTHS.

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month

That's why. Add -12 months to February 28, 2013. You don't get back February 28, 2012, you get back the 29th, as it should be. Do the same thing starting with February 29th, 2012. Add 12 months, subtract 12 months. It's always right. Yay for someone figuring this out so I don't ever have to do so.

Sadly, OBIEE doesn't have the equivalent of ADD_MONTHS (or LAST_DAY), you have to build out a string and then concatenate it all together, not pleasant. So I cheated, I used EVALUATE. Here's my working solution.

TO_NUMBER( TO_CHAR( ADD_MONTHS( MONTHEND_DATE, -12 ), 'YYYYMMDD' ) ) = DW_MONTH_DIM_KEY

Oops, that's the physical SQL. How about the OBIEE SQL:

CAST( EVALUATE( 'TO_NUMBER( TO_CHAR( ADD_MONTHS( %1, %2 ), %3 ) )',
 "EDW".""."DW"."Dim_DW_MONTH_DIM_CBM"."MONTHEND_DATE", 
-12, 'YYYYMMDD' ) AS INTEGER ) = "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"

PDI: Pass Parameters to Jobs/Transformations

I had been working on trying to get a process to run for each file. I used the Get File Names step followed by the Copy rows to result step. I had placed this in front of my Text file input step, which is where you define the file for further processing.

That method produced a stream (that's what it's called in PDI) with each and every file and each and every record in those files. If I were just loading that into a table, it would have worked. However, I was assigning a identifier to each file using a database sequence. I needed a sequence for each file, but I wasn't getting it.

With some help and pointers from the ##pentaho IRC channel, I found this post (more on that one in the future), Run Kettle Job for each Row. I downloaded the sample provided to see how it worked.



The calc dates transformation just generates a lot of rows. Not much to see there. The magic, at least for me, was in the run for each row job entry.



Specifically, the Write to log step. (I have this need to see things, since I don't understand everything about the tool yet, Write to log provides me that ability.)



See date, better, ${date}? That's how you reference parameter and variables.

I ran the job and watched the date scroll by. Nice. Then I tried to plug it into my job.

Zippo. Instead of seeing, "this is my filename: /data/pentaho/blah/test.csv" in the log output, I just saw "this is my filename:" Ugh. I went back to the sample and plugged in my stuff. It worked. Yay. Went back to mine, it didn't. Gah! I tried changing the names, then I'd just see "this is my filename: ${new_parameter_name}" so it wasn't resolving to the value.

Finally...after comparing XML for the sample file and mine and finding no real differences, I just about gave up.

One last gasp though, I went to the IRC channel and asked if there was some way to see the job or transformation settings. No one was home. I tried right-clicking to bring up the context menu and there was Job Settings



Job Settings brought up this one:



date is defined there. I checked mine. Nothing defined. Added filename to mine, ran it, Success!

Learning Pentaho Data Integrator

aka Kettle, aka PDI.

I've recently taken on a data integration gig and I'll be using Pentaho Data Integrator (PDI). I've read about Pentaho for years but never got around to actually using it. I'm excited about the opportunity.

What is PDI?

...delivers powerful Extraction, Transformation and Loading (ETL) capabilities using an innovative, metadata-driven approach. With an intuitive, graphical, drag and drop design environment, and a proven, scalable, standards-based architecture, Pentaho Data Integration is increasingly the choice for organizations over traditional, proprietary ETL or data integration tools.

I'll be using the enterprise edition (EE), which is supported, similar to how RedHat works...I think.

This post if mainly for me, naturally. I'm going to list out the references I've found so far and add to it over time. Similar to what I do (err, did) for Learning OBIEE.

Actually, I'll just start with the helpful email I received after being added to the account.

• Support - https://pentaho.zendesk.com/home - ask questions of the engineers.
• Evaluation Resources - http://www.pentahoevalcenter.com - great technical resource for those new to the tools – recorded tutorials as well - NOTE the drop downs.
• Documentation - http://infocenter.pentaho.com/help/index.jsp
• Download - and run the full suite (or just Pentaho Data Integration) right on your own computer. Perfect for eval, test/dev, training. Getting started docs included: http://www.pentaho.com/download/
• JIRA Tracking - http://jira.pentaho.com - you can request and track features and fixes – triaged twice week by our engineering team. As a reminder, let Support enter any JIRA tickets you want to open.

I love that you can download and play with the software yourself. Of course the Community Edition (CE) is open source, so that makes sense. I'm not sure if you can get the EE version for free though.

There's a community page as well with links to a lot of great resources. So far, my favorite has to be the IRC channel hosted at freenode. Note, there are two hashtags as in ##pentaho. I've been lurking there for a few weeks and finally got up the nerve (what? me shy?) last week. HansVA and mburgess_pdi helped get me moving again on a particular problem. Good stuff.

I'm sure I'll add more as time goes on. That's it for now.

Update after original posting...

• (Kettle, aka PDI) Wiki