The Boolean is Lying to You

Let's get straight to the point: a boolean should never be your first move in a physical data model.

I know, I know. It's incredibly tempting. You're building a dimension table, you need to know if a row is the current one, so you slap an is_current flag on the end of the script and call it a day. It feels clean. It feels simple.

I'm not saying a boolean is never justified. There are cases where an attribute really is just true or false, no history, no drift, no reason code required. But that case has to be earned, not assumed. In my own agent instructions, a boolean (same as jsonb) requires a waiver before it's allowed into a physical model: a deliberate, written justification, not a shortcut reached for because the alternative required more thought. The default posture is no, and the burden of proof sits on the column, not on the reviewer.

Nowhere does a lazy boolean cost you more than in a dimensional model.

The OLAP Failure: Redundancy

If you have a perfectly modeled dimension table using a Slowly Changing Dimension (SCD Type 2), tracking a physical is_current boolean alongside it is fundamentally redundant.

The truth of whether a record is the current version at a given moment is already perfectly contained within your temporal boundaries (valid_from and valid_to). Adding a physical boolean flag right next to those dates introduces the very real risk of data anomalies where the flag eventually drifts out of sync with the timestamps: an ETL job dies halfway through, and now is_current = true on a row whose valid_to says otherwise.

In a strict physical model, you store only the primary source of truth: the explicit temporal versioning columns. No independently maintained derivation sits next to them.

The "Bit Bucket" Trade-off

Inevitably, someone building a dashboard on top of that dimension will push back:

"Every consumer has to remember how this dimension represents the current row. Just give us an is_current flag so every report uses the same simple predicate."

I understand the argument. But this is the same divide I wrote about back in 2008 and 2010: the database is not a Bit Bucket that exists to mirror the current report's WHERE clause. The physical model exists to be the single source of truth first, and convenient for one consumer second.

If the derivation is genuinely a performance problem, use a materialized view, a semantic-layer cache, or a generated expression that cannot drift independently from the temporal source of truth, not a physical flag sitting next to the dates it duplicates and can silently disagree with.

"But it makes it easier for the analysts!"

Inevitably, whenever I make this argument, someone across the table will push back: "But having an is_current flag just makes it easier for the analysts!"

Every single time I hear that, I cringe. The phrase that immediately comes to mind is "the soft bigotry of low expectations."

Are we really going to permanently cripple our physical data model and introduce risk of out-of-sync data anomalies because we assume an analyst is incapable of writing a WHERE valid_to IS NULL clause? Analysts are smart. They understand temporal data. Dumbing down the physical schema because we assume they can't handle reality is insulting to them, and dangerous for the database.

The Semantic Layer: Where Booleans Actually Belong

Don't get me wrong: while a raw boolean is a terrible way to store state, it remains a genuinely useful way for a human or a BI tool to consume it. End users love a good checkbox on a dashboard.

That's exactly what the semantic layer is for: abstracting complex, high-fidelity underlying reality into a simple, ephemeral business definition. Your semantic layer exposes a calculated Is Current Version dimension that evaluates valid_to IS NULL (or your system's max-date sentinel) on the fly, at query time, against the one column that's actually the source of truth.

The Idealistic Layer Boundary

If you want a physical record that never loses temporal precision or suffers from redundant flag logic, here's the boundary between physical schema and semantic abstraction:

Modeling Challenge	The Purist Physical Reality	The Semantic Abstraction
State Evaluation	Temporal boundaries (valid_from to valid_to).	Ephemeral True/False flag calculated on the fly for dashboard filtering.
Data Integrity	Enforced by the temporal columns themselves, single source of truth.	Enforced by a standardized definition applied consistently across every downstream BI tool.

By keeping the boolean entirely out of the physical schema and entirely inside the semantic layer, you get the best of both worlds.

---

References

• Application Developers vs Database Developers (Part I)
• Application Developers vs. Database Developers: Part II
• The "Database is a Bit Bucket" Mentality
• The Case for the Bit Bucket (by Michael Cohen)
• Everything is a Bit Bucket (by Michael O'Neill)
• Capturing Record History

Design Documentation

I got bit recently. I didn't ask for requirements and I didn't create a design document. Bad Chet.

It made for a difficult time getting that first cut done...and I missed a few very important items.

Talking with a friend today about it, and he kind of hammered the point home. Do the design document and you spend fewer cycles spinning your wheels. As an example he use a project we both worked on last summer. At first, he didn't do a design document and "struggled" for a couple of weeks. When it came time to do it again (it had been dropped from that first release), he started with the design document and it only took a week.

Forced him to think about how to do it, the possible roadblocks and the ability to raise those roadblocks to the appropriate people.

For whatever reason, I had never thought of the design document in terms of OBIEE. I don't know why, it just was. For OLTP type applications, I would always, at a minimum, create a Visio diagram. That would allow me to quickly and easily spot any problems. That world is much more intuitive to me, so I can visualize it easier. OBIEE, not as easy.

So here's my pledge to, no matter how much I loathe formal design documents, to go forth and create formal design documents. After all, this is development, just a different tool (and a layer of abstraction). No more ad-hoc development for me.

APEX Architecture

I'm not talking about how APEX internally or within the database works, I'm talking about how to best manage it from an infrastructure point of view.

I recently raised the issue with my client about how they were using APEX. I'm not here as an APEX consultant, but they didn't have a lot of experience in-house and the DBA who got it implemented had been trying for years. So I wrote up my thoughts and passed them along to my supervisor who then shared them with others.

The current setup was a stand-alone instance that was only used for APEX applications using database links and synonyms to access the source tables. I was arguing to have APEX installed on the database where the data lives.

When I got on the call, I listened to their arguments and concerns.

Then I began to think...what do you do if you have, say, 100 databases? Do you install APEX on each and every one? That's just another piece that has to be supported and maintained right?

After the call, I began to do a little research on the subject...and couldn't find a whole lot of information out there. Perhaps I wasn't searching on the right terms...not really sure.

Thinking about it further and talking with some friends, I came up with a short list of potential architectures.

1. Install it Locally - This is my preferred method. The APEX metadata (application) lives in the same database as the data it manipulates.
2. Database Links - Target Tables Remote, Source Code Local - This is the configuration I spoke of above. There is a single database where APEX is installed which accesses tables across database links. Synonyms would be used for tables and views to "hide" the long names and make them visible. Any source code (PL/SQL) would be local.
3. Database Links - Target Tables Remote, Source Code Remote - Same as #2, but the source code lives in the remote database. Again, synonyms would need to be used to make database objects visible to APEX.

Number 2 and 3 both limit the functionality of APEX. I am aware of the Automated Row Fetch not working across database links. I know, for #3, you can't use the RETURNING clause, which isn't a huge deal...just annoying.

For #2 and #3, the synonyms add additional maintenance and complexity to your application(s).

Also with #2 and #3, you have additional network traffic, which, depending on the environment and/or the number of users, could be significant.

Like I said, my first thought was to install it locally. In addition, just put it on the databases that you will use it, not every single one. I still believe that, but am trying to give a balanced and reasoned answer.

Ultimately, maybe it just depends on how you use it. If this is going to be a "professionally" written application (as opposed to something a power user might build), I think having it installed on the databases where your data lives. If it's going to be driven by those power users as a replacement for the Excel and Access databases, I think the best approach might just be the single instance accessing tables remotely would be the way to go.

Anyway, just my thoughts. Please scream (write) loudly if you have thoughts on the matter.

Developing in APEX

I see 3 possible ways to develop in APEX.

1. Packaged Code: PL/SQL - With this method you take, possibly, an existing application and "skin" it with APEX. Using APEX's built in Form based on a Procedure wizard, this is quick and painless. You spend most of your time just moving buttons around to suit your needs. Maybe I've been reading AskTom for too long, but that's the general approach I take.
2. Declarative - Using all the built-in functionality of APEX which creates all kinds of objects (triggers for example) that will be managed by APEX. This is probably most geared towards the power users who don't yet have the ability to write a lot of PL/SQL but have a pretty good understanding of building an application.
3. Hybrid - This approach mixes and matches the 2 previous approaches. You use packaged code for the bulk of your application and the declarative functionality within APEX for everything else. For example, the multi row INSERT/UPDATE/DELETE. I can write that whole thing out using TYPEs in PL/SQL, populate an APEX collection, convert that collection to a TYPE and then pass that TYPE back to PL/SQL...but do I want to? It saves a boat load of time to use the built-in features. Ultimately it depends, of course, on many different factors.

I think I will always prefer #1...like I said, I read AskTom for years, mostly for kicks.

Mr. Kyte on where to put data, in the DB or in the web tier:

They belong right next to the data - allows you flexible access to the data - you can build a new application WITHOUT reinventing all of that stuff.

where does the logic to display a bunch of numbers in a pie chart belong? In the application.

where does the logic to ensure that if X=0, then Y must be >= 0? That belongs right next to the data.

So, programming logic - some of it in the application - user interface code, user interaction code, error handling, display of data, report generation - in the application.

Much of it in the database - security, access control, data integrity.

I love APEX, but will it be around forever?

APEX: Database Links

I've talked about APEX being hardly known among quite a few people in the Oracle world...at least the ones I have worked with. When I first moved to Tampa, people had heard of it, but never got around to trying it out. This was late 2006, around version 2.2. My next job, same thing. The DBA had heard of it, but hadn't used it yet. Thankfully he was open and willing to try out new things. This is the same guy who had an 11gR1 RAC install within months of 11g being released in 2007. Can you say glutton for punishment?

My next assignment had it installed, but it was mostly for reporting so it had it's own instance. APEX utilized database links to access other databases. I was tasked with re-engineering their payment processing application using APEX as a skin. I worked to convince them that APEX needed to be installed on the same machine. The DBA fought me tooth and nail (and ultimately won) on that issue. I didn't get the opportunity to finish that application.

Anyway, the point I try to make is that you take away a lot of the features of APEX when you use it across a database link.

I'm not one to use a lot of the wizards or declarative stuff, I like to use packages and use the "Create Procedure from Form" wizard.

It's amazingly simple and only requires a little tweaking on the front end to finish up a input/update/delete form.

Using database links though, this isn't possible.

For example:

CREATE USER table_owner
  IDENTIFIED BY testing
  DEFAULT TABLESPACE users
  QUOTA 10M ON users;

CREATE DATABASE LINK my_application
  CONNECT TO table_owner 
  IDENTIFIED BY testing
  USING 'TESTING';

That's a loopback database link pointing to my sandbox.

CREATE TABLE table_owner.t
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30)
    CONSTRAINT nn_firstname_t NOT NULL,
  last_name VARCHAR2(30)
    CONSTRAINT nn_lastname_t NOT NULL
);

CREATE SEQUENCE table_owner.sq_t
  START WITH 100
  INCREMENT BY 1
  CACHE 10
  NOCYCLE;

CREATE OR REPLACE
FUNCTION table_owner.create_t
  ( p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2 ) RETURN NUMBER
IS
  l_id NUMBER;
BEGIN
  INSERT INTO t
    ( id,
      first_name,
      last_name )
  VALUES
    ( sq_t.nextval,
      p_first_name,
      p_last_name )
  RETURNING id INTO l_id;
  
  RETURN l_id;
END create_t;
/
show errors

I then create some basic objects in the TABLE_OWNER schema including a function that returns the ID of the newly created record. On to APEX where I have created a simple one page app. This workspace is mapped to my schema, CJUSTICE and the database link is private to that schema.

Now I want to create a new region on the page



Click Next.



Click Next.



Click Next.

Now select your schema. Your workspace can map to multiple schemas, but I have only one, my own.



Click Next.

Now click on the little button thing in red



You'll see a popup window listing out all the procedure and functions in your schema.



So it's not there...but it is in another schema (and since the database link is using the schema owner, there is no need to GRANT EXECUTE on the procedure).



Nothing there.

Let's try entering it in manually



Well that sucks.

Can I do it manually?

First I create the process:



Build a simple form:



Enter in some data, click on submit.



Then verify:

TABLE_OWNER@TESTING>SELECT * FROM T;

        ID FIRST_NAME                     LAST_NAME
---------- ------------------------------ ------------------------------
       100 JOHN                           PIWOWAR

Amusingly, I seemed to recall an issue, a few years back about using the RETURNING clause across a database link. I wonder if that's because it's not a true database link? (Figured it out the next day of course, ORA-22816)

Anyway, the point is, install APEX on the database you plan on building your application. You'll only add unnecessary work and time to your development efforts which means you might as well use Java. :)

WHERE rownum = 1

Or it's evil twin, WHERE rownum < 2

I've seen this a lot over the past few years. When I say a lot, I mean approaching infinity a lot. Well, it feels like it anyway. I'm allowed to exaggerate.

I'm pretty much convinced that this is a bug. I see it and immediately say, WTF?

The only thing I could see it being used for is some sort of EXISTS functionality, like this:

DECLARE
  l_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM user_line_of_business
  WHERE username = 'KPEDERSEN'
    AND rownum < 2;
  
  IF l_count = 1 THEN
    do_something;
  ELSE
    raise_some_error;
  END IF;
END;

I have multiple records in the table for KPEDERSEN, I just need to know if one exists. This would add a STOPKEY (command?) to the query plan and force it to...(ah, who am I kidding, I don't know what I am talking about...yet).

BTW, here's the table definition and data if you want to try it out.

CREATE TABLE user_line_of_business
(
  username VARCHAR2(30),
  line_of_business VARCHAR2(3)
);

INSERT INTO user_line_of_business
  ( username,
    line_of_business )
VALUES
  ( 'KPEDERSEN',
    'TEN' );

INSERT INTO user_line_of_business
  ( username,
    line_of_business )
VALUES
  ( 'KPEDERSEN',
    'OSX' );

INSERT INTO user_line_of_business
  ( username,
    line_of_business )
VALUES
  ( 'KPEDERSEN',
    'BOL' );

INSERT INTO user_line_of_business
  ( username,
    line_of_business )
VALUES
  ( 'JKURAMOT',
    'OSX' );

INSERT INTO user_line_of_business
  ( username,
    line_of_business )
VALUES
  ( 'JPIWOWAR',
    'OSX' );

So the EXISTS functionality is OK. Not great, not something I'd really like to see, but it works.

How about this though?

What if I, say, I was filtering queries based on a users line of business? Just to add, I am using something like APPLICATION CONTEXT to set the variable so it will hold only one value (please, just go with me on this...I know there is a wway around). My point is, the variable only holds one value. I promise, I am not making this scenario up.

DECLARE
  l_lob VARCHAR2(5);
BEGIN
  SELECT line_of_business
  INTO l_lob
  FROM user_line_of_business
  WHERE username = 'KPEDERSEN'
    AND rownum < 2;

  dbms_output.put_line( 'l_lob:  ' || l_lob );
END;

Which returns TEN. TEN might be the right answer.

What happens if you run that again? Well, you'll probably get the same result.

However, say this table grows and username KPEDERSEN gets more records? Do you think you could guarantee that TEN would be returned each and every time?

The short answer (and the one I am able to provide) is NO. You can't guarantee the order of the rows returned without explicitly putting an ORDER BY clause on there.

WHERE rownum = 1 or WHERE rownum < 2 are the devil (which contains evil).

The Case for the Bit Bucket

By Michael Cohen

Mr. Cohen is most famous here for the discussions we've had before about Application Developers vs. Database Developers. Part I is here, Part II is here. Mr. Cohen is a friend of mine. I have a great deal of respect for him. We obviously disagree in some areas, but I've learned to appreciate his push-back and learn from it.

He had left a comment the yesterday on my most recent post, The "Database is a Bit Bucket" Mentality (Michael O'Neill posted his followup, Everything is a Bit Bucket, as well). I thought the comment would get short shrift, so I asked him to write up a full post. I deleted that comment and here is his post.

Modern RDBMS's are quite powerful today. Pretty much every one of them has full support for SQL, including vendor extensions, all of the features we've come to expect from a relational database, a full fledged programming language built in, and quite often support for extras like full text search or native handling of XML. Most now also now ship with highly feature specific add-ons - PostgreSQL has a geospatial package that makes it the defacto standard in that domain, MySql has hot replication in a master-slave paradigm, Oracle has....well, Oracle has all kinds of things, a full object system and Java inside, a message broker, an HTTP server, a complete UI toolkit, among other things.

So the question arises as to how much of this capability one should use. I think it's becoming apparent that the answer to this is, "not much." Why shouldn't you take advantage of as much of the database's feature set as possible? The answer is performance and scalability. But wait, aren't stored procedures faster than ad hoc queries? Yes (theoretically). Won't it be more performant to execute business logic as close as possible to the data it operates on? Why should we introduce yet another component into the architecture when the database is perfectly capable of handling a particular task?

For one thing, the programming languages and environments offered by relational databases are now relatively long in the tooth, and have been eclipsed by modern OO languages. Developers are much more productive building applications with these new languages, and find it painful and tedious to work within the relational model, with SQL. You can see proof of this now with the overwhelming popularity of ORM frameworks in most of the popular OO languages out there. Java has Hibernate/EJB/JPA and many others. Ruby has ActiveRecord, DataMapper, and Sequel. Python has SqlAlchemy and Djanjo's ORM. And it's not because these developers lack the skills to work with the database directly. Quite the contrary actually, it takes intimate knowledge of the database to work effectively with an ORM. What's more, the ORM is often able to make runtime optimizations that would be difficult or prohibitively time consuming to hand code. Finally, clustered caches offer massive performance and scalability improvements, handling writes back to the database transparently behind the scenes, but for the most part they preclude implementing complex business logic in the database.

The overall trend is clear, across languages and platforms. It's the movement of data out of the database and into the application layer. Less and less reliance on the database, perhaps only for archival purposes. John Davies has a good comment on this. He's operating in a unique environment with extremely rigorous performance requirements, but we're now starting to see similar constraints imposed by the web. There's a whole class of software that has come about due to the inability to scale the relational database beyond a certain point. Facebook developed Cassandra, now used by Twitter, Reddit, and Digg, among others. LinkedIn built Voldemort. My employer doesn't deal with the massive scale of these companies, but we do large scale data processing with Hadoop. HBase, another non-relational persistent data store, is a natural fit, and just about the only option really. We use MySql less and less.

Of course, not everybody is building applications with such high scalability requirements. But even for applications with less intensive scalability requirements I would argue the same tendency to minimize the workload on the database should apply. Cameron Purdy has a good quote, "If you don't pick your bottlenecks, they'll pick you." Design your application to bottleneck, he says. What he means is, your application is going to bottleneck on something, so you need to explicitly decide what it will bottleneck on. Unfortunately, most applications bottleneck on the database, as this is the hardest layer to scale. It's pretty easy to scale the front end, we just throw more instances of Apache out there. It's a little bit harder, but not much, to scale the app server. But it's pretty hard to scale the database tier, particularly for write intensive applications. For well funded organizations, Oracle RAC is the standard. MySql's master-slave setup and hot replication saw it win out over PostgreSQL despite the fact that Postgres is a much better database in just about every other respect. The NoSql projects listed above grew out of the inability even to scale out MySql.

The trend is clear. We're collecting and processing more data than ever before, and this will only increase as we go forward. Unfortunately, the relational database (at least in it's current form) isn't well suited to the scale of data processing an already significant and growing number of organizations deal with on a daily basis. We're now seeing new solutions come forth to address the shortcomings of the traditional RDBMS, and the same forces that have necessitated such developments are at work even in smaller organizations. At all levels, developers would do well to require as little functionality as possible from the database, essentially, to treat it as a bit bucket.

The "Database is a Bucket" Mentality

Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

Database Application Security - A Visualization

Security, in regards to the database, is pretty broad. There are a multitude of ways to secure your database. From physical or logical access to SQL Injection.

This is a (not so) pretty picture of how I visualize security from an application point of view.



The base of any application are the tables. Access to those tables should be limited to database views and PL/SQL packages (API) that are owned by the application (schema).

SELECT privileges on those views should be given to only users who need it (users also includes other applications). Views present the data in the format that is required by the application. VPD (column or row) can be used to further restrict access to sensitive data.

EXECUTE privileges on the APIs (PL/SQL) should only be given to those applications that need it. Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user.

Much of my thoughts on this matter are directly traceable to reading AskTom for so many years. I can't point to any one specific article, I think it's the accumulation/internalization of reading for so long.

It makes sense though.

Who better to decide the functions (INSERT/UPDATE/DELETE) that can be performed against a given set of tables than the person(s) who created them? If said person has left, make someone else responsible for them. I've fought hard over the years to implement this "pattern" and have been fairly unsuccessful. It is difficult to go against years of doing it one way (full access to the underlying tables) to forcing someone or something to use the API.

The idea to visualize it came to me recently and I need to put it down.

What do you think? Am I full of it? Is this reasonable? Anything I left out?

PARALLEL Rant?

Let's say you have DEGREE set at the table and index level.

I ask you if this is appropriate, instead offering up using whatever tool accessing the data to provide the PARALLEL hint.

The DOP is set pretty low, given our current system.

But it's still set and can't be easily turned off.

I'm all for PARALLEL, but it's been beaten into my head that it should be used, specifically for batch operations. In other words, transforming or loading data.

With the DOP set at the table or index level, it is not (necessarily, see resource limits below) controllable. If you have 1 or 2 users issuing SELECT statements against the table, it's not a big deal. Let's say the DOP is set to 8. 8 processes are spun off for each user. That's 16 processes now running that SELECT statement. Now let's say you have 1000 users. You probably won't make it to 8000 processes...your machine will probably keel over and die...or worse, just sit there forever.

But we have to set DOP at the table/index level...our users don't know how to write SQL.

Fair enough...teach them how to write it.

That takes too much time.

How do you ever expect them to learn? It might be a good short term solution, but is it really a good long term solution? Teaching your users how to write better SQL would be in everyone's best interest.

1. Initially, you'll be swamped with "How do I?" type questions.
2. Then the questions will only trickle in.
3. You'll have much more savy business users who can now probably articulate their needs much better which will lead to
   1. Better design documents
   2. Better requirements
4. You can finally begin to push off more of this "reporting" type functionality out to the business (where it should be in my opinion).

OK, that might be a bit of a fantasy.

What about setting up resource profiles for the users?

I've never used them, but I was reading up on them tonight for this post.

What can you control with this feature?

• Limiting the User Session Level
• Limiting Database Call Levels
• Limiting CPU Time
• Limiting Logical Reads
• Limiting Other Resources
  • You can limit the number of concurrent sessions for each user
  • You can limit the idle time for a session.
  • You can limit the elapsed connect time for each session.
  • You can limit the amount of private System Global Area (SGA) space (used for private SQL areas) for a session

What's the point of all this? I'm not too sure. It's a rant I guess. I just got done reading Dom Brooks recent post and reminded me of this conversation...which I have all too often.

OBIEE and Source Control?

One very difficult aspect of using OBIEE (or APEX for that matter) is that it doesn't lend itself very well to source control.

There is one RPD (metadata) file in use at any given time. Changes to this environment will affect anyone using or developing on the presentation server layer (Dashboards/Answers/etc).

For the web catalog (Answers/Dashboards/Prompts/etc) you can make changes, but again, it will affect anyone who is also using the tool. If you want to tweak a report that has prompts or filters, you need to save everything off to your own folder in order to work on it or risk colliding with others or worse, messing up the report beyond repair (also known as FUBAR).

Developers usually need to break things to fix them and giving them an environment where they can do this (also known as tinkering), without repercussions, should be high on the list of must-haves.

Inspired by a meeting I attended last week and Jake's recent welcoming of VirtualBox into the Oracle fold, I decided to think (yikes) my way through a possible solution.

After the meeting last week, I was convinced I could build an environment using subversion as the source control tool. Tie that in with Jira, Fisheye and ultimately Bamboo and you'd have a pretty sweet environment to work in. How to do it though?

1. Set up source control. SVN is free and runs on Linux. Free.
2. Convince a multitude of developers to install and configure OBIEE on their own workstations. Yeah...not so sure about that. I accidentally said in that meeting, "I don't know a single, good developer who doesn't have a local install of Oracle (the database)" May have been just a tad hyperbolic...I like to tinker and appreciate those environments which allow me to do so. Having a local sandbox has been indispensable for me.
3. Not sure where or what 3 is. That's where I got hung up...until reading Jake's post.

How about this then?

1. No subversion (for the time being).
2. Virtualize the development environment.
3. Hand out the VDIs to the developers, and let them run with it. When they make changes, they can promote them through the usual channels. Once those changes are accepted/merged with the development environment, a new snapshot is taken and distributed.

Having the snapshot of dev is the key I think. Those who don't like to tinker, who just like to get their job done, won't have to worry about configuring their environment. They'll just fire up the VM and do their work. For those that do like to tinker, they can fiddle with the VM as much as they want without fear of breaking things for everyone else. If they need a fresh start, just get the original VDI and go crazy again.

This hasn't been completely thought at (if you couldn't tell). I haven't considered passwords or other such sensitive data. It sounds good in my head though. What's the worst that could happen?

