Analyzing Keywords in Enron’s Email

You probably can’t read all the 252K email messages in the Enron email dataset by yourself.

But with SQL it’s easy to search for keywords, like “Special Purpose Entity”, “Bankrupt”, “Fraud”, “Shutdown”,  “Talking Points”, “FERC” and so on. They begin to reveal what really went on inside the minds at Enron.

TALKING POINTS:

Many Enron employees took MBA courses at UC Berkeley HAAS Business School.

Since the Enron bankruptcy, classes at UC Berkeley School Of Information began to analyze Enron’s emails, as early as 2004. Like this one: http://courses.ischool.berkeley.edu/i290-2/f04/assignments/a4_solutions/qu_poon.doc.

In this document, they search for “Talking Points“: an especially persuasive point helping to support an argument or discussion.

Enron – A Few Good Guys

Sherron Watkins:

Sherron Watkins

Sherron Watkins is the former Vice President of Enron Corporation who alerted then-CEO Ken Lay in August 2001 to accounting irregularities within the company, warning him that Enron ‘might implode in a wave of accounting scandals. From her website:
sherronwatkins.com/sherronwatkins/Sherrons_Bio.html

At the House Hearing on Enron, Sherron Watkins said:
“I wish we could get caught. We are such a crooked company.” Sherron Watkins former Vice President of Corporate Development at Enron

In the emails made public, what can we find about Sherron Watkins? Unfortunately, not as much as we might hope.

There is no entry like “Watkin” in the table: employeelist. Again, like other senior executives, there are not many emails from or to Sherron Watkins.

In the entire email set, where the sender or receiver is sherron.watkins@enron.com, there are only 24 unique messages. If a group by is done on the sender and receiver, there are only 46 unique messages.

So, does this indicate:
– Sherron Watkins did not email much
– her assistants did her email for her
– the email list has been overly sanitized
– or something else?

If we search the body of the messsages, we find Sherron Watkins in emails that she sent, that others then forwarded along. So, I would tend to think that the email list is a very small subset of actual email data. What we do find is rather interesting.

Sherron’s Resentment In The Behavior Of Others:  

Enron Email Analysis – Persons Of Interest

Persons Of Interest:

Continuing my analysis of the Enron scandal, I looked at some of the key players in the Enron scandal.

Kenneth Lay

A good list of who played which position at Enron is at:
http://enrondata.org/assets/edo_enron-custodians-data.html
and at:
http://www.infosys.tuwien.ac.at/staff/dschall/email/enron-employees.txt

What can we discover about Enron’s People Of Interest by analyzing their email with SQL?  Among other things, there was some very abusive management at Enron.

Person Of Interest – Andrew Fastow:

Interestingly, there is very little in the emails regarding Andrew Fastow, the CFO of Enron, who was one of the main culprits. He is rather absent in this dataset.

Analyzing Enron Email Metadata Using SQL

enron-logo

What can we discover analyzing Enron emails using SQL? Quite a bit actually.

The Enron scandal in 2001 was huge.  As part of the discovery process, prosecutors started looking at emails to find evidence to convict the guilty. These email sets have since been made public.

Recently, I downloaded a set of Enron emails from USC:
http://www.isi.edu/~adibi/Enron/Enron.htm
and installed them into a MYSQL database.

There were over 252K email messages, sent to over 2 million recipients.

+-----------------+
| Tables_in_enron |      Count
+-----------------+
| employeelist    |        151
| message         |    252,759
| recipientinfo   |  2,064,442
| referenceinfo   |     54,778
+-----------------+

Analyzing the emails produced some very interesting findings about what went on inside Enron!

MetaData:

Reverse Engineering Netbeans

Recently, I took a java course. A number of co-students had problems with version compatibility after they used their Netbeans, MYSQL, and Java stack at home and brought their work to school. So, I wanted to have the exactly same versions when I did my installation.

But, immediately after I had installed Netbeans 7.3 on Windows 7 (64 bit) and started it, a message popped up indicating that 12 updates were available. I’ve only just started, and already it wants to update.

How to turn the updates off? I don’t want them updating at all. And I especially don’t want automatic updates going on behind my back, the way Adobe Acrobat reader does.

Building Better Systems Through Better Analysis

From Reqs to Specs.

In my other piece, I wrote how business requirements documents don’t work very well, how they create a trial and error process, and prototypes make it into production.

Much of the reason is that there is no analysis, general design, or detailed design. Nor is anyone in charge of these areas.

Much of my work in development has been doing the analysis and design that were not done in the first place. Here are some of the techniques that I’ve used. And some that have been very useful when others have done them for me.

Why “Business Requirements” Don’t Work

From Reqs to Specs

One aspect of IT Development that I’ve noticed for a very long time is that no one is thinking things through.

The result, is not a solid piece of software, but just a prototype. Unfortunately, these prototypes make it into production too much of the time.

Is it any wonder that so much software does not work? How many days since the last glitch in some website or software that you worked with? I’ll bet not many.

Why does this occur? A lot of it is the way that IT work is divided now. Think of the classic Waterfall Method and the responsibilities for the tasks:

Task	Responsibility
Requirements	User, Business Analyst
Analysis	???
General Design	???
Detailed Design	???
Build	Developer
Maintain	DBA

Notes on DEPTREE

After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:

$ORACLE_HOME/rdbms/admin/utldtree.sql

It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation

Running the script, creates a number of objects:

