Detailed Design Documentation

Today I had to present my detailed design document for an OBIEE project.

The response was...not great. Not even good. So bad, in fact, my boss had to call me afterwards. Fortunately for me, he's a good boss and remembers the good with the bad.

I'm not sure if it is evident, but I'm not afraid to fail. I've done it before, I did it today, I'll do it again in the future. It's just life. As much as I would like to believe (and as much as I tell my wife and kids), I am not perfect. Never have been, never will be.

What should be evident though, through 686 posts here and the untold pages of documentation I have written for wikis, email, groups, blog comments, or some other medium, I don't have a problem with documentation. I like it. I find a lot of value in it. I usually take the lead on it if nothing exists.

Not too long ago I talked about Design Documentation, but that was more of the high level kind. The kind I was expected to deliver today was to be much more in depth. How much more so? Well...

Physical Layer
Each table and the columns that were to be used. This includes aliasing these tables and then their respective joins.

No, that's nothing terrible to ask. I don't argue that.

BMM (Business Model Mapping) Layer

1. I was to have every column defined (both sourced and calculated).
2. Each and every dimension and fact table, including all the Logical Table Sources (LTS) for each of those, including their joins (INNER/OUTER/FULL OUTER), Content, Fragmentation and Leveling defined.
3. The heirarchies (aka dimensions, but not of the table kind) defined by level with all the accompanying columns at each level.

This section gave me the most problems today. While I appreciate the high level design, I felt this encroached a bit on actual development. The more I think about it though, the more reasonable it sounds.

It is a departure for me though.

I tend to develop and design in tandem. Documenting everything up front feels...dirty to me.

Perhaps it's the fact that I was using Word? I mean, seriously, it's 2011. Why are we using binary files? Data should be free!

I prefer wikis. I prefer email over Word. I've used Atlassian products before and think they are great.

Where I have a problem is this (seeming) waterfall approach. How do you know what you don't know?

Then I'm back at the format of the documentation, Word, stored in...ugh...SharePoint. I don't believe I have ever met a less desirable solution. SharePoint is the suck.

Documentation should live and breathe. I have never seen that happen with a Word document. Never. I consider myself somewhat disciplined, and I couldn't fathom maintaining this. Let's make it more narrative (wiki). Let's have a place where the latest and greatest lives, with a possible story of how it got that way. The decisions were made because of A, B and C. You miss most, if not all, of that in the design documents.

Perhaps it's not even Word, or SharePoint. Perhaps it is the process. I'm probably not asking the right questions yet.

To sum it up, I failed today. I have to follow the guidelines set forth...but I can try to influence them...and of course you know I will.

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.

RTFM Minimalism

Just found this at @rnm1978's blog and found it quite amusing...and poignant.



I'm not sure who that is, but it looks like Kathy Sierra, the sentiment expressed in the slide sounds like her as well.

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.

Oracle Concepts: The Data Dictionary

I have come to rely pretty heavily on the Data Dictionary as I don't typically use a GUI. This stems from the fact that in my first professional IT job (reporting off of Oracle), I was given a tnsnames.ora file and something called SQL*Plus. I came from Microsoft Access...I wanted pretty pictures of my database objects. My first year was spent asking how to find the tables in a schema (schema? what's that?), how to view the SQL that made up a view, and how to view the source code.

Find Tables

SELECT * FROM user_tables;

View Text

SET LONG 100000
SELECT text FROM user_views WHERE view_name = :your_view;

View Source

SELECT line, text 
FROM user_source 
WHERE name = :package_name
  AND type = :package_spec_or_package_body;

Needless to say it wasn't fun.

Without pain there is no progress.

What is the Data Dictionary?

...data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:

* The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
* How much space has been allocated for, and is currently used by, the schema objects
* Default values for columns
* Integrity constraint information
* The names of Oracle Database users
* Privileges and roles each user has been granted
* Auditing information, such as who has accessed or updated various schema objects
* Other general database information

Not completely read-only as I found out once. What's the point? None really. I just like being able to copy and paste the documentation. OK, that's only partially true.

All the information that you retrieve via your nice GUI interface comes from these tables. SQL Developer, JDeveloper, Toad, etc. I believe some even have an option to view the SQL being submitted to the database. If you haven't used (knowingly) the Data Dictionary before, check it out. You can easily view all 651 views (10gR2) by issuing:

SELECT * FROM dictionary;

Happy hunting.

Constraints: ENABLE NOVALIDATE

Yesterday while perusing the Concepts Guide, I stumbled across the ENABLE NOVALIDATE keywords for the definition of a Foreign Key constraint. I've always known it was there, just never used it, or thought to use it.

It can be a big benefit while working on a legacy system.

Suppose you have a table, T_CHILD:

CREATE TABLE t_child
(
  child_id NUMBER(10)
    CONSTRAINT pk_childid PRIMARY KEY,
  soon_to_be_parent_id NUMBER(10)
);

INSERT INTO t_child
  ( child_id,
    soon_to_be_parent_id )
SELECT
    rownum,
    TRUNC( dbms_random.value( -9999, -1 ) )
FROM dual
  CONNECT BY LEVEL <= 10;

This table has been around for quite some time. You decide that you would like to constrain the values in the SOON_TO_BE_PARENT_ID column. First, here's the data that exists:

CJUSTICE@TESTING>SELECT * FROM t_child;

  CHILD_ID SOON_TO_BE_PARENT_ID
---------- --------------------
         1                -5560
         2                -1822
         3                -2499
         4                -7039
         5                -8718
         6                -1019
         7                -9997
         8                -9553
         9                -4477
        10                -1458

Now I'll create a table that will contain the values I want to constraint SOON_TO_BE_PARENT_ID to, call it a lookup or reference table.

CREATE TABLE t_parent
(
  parent_id NUMBER(10)
    CONSTRAINT pk_parentid PRIMARY KEY
);

I'll populate it with some data:

INSERT INTO t_parent( parent_id )
SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 10;

CJUSTICE@TESTING>SELECT * FROM T_PARENT;

 PARENT_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Now I'll add the constraint that references the PARENT_ID column of T_PARENT

ALTER TABLE t_child
  ADD CONSTRAINT fk_parentid
  FOREIGN KEY ( soon_to_be_parent_id )
  REFERENCES t_parent( parent_id )
  ENABLE
  NOVALIDATE;

and rename the column to PARENT_ID:

ALTER TABLE t_child RENAME COLUMN soon_to_be_parent_id TO parent_id;

What will this do? I should no longer be able to enter a value into T_CHILD.PARENT_ID that does not exist in T_PARENT, but it will ignore anything that already exists.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    11 );

INSERT INTO t_child
*
ERROR at line 1:
ORA-02291: integrity constraint (CJUSTICE.FK_PARENTID) violated - parent key not found

Perfect! Now I'll add a value that does exist in T_PARENT.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    10 );

1 row created.

Win!

This is just another reminder why you must read the Concepts Guide. By the way, I found the quote I was looking for from Mr. Kyte (h/t @boneist)

"...if you simply read the Concepts Guide...and retain just 10%..., you’ll already know 90% more than most people do"

Oracle Concepts: Data Integrity Rules

I'm reading through the Concepts manual again as mentioned on last week.

I'm going to make a small effort to post some of the key concepts here over the next couple of weeks. If you've read through the Concepts Guide before, this can serve as a brief refresher. If not, good, you're exposed to something new.

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

Reading on past the brief section to the Constraint States I found this nugget:

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.

Of course the ideal is to somehow clean the data up, but you don't always have that option. This is a good first step towards to overall cleanup of your legacy system.

Oracle Concepts

Years and years of reading AskTom, if there's one thing that I really come away with, it's read. RTFM. Specifically though, he's telling everyone to read the Concepts Guide. Paraphrasing:

If you can get a good look at the Concepts Guide by sticking your head...

That's not the one.

If you read the Concepts Guide you'll have 10% of the knowledge that 90% don't have.

Nope.

If 90% of the people would read 10% of the Concepts Guide, they'd be able to program in C...?

Seriously, I need help.

If you do know the saying I'm talking about, please share. I can't seem to find it anywhere.

Ultimately it doesn't matter what was said. I'm reading the Concepts Guide again, browsing through it really, trying to find something new. Then I noticed this:


I know it's a "feature" and all, but does it have to be so prominently displayed? Couldn't it be buried in the Appendix of the PL/SQL Guide or something? This is especially important since Mr. Kyte is telling all the new people to read the Concepts Guide.

Perhaps the new people figure it out, the ones that have read the Concepts Guide anyway. I've run across too many "experienced" database developers that can't create apps without them. My guess is that they have barely hit any of the documentation, let alone the Concepts Guide.

Financial Services: Learning the Business

I'm on a quest to find all the information I can about the Financial Service industry...specifically how it works.

I started with CardReport, which besides being pretty ugly, has some fairly useful information.

Of course it led me to much more reliable info sources.

First up, the Federal Reserve. That took me to the Government Printing Office (GPO) where I can then read in excruciating (and exciting!) detail all about Title 12, Banks and Banking.

That takes me to Regulations.gov, which I can then search through thousands of documents online (very nice). Sadly the material isn't all that exciting.

Finally, I found NACHA, The Electronic Payments Association. Here you can learn all about ACH (Automated Clearing House) payments. Joy.

I'm sure glad I've got this on my reading list. If I weren't a software developer, I'd probably go mad.

Oracle Docs New Look

Oracle Docs New Look

Today I opened my List of Books page, opened up the Java Developer's Guide and voila!, a new look:



I had seen this in the 11g documentation but this was the first time I had seen it spread elsewhere. I can't say I like it either. Too fancy.

That's scary...change scares me? What? Oh well, I'll get used to it and someday wonder how I lived without it, just like the iPhone!