Documenting a Database: Tell the Story of each Table - A Proposal

I have often dealt with existing database system with poor to non-existent documentation.  I considered myself lucky when meta-data about the database had been supplied!  The absence of documentation made its modifications, as well as changes to the middle-tier code a slow-going process since I had to reason about the database largely on my own, trying to understand how to navigate its key structures as well as the Business Process that it modeled.

To improve this common situation, I think it will be a good idea to approach the documentation from the point of view of storytelling; that is, document the Story or Stories that the data tells.  In a previous post, I proposed telling the Story of Data using such tools as Dramatica.

In this post, I am suggesting something less ambitious: write a single paragraph - a story - that describes the relationships among the attributes of that table and how they support a Business Process (how they help run the business).

My expectation would be that knowledgeable business owners would work with IT to produce this sort of documentation.

For example, consider an "Order" table that tracks all the orders placed in a business enterprise.  This table could have the following attributes:

• OrderID (PK)
• CustomerID (FK → Customer.CustomerID)
• OrderStatus (e.g., "Pending", "Processing", "Shipped", "Delivered", "Cancelled")
• OrderDate
• TotalAmount
• ShippingMethod
• ShippingCost
• TrackingNumber
• EstimatedDeliveryDate
• CreatedAt
• UpdatedAt

A possible story could be:

"A human being whom we think of as our Customer - here identified by his CustomerID - has placed a sales order for an item - identified by an OrderID, on the date identified by OrderDate, which is in the fulfillment status identified by "OrderStatus".  Furthermore, the shipping method and shipping costs are identified by ShippingMethod and ShippingCosts fields and the estimated delivery date is stored in the EstimatedDeliveryDate.  Total order amount is TotalAmount field and the actual cost of the order minus the shipping costs is an inferred quantity given by (TotalAmount minus ShippingCost)."

Another possible story could be:

"The shipping method identified by ShippingMethod attributes is used to ship the order identified by OrderID, which has the actual cost of (TotalAmount minus ShippingCost) is used when the OrderStatus has the  value of  "Shipped"."

Depending one one's preferences, one or another of the attributes could be made to be the central character of the story - its main protagonist.

Data as Story - A Proposal

Please find the IP that I developed below on transforming data into stories in 2014 which was disclosed here:

Babak Makkinejad, “Data as Story”, database number 617040, "Research Disclosure", Published in the September 2015 paper journal, Published digitally 21 August 2015 14:06 UT

With the advent of LLMs, my proposal would be easier to implement now.

Subject Matter & Problem

The main idea is this disclosure is the transformation of the relational data – often found in Relational Database Management Systems into something resembling a story; that is: a textual representation of the relational data that is telling a story in a natural language.

This disclosure does not discuss an automated system, rather it describes a semi-automated process in which a human expert would invoke software tools to transform data into a story. 

What is presented in this disclosure is akin to the process of report creation from available relational data using such tools as MS SQL Server Reporting Services, Apache’s Java BIRT etc. in which a human user designs a report – which consumes relational data – using a variety of software tool at the end of which an automated system published that report or makes it otherwise available.

This is not meant as a replacement for other modalities of data presentation such a charts and graphs but is meat as a complementary modality.  However, the presentation of the data as a story will be found by most human beings to be more engaging than looking at reams of form-based data or columnar data as represented by database extracts of MS Excel files.

Once the data is turned into a textual, human language story, it could be read out to a human being mechanically, or it could be automatically translated to a different human language. 

Solution

This solution crucially and fundamentally relies on the prior art embodied in the software tool called Dramatica (www.dramatica.org) and its Theory of Story (http://dramatica.com/theory/book).  The Theory of Story is briefly sketched out below.

Introduction to Dramatica’s Theory of Story

The Theory of Story embodied in Dramatica models a story as a single at work finding a solution to a single problem.  This is very analogous to the situation in Business Intelligence arena when different users, in trying to answer different questions, ask for different reports out a database system.  The BI users, in other words, are trying to solve a problem.

We have 4 main areas:

1. The overall story
2. The main character through whom we see everything
3. Impact character
4. The dynamics of the Impact Character vs. Main Character       

In each of the above areas one has to answer these essential questions:

1. 1.    Main character’s resolve: will he change or remain steadfast (no story if none of the characters in the story change)?
2. 2.    What drivers the story –actions or decisions
3. 3.    What is the main problem class of the story – Fixed Attitude, Manipulation, Situational, or Activities?
4. 4.    What is the main concern of the story – Past, Present, Future, and Dynamic (How things are changing)?
5. 5.    What is the overall story issue – Openness, Delay, Choice, Pre-conceptions?
6. 6.    What is the overall story problem – Control, Help, Hinder, Uncontrolled

Additionally, there could be multiple development lines in each story each with their own thematic arguments.  Themes are perspectives and thus could represent data as viewed from different view point of other story characters.

An argument’s topic may be further explored through dialogue, images, charts, pictures music etc. that complement the story. 

These later supporting material such as charts and graphs ties us to the common data representations via the applications of statistical algorithms and standard charting techniques.

Dramatica’s Theory of Story posits the existence of an overall story symptom and an overall story response; each story consists of a Problem, a Direction, a Focus, and a Solution.  The Problem is finally recognized some time near the climax of the story.  “Success” means the problem is replaced with a “Solution”.  “Failure” means that the problem is persisting.

Drmatica’s Theory of Story further posits that each story could contain up to 8 archetypes:

• Protagonist vs. Antagonist
• Guardian vs. Contagonist
• Reason vs. Emotion
• Side Kick vs. Skeptic

The Dramatica Structural Matrix

This is a framework for holding dramatic topics pertinent to Genre, Plot, Theme, and Character in relationships that describe their effect upon one another.  There are 4 Classes, Universe, Physics, Psychology, and Mind.  Each class contains 4 Types, and 16 variations (4 each) for each Type.  Each of those 16 Variations, in turn, contain 4 Elements for a total of 64 elements.

During the process of story-forming, these topics (called "themantics") are re-arranged much as a Rubik's cube might be scrambled, all in response to the author's choices regarding the impact they wish to have on their audience. As a story unfolds, the matrix unwinds, scene by scene and act by act until all dramatic potentials, both large and small have been completely explored and have fully interacted.

It is during this phase of story-forming that the relational data – based on their semantics (i.e. the meaning of the data columns in the database) are mapped into these 64-elements for each of the 4 Dramatica Classes.

Approach to Story Construction

Enterprises, commercial, governmental or non-profit, internally execute a set of (business) processes.  This is where the work for the story creation starts.  Examples of such processes are Human Resources, In-patient Management, Out-patient management, Manufacturing Quality Management and very many more.

One selects an existing business processes which is being executed and for which one wishes to tell a story.  That is, a specific business problem or question would be addressed via the story that is being developed.

For this process – or indeed any process – then tries to find the answer to the following questions:

 

1.       How

2.       What

3.       When

4.       Where (to/from)

5.       Who

6.       Whom

7.       Whose

8.       Why

 

Not all of these questions could potentially have answers within an arbitrary business process but some of them will have answers by necessity.

For example, for a Human Resources Management process, the questions could be:

What: role, title

When: hired, left the company, promoted, demoted, reprimanded, recognized, rewarded

Which: salary, rewards, taxes, expenses

Who: The Specific Employee (the Protagonist)

Where: Head-Quarters, Working-from-Home, Branch Office

This step may be automated via software Wizards that guide the user in determining the answers.  Such an automated systems will consume the relational data that supports that business process.  This identification may be based on automated inference or via data dictionaries available for the targeted process.

[A data dictionary contains the semantics of the data elements in the database; it may be viewed as an Ontology for that process – or it could be a subset of the larger Ontology of the Entire Enterprise.]

Next, with the answers to the above questions, and in conjunction with the data dictionary for the database tables, the dominant Class of the story may be selected.  It might be that the story to be developed does not have a dominant Class and all Classes need to be included to present the data.  An automated “Semantic Extraction” tool may be used to facilitate the assignment of the data fields in the database tables to these 4 Classes, their 4 Types, 16 Variations, and 64 Elements.  Alternatively this step could be performed manually. 

This is the step that ties the RDBMS data to Dramatica’s structures.

In practice, the meta-data from the data dictionary may not be sufficiently numerous to cover all 256 bins (Elements) that are available for all the 4 Classes.  Or, alternatively, there could be multiple meta-data elements (concepts) that are mapped to the same Element.  It is a judgment call by the story-teller, looking at the requirements of the story, to decide which meta-data elements to keep and which ones to discard.

The story, ultimately, is a report and must supply answers to the business questions/problems that are posed by its consumers/users.

At this point, the storyteller is in position to utilize a system based on Dramatica and its Wizards, to guide him through the construction of the story. 

For example, the story might be one that is required to tell what happened to a (heart) patient admitted to the emergency room.  Within the Theory of Story of Dramatica, the storyteller would identify the patient (at this stage an unknown person) as the Protagonist, (Heart) Disease as the Antagonist, the (Heart) Surgeon as the Guardian, and Pre-existing Medical Conditions as the Contagionist.  The story would tell the how/when/where/who was admitted, the initial diagnosis, the climax (open heart surgery) and the recovery and discharge as well as other details per the requirements (members of the surgical staff, length of the operation, type of procedures, etc.).

Another example could be Manufacturing Quality Management process in which a type of widget is identified as the Protagonist, Manufacturing Process is considered as the Antagonist, Quality Engineer is identified as the Guardian and the Contagionist could be a production worker or the production machinery.

It should be noted that the stories that are being discussed in this disclosure are all generic, the identity of the patient, or the disease or the widget type are left undefined.  In the sense of RDBMS Reports, these stories may be understood as parameterized reports.

Dramatica already has Wizards that guide one in the construction of one’s story.  However, for this disclosure, it is envisioned that Dramatica’s Wizards and perhaps engine would be augmented in such a manner as to facilitate the construction of stories in which the 8 archetypes are not necessarily human being but could be things or processes.

For example, SQL queries to automatically pull data for an unknown patient, with an unknown disease could be formulated via Wizards during the story-forming stage.  The (augmented) Dramatica will then generate the story per the relational data, its meta-data, and story-teller’s decisions.

The user could then access an online system and request a story that tells what happened to Mr. Smith, with heart disease, who entered the emergency room of the General Hospital between the hours of 8:00 PM to 8:00 AM during the month of August – if any.  The system will generate a textual story as discussed in the above disclosure which could be printed, emailed, turned into an audio-file or exported to a suitable format for printing such as MS Word or Adobe PDF.

Possible Modifications

Extension to non-SQL, unstructured data.

Extension to telling multiple stories at once – by following different business processes within the same story.  For example, while telling the story of the treatment of a heart disease patient which is within the In-Patient Management process, one can also tell the story of specific surgeon who operated on him within the Human Resource Management process.

A story may be incorporated into a more customary report which contains tabular data and charts.  Alternatively, the story itself, in its published form when its parameters are specified, may contain such tabular data and charts.

The Dramatica Documentation

Downloadable Items - Dramatica

 

  

Data Monetization Estimation

 

William Thomson, Lord Kelvin

"When you can measure what you are speaking about, and express it in numbers, you know something about it, when you cannot express it in numbers, your knowledge is of a meager and unsatisfactory kind; it may be the beginning of knowledge, but you have scarcely, in your thoughts advanced to the stage of science.”

Lecture on "Electrical Units of Measurement" (3 May 1883), published in Popular Lectures Vol. I, p. 73

Estimating Data Value

Introduction

There has been a lot of interest in data monetization over the last decade or so and this discussion is meant as a way of thinking about estimating the potential value of data and in contradistinction to realization of that value.  Think of this as an analogue of a geological survey without any guarantee of finding economically viable mines; an attempt at a quantitative analysis of what value data have along the lines that the late Lord Kelvin had suggested.

Column Value Model

We assume a company’s value to be a combination of human, physical, and data capital; a fully automated company with no human resources is a pipedream, a company must have physical assets to conduct business (be they rented) and must make decisions based on some information or other.  (For simplicity, Goodwill valuation is excluded from this model).

So we start with the following formula for the company’s valuation and the proceed to give an estimate of its data assets in a more defined manner.

            Company Valuation = Human Capital + Physical Capital + Data Capital

Assume all three parts have the same value and the company’s valuation is $ 3 billion:

               Data Capital = Company Valuation/3 = $ 1 Billion

In this model, we would like to estimate the average value of each column of data in all the relational schemas that the company uses, i.e.

              

Let us further assume that there are 50 relational schemas in this company, that they each have 50 tables of 20 columns each.  This gives a total of 50,000 columns in total that yields an average value of $ 20,000 per column.  In this approach, all columns are considered to be of equal value; be they customer names (let us say) or such ubiquitous columns as “Last Updated Date”.

We can then proceed to estimate an Average value for each Schema

In this discussion, since all schema are assumed to be identical in the number of tables and columns, their average data value is $ 20 million.  However, in practice, there are different schema and each is different from the other and this type of model serves to identify the most valuable schema that a company has.

Alternative Models

Data Volume Model

In this model, the value of each schema is estimated based on its data volume.  That is:

The total Data Valuation will be divided by this number, an average value per Gigabyte is extracted, and the value of each schema is then computed by multiplying its data volume by that average number. 

Time Dependent Model

Another model is one with time-dependent variable weights for value of each part of the company’s valuation, i.e.:

 Valuation = h(t) Human Capital + p(t) Physical Capital + d(t) Data Capital

With constrains:

h(t) + p(t) + d(t) = 1 and h(t) ≥ 0, p(t) ≥ 0, d(t) ≥ 0

Weighted Schema Model

In this model, we are capturing the importance of each schema – via the factor a(n) – and the historical data volume available for each Schema by the second sum and the factor b(m). 

The exp(1-m) is intended to model the aging and staleness of the data.

 

Is Excel a Problem?

These thoughts were triggered in my mind by a recent Wall Street Journal article, titled: "

Stop Using Excel, Finance Chiefs Tell Staffs".  (Please see: https://www.wsj.com/articles/stop-using-excel-finance-chiefs-tell-staffs-1511346601 and read the very many insightful comments.)

Yes, I too am tired of MS Excel paradigm.  It is like always a wearing gingham shirt - so many men in IT express their inner-nerd that way.  I mean, if they want Excel-like features, we could always use VBA to tie a UI, in Excel, to the back-end services (be they direct database connections or REST API calls.)

We serve the end users poorly by giving them Excel look alike UI, but in HTML.  We are missing something essential in requirements engineering by not providing a different paradigm (say a Wizard, or an Informative AI Agent),  thus hiding all that cognitive complexity from them.

I think Excel is widely used precisely because IT cannot create light-weight tactical solutions, or, as often is the case, plain refuses to do so.

Myself and others have created such tools for small groups, say less than 10 users, using VBA, REST API, or ODBC-based database access solutions.  But that has never been where the action was. 

Monolithic Web applications rule the roost.  Likely because CIOs understand that level of budgeting and find such systems to be a feather in their hats.

There are very good reasons for small teams (~ 10) to use Excel; IT is not going to make a better tool for small groups of people, Excel is ubiquitous with tremendous analytical capabilities built-in, that team controls the evolution of their Excel tool themselves; changes do not have to be begged from IT, and lastly because of the heterogeneity of corporate data stores which forces people to use data from semantically distinct systems with little or no overlaps in their respective ontologies.

This last one could be ameliorated via some sort of AI-enabled Data Buddy, which could infer missing data from what is in Excel and go and fetch it from whichever database that contains that missing data - all without the end-users being aware of them; sort of like an active Data Management agent on steroids.

In the many engineering firms, there are engineering teams that use home-grown Excel tools for their data management needs.  Generic tools, Cloud-based or not, are not useful to them: they are not accountants.  And yet, how quickly we at IT can enhance their existing tools, let alone replace them?  How long does it take to gather requirements, build another silly Angular/Spring-Boot system, and fit it with Excel import-export facilities?

In Web applications that follow the Excel tabular data presentation paradigm, there are great opportunities to move away from tabular data presentation to Insights gleaned from data and automatically generated and presented to the end-users.  That is what IT should do rather than implementing yet another useless and expensive CR.

I own many gingham shirts, but I also wear many different fabrics and patterns.  We, at IT,  need to give end-users more options and unless they positively and adamantly ask for little grids, we should engage in a dialogue to explore the design space: and not just for today or tomorrow, but for the Day after Tomorrow as well.

Data-storage Records for DNA

Researchers break record for DNA data storage

http://www.nature.com/news/dna-data-storage-breaks-records-1.11194 

Recent Advances in Data Science

Last year, Zoubin Ghahramani published an algorithm that automates the job of a data scientist, from looking at raw data all the way to writing a paper.

His software, called Automatic Statistician, spots trends and anomalies in data sets and presents its conclusion, including a detailed explanation of its reasoning.

The paper is @ http://www.nature.com/nature/journal/v521/n7553/full/nature14541.html
 
I think the implications are quite clear.

Already, there is: https://www.automaticstatistician.com/about/#

A Step-by-Step Example of Turning Data Actionable

This article, by Gretchen Peck, is a walk-thru of data analytics in the concrete case of print publishing. Please note the primacy of knowing the business - in this case the publishing business - to the effort; Big Data is not about technology, it is about asking the right questions about the business that is being analyzed.
http://www.editorandpublisher.com/feature/put-your-data-into-action-with-this-step-by-step-guide/

Managing MongoDB

These are 2 useful Administrative UI tools for managing MongoDB.

Robomongo: Simple to use and open-source but limited functionalities with major usability defects.

MongoChef: This has a free and a commercial license and is full-featured.

Free: Microsoft SQL Server Developer Edition

Microsoft SQL Server Developer Edition is now free
https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free

The Promise of Action-able Intelligence

Actionable Intelligence for Social Policy (AISP) is an initiative funded by the John D. and Catherine T. MacArthur Foundation, with the purpose of improving the quality of education, and health and human service agencies’ policies and practices through the use of integrated data systems.


A Q&A session with Dennis Culhane and John Fantuzzo, principal investigators may be found here:

http://www.brookings.edu/blogs/up-front/posts/2016/01/19-promise-integrated-data-butler

Don’t Let the Billions of Data Points Fool You

From CSIS:


Big Data: Don’t Let the Billions of Data Points Blind You to the Problem of Too Few Sources to Check the Results - please see http://arxiv.org/ftp/arxiv/papers/1403/1403.7400.pdf

Forecasting Mobile Data Traffic Growth

From CSIS:


http://www.brookings.edu/blogs/techtank/posts/2014/04/18-cisco-mobile-growth-projection-bleiberg-west

On Big Data

The rise of Big Data:

http://www.foreignaffairs.com/articles/139104/kenneth-neil-cukier-and-viktor-mayer-schoenberger/the-rise-of-big-data?cid=oth-cfr-callout-mj13-04232013

and 

Think Again: Big Data

http://www.foreignpolicy.com/articles/2013/05/09/think_again_big_data

Cascading

Cascading is a Java application framework that enables quick and easy development of rich Data Analytics and Data Management applications that  work seamlessly with Apache Hadoop 1.0 and API compatible distributions.

Learn more about it @ http://www.cascading.org/about/

MapReduce Book

The book "MapReduce Design Patterns" by Donald Miner and Adam Shook is a good intermediate resource on MapRedue.

Each pattern is explained in context, with pitfalls and caveats clearly identified to help avoid common design mistakes when modeling large data architecture. It also provides a complete overview of MapReduce that explains its origins and implementations, and why design patterns are so important. All code examples are written for Hadoop.  They are:

• Summarization patterns: get a top-level view by summarizing and grouping data
• Filtering patterns: view data subsets such as records generated from one user
• Data organization patterns: reorganize data to work with other systems, or to make MapReduce analysis easier
• Join patterns: analyze different datasets together to discover interesting relationships
• Metapatterns: piece together several patterns to solve multi-stage problems, or to perform several analytics in the same job
• Input and output patterns: customize the way you use Hadoop to load or store data

This book does not have the step-by-step instructions of a "recipe" book, thus avoiding line-by-line breakdowns and delivering a lot of content in its 436 pages. (There is also a usable summary in 30 or so pages.)

Six free databases

From the Network World:

http://www.networkworld.com/reviews/2012/120312-databases-test-263922.html

Useful Introductions to MapRedue

Some useful material on MapReduce

http://code.google.com/edu/parallel/mapreduce-tutorial.html

web.cs.wpi.edu/~cs4513/d08/OtherStuff/MapReduce-TeamA.ppt

http://www.sdtimes.com/link/36028

PowerPivot

Microsoft’s PowerPivot add-in for its Excel 2010 enables users to work with much larger sets of data - say 3 million records.

Another reason to go to Office 2010.

http://www.eweek.com/c/a/Enterprise-Applications/PowerPivot-Raises-the-Bar-on-Row-Limitations-441592/

Hadoop

Hadoop is an open-source Java software framework for data-intensive distributed applications. It enables applications to interact with thousands of data nodes.

Hadoop is an implementation of MapReduce which is a programming model for processing and generating large data sets. Users specify a map function that processes a key/value pair to generate a set of intermediate key/value pairs, and a reduce function that merges all intermediate values associated with the same intermediate key.

Programs written in this functional style are automatically parallelized and executed on a large cluster of commodity machines. The run-time system takes care of the details of partitioning the input data, scheduling the program's execution across a set of machines, handling machine failures, and managing the required inter-machine communication. This allows programmers without any experience with parallel and distributed systems to easily utilize the resources of a large distributed system.

Hadoop was created by Doug Cutting, currently with Cloudera.

Data Preservation

In the 08/28/2009 issue of the Wall Street Journal, we read:

"Our ability to collect data now outstrips our ability to maintain it for the long run," says William Michener at the University of New Mexico, who leads a data-preservation network called DataONE. "We lose an awful lot of data that is collected with public funds."