Is ETL software needed to build a model-driven DSS?
by Daniel J. Power
Editor, DSSResources.COM
ETL refers to software used in the process of extracting data from a data source, transforming or altering the data so it is useful for decision support or another need, and then loading the transformed data into a new data store, i.e., an RDBMS, a flat file, or spreadsheet. Extract, transform and load (ETL) tasks are part of building many types of DSS, including some model-driven DSS. BUT, the complex ETL software developed for creating and refreshing large data stores created from transaction processing, enterprise resources planning (ERP) and/or operational systems is usually NOT needed for model-driven DSS.
Power (2002) in "What is ETL software and how is it related to DSS?" discusses the basics of this type of software. An article by Larry English published at DSSResources.COM on
Model-driven DSS use complex financial, simulation, and/or optimization models to provide decision support. The needed data sets are usually small, and certainly much smaller than the 500 megabyte-5 terabyte data stores common with data-driven DSS.
In some model-driven DSS, the user enters ALL of the data needed by the system. The DSS performs data validation and data storage. The data entry may be 5-15 parameter values, text or other inputs. No data is imported from a source system. For example, most of the model-driven decision aids at DSSResources.COM have users input all of the data required by the model. To try the Cost/Benefit Analysis decision aid designed by D. J. Power and programmed in JavaScript by Alex Power, check URL http://dssresources.com/decisionaids/cbanalysis.html.
In other model-driven DSS, a time series of data on one or more variables needs to be imported into the DSS. The data set may be 1000 to even 10,000 values. It is common to perform extract and transform tasks to create the data. A report or data set is exported from a source system. Then because the data set is small the data is usually cleaned up and formatted in a text editor or in a desktop application like Excel. Excel is often a useful tool for creating small data sets for use with a model-driven DSS. The data set can then be incorporated into a spreadsheet-based DSS built using Excel or Lotus 123 or imported into another DSS development environment.
Larger data sets are used for some specialized model-driven DSS, but the size of the data set remains modest compared to data marts and data warehouses. For example, Radical Logistics sells transportation software for calculating distances and rates. Data on "thousands of shipments" is used for the analysis and the data needs to be verified and cleaned up for correct ZIP codes and mileages. The Radical Logistics software help with ETL, analysis and decision support.
Another common type of model-driven DSS uses a small number of data values from an external database that is needed for the analysis by the DSS user. The user defines the analysis and inputs some parameter values. For example, many model-driven investment DSS extract data from a historical stock market database. The Intrinsic Value per Share Calculator at Quicken.com extracts earnings and price information from a general purpose database of stock information and the user inputs assumptions about interest rates for "What if?" analysis.
Finally, some model-driven DSS need very large data sets to create a visual simulation that the DSS user can interact with. These data sets are created and data may be imported from video files, maps and other sources. For example, DaimlerChrysler has a Virtual Reality Center to analyze and understand digital models and simulation results. The data used is not ERP or transaction data. The extract, transform and load tasks are very different from those associated with data warehousing, business intelligence and data-driven DSS.
So as the data needs of a model or models in a model-driven DSS increases, it becomes more likely that specialized ETL software will be needed to help the DSS developer create the specific decision support data store. The software used to extract, transform and load the data depends on the data, the DSS development environment and the preferences of the developer.
References
English, L. P., "Ten mistakes to avoid if your data warehouse is to deliver quality information", DSSResources.COM, 08/11/2002.
Power, D. J. "What is ETL software and how is it related to DSS?", DSS News, 08/04/2002 at URL dssresources.com/faq/index.php?action=artikel&id=50.
The above response is modified from Power, D., Is ETL software needed to build a model-driven DSS? DSS News, Vol. 3, No. 17, August 18, 2002; modified 4/15/2017.
Last update: 2017-04-15 04:28
Author: Daniel Power
Print this record
Show this as PDF file
You cannot comment on this entry
