What is a table function? Why do we need table functions?

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)

Distribution as table functionA typical distribution problem is illustrated at the left: The costs that are initially calculated at department level are subsequently distributed further to teams within departments.

Why do we need table functions?


There are broadly two kinds of database processing related with tables:

  1. Recording new data, or updating existing data.
  2. 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

Capturing calculation patterns with table functionsIn 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

Table multiplexerEven 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

DataTable as integration gateway for finaquant tablesThe 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

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu

2 Responses to What is a table function? Why do we need table functions?

  1. shakirshabbir says:

    Can we type cast MatrixTable class of finaquant to a DataTable class of .NET &/or vice versa?

  2. tuncalik tuncalik says:

    Casting no, but there are import and export functions for conversions.

    By using the MatrixTable method Export_To_DataTable() any MatrixTable object can be converted to a DataTable object.

    A DataTable can be converted to a MatrixTable with the MatrixTable method Import_from_DataTable(). All the fields of the DataTable must be predefined in MetaData with proper field types (text attribute, numeric attribute of type integer or date, or key figure) before applying this conversion.

Leave a Reply