OBJECT_AND_OBJECT_ID
----------------------------------------------------------------------------------------------------
PROCEDURE   SYS.DEPTREE_FILL    3304377
SEQUENCE   SYS.DEPTREE_SEQ    3304375
TABLE   SYS.DEPTREE_TEMPTAB    3304376
VIEW   SYS.DEPTREE    3304378
VIEW   SYS.IDEPTREE    3304379

7 rows selected.

 

Very Useful Scripts To Find Object Dependencies

For DBAs and Developers.

When you are developing schemas, a lot of objects get created. It can be really confusing to keep track of them all. Especially when you now need to create a few dozen in a new environment.

What is even worse, is when you start to maintain databases and schemas that you didn’t build yourself. If you drop a table or unsuccesfully modify a view, all their children will now become invalid. Suddenly child procedures and packages that reference them will not work.

Here you can find some sqlplus scripts that I have used for years to see the dependencies in schema objects. Run the script, and enter in the object you have questions on. Very fast and effective.

They have been extremely useful to determine things like:
– The order of operations to create objects. Parents first. Then children.
– The impact of dropping a view, package, procedure.
– Causes of object invalidation such as another invalid object.
– All the tables that a package is accessing directly. For those multiple 18 table joins.
– Whether a package, function or procedure calls other prodecures.
– Obscure issues, such as references to variables in packages.

————————————————————
————————————————————
————————————————————

To Find Just The Parents of an Oracle Database Object:

Parent objects must be created first, before creating the child object. If the parent object is dropped, all subsequent child objects will become invalid.

This info is very important in new environments, such as moving from Dev to Test. Or Test to Prod.

Analysis of Adobe Acrobat Reader Update: How To Finally Shut It Up!

Analysis of Adobe Acrobat Reader Update: How To Finally Shut It Up!

Adobe Acrobat Reader likes to update. And update. And update. Ever notice? I turned the update off. But it still continued to update.  I expect computers and software to serve me. Not the other way around. When I tell software to not update, guess what that means?

Analysis of Adobe Acrobat Reader and Updater:

So I decided to do what I do at work a lot. Reverse engineer much of the system and solve the problem.   Here the things I tried, and some of my analysis.  Not in chronological order.

Installed Programs:

I looked at the installed programs.

Adobe Installed Programs From Windows Installer

There is Abobe. But there is no specific program for the updater.

Using Opera And Zone Alarm To Stop Annoying Flash Advertising!

Don’t you just hate all those websites that use flash? And more flash. And banners. And animation. And videos. All over the webpages. And any and all other crap that interferes with the content that you went to see in the first place?

I often have many browser windows open, and multiple tabs in each window. Most of the computer memory gets taken up by the browsers. And then much of the CPU being used, is just being used to display webpages!

I get really annoyed with all the crap that is thrown at me by some websites. All it does is slow down my machine. The swine! What did they think the result would be? That I would reward them with a click? Or a purchase???? Duh!

I discovered some cool features in Opera that I’ve been able to use, along with the Zone Alarm firewall to make things easier.

Overcoming The Malware “XP Internet Security”

Viruses, worms, trojans, and rogue antispyware software are getting pretty sophisticated.  As careful as I am, one caught me today, on a computer I don’t usually use.  Usually I use Firefox.  But I opened up Google Chrome, right clicked on a link, and open in new window.  Suddenly, I got one of those phony webpages telling me that I had a virus, and doing a scan.

Zone Alarm did not catch it, which is pretty disappointing.  I set it to deny, as I usually do, but it still executed.

ZoneAlarm and XCV.exe

Oracle Apps R12: DBA Analysis Of TableSpaces, Log, And Control Files

Oracle Apps R12: More DBA Analysis Of TableSpaces, Log, And Control Files

Lots of tablespaces! 

select TABLESPACE_NAME
from dba_tablespaces
order by TABLESPACE_NAME

TABLESPACE_NAME
------------------------------
APPS_CALCLIP
APPS_OMO
APPS_TS_ARCHIVE
 

Oracle Apps – Analysis of the Files and Filesystems

Continuing my analysis of Oracle Apps R12.  This time, the actual files on the server.  There are over 1/2 million files in APPL_TOP and COMMON_TOP!  Wow!

—————-

On my installation of Apps R12, there is:

APPL_TOP
/oapps/applmgr/VIS/apps/apps_st/appl

COMMON_TOP
/oapps/applmgr/VIS/apps/apps_st/comn/

ORACLE_HOME
/oapps/oracle/VIS/db/tech_st/11.1.0

—————-

The top parts of the directory structure:

find /oapps  -maxdepth 5 -type d

Oracle Applications R12: More Schema Analysis

Oracle Applications R12: More Schema Analysis

Some more analysis of the schemas and objects in Oracle Applications R12 sample database, VIS.

Lots of objects, triggers, and packages.

And over 57 million lines of source code in the APPS schema alone!

Why I Like The Merge Statement

Over the past few years, I’ve stopped using the Update statement, and  started using the Merge statement for updates instead.  While many use it to do both inserts and updates at the same time, you can use Merge for only updates, or inserts.

Say I want to update some fields in the SCOTT schema.  Traditionally, I would first do the analysis by looking at the data.

Consuming Huge Amounts Of Unstructured Data

The recent presentation about Oracle Data Storage started me thinking about data.  I’ve worked with the Oracle database since 1995.  Lots of structured data in fields, tables and databases.  A certain amount of my job has been to turn these large amounts of data into usable information.  The result of which itself may in fact be unstructured, such as “the trend is …”.

But when I think of it, so much of what I actually consume and interact with, is in fact, very unstructured.  I started making a list of everything.

So much Content on the Web!

Google
Blogs and feedback
Webpages, articles, including animated GIF files that help explain things
News
Linked IN