How Do You Normalize a Tweet?

Second post by Mr. Myers, you can read his first one, How To Kill a Code Review here. I have always liked design topics, I don't think they are covered enough on the web, which is why I liked this one. I have often wondered what trade-offs designers make for these types of applications (Twitter, Facebook, etc). Are they even "designed" by a data modeler? Or are they created by application developers? Not really sure it matters to those companies as they are successful (in a strange, no business model kind of way) and, I don't believe, represent many of the realities that we as Oracle professionals are likely to deal with.

Firstly, I don't tweet. Alex (Gorbachev) mentioned it at a Sydney meetup. I had a look, but didn't get entrenched and I assume there will be others out there who aren't tweeters. Suffice to say a 'tweet' is a message broadcast by a twitter user to the twitter consumers. They are up to 140 characters long.

So what's to normalise ? Isn't it just a value ? Even Oracle 6 could cope with VARCHAR2(140).

But actually, a tweet isn't just a simple value.

A search for "beer" would turn up all messages that included #beer.
Similarly, the @ sign followed by a username allows users to send messages directly to each other. A message with @example would be directed at the user [example] although it can still be read by anyone.

Source: Wikipedia

First normal form states

There's no left-to-right ordering to the columns.

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

Source: Wikipedia

The problem is that the tweet "@tom Come for a #beer or #burger. Don't let @harry come" definitely has hidden components, but there is a sequencing in the message that is just as important.

