EduGuru

ETL—“All your dreams will come true.” –Vote for Pedro!*

To understand ETL, one must master the arena of maps.  Maps show the way to transform codes or values in one format (source) into the desired codes or values of another (destination).  Sometimes you’ll hear crosswalks rather than maps—same thing.  Sometimes you’ll hear of transformation rules or business rules—gets complicated here.  The business rules describe exactly how a value in one format must be transformed.  A simple map or crosswalk might take a code of “1” and make it an “a.”  A business rule might demand that “1” become “a” when referring to a first grader, but “b” when referring to a second grader, but still “a” if the second grader was socially promoted, however, “b” if the social promotion occurred in kindergarten.

One must not underestimate the importance or complexity of these ETL processes.  A special education December child count data collection from districts to the state education agency might require 50 or more business rules.

The EduGuru speaks:  Remember when the ugly duckling transformed into the beautiful swan?  Our data transform from whatever they need to be in the school or district database to the “beautiful,” comparable data at the state level when they pass all the business rules.  Be clear.  This is not a fairy tale.  This is a dream come true.

*from the movie Napoleon Dynamite

ETL—Cheese? Jalepenos? Mayo? $6.45.

A previous blog warned you about ETL. This one gives some answers.  Getting data from where they live to where they can be used is possible. Interoperable is the term for information systems that exchange data efficiently.

Standards—when every information system, every software application, every reporting tool uses the same standards for their data, then ETL can be efficient. Standards cover formats, definitions, codes, business rules, and processes. Comma separated values, XML, SIF, SPEEDE, PESC HS, ODBC, your state’s data submission specifications are all standards.

Schools Interoperability Framework (SIF) is an example of a standard that allows software applications to remain proprietary in their internal design but exchange their data efficiently with each other.

National Transcript Center is an example of an application that accepts data in various formats and transforms them into any of a number of other formats. The users pick the one standard they want to follow, and what they send is translated into what the receiver wants. Everything sent to them is translated into their one selected format.

The EduGuru speaks:  Toppings—the ETL of the fast food industry. Been to the fast food shop where the person behind the counter only speaks English when it comes to menu items? A conversation about the weather, politics, or sports is out of the question, but the finite vocabulary words of the hamburger, sandwich, pizza, or burrito allow the customer and the employee to conduct business within the well-defined scope of “your way.” To be fair, the vocabulary also includes prices, tendered amounts, and change. More complicated directions such as where the napkins reside or can I have another (fill in the blank) are communicated with pointing and head nods. Don’t try to ask for directions to your next destination.  Those words are not part of the interoperability standard of the fast food industry.

The Aura of COTS.

Commercial Off The Shelf Solution—the holy grail of software applications.  Buy it; install it; use it.  What could be simpler? Reality is that when an organization buys a COTS, they typically get (and pay for) features they won’t use and miss out on features they really wanted. COTS has an “aura.”  Look up the word “aura” and find, “a characteristic or distinctive impression created by somebody or something.” 

What the COTS sellers fear is that a buyer would discover all the related efforts and costs required to make the COTS work, i.e., ETL, retrofitting the COTS definitions back into other existing applications, changing the vernacular of the organization to that of the COTS, finding a DBA that knows the COTS, making the COTS interoperable with current and future applications, paying the annual license fees, etc.   

That said, the vendor of a COTS may offer some configuration or even customization services that make the solution more acceptable.  In that case, the C becomes Customized Off The Shelf Solution.  Keeping aligned with the application’s enhancements then becomes an additional challenge.

Knowing what you want and recognizing all the costs before choosing a COTS is advisable. 

The EduGuru speaks: Interesting how a cot is the least desirable place to sleep, but is accepted by many who really want a Sleep Number Bed.

ETL—Are your data missing the bus?

Learn this information systems term!  Extract (get the data out of one place), Transform (change them from their original format into another one), and Load (put them into another place).  ETL is the toughest process for getting data in front of a teacher, administrator, parent, or other audience.  Schools, districts, and states spend millions of dollars to buy the best reporting software tools, but fail to influence instruction, school management, or decision making because the process of getting the right data, in the right place, in the right format, at the right time is so difficult.

Software vendors can purposely ignore or under estimate this effort because if their customers knew how hard ETL is, they might not spend millions on software that won’t have necessary data in it.  Budgets can dangerously under provide the dollars needed.  Project plans can miss targets because of the time required. 

ETL problems are at the top of the list of why information systems fail to satisfy their users. 

The EduGuru speaks:  Sounds like the challenge parents have to get their kids out of bed, dressed, and onto the school bus on time.  No learning takes place if the kids miss the bus.  No data-driven decision making takes place if the data miss the bus.