In that regard, I'm doing a fair amount of analysis right now. So I need help finding two tools:
1. A tool that will allow me to map (visually or otherwise) a single data point from source to target(s). I typically use Excel. It's easy to use and available everywhere. Where it falls apart, slightly, is that a single data point may have one or more middle steps (i.e. not target) and one or more targets. I think I want something like this:
Keep in mind though, I have potentially hundreds of columns in a system with thousands upon thousands of...
A couple of people have suggested using an ETL tool like Informatica, Pentaho or ODI. Yes. But I don't see it yet. Besides, I don't want to map to actually do something...most of the conversion has already been done and I'm picking it up at a particular step (near the beginning). What's missing is that mapping document that I want to create for everything...but that's another story.
2. I want to to look at a view and know where those stupid unaliased columns are sourced from. A very, very basic example:
SELECTVAR_VALUE_01 and VAR_VALUE_02, why don't you have aliases? Why did your developer neglect you so? Why can't every single developer just remember that someone, someday, will have to look at their code? Please? Pretty please? Or did you know it would be me and thus you did it on purpose? If so, I'm not talking to you again.
WHERE hs.address_id = add.address_id
AND hs.random_id = ran.random_id
Anyway, it doesn't take me very long to figure where those columns are sourced from. What if there are 10's of those in a view with 100's of columns? Yes, not enjoyable. What if there are many views just like this that you have to analyze? Yes!
Not yet. DBA_TAB_COLUMNS? Nope. Come on! It's got to be there somewhere...when you compile a view Oracle checks to make sure everything is a-ok right? Doesn't it store that information somewhere? It must!. I took to Twitter, naturally, and Steve Karam, aka @OracleAlchemist found this possible gem:
March 26, 2013
I'm also requesting a feature in SQL Developer...or, trying to anyway. Back channels of course.
I've done this kind of analysis in the past, but it is usually a one off, so there never seemed to be a need to make it repeatable. Now, there is a need. A giant need. If you've got any ideas for me, let me know...
I think it's hilarious.
Riding to batting practice with LC, he starts up with me...
LC: (in response to some statement I made) "Where'd you hear that?"
Me: "The Internet"
LC: "And you believed it?"
Me: "Yeah, they can't put anything on the internet that isn't true."
LC: "Where'd you hear that?"
Together: "The Internet"
We also do the "And then...?" skit from Dude, Where's My Car?. He used to be able to rattle off the saying from Tommy Boy, "You can get a good look at a t-bone by sticking your head up a bull's..." - I'm pretty sure this is better than that.
One thing I've seen a lot of is calls to dbms_output.put_line. Fortunately, I've seen some dbms_application_info.set_module and other system calls too. But back to that first one.
1. When I used dbms_output, I would typically only use it in development. Once done, I would remove all calls to it, test and promote to QA. It would never survive the trip to production.
2. Typically, when I used it in development, I would tire of typing out d b m s _ o u t p u t . p u t _ l i n e so I would either a, create a standalone procedure or create a private procedure inside the package, something like this (standalone version).
CREATE OR REPLACEEasy. Then, in the code, I would simply use the procedure p all over the place...like this:
PROCEDURE p( p_text IN VARCHAR2 )
dbms_output.put_line( p_text );
l_start_time date;Since the procedure is 84 characters long, I only have to use the p function 4 times to get the benefit. Yay for me...I think. Wait, I like typing.
l_start_time := sysdate;
p( 'l_start_time: ' || l_start_time );
--do some stuff here
--maybe add some more calls to p
l_end_time := sysdate;
p( 'l_end_time: ' || l_start_time );
Over the past few weeks we've finalized (mostly) the Sunday Symposium schedule. We're currently working on finalizing Hands-on-Labs (HOL).
Beginning last year, we've had the Oracle product teams running the Sunday Symposia. This gives them an opportunity to showcase their wares and (hopefully) provide a bit of a road map for the future of said wares. This year, we have three symposia: APEX, ADF and Fusion Development and The Database and Developer's Toolbox.
ADF and Fusion Development
- Oracle Development Tools – Where are We and What’s Next - Bill Patakay, Oracle
- How to Get Started with Oracle ADF – What Resources are Out There? - Shay Shmeltzer and Lynn Munsinger, Oracle
- The Cloud and What it Means to Oracle ADF and Java Developers - Dana Singleterry, Oracle
- Going Mobile – What to Consider Before Starting a Mobile Project - Joe Huang, Oracle
- Understanding Fusion Middleware and ADF Integration - Frederic Desbiens, Lynn Munsinger, and Shay Shmeltzer, Oracle
- Open Q&A with the ADF Product Management
I love that they are opening up the floor to questions from their users. I wish more product teams would do that.
- Oracle Database Tools - Mike Hichwa, Oracle
- Technology for the Database Cloud - Rick Greenwald, Oracle
- Developing Great User Interfaces with Application Express - Shakeeb Rahman, Oracle
- How Do We Build the APEX Builder? - Vlad Uvarov, Oracle
- How to Fully Utilize RESTful Web Services with Application Express - John Snyders, Oracle
- Update from APEX Development - Joel Kallman, Oracle
(If you see Joel Kallman out and about, make sure you you mispronounce APEX). This is a fantastic group of people (minus Joel of course). Not mentioned above is the affable David Peake who helps put all this together. The community surrounding APEX is second-to-none.
Finally, The Database and Developer's Toolkit. I'm partial to this one because I've been involved in the database track for the past couple of years. Like last year, this one is being put together by Kris Rice of Oracle. There are no session or abstract details for this one as it will be based mainly on the upcoming 12c release of the database. However, we do have the list of speakers lined up. If you could only come for one day of this conference, Sunday would be the day and this symposium would be the one you would attend.
This symposium will start off with Mike Hichwa (above) and then transition to the aforementioned (too many big words tonight) Mr. Rice. He'll be accompanied by Jeff Smith of SQL Developer fame, Maria Colgan from the Optimzer team and Tom Kyte.
How'd we do? I think pretty darn good.
Don't forget to sign up. Early Bird Registration ends on March 25, 2013. Save $300.
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?
Error starting at line 38 in command:
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.
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)For reference, YAGO is "Year Ago."
01/31/2013 $1,000,000 $900,000
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 ) * 10000I 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.
+ MONTH ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" ) * 100
+ CASE WHEN DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE") = 29 THEN 28 ELSE
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 +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.
TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 +
WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
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' ) ) )
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.
SELECTThat resulted in this:
( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 part_1,
TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 part_2,
WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
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' ) )
PART_1 PART_2 PART_3So 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.
20120000 100 31
20120000 200 28
20120000 300 31
One of my favorites, especially when dealing with leap years, is ADD_MONTHS.
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_KEYOops, that's the physical SQL. How about the OBIEE SQL:
CAST( EVALUATE( 'TO_NUMBER( TO_CHAR( ADD_MONTHS( %1, %2 ), %3 ) )',
-12, 'YYYYMMDD' ) AS INTEGER ) = "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"