Table functions can be explained best by the example of matrix functions.
Mout = MatrixFunction(M1, M2, ... Mn)
A matrix function has matrix parameters as inputs, and matrix parameters as outputs. For example, the matrix multiplication Mout = M1 x M2 that we know from linear algebra is a typical matrix function.
Similarly, table functions have tables as input and output parameters:
Tout = TableFunction(T1, T2, ... Tn)
The distribution function often used in financial planning is a typical table function:
Tout = DistributionWithReferenceTable(Tbase, Tref)
Why do we need table functions?
There are broadly two kinds of database processing related with tables:
- Recording new data, or updating existing data.
- Analysis and calculations with historical data stored in tables.
Data processing at entry level
Recording and updating data are about processing individual table entries. The rest of the data (i.e. other entries) in tables are generally not relevant for this kind of data processing. That is, the data processing is rather at entry level; the table is not taken as a whole as information unit.
Traditional SQL-based table processing including stored procedures is probably the simplest and best approach for this type of data processing.
Data processing at table level
For analysis and calculations tables are taken as a whole as historical input data for generating some meaningful results. These results can be reports or the outcomes of some calculations. A more mathematical approach is needed for this type of data processing compared to SQL-based programming.
Mathematical table functions (in finaquant products like finaquant® protos or calcs) with well-defined input and output parameters enable users easily to formulate this second type of data processing with in-memory data (just like matrices) where tables are taken as a whole as input or output parameters.
Typical applications for this sort of data processing are:
- Data and risk analysis
- Simulation and optimization problems with structured table data
- Data preparation for reporting
- Forecasts and estimations based on historical data
- Financial Planning and Business Intelligence
- Commission and fee calculations in financial services
Note that table functions can also be written with stored procedures (using dynamic SQLs) offered by most database products like Oracle, MySQL or Microsoft SQL. Writing a library table functions with C#/.NET has but some advantages like better integrability and portability, database independence, and a richer programming environment.
Capturing general calculation patterns with table functions
In applications like financial planning, or fee calculations in financial services, most of the required calculation patterns (if not all) can be captured already at the topmost table level with a software library which offers you a broad spectrum of table functions.
The advantage of this approach is obvious: All the calculations can be implemented just by combining and setting parameters to some high-level table functions, without detail coding with matrix and scalar computations. In other words, lower-level programming is replaced by higher-level programming with well-tested functional units.
Creating user awareness about general calculation patterns is another important added value of table functions. Calculations that seemed quite complex and case-specific before like conditional cost distributions and date sampling might look much easier and general after learning and using table functions. The upcoming application examples that can be downloaded at product pages (finaquant® protos or calcs) will make this point more concrete and clear.
Integration with matrix computations
Even for applications like risk analysis, forecasts and estimations, asset valuation etc. that typically require matrix computations table functions can be of great use as high-level organizers. After all, all these applications are based on historical table data that are the starting point (oftentimes also the end point) for any calculation.
With table functions like subtable transformers and function routers, filters and date samplers one can convert a data table to multiple time series (or subtables) for each asset, or whatever entity is calculated, before feeding them to matrix computations as input parameters.
DataTable of .NET as data integration gateway
The DataTable class of the .NET framework for keeping and manipulating tables directly in the memory (i.e. in-memory tables) plays a crucial role for integrating finaquant tables with data repositories (excel, database, XML …) and other .NET applications.
MatrixTable class of finaquant is a mathematical representation of a table with a simplified data structure compared to DataTable of .net. Luckily, a MatrixTable can easily be converted to a DataTable, or vice versa.
Once you have your data in DataTable, you have access to all other .NET libraries and functionalities for importing or exporting data as well as for reporting. Nevertheless, finaquant libraries include some DataTable extentions to make life easier for users.
Written by: Tunç Ali Kütükçüoglu