Thanks for the question, Wijnand.
Asked: November 21, 2016 - 12:16 pm UTC
Last updated: November 22, 2016 - 11:08 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Every now and then, a new application with Oracle database comes to us. The manual is usually only end-user orientated. Seldom we get cubes, packages or frameworks for Cognos. A full database description is even more rare. What would be the fastest, structured method to examine how the data is organised?
Purpose is to write reports and get management info that is not provided by the application itself. The managers are very inventive in posing new unexpected questions that are not covered by the application.
At the moment I walk around some time in the menus of the new application, examine some records, then open the SQL> prompt and I look at all_tables, all_views and all_tab_columns.
Tables and views with the most records, examine the view_texts, column_names that occur most in tables and views, index fields etc.
Then, provide some reports, get user feedback on the results, ("THAT can't be right!";"Why not?") rewrite until satisfied.
Are there more sofisticated, structured approaches?
and Chris said...
The meaning of data are closely tied to the application and business which created it. So without documentation it's a challenge to understand it!
There are a couple of main methods:
- Top-down: speak with the data consumers in the business. Find out what items mean. Then map through the application to the database.
- Bottom-up: work from the database. Look at the table structures and see what queries are run against the data. Then map to the user functions.
To fully understand you'll want to do both. For the bottom up approach, I'd start by:
- Getting Oracle Data Modeler
- Extracting the schema(s) into this
- Use this to check the entities, follow the foreign keys, etc.
You can group related tables into subviews making it easier to see what's going on. For example, you could create a view of "customer" tables, "order" tables, etc. You can also add annotations to document what's going on as you build knowledge.
This of course assumes that:
- The tables etc. have sensible names
- Tables only represent a single entity
- Constraints are defined on the tables as appropriate
If there's no documentation for the database and any of the above are true figuring out what means what can be a struggle...
Ultimately it'll be a matter of iteratively improving your understanding. So your method of producing reports and getting feedback on the results is a good one.
But be warned. Data consumers often don't understand their data as well as they should do. I've seen businesses rely on reports which turned out to have fundamental flaws giving incorrect results! So just because someone believes a report is correct doesn't mean it is...
If possible, find another data set you can compare your results to.
Good luck!
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment