database management systems | Life, Project Management, and Everything

Potato, Potahto, Tomato, Tomahto: Data Normalization vs. Standardization, Why the Difference Matters

In my vocation I run a technology company devoted to program management solutions that is primarily concerned with taking data and converting it into information to establish a knowledge-based environment. Similarly, in my avocation I deal with the meaning of information and how to turn it into insight and knowledge. This latter activity concerns the subject areas of history, sociology, and science.

In my travels just prior to and since the New Year, I have come upon a number of experts and fellow enthusiasts in these respective fields. The overwhelming numbers of these encounters have been productive, educational, and cordial. We respectfully disagree in some cases about the significance of a particular approach, governance when it comes to project and program management policy, but generally there is a great deal of agreement, particularly on basic facts and terminology. But some areas of disagreement–particularly those that come from left field–tend to be the most interesting because they create an opportunity to clarify a larger issue.

In a recent venue I encountered this last example where the issue was the use of the phrase data normalization. The issue at hand was that the use of “data normalization” suggested some statistical methodology in reconciling data into a standard schema. Instead, it was suggested, the term “data standardization” was more appropriate.

These phrases do not describe the same thing, but they do describe processes that are symbiotic, not mutually exclusive. So what about data normalization? No doubt there is a statistical use of the term, but we are dealing with the definition as used in digital technology here, just as the use of “standardization” was suggested in the same context. There are many examples of technical terminology that do not have the same meaning when used in different contexts. Here is the definition of normalization applied to data science from Technopedia, which is the proper use of the term in this case:

Normalization is the process of reorganizing data in a database so that it meets two basic requirements: (1) There is no redundancy of data (all data is stored in only one place), and (2) data dependencies are logical (all related data items are stored together). Normalization is important for many reasons, but chiefly because it allows databases to take up as little disk space as possible, resulting in increased performance.
Normalization is also known as data normalization

This is pretty basic (and necessary) stuff. I have written at length about data normalization, but also pair it with two other terms. This is data rationalization and contextualization. Here is a short definition of rationalization:

What is the benefit of Data Rationalization? To be able to effectively exploit, manage, reuse, and govern enterprise data assets (including the models which describe them), it is necessary to be able to find them. In addition, there is (or should be) a wealth of semantics (e.g. business names, definitions, relationships) embedded within an organization’s models that can be exposed for improved analysis and knowledge transfer. By linking model objects (across or within models) it is possible to discover the higher order conceptual objects for any given object. Conversely, it is possible to identify what implementation artifacts implement a higher order model object. For example, using data rationalization, one can traverse from a conceptual model entity to a logical model entity to a physical model table to a database table, etc. Similarly, Data Rationalization enables understanding of a database table by traversing up through the different model levels.

Finally, we have contextualization. Here is a good definition using Wikipedia:

Context or contextual information is any information about any entity that can be used to effectively reduce the amount of reasoning required (via filtering, aggregation, and inference) for decision making within the scope of a specific application.^[2] Contextualisation is then the process of identifying the data relevant to an entity based on the entity’s contextual information. Contextualisation excludes irrelevant data from consideration and has the potential to reduce data from several aspects including volume, velocity, and variety in large-scale data intensive applications

There is no approximation of reflecting the accuracy of data in any of these terms wihin the domain of data and computer science. Nor are there statistical methods involved to approximate what needs to be accomplished precisely. The basic skill required to accomplish these tasks–knowing that the data is structured and pre-conditioned–is to reconcile the various lexicons from differing sources, much as I reconcile in my avocation the meaning of words and phrases across periods in history and across languages.

In this discussion we are dealing with the issue of different words used to describe a process or phenomenon. Similarly, we find this challenge in data.

So where does this leave data standardization? In terms of data and computer science, this describes a completely different method. Here is a definition from Wikipedia, which is the proper contextual use of the term under “Standard data model”:

A standard data model or industry standard data model (ISDM) is a data model that is widely applied in some industry, and shared amongst competitors to some degree. They are often defined by standards bodies, database vendors or operating system vendors.

In the context of project and program management, particularly as it relates to government data submission and international open standards across vendors in an industry, is the use of a common schema. In this case there is a DoD version of a UN/CEFACT XML file currently set as the standard, but soon to be replaced by a new standard using the JSON file structure.

In any event, what is clear here is that, while standardization is a necessary part of a data policy to allow for sharing of information, the strength of the chosen schema and the instructions regarding it will vary–and this variation will have an effect on the quality of the information shared. But that is not all.

This is where data normalization, rationalization, and contextualization come into play. In order to create data for the a standardized format, it is first necessary to convert what is an otherwise opaque set of data due to differences into a cohesive lexicon. In data, this is accomplished by reconciling data dictionaries to determine which items are describing the same thing, process, measure, or phenomenon. In a domain like program management, this is a finite set. But it is also specialized knowledge and where the value is added to any end product that is produced. Then, once we know how to identify the data, we must be able to map those terms to the standard schema but, keeping on eye on the use of the data down the line, must be able to properly structure and ensure interrelationships of the data are established and/or maintained to ensure its effective use. This is no mean task and why all data transformation methods and companies are not the same.

Furthermore, these functions can be accomplished efficiently or inefficiently. The inefficient method is to take the old-fashioned business intelligence method that has been around since the 1980s and before, where a team of data scientists and analysts deal with data as if it is flat and, essentially, reinvents the wheel in establishing the meaning and proper context of the data. Given enough time and money anything can be accomplished, but brute force labor will not defeat the Second Law of Thermodynamics.

In computing, which comes close to minimizing that physical law, we know that data has already been imbued with meaning upon its initial processing. In lieu of brute force labor we apply intelligence and knowledge to accomplish this requirement. This is called normalization, rationalization, and contextualization of data. It requires a small fraction of other methods in terms of time and effort, and is infinitely more transparent.

Using these methods is also where innovation, efficiency, performance, accuracy, scalability, and anticipating future requirements based on the latest technology trends comes into play. Establishing a seamless flow of data integration allows, for example, the capture of more data being able to be properly structured in a database, which lays the ground for the transition from 2D to 3D and 4D (that is, what is often called integrated) program management, as well as more effective analytics.

The term “standardization” also suffers from a weakness in data and computer science that requires that it be qualified. After all, data standardization in an enterprise or organization does not preclude the prescription of a propriety dataset. In government, this is contrary to both statutory and policy mandates. Furthermore, even given an effective, open standard, there will be a large pool of legacy and other non-conforming data that will still require capture and transformation.

The Section 809 Panel study dealt directly with this issue:

Use existing defense business system open-data requirements to improve strategic decision making on acquisition and workforce issues…. DoD has spent billions of dollars building the necessary software and institutional infrastructure to collect enterprise wide acquisition and financial data. In many cases, however, DoD lacks the expertise to effectively use that data for strategic planning and to improve decision making. Recommendation 88 would mitigate this problem by implementing congressional open-data mandates and using existing hiring authorities to bolster DoD’s pool of data science professionals.
Section 809 Volume 3, Section 9, p.477

As operating environment companies expose more and more capability into the market through middleware and other open systems methods of visualizing data, the key to a system no longer resides in its ability to produce charts and graphs. The use of Excel as an ad hoc data repository with its vulnerability to error, to manipulation, and for its resistance to the establishment of an optimized data management and corporate knowledge environment is a symptom of the larger issue.

Data and its proper structuring is at the core of organizational success and process improvement. Standardization alone will not address barriers to data optimization. According to RAND studies in 2015 and 2017* these are:

Data Quality and Discontinuities
Data Silos and Underutilized Repositories
Timeliness of Data for use by SMEs and Decision-makers
Lack of Access and Contextualization
Traceability and Auditability
Lack of the Ability to Apply Discovery in the Data
The issue of Contractual Technical Data and Proprietary Data

That these issues also exist in private industry demonstrates the universality of the issue. Thus, yes, standardize by all means. But also ensure that the standard is open and that transformation is traceable and auditable from the the source system to the standard schema, and then into the target database. Only then will the enterprise, the organization, and the government agency have full ownership of the data it requires to efficiently and effectively carry out its purpose.

*RAND Corporation studies are “Issues with Access to Acquisition Data and Information in the DoD: Doing Data Right in Weapons System Acquisition” (RR880, 2017), and “Issues with Access to Acquisition Data and Information in the DoD: Policy and Practice (RR1534, 2015). These can be found here.

Takin’ Care of Business — Information Economics in Project Management

Neoclassical economics abhors inefficiency, and yet inefficiencies exist. Among the core issues that create inefficiencies is the asymmetrical nature of information. Asymmetry is an accepted cornerstone of economics that leads to inefficiency. We can see in our daily lives and employment the effects of one party in a transaction having more information than the other: knowing whether the used car you are buying is a lemon, measuring risk in the purchase of an investment and, apropos to this post, identifying how our information systems allow us to manage complex projects.

Regarding this last proposition we can peel this onion down through its various levels: the asymmetry in the information between the customer and the supplier, the asymmetry in information between the board and stockholders, the asymmetry in information between management and labor, the asymmetry in information between individual SMEs and the project team, etc.–it’s elephants all the way down.

This asymmetry, which drives inefficiency, is exacerbated in markets that are dominated by monopoly, monopsony, and oligopoly power. When informed by the work of Hart and Holmström regarding contract theory, which recently garnered the Nobel in economics, we have a basis for understanding the internal dynamics of projects in seeking efficiency and productivity. What is interesting about contract theory is that it incorporates the concept of asymmetrical information (labeled as adverse selection), but expands this concept in human transactions at the microeconomic level to include considerations of moral hazard and the utility of signalling.

The state of asymmetry and inefficiency is exacerbated by the patchwork quilt of “tools”–software applications that are designed to address only a very restricted portion of the total contract and project management system–that are currently deployed as the state of the art. These tend to require the insertion of a new class of SME to manage data by essentially reversing the efficiencies in automation, involving direct effort to reconcile differences in data from differing tools. This is a sub-optimized system. It discourages optimization of information across the project, reinforces asymmetry, and is economically and practically unsustainable.

The key in all of this is ensuring that sub-optimal behavior is discouraged, and that those activities and behaviors that are supportive of more transparent sharing of information and, therefore, contribute to greater efficiency and productivity are rewarded. It should be noted that more transparent organizations tend to be more sustainable, healthier, and with a higher degree of employee commitment.

The path forward where there is monopsony power, where there is a dominant buyer, is to impose the conditions for normative behavior that would otherwise be leveraged through practice in a more open market. For open markets not dominated by one player as either supplier or seller, instituting practices that reward behavior that reduces the effects of asymmetrical information, and contracting disincentives in business transactions on the open market is the key.

In the information management market as a whole the trends that are working against asymmetry and inefficiency involve the reduction of data streams, the construction of cross-domain data repositories (or reservoirs) that allow for the satisfaction of multiple business stakeholders, and the introduction of systems that are more open and adaptable to the needs of the project system in lieu of a limited portion of the project team. These solutions exist, yet their adoption is hindered because of the long-term infrastructure that is put in place in complex project management. This infrastructure is supported by incumbents that are reinforcing to the status quo. Because of this, from the time a market innovation is introduced to the time that it is adopted in project-focused organizations usually involves the expenditure of several years.

This argues for establishing an environment that is more nimble. This involves the adoption of a series of approaches to achieve the goals of broader information symmetry and efficiency in the project organization. These are:

a. Instituting contractual relationships, both internally and externally, that encourage project personnel to identify risk. This would include incentives to kill efforts that have breached their framing assumptions, or to consolidate progress that the project has achieved to date–sending it as it is to production–while killing further effort that would breach framing assumptions.

b. Institute policy and incentives on the data supply end to reduce the number of data streams. Toward this end both acquisition and contracting practices should move to discourage proprietary data dead ends by encouraging normalized and rationalized data schemas that describe the environment using a common or, at least, compatible lexicon. This reduces the inefficiency derived from opaqueness as it relates to software and data.

c. Institute policy and incentives on the data consumer end to leverage the economies derived from the increased computing power from Moore’s Law by scaling data to construct interrelated datasets across multiple domains that will provide a more cohesive and expansive view of project performance. This involves the warehousing of data into a common repository or reduced set of repositories. The goal is to satisfy multiple project stakeholders from multiple domains using as few streams as necessary and encourage KDD (Knowledge Discovery in Databases). This reduces the inefficiency derived from data opaqueness, but also from the traditional line-and-staff organization that has tended to stovepipe expertise and information.

d. Institute acquisition and market incentives that encourage software manufacturers to engage in positive signalling behavior that reduces the opaqueness of the solutions being offered to the marketplace.

In summary, the current state of project data is one that is characterized by “best-of-breed” patchwork quilt solutions that tend to increase direct labor, reduces and limits productivity, and drives up cost. At the end of the day the ability of the project to handle risk and adapt to technical challenges rests on the reliability and efficiency of its information systems. A patchwork system fails to meet the needs of the organization as a whole and at the end of the day is not “takin’ care of business.”

Big Time — Elements of Data Size in Scaling

I’ve run into additional questions about scalability. It is significant to understand the concept in terms of assessing software against data size, since there are actually various aspect of approaching the issue.

Unlike situations where data is already sorted and structured as part of the core functionality of the software service being provided, this is in dealing in an environment where there are many third-party software “tools” that put data into proprietary silos. These act as barriers to optimizing data use and gaining corporate intelligence. The goal here is to apply in real terms the concept that the customers generating the data (or stakeholders who pay for the data) own the data and should have full use of it across domains. In project management and corporate governance this is an essential capability.

For run-of-the-mill software “tools” that are focused on solving one problem, this often is interpreted as just selling a lot more licenses to a big organization. “Sure we can scale!” is code for “Sure, I’ll sell you more licenses!” They can reasonably make this assertion, particularly in client-server or web environments, where they can point to the ability of the database system on which they store data to scale. This also comes with, usually unstated, the additional constraint that their solution rests on a proprietary database structure. Such responses, through, are a form of sidestepping the question, nor is it the question being asked. Thus, it is important for those acquiring the right software to understand the subtleties.

A review of what makes data big in the first place is in order. The basic definition, which I outlined previously, came from NASA in describing data that could not be held in local memory or local storage. Hardware capability, however, continues to grow exponentially, so that what is big data today is not big data tomorrow. But in handling big data, it then becomes incumbent on software publishers to drive performance to allow their customers to take advantage of the knowledge contained in these larger data sets.

The elements that determine the size of data are:

a. Table size

b. Row or Record size

c. Field size

d. Rows per table

e. Columns per table

f. Indexes per table

Note the interrelationships of these elements in determining size. Thus, recently I was asked how many records are being used on the largest tables being accessed by a piece of software. That is fine as shorthand, but the other elements add to the size of the data that is being accessed. Thus, a set of data of say 800K records may be just as “big” as one containing 2M records because of the overall table size of fields, and the numbers of columns and indices, as well as records. Furthermore, this question didn’t take into account the entire breadth of data across all tables.

Understanding the definition of data size then leads us to understanding the nature of software scaling. There are two aspects to this.

The first is the software’s ability to presort the data against the database in such as way as to ensure that latency–the delay in performance when the data is loaded–is minimized. The principles applied here go back to database management practices back in the day when organizations used to hire teams of data scientists to rationalize data that was processed in machine language–especially when it used to be stored in ASCII or, for those who want to really date themselves, EBCDIC, which were incoherent by today’s generation of more human-readable friendly formats.

Quite simply, the basic steps applied has been to identify the syntax, translate it, find its equivalents, and then sort that data into logical categories that leverage database pointers. What you don’t want the software to do is what used to be done during the earliest days of dealing with data, which was smaller by today’s standards, of serially querying ever data element in order to fetch only what the user is calling. Furthermore, it also doesn’t make much sense to deal with all data as a Repository of Babel to apply labor-intensive data mining in non-relational databases, especially in cases where the data is well understood and fairly well structured, even if in a proprietary structure. If we do business in a vertical where industry standards in data apply, as in the use of the UN/CEFACT XML convention, then much of the presorting has been done for us. In addition, more powerful industry APIs (like OLE DB and ODBC) that utilize middleware (web services, XML, SOAP, MapReduce, etc.) multiply the presorting capabilities of software, providing significant performance improvements in accessing big data.

The other aspect is in the software’s ability to understand limitations in data communications hardware systems. This is a real problem, because the backbone in corporate communication systems, especially to ensure security, is still largely done over a wire. The investments in these backbones is usually categorized as a capital investment, and so upgrades to the system are slow. Furthermore, oftentimes backbone systems are embedded in physical plant building structures. So any software performance is limited by the resistance and bandwidth of wiring. Thus, we live in a world where hardware storage and processing is doubling every 12-18 months, and software is designed to better leverage such expansion, but the wires over which data communication depends remains stuck in the past–constrained by the basic physics of CAT 6 or Fiber Optic cabling.

Needless to say, software manufacturers who rely on constant communications with the database will see significantly degraded performance. Some software publishers who still rely on this model use the “check out” system, treating data like a lending library, where only one user or limited users can access the same data. This, of course, reduces customer flexibility. Strategies that are more discrete in handling data are the needed response here until day-to-day software communications can reap the benefits of physical advancements in this category of hardware. Furthermore, organizations must understand that the big Cloud in the sky is not the answer, since it is constrained by the same physics as the rest of the universe–with greater security risks.

All of this leads me to a discussion I had with a colleague recently. He opened his iPhone and did a query in iTunes for an album. In about a second or so his query selected the artist and gave a list of albums–all done without a wire connection. “Why can’t we have this in our industry?” he asked. Why indeed? Well, first off, Apple iTunes has sorted its data to optimize performance with its app, and it is performed within a standard data stream and optimized for the Apple iOS and hardware. Secondly, the variables of possible queries in iTunes are predefined and tied to a limited and internally well-defined set of data. Thus, the data and application challenges are not equivalent as found in my friend’s industry vertical. For example, aside from the challenges of third party data normalization and rationalization, iTunes is not dealing with dynamic time-phased or trending data that requires multiple user updates to reflect changes using predictive analytics which is then served to different classes of users in a highly secure environment. Finally, and most significantly, Apple spent more on that system than the annual budget of my friend’s organization. In the end his question was a good one, but in discussing this it was apparent that just saying “give me this” is a form of magical thinking and hand waving. The devil is in the details, though I am confident that we will eventually get to an equivalent capability.

At the end of the day IT project management strategy must take into account the specific needs of classes of users in making determinations of scaling. What this means is a segmented approach: thick-client users, compartmentalized local installs with subsets of data, thin-client, and web/mobile or terminal services equivalents. The practical solution is still an engineered one that breaks the elephant into digestible pieces that lean forward to leveraging advances in hardware, database, and software operating environments. These are the essential building blocks to data optimization and scaling.

Stay Open — Open and Proprietary Databases (and Why It Matters)

The last couple of weeks have been fairly intense workwise and so blogging has lagged a bit. Along the way the matter of databases came up at a customer site and what constitutes open data and what comprises proprietary data. The reason why this issue matters to customers rests of several foundations.

First, in any particular industry or niche there is a wide variety of specialized apps that have blossomed. This is largely due to Moore’s Law. Looking at the number of hosted and web apps alone can be quite overwhelming, particularly given the opaqueness of what one is buying at any particular time when it comes to software technology.

Second, given this explosion, it goes without saying that the market will apply its scythe ruthlessly in thinning it. Despite the ranting of ideologues, this thinning applies to both good and bad ideas, both sound and unsound businesses equally. The few that remain are lucky, good, or good and lucky. Oftentimes it is being first to market on an important market discriminator, regardless of the quality in its initial state, that determines winners.

Third, most of these technology solutions will run their software on proprietary database structures. This undermines the concept that the customer owns the data.

The reasons why software solutions providers do this is multifaceted. For example, the database structure is established to enhance the functionality and responsiveness of the application where the structure is leveraged to work optimally with the application’s logic.

But there are also more prosaic reasons for proprietary database structures. First, the targeted vertical or segment may not be very structured regarding the type of data, so there is wide variation on database configuration and structure. But there is also a more base underlying motivation to keep things this way: the database structure is designed to protect the application’s data from easy access from third party tools and, as a result, make their solution “sticky” within the market segment that is captured. That is, database structure is a way to build barriers to competition.

For incumbents that are stable, the main disadvantages to the customer lie in the use of the database as a means of tying them to the solution as a barrier to exit. At the same time incumbents erect artificial barriers to data entry. For software markets with a great deal of new entries and innovation that will lead to some thinning, picking the wrong solution using proprietary data structures can lead to real problems when attempting to transition to more stable alternatives. For example, in the case of hosted applications not only is data not on the customer’s own database servers, but that data could be located far from the worksite or even geographically dispersed outside of the physical control of the customer.

Open APIs in using data mining and variations of it as the Shaman of Big Data prescribe unstructured and non-relational databases has served to, at least in everyone’s mind, minimize such proprietary concerns. After all, it thought, we can just crack open the data–right? Well…not so fast. Given a number of data scientists, data analysts, and open API object tools mainframe types can regain the status they lost with the introduction of the PC and spend months building systems that will eventually rationalize data that has been locked in proprietary prisons. Or perhaps not. The bigger the data the bigger the problem. The bigger the question the more one must bring in those who understand the difference between correlation and causation. In the end it comes down to the mathematics and valid methods of determining in real terms the behavior of systems.

Or if you are a small or medium-sized business or organization you can just decide that the data is irretrievable, or effectively so, since the ROI is not there to make it retrievable.

Or you can avoid the inevitable and, if you do business in a highly structured market, such as project management, utilize some open standard such as the UN/CEFACT XML. Then, when choosing a COTS solution in communicating with the market, determine that databases must, at a minimum, conform to the open standard in database design. This provides maximum flexibility to the customer, who can then perform value analysis on competing products, based on a analysis of functionality, flexibility, and sustainability.

This places the customer back into the role of owning the data.

Do You Believe in Magic? — Big Data, Buzz Phrases, and Keeping Feet Planted Firmly on the Ground

My alternative title for this post was “Money for Nothing,” which is along the same lines. I have been engaged in discussions regarding Big Data, which has become a bit of a buzz phrase of late in both business and government. Under the current drive to maximize the value of existing data, every data source, stream, lake, and repository (and the list goes on) has been subsumed by this concept. So, at the risk of being a killjoy, let me point out that not all large collections of data is “Big Data.” Furthermore, once a category of data gets tagged as Big Data, the further one seems to depart from the world of reality in determining how to approach and use the data. So for of you who find yourself in this situation, let’s take a collective deep breath and engage our critical thinking skills.

So what exactly is Big Data? Quite simply, as noted by this article in Forbes by Gil Press, term is a relative one, but generally means from a McKinsey study, “datasets whose size is beyond the ability of typical database software tools to capture, store, manage, and analyze.” This subjective definition is a purposeful one, since Moore’s Law tends to change what is viewed as simply digital data as opposed to big data. I would add some characteristics to assist in defining the term based on present challenges. Big data at first approach tends to be unstructured, variable in format, and does not adhere to a schema. Thus, not only is size a criteria for the definition, but also the chaotic nature of the data that makes it hard to approach. For once we find a standard means of normalizing, rationalizing, or converting digital data, it no longer is beyond the ability of standard database tools to effectively use it. Furthermore, the very process of taming it thereby renders it non-big data, or perhaps, if a exceedingly large dataset, perhaps “small big data.”

Thus, having defined our terms and the attributes of the challenge we are engaging, we now can eliminate many of the suppositions that are floating around in organizations. For example, there is a meme that I have come upon that asserts that disparate application file data can simply be broken down into its elements and placed into database tables for easy access by analytical solutions to derive useful metrics. This is true in some ways but both wrong and dangerous in its apparent simplicity. For there are many steps missing in this process.

Let’s take, for example, the least complex example in the use of structured data submitted as proprietary files. On its surface this is an easy challenge to solve. Once someone begins breaking the data into its constituent parts, however, greater complexity is found, since the indexing inherent to data interrelationships and structures are necessary for its effective use. Furthermore, there will be corruption and non-standard use of user-defined and custom fields, especially in data that has not undergone domain scrutiny. The originating third-party software is pre-wired to be able to extract this data properly. Absent having to use and learn multiple proprietary applications with their concomitant idiosyncrasies, issues of sustainability, and overhead, such a multivariate approach defeats the goal of establishing a data repository in the first place by keeping the data in silos, preventing integration. The indexing across, say, financial systems or planning systems are different. So how do we solve this issue?

In approaching big data, or small big data, or datasets from disparate sources, the core concept in realizing return on investment and finding new insights, is known as Knowledge Discovery in Databases or KDD. This was all the rage about 20 years ago, but its tenets are solid and proven and have evolved with advances in technology. Back then, the means of extracting KDD from existing databases was the use of data mining.

The necessary first step in the data mining approach is pre-processing of data. That is, once you get the data into tables it is all flat. Every piece of data is the same–it is all noise. We must add significance and structure to that data. Keep in mind that we live in this universe, so there is a cost to every effort known as entropy. Computing is as close as you’ll get to defeating entropy, but only because it has shifted the burden somewhere else. For large datasets it is pushed to pre-processing, either manual or automated. In the brute force world of data mining, we hire data scientists to pre-process the data, find commonalities, and index it. So let’s review this “automated” process. We take a lot of data and then add a labor-intensive manual effort to it in order to derive KDD. Hmmm.. There may be ROI there, or there may not be.

But twenty years is a long time and we do have alternatives, especially in using Fourth Generation software that is focused on data usage without the limitations of hard-coded “tools.” These alternatives apply when using data on existing databases, even disparate databases, or file data structured under a schema with well-defined data exchange instructions that allow for a consistent manner of posting that data to database tables. The approach in this case is to use APIs. The API, like OLE DB or the older ODBC, can be used to read and leverage the relative indexing of the data. It will still require some code to point it in the right place and “tell” the solution how to use and structure the data, and its interrelationship to everything else. But at least we have a means for reducing the cost associated with pre-processing. Note that we are, in effect, still pre-processing data. We just let the CPU do the grunt work for us, oftentimes very quickly, while giving us control over the decision of relative significance.

So now let’s take the meme that I described above and add greater complexity to it. You have all kinds of data coming into the stream in all kinds of formats including specialized XML, open, black-boxed data, and closed proprietary files. This data is non-structured. It is then processed and “dumped” into a non-relational database such as NoSQL. How do we approach this data? The answer has been to return to a hybrid of pre-processing, data mining, and the use of APIs. But note that there is no silver bullet here. These efforts are long-term and extremely labor intensive at this point. There is no magic. I have heard time and again from decision makers the question: “why can’t we just dump the data into a database to solve all our problems?” No, you can’t, unless you’re ready for a significant programmatic investment in data scientists, database engineers, and other IT personnel. At the end, what they deploy, when it gets deployed, may very well be obsolete and have wasted a good deal of money.

So, once again, what are the proper alternatives? In my experience we need to get back to first principles. Each business and industry has commonalities that transcend proprietary software limitations by virtue of the professions and disciplines that comprise them. Thus, it is domain expertise to the specific business that drives the solution. For example, in program and project management (you knew I was going to come back there) a schedule is a schedule, EVM is EVM, financial management is financial management.

Software manufacturers will, apart from issues regarding relative ease of use, scalability, flexibility, and functionality, attempt to defend their space by establishing proprietary lexicons and data structures. Not being open, while not serving the needs of customers, helps incumbents avoid disruption from new entries. But there often comes a time when it is apparent that these proprietary definitions are only euphemisms for a well-understood concept in a discipline or profession. Cat = Feline. Dog = Canine.

For a cohesive and well-defined industry the solution is to make all data within particular domains open. This is accomplished through the acceptance and establishment of a standard schema. For less cohesive industries, but where the data or incumbents through the use of common principles have essentially created a de facto schema, APIs are the way to extract this data for use in analytics. This approach has been applied on a broader basis for the incorporation of machine data and signatures in social networks. For closed or black-boxed data, the business or industry will need to execute gap analysis in order to decide if database access to such legacy data is truly essential to its business, or given specification for a more open standard from “time-now” will eventually work out suboptimization in data.

Most important of all and in the end, our results must provide metrics and visualizations that can be understood, are valid, important, material, and be right.

Doctor My Eyes — Excel is Not a Project Management Tool (and neither is PowerPoint)

This is not to disparage the utility of a good spreadsheet to take care of those transient requirements to take a bit of data from the reporting systems and to run some custom algorithms or trends to perform what-if or other one-off analysis. Probably most of us do this occasionally.

What I am referring to is the condition in many organizations in which data that consists of information essential to business operations is kept and analyzed using spreadsheets or other flat delimited storage or text methods. The issue here is the optimum use of information, which the use of Excel and PowerPoint does not achieve. Before anyone thinks that this is a contrarian’s post that is critical of Microsoft products, one need only read the technical advantages of true relational database management systems that are managed by specialized language like MS SQL. Each of these applications and products has their proper place.

I would prefer that this post would be unnecessary since the title should be considered self-evident at this point–it is very similar to a presentation I gave almost 20 years ago when still advising senior managers in the U.S. Department of Defense and the aerospace industry–but the facts tell us that it’s assertion not so self-evident. For example, a survey of 262 financial executives by the Financial Executives Research Foundation and the staffing firm Robert Half in 2012 indicated that 64% of public and private companies in the U.S. still used spreadsheets and manual methods for their financial solutions. In 2011 a study by the ERP company IFS found that “of more than 281 manufacturing executives, 75 percent of study respondents aged 35 and under report using desktop spreadsheet software like Microsoft Excel instead of their company’s ERP, customer relationship management (CRM), supply chain management (SCM) or other enterprise software” while “respondents aged 36 to 45, 58 percent said they would use desktop spreadsheet software instead of their company’s designated enterprise applications.”

There are certainly good reasons in each study that people sub-optimize in using their data. First and foremost is the hassle and expense of the centralized IT office. The same methods and organizations that secure data and are tasked with maintaining configuration control for the IT resources of an organization are the same ones that oftentimes tend to impede flexibility in the acquisition of needed digitized business process solutions. Back in the 1980s as the PC began to displace the old mainframes run by the stodgy folks wearing white shirts, pen holders, and black glasses who had to be bribed, cajoled, and stroked in order to obtain processing time, access to archived data, or–heaven forbid–devise and run a program, a new world order was declared in which the centralized and non-responsive central IT office was declared dead forever. Then digital crime raised its ugly head along with a cacophony of new products, some of which delivered on their promises, but most which did not. Thus the need, once again, to bring some semblance of order to the chaos that incompatible and ineffective products (among other problems) wrought. So IT has come full circle and we are back to highly bureaucratized IT organizations (or aggressive IT services companies) that in many cases will defend their turf ruthlessly, many times to the detriment of the interests of the organization. Thus, people do what they have always done when inflexible rules get in the way–they find ways around them. The most convenient way is to use the “legal” workaround, which is the spreadsheet, the Word document, or the PowerPoint presentation. Along with being able to achieve what is needed without having to ask permission or miss a deadline, managers and employees oftentimes learn these basic applications first. They are intuitive, readily available, and familiar.

The problem, of course, takes many forms. The first is that manual methods are rife with errors. In fact, study after study shows that almost 90% of spreadsheets contain errors. In 2007 CIO Magazine published an article listing the eight worst spreadsheet errors of all time, highlighting the very real damage that relying on these methods have created to organizations and businesses. And this was before the financial crisis revealed similarly large errors in the financial markets during the most recent housing bubble deflation and resulting Lesser Depression.

In providing project management solutions to my target verticals, the area most in need of remediation–and which presents the most immediate opportunity for return on investment, improving data and process credibility, and preventing fatal business errors–is in displacing those processes built around managing data using Excel, Word, and PowerPoint. In identifying data that is most appropriate for moving to a relational database management solution, I have found that this data shares certain characteristics:

a. The data is an element essential to decision-making and must therefore be credible.

b. The data is an element essential to trend analysis, organizational history, or corporate knowledge.

c. The methods and algorithms used in the data’s processing must be provide results that are repeatable and consistent.

d. The various elements of data are interrelated and may originate from systems of record.

g. The output from the processing of the data is essential to job of more than one person or one process.

h. The time it takes to construct, maintain, update, adjust, and use the manual processing environment for the data greatly exceeds the marginal value of the effort that could be saved using more automated methods to achieve the same or greater results. That is, replacing the manual and spreadsheet method of using the data results in greater productivity and either cost savings or work shifting to more productive tasks tied to project success.

As a localized tool or as an intermediate point of review for data residing in tables augmented by more robust automation, Excel has proven itself to be a workhorse. For in-depth papers and extended communication Word is the appropriate medium and for presentations PowerPoint provides a good basis for simplified communication of an idea or set of ideas. But for the processing and integration of enterprise data these applications are inappropriate–and can be quite damaging–to business operations.