In a practical implementation, we would probably have the following tables:

TWITTER_USERS        : username (eg @tom), date_joined, email....
TAGS                 : tag (eg #beer)
TWEETS               : tweet_id (surrogate key), created_by (referencing twitter_user), created_timestamp, tweet_text...
TWEET_TAGS           : tweet_id, tag (eg #beer)
TWEET_DESTINATIONS   : tweet_id, username (eg @tom)

Our message would have the two child tag records (#beer and #burger) and two child destination records (@tom and @fred).

At the logical level, we are not properly normalized because we have the tweet_text duplicating information from the child entities and the potential for inconsistencies between them. We can say that the tweet just seems to contain duplicate information but it is really different. Is that just being picky ?

I am not suggesting the relational model is wrong, broken, incomplete or inadequate. Quite the reverse, in fact. In this case the value of the model is that it tells us the problems that will arise when we denormalise data.

For example, if @harry deletes his twitter account (because he was never invited for beers), do we delete the tweet_dest that referred to him or do we keep it and not enforce that referential integrity constraint ? If we delete the tweet_dest, we have an inconsistency between the tweet_text attribute and the tweet_dest child entities. Or maybe we delete the tweet entity itself and all its children. Those are really choices for the business (possibly with some legal implications though).

I don't have a solution to the logical model representation, and would be interested in feedback. But not by twitter please :)

Database Cleanup: Metrics

Before my current refactor/redesign goes to production, I would like to capture some metrics. I'm fairly limited in what I can actually do (i.e. I can't use DBMS_PROFILER in production).

So far, this is what I have come up with:

1. Lines of Code (LOC) - I don't believe this is necessarily a reflection of good or bad code. For instance, I can take that 2 line INSERT statement and turn it into 20 lines.

Was

INSERT INTO my_table(id, col1, col2, col3, col4, col5, col6, col7, col8, col9 )
VALUES ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

Is

INSERT INTO my_table
  ( id, 
    col1, 
    col2, 
    col3, 
    col4, 
    col5, 
    col6, 
    col7, 
    col8, 
    col9 )
VALUES 
  ( 1, 
    2, 
    3, 
    4, 
    5, 
    6, 
    7, 
    8, 
    9, 
    10 );

That's a pretty sane example. The 2 line version isn't all that bad, but it does run off the page. The point I am trying to make is that "cleaning" up can actually add more lines to your code.

In my opinion, when more than one person is going to support the code, readability is a nice thing. Whether or not you like my style, it is (more) readable. So LOC is not necessarily a great metric, but it can give you an idea which way you are going (after it has been properly formatted anyway).

2. COMMITs - Many argue that there should (almost) never be commits (an exception is logging with the AUTONOMOUS_TRANSACTION pragma) in the database. The calling application should perform the commits. Unfortunately that general rule is not always followed. I've added it to my list of metrics because it is pertinent to our environment. Of course I have gone to great pains to make sure that the removal of one commit will not impact the entire system...that possibility does exist when you have commits everywhere.

3. Text - This was a real stretch. What is the size of the individual procedure, package or function? I wouldn't have considered it (I never have until now), but I was desperate to define something...anything. How do you determine that?

SELECT name, type, SUM( LENGTH( text ) ) t
FROM dba_source
WHERE owner = 'MY_OWNER'
  AND name = 'MY_NAME';

4. Dependencies - Also known, to me, as modular code. Why have 200 INSERT statements into a single table when you could wrap that up into a single procedure and call that? If you add a column, you'll still have to go through and fix all those occurences (if it's not defaulted to something). But if you remove a column from that table, it can easily be hidden from the calling code, thus you only have to change it in one place. Of course you wouldn't want to leave it there forever, but it can be done piece-meal, bit by bit as you work on affected parts.

Have you ever thought about this before? What kind of metrics would you suggest? I know mine are a bit of a stretch...so please share.

The Database Cleanup

I found a recent discussion on Ask Tom about "Unused Objects" via David Aldridge's post, Metacode Gone Wrong. The original poster's question:

We are in a process of removing unused objects (tables/functions/procedures/packages) from the database. Is there any script(suggestions) or short-cut method to find these unused objects (tables/functions/procedures/packages not used in ddl/dml/select statements for more than 3 months).

There are more than 500 objects(tables/functions/procedures/packages) in our database.

At least PLEASE help me in finding unused TABLES.For other objects I'm thinking to check manually in the application code(using find and grep commands)

Please Help me.

To which Tom replied:

You'll have to enable auditing and then come back in 3 months to see.

We don't track this information by default -- also, even with auditing, it may be very possible to have an object that is INDIRECTLY accessed (eg: via a foreign key for example) that won't show up.

You can try USER_DEPENDENCIES but that won't tell you about objects referenced by code in
client apps or via dynamic sql

I'm always perplexed by this. How does one get into a production environment where by
they don't know what the objects are used by/for? No documentation or anything?

I've been in several environments where production was not documented very well (if at all). I guess that's fortunate for me, as there is always work to be done.

At my last gig, I went about an effort to clean up the database. We had close to 600 tables in a single schema. The one good thing (for me anyway), what that those tables were not accessible outside of the database, they were called through PL/SQL. Finding dependencies (DBA_DEPENDENCIES) was fairly easy...but I also ran across the caveat that he mentions, Dynamic SQL. Nothing strikes fear in you quicker than the realization that all of your work might be nullified because you didn't consider the use [Dynamic SQL] up front.

I would complain during code review/architectural sessions about the use of Dynamic SQL...not sure if it was listened, but I got my opinion in.

Documentation of a database, in my recent experience anyway, is the last thing on anyone's mind. It's seen as time-consuming and un-important. I like what David says in the Ask Tom comments:

I think that if documenting code makes people sad then they ought to be in their bedroom writing card games in VB. The sad thing is that it doesn't have to be a huge overhead, it just has to be well thoughtout and #actually done#.

How To Clean Up The Database
Since I've had so much experience at this of late, I'll list the steps I have taken in the hopes that you can find something useful yourself.

1. DBA_DEPENDENCIES - It's a great place to start, but it's not a panacea. You can get 90% of everything you need here. It's that last 10% that is the hardest. For example, I'll focus in on one table, query DBA_DEPENDENCIES, and then put that list into a spreadsheet where I can then track my progress. Usually I'll add "fixed", "fixed date" and "comments" columns so I'll know that I have addressed it. I'll typically have a worksheet for each table.

2. Privileges - Specifically in relation to tables. Do other database users have DML access? SELECT is one thing (still important) but INSERT/UPDATE/DELETE is entirely different. If other users do have access, are they service accounts (used by outside applications) or are they solely database users (another application)?

3. Auditing - I had never thought to use auditing for this purpose, but it might be helpful in the future.

4. Logging - If you suspect a piece of code is no longer used (naturally there is no documentation), but are not sure, you can add a bit of logging code to it. It's not the best method in the world, but it works. With all things, it's not a 100% guarantee either, the code may be called once a year, there is really no way to tell.

5. Thorough and Meticulous Analysis - This isn't really a method but it's going in here anyway. Document everything you can which includes everything you've done. At the very least, you'll have some documentation to show for it. At the very least, you'll have a much better understanding of your application and it's inner-workings.

Update 09/17/2009 12:30 PM
Dom Brooks reminded me of DBA_SOURCE in the comments so I'm adding that in.

6. DBA_SOURCE - A case-insensitive search of DBA_SOURCE is a must have as well. Allows you to find all the references to a table/procedure/etc. Some may have just be in comments, but some may also be contained in Dynamic SQL.

Database Tutoring

Last week a friend of mine sent over a craigslist posting, someone looking for a tutor. Here's the ad:

Looking for an experienced SQL Database Systems analyst to help with homework assignments for a graduate level database course. Would like to meet 2 times per week (for 2 hours each session) over the next four months. Evening, weekends or Wednesdays preferred. The candidate must be able to explain the technical to the non-technical. Please reply with resume and availablility.

Course Topics Are:
* Relational Model and Languages ( SQL)
* Database Analysis and Design
* Methodology (Conceptual and Logical Design)
* Social, Legal, etc. Issues
* Distributed DBMSs and Replication
* Object DBMSs
* The Web and DBMSs
* Business Intelligence

I replied immediately and heard back the next day. I sent my resume but I thought the blog would be more appropriate. Apparently it was enough.

We spoke on Saturday for about an hour and I received all the materials necessary to start doing research including a sample database (in Access).

I have to say I'm pretty excited about it. I thoroughly enjoy trying to explain database concepts so that others (non-techies) can understand. It's a Masters level class filled with students from Computer Science and from an Education Technology tract. Bet you can guess which side my "student" falls in.

Seems a little odd that the Educational Technology folks are in the class, but I think it's a good thing. When they need an application in the future, they'll have a much better grasp of what to ask for and hopefully they'll be more involved in the process.

I'll use this space both for reporting on progress and helping to explain things. Wish us luck!

Data vs. Information

Last week in The Case For Views on the very last line I said

Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information.

That elicited a very, very strong reaction from a good friend and mentor. In the comments he left this

Turn data into information? That doesn't make a whole lot of sense to me-- All data is information. Can you clarify that statement a little?

On the face of it, that's not a very strong reaction. He tends to be a lurker though, rarely leaving comments.

Then there was twitter, where he sent me a few more links on the subject.

I'm pretty sure he was fired up.

Once a week or so, we'll get together over beers and have excellent conversations. Occasionally, I'll try to hold my ground from the database perspective. Last week we had a discussion about whether the database should be making web service calls.

Security aside, I thought it was appropriate given the size and skills of the shop, but he and our other friend staunchly disagreed.

Point is, we have some great conversations. It has never come down to "You are stupid!" or anything like that, it's a conversation with each side presenting their arguments.

Since my friend has like 28 degrees in Engineering, I've learned to give him the benefit of the doubt, so I wanted to study up on it.

I asked the oracle-l mailing list on Friday.

My contention, or what I have heard and read, is that a database stores data, only through the use of SQL or some reporting tool, does that data get turned into information. I don't know where I heard or read that for the first time, but I've probably been saying it for years.

Through my friends response and others on the mailing list, I probably need to rethink that particular statement.

Here are some relevant links provided by my friend and others on the oracle-l mailing list:

Principles of Communication Enginnering, By John M. Wozencraft, Irwin Mark Jacobs

Information Theory and Reliable Communication, By Robert G. Gallager

Nuno Suto, aka Noons suggested Fabian Pascal, which can be read here. He also suggested reading up on Chris Date and Ted Codd as well as

Conceptual Schema and Relational Database Design: A Fact Oriented Approach, By G. M. Nijssen, T. A. Halpin

Have you ever used the phrase, "data into information" or some derivation there of? I'd like to track down where I first came across it if possible. Thoughts on Data vs. Information as separate entities?

The Case For Views

I recently had to "defend" my use of views.

To me, they seem natural. Using them is almost always a good thing to do. I've met those that don't really care for them...I just never understood why. Then again, those same people are still not convinced of PL/SQL APIs. Maybe there is something to that mindset...

Being forced to articulate one's views is a good thing, it's part of why I blog. I won't lie though, it gets frustrating to have do this, seemingly, all the time.

I'm going to do it here, again.

Complex Joins
No, I'm not afraid of joins. I am afraid of others who are afraid of joins though. More specifically, I'm afraid of those who aren't proficient at writing SQL. Let me do it, once, and let everyone else have access to the view. Besides, I'm the subject matter expert (SME) on the given set of tables, so it follows that I should create the interface to those tables.

Yes, I said interface. It's exactly what a view is and interface to the underlying data.

Encapsulation
Write it once and let it propogate everywhere.

When I had to "defend" my use of views, I mistakenly used the example of adding columns. Oops. That would (possibly) require changes throughout the system. I meant to say remove columns, in which case you could keep the placeholder in the view using NULL without having to change all of the code. This does not mean that proper analysis does not need to be performed, it does, but you could possibly get away with not having to change everything that references the view.

My second example was a derived value. This makes more sense to some people thankfully. I've seen the same calculation done on a specific field done 10s, even 100s of times throughout the code. Why not do it one time? Perfect use for views.

Security
Following the least privileges necessary to perform a given action, views allow you to give access to the data without direct access to the tables. Views can also be used to hide or mask data that certain individuals should not have access to. In conjunction with VPD or Application Contexts, it's a powerful way to prevent unauthorized access.

Maintenance
Maintenance has been alluded to above, but not explicitly stated.

For derived values: If you have a derived or calculated value and that calculation is performed all over the place, what happens when it changes? You have to update it everywhere. If you had used a view, change it once and it propogates everywhere. What was once a project is now a "simple" code change. This affects IT in how they choose and assign resources as well as the Business.

For complex joins: What if one table is no longer used or needed? What if that table is littered throughout the code base? You have a project on your hands.

If that table were part of a view, you could "simply" remove it, keep the columns in the view and you're done. There might be places where code needs to be adjusted, but overall, you have a much smaller impact. That's a good thing.

Other
I tried putting the following statement in a category up above, but couldn't make it fit.

Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information.

PL/SQL: Coding Practices

I've struggled this week to write. Lately I've had lots of technical content and not much philosophical content. I have lots of philosophical content now, but the blog isn't the place for it...at least not now.

The big project I'm working on now is refactoring our payment processing system. We interface with multiple gateways for redundancy purposes. The code is comprised of 3 stand-alone procedures. One of those procedures has (had) 17 private procedures/functions in it. On one hand, it made sense, since everything was a stand-alone procedure or function, you didn't want all these dependencies on other objects. On the other hand, testing was virtually impossible. If you needed to make a change to private procedure #13, you had to run through an almost infinite number of test cases to ensure that you covered that particular case.

I've now moved those 3 procedures into a package. Theoretically, it's hot deployable now as long as we don't change the package signature. That's a big win in my book. I've also moved those 17 private functions into their own individual procedures and functions that can be exposed via the package specification for unit testing purposes (in production they will be private as nothing else needs access). The hardest part of that effort was there were no parameters being passed to the procedure, it just relied on the declared variables. So for each and every one of those I had to figure out what it relied on to work and what variables it set. No small task.

Here is the table:

CREATE TABLE t
(
  id NUMBER PRIMARY KEY,
  col_1 NUMBER(1) DEFAULT 0 NOT NULL,
  col_2 NUMBER(1) DEFAULT 0 NOT NULL,
  col_3 NUMBER(1) DEFAULT 0 NOT NULL,
  start_date DATE DEFAULT SYSDATE NOT NULL,
  end_date DATE
);

and a procedure (which does nothing obviously)

CREATE OR REPLACE
PROCEDURE update_t
  ( p_id IN NUMBER,
    p_col_1 IN INTEGER DEFAULT 0,
    p_col_2 IN INTEGER DEFAULT 0,
    p_col_3 IN INTEGER DEFAULT 0 )
IS
BEGIN
  NULL;
END update_t;

What's the best way to integrate the procedure into your code? I've seen this:

CREATE OR REPLACE
PROCEDURE some_other_procedure
  ( p_id NUMBER,
    p_variable VARCHAR2(1) )
IS
BEGIN
  IF p_variable = 'A' THEN
    update_t
      ( p_id => p_id,
        p_col_3 => 1 );
  ELSIF p_variable = 'B' THEN
    update_t
      ( p_id => p_id,
        p_col_2 => 1,
        p_col_3 => 1 );
  ELSIF p_variable = 'C' THEN
    update_t
      ( p_id => p_id,
        p_col_1 => 1 );
  ELSIF p_variable = 'D' THEN
    update_t
      ( p_id => p_id,
        p_col_1 => 1,
        p_col_3 => 1 );
  END IF;
END some_other_procedures;

Since I default the input parameters to 0, I didn't have to specify each individual parameter every time I called it. I like that.

I don't much like having 4 separate calls to UPATE_T though.

1. It makes it difficult (without further logging), to determine where exactly it's being called in the control statement.
2. Seems like a waste of space. P_ID is always going to be the same, why set it 4 times?

I decided to make just one call to UPDATE_T. I create local variables, then set them in the control statement, and then make the call to UPDATE_T.

CREATE OR REPLACE
PROCEDURE some_other_procedure
  ( p_id NUMBER,
    p_variable VARCHAR2(1) )
IS
  l_col_1 INTEGER := 0;
  l_col_2 INTEGER := 0;
  l_col_3 INTEGER := 0;
BEGIN
  IF p_variable = 'A' THEN
    l_col_3 := 1;
  ELSIF p_variable = 'B' THEN
    l_col_2 := 1;
    l_col_3 := 1;
  ELSIF p_variable = 'C' THEN
    l_col_1 := 1;
  ELSIF p_variable = 'D' THEN
    l_col_1 := 1;
    l_col_3 := 1;
  END IF;

  update_t
    ( p_id => p_id,
      p_col_1 => l_col_1,
      p_col_2 => l_col_2,
      p_col_3 => l_col_3 );
  
END some_other_procedures;

Not much savings in space (and sometimes you'll actually have more), but for me, this is much easier to read. If I have to debug this, it feels a lot easier to concentrate on the control statement without the calls to UPDATE_T.

What do you do in these kinds of situations? Same as me? Different? Think I'm off my rocker (yeah, I know some of you do)?

Baseball Data Modeling

Anyone out there like baseball? Ever had a desire to model a baseball game?

I do and I have tried a few times in the past. It get's pretty hairy down at the game game/inning/player level. If I remember correctly, substitutions tripped me up a bit. There there's the whole datawarehouse side, I'd like that to be part of the project as well.

I started a project on Google Code here, the name is pretty vanilla, baseball-database. If you join you can suggest a better name.

I'd like to try and talk Oracle into giving a few licenses to the recently released production version of SQL Developer Data Modeler. I've hit up @krisrice on Twitter, but he has no control over licenses, just development. I've also hit up Justin Kestelyn (@oracletechnet) who said he would look into it.

I've had no time lately to bother him; perhaps with a few more people...

If you are interested, just drop me a line chet at oraclenerd or message me through twitter.

Large-Scale Solutions for Small Enterprises: a Brief How (and Why) To

This is the first (technically, second I guess) in what will (hopefully) be a series of guest posts, from Ted and others.

One thing I really liked was the small shop that Ted talks about. So often you see IT become the cost center typically due to a lack of planning. 5 people supporting 3000? That's pure awesome. Speaks volumes for Ted and the choices he has made.

Wouldn't it be great if you could effectively run a major ERP suite on a tight budget with only a handful of staff? That is one of the things I do, so Chet asked me to write a bit about it. Specifically, he asked me "how have you integrated Oracle into a small shop? How much work? Hard? Easy?" Hopefully this post will cover those questions.

The What

The enterprise I manage runs a large and scalable ERP application suite: Oracle PeopleSoft Financials, HCM, Campus Solutions, and Enterprise Portal (all on application version 9.0 on PeopleTools 8.49). We have those applications running on an essentially Microsoft technology stack (Windows servers, SQL Server, Active Directory, ILM, and so forth). Our environment is heavily virtualized using VMware. Our mission has always been to maximize functionality and minimize cost. We do a fair job at that, as we deliver this ERP suite to a user base of ~3000 with an IT staff of 5 and a very small budget.

The How

Our modus operandi is basically to run our applications very near vanilla and to keep them as current as possible. This allows us to take full advantage of vendor and peer support and leverage the latest delivered functionality. Staying vanilla keeps operating costs down (no development costs, quick patch cycles, etc.) and allows rapid upgrades. We also do not diversify the technology within our enterprise architecture unless absolutely necessary. That allows our staff to use one skill set across multiple applications. That, in turn, allows us to hire ambitious generalists who are comfortable moving across applications. We minimize training costs by maximizing peer collaboration (very common in the Education and Research industry) and staying very active in user groups (our industry has an exceptional user group, -1 for my bias). We also rarely use consultants and never use implementation/upgrade partners anymore.

The Why

A colleague from a large university asked me recently why, for an organization our size, we did not use a much smaller solution (I think he suggested QuickBooks, +1 for snark). It is a fair question with a simple answer. While our organization is smaller than a larger university, the complexity of our business requirements is comparable. For example, our payroll contains all of the variations of a larger university: full time and part time staff, faculty contracts of every imaginable period of time, student employees, contingent workers, and so on. To use a smaller solution to handle that complexity would require a larger and far more specialized payroll staff, at least some custom application development, and a different IT support structure. All of those things are costly. Instead, we let Oracle worry about providing the functionality to meet those requirements, we let our payroll staff adjust their business processes around that functionality (we are mean that way), provide general IT support from our small pool of staff, and leverage our user group for strategic direction and answers to tough questions. So, to answer my colleague's question, we use a large solution like Oracle PeopleSoft to accomplish our mission: to maximize functionality and minimize cost.

The Importance

I am no analyst, but it does seem to me that business requirements, regulations, and compliance issues are getting more complex. It is probably safe to assume that they will not get simpler any time soon. We are also in a massive recession and resources are scarce. It is probably safe to assume this won't change anytime very soon, either. One way to meet these challenges is to take advantage of the benefits of larger enterprise solutions (to handle increasing complexity) and operate them as efficiently as possible (to handle decreasing resources). The major enterprise technology players (Oracle, Microsoft, SAP, IBM) are spending time and money focusing on small and medium enterprises (SME) recently. I think this is mostly for sales (what isn't?) but I also get the sense that they want to know how SMEs actually run these applications. I wrote a white paper last year (Effective ERP Practices for the Small Institution) that got a good bit of attention from Oracle. I also spoke at Open World last year about this stuff [http://www.slideshare.net/badgerworks/higher-education-acheives-oracle-peoplesoft-roi-presentation] (and went for free, in true oraclenerd style).

I hope that answered some of Chet's questions and maybe raised some new ones. If you have questions or comments drop them here. You might also want to hit my blog for the top 5 FAQs that I get when I speak, write, or consult on this topic. Thanks for reading!

About
Ted [ linkedin | twitter ] is Vice President for Communications and Membership at the Higher Education User Group, MBA and MSIS student at the Johns Hopkins University, Director of Administrative Systems at MICA, and blogger at badgerworks.