where grayMatters meets Data

Starting afresh

So, it's a New Year.
And what is my resolution(s) for this year.
Well, nothing that I am putting to electronic print, that's for sure...

But, I am and always be intrigued by process, what's there, how it's used, who uses it, who doesn't, etc.  So, with that in mind, I am going try and use the PPDM v3.8 data model to truly capture process.  I have a real nice little query that details a lot of information about privileges and grants in an Oracle database that I want to load into the PPDM meta-model.  So, I will.  And I will use this blog as a way of explaining what/how I have done.

So, I have done the preliminary data mapping between the result set from the query and tables in PPDM.  And, as usual; it does not map 100%.  So, I have the option of either trying to get the PPDM to alter the model or just do it myself.  Now, that means that I don't have a 100% compliant PPDM implementation, but then, who does?

The next step is to define how to load/update the data.  Here is what I have in mind:
- select an Oracle ID that I want to put my temporary load table in
- create temporary table & sequence
- write the sqlldr script to purge & then load the temp table
- grant permissions (Select, Insert, Update, Delete) on PPDM tables that I access
- write a data loader that will load the data as well as track that I loaded the data.  I have not mapped out how to do this, but it's on the list

One last problem is where to store what I have done.  I like MindManager as it's a very graphical way to display what you have done.  I also like my wiki as it's really portable.  And then there's this blog...

Review what I did, find some problems, fix it and repeat

I imported the schema into my XE database on my tablet computer and was looking at the data via my Load of the Rings application and found that the PPDM Groups was only populated with 'CORE' tables, not the referenced tables.  So I went back to the main database and set about populating these rows.

Well, that uncovered that I did not populate the reference constraints properly, so I had to fix that first and then populate the 'REFERENCED' tables. 

That's all done and I will export the schema again, copy it to my tablet and then what else I find out that I missed.

Getting Started

Okay, so I have a new set of DDL from the PPDM to load up the RMOTC data set.  Now where to start?  Well, here are the steps that I did.

1. Create a new Oracle schema called P38 with connect & resource.  Not very exciting or current, but it works.
2. edit the .sql script that came with it so a .log file is created and I can see what, if any errors are create.
3. run the .sql script file and review the .log file; no errors.  Okay, I have a empty PPDM v3.8 Alpha schema with all 1692 tables and full RI.  Yippy.
4. Have to load the base tables, R_SOURCE & R_PPDM_ROW_QUALITY.  There are two ways of doing this; the first is to run a series of SQL Insert & Update statements in the right order or disable the RI between the two tables, run simple SQL Insert statements and then turn the RI back on.  That's the key, turning the RI back on.
5. The next step is to populate the R_* tables of the tables that I want to use.  Fortunately I have a series of SQL Insert files kicking around and just modded these.  I did review what was stored in the row_quality and source columns.
6. Now on to the capturing the meta-data object info.  Again, I have a series of SQL statements that do this.  I had to assign 'dba' to the account; temporarily, so that I could query the 'dba_*' views.  I got lazy about this but what the hay.  It's my database.  I did remember to revoke this privilege at the end.
7. Did an export at this point and imported the dataset into the Oracle XE database on my tablet.

A Journey

Hey, it's been awhile.
Where have I been?

Mainly trying to wrap up stuff at the lake and then prep for the PPDM Fall conference.  I volunteered to try and load a public data set into the PPDM v3.8 data model.  The reason for this was to provide a standard data set that shows the integration of PPDM.  So, that's done and two things came out of it:
- members showed great enthusiasm about it (but that's about it)
- meta data should be able to store what we decided and how / where / why /when we did it.

So this blog is a great place to document what was done in the project and where it could be stored in the meta-data.

R_CHICKEN_OR_EGG

So, which comes first?
That's always the quandary. 
Especially when you want to start to populate a PPDM database without turning off any constraints.  Do you populate the R_SOURCE table first?  But then, how can you put a value in the Row_Quality column?  Okay, so then let's populate the R_PPDM_ROW_QUALITY table first.  And you are going to put what in the Source column?
So, which comes first?

Well, it really does not matter.  If you take a bit of time, you can populate these two tables with a couple of SQL statements, without disabling constraints.  How do you do it?  Well, lets find out.  Say I wanted to store PPDM and dMCI as sources with REAL, DERIVED & NOT CLASSIFIED as row quality values.  One way is:

• store PPDM as a source, without a row quality or source value
• store REAL & NOT CLASSIFIED as row quality with the source of PPDM
• update the row quality value in the row in the source table to REAL
• add dMCI as a source, with a row quality of NOT CLASSIFIED and source of PPDM
• add DERIVED as row quality with the source of dMCI

There you have it.  From this, you should be able to build on populating your database.  That's all there is to it.  You could also start with populating the row quality table first; just make sure that you don't put anything into the source column in the initial insert.  You have to update it later.
There are several other tables in PPDM that have this type of relationships.  You just have to slow down, think about it and realize that it will take a couple of statements to make it happen. 

Now, back to that chicken....

Too much fun

Well, I finally got the Oracle package working for the PPDM Association.  This should greatly help speed up the release of v3.8.  I can't wait for it to come out as the meta-data section promises to be nice to work with. 
I am on holidays up at Sylvan Lake and of course, expected to get a bunch of stuff done.  Computer/data stuff.  Well, about all I got done is some water-skiing, wake-boarding and the new sport: wake surfing.  Not that I am complaining, either. 
Did fine one interesting product;  FileList.  I needed to list all of the files in a directory (and the subsequent sub-directories), when they were created and if I could, get a column of all of the unique file-types.  Now, you can do this with a dos command like:  dir /a /b >output.csv etc.  And that's what I did and spent two plus hours editing the output into a usable format.  After I was done, I then looked for this program and be darned if I did not find it; on my own c:\ drive.  So, I redid my work in a couple of minutes and liked the result even more than what I did by hand.  So, the lesson here?  Be lazy and expect that someone else has done what you need done before and go & find it.
Now to got on to serious business... logical data architecture.

Finally, at it again

I finally have freed up some time to work on the Case procedures that I wrote for the PPDM years ago.  Lots of cobwebs there but I can see why the PPDM Association wants it done.  Saves lots and lots of time.  It will be interesting working on it from the lake.  Wireless to the internet and then VNC'ng to their network.  It has worked for other contracts, so it should work for this one.  NOT that this is a contract...  Damn, I'm good to some people.

Also found this:  ASuite   It should allow me to 'bake my own' since it's written in Delphi.  I have some new ideas around how to do the work I do, especially since my hard drive failure.  This might be one of them.

And let's not forget about some playtime.  More on that later.

Working up to a new release

The PPDM Association is currently in the process of releasing version 3.8.  There are several new modules in this release, such as well operations and geochemistry.  What really interests me are the data Management and the meta-data mapping additions.  Finally, there will be a standards model where someone can track meta-data information from multiple systems in a single repository.  I have been doing that for several years and basically, it makes me look real smart.  I wish that it was there in v3.7 but oh well, better late than never.
I am helping out the PPDM by running some rudimentary checks on field lengths, data types, column name mismatches.  Nothing that hard but it is time consuming.  And nothing that I hate more than working with a table with a misspelt word or a data type that is in-correct.  Always easier to catch these things early in the release cycle rather than later.  My understanding that this will be the last alpha and that a beta release will happen in the summer with the full production release anticipated for the fall.  Possibly the fall conference?

It does mean that I will have to update my Load of the Rings application.  That's alright.  It's been a couple of years and maybe with this new meta model, I can really improve it.

Wouldn't it be great if ...

This book really existed:

PPDM Modeling Committe

Went to a modeling committee meeting on Friday.  Have a good session and I think we accomplished what was on the agenda.  Took my tablet with me and am continued to be amazed that it lasts the whole time.  It seems quite natural to be in a meeting with someone writing on, what at first glance looks to be a thick pad of paper.  In order to get the final alpha version of the PPDM 3.8 data model out, I volunteered to run a series of tests against the data model and report back to Trudy.  I will post what I have done when I do it.

Found out that there is a startup meeting of 'Data Architect's' here and want to become more involved with the group.  I guess the first step is to find out about it and then attend some meetings.