Persistent Table Array

Posted on April 9, 2013 by tuncalik

PersistentTableArray and DataStore are new classes of the non-commercial .net library finaquant® protos (starting from release 1.04) that enable connection to relational databases like MySQL and Microsoft SQL in order to read/write complete tables from/to a database together with their instance information.

Each table instance is an element of a persistent table array. Following C# code example illustrates how separate cost tables for each country and year can be stored in a MySQL database with a persistent table array:

Cost tables for different countries and years

using FinaquantProtos;
using System.Data.Odbc;
 
// Connection string for MySQL database: 
// Replace it with your own valid connection string on your computer
string ConnStr = @"Driver={MySQL ODBC 5.2w Driver};Server=127.0.0.1;Database=testdb;uid=root;pwd=";
 
// Data provider
// You need to install ODBC driver for MySQL on your computer (win 32 bit version)
// see: http://dev.mysql.com/downloads/connector/odbc/
string DataProvider = "System.Data.Odbc";
 
// initiate DataStore object for database connection
DataStore datstore = new DataStore(Provider: DataProvider); 
 
// open database connection
datstore.OpenConnection(ConnStr);
 
// initiate "persistent table array" objects
PersistentTableArray PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);
 
// Create TableRow objects as instance keys for tables
TableRow trow1 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2008);
TableRow trow2 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2012);
TableRow trow3 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Bolivia", 2010);
 
// store each table instance separately into database (yes, just by assigning!)
PTblCost[trow1] = CostTblPeru2008;      // store CostTblPeru2008 with instance information in trow1
PTblCost[trow2] = CostTblPeru2012;
PTblCost[trow3] = CostTblBolivia2010;

Note that CostTblPeru2008, CostTblPeru2012 and CostTblBolivia2010 are different instances of in-memory cost tables (of type MatrixTable) with the same field structure.

All you need to do is prepare an empty database and assign the connection string in order to initiate the DataStore object. All necessary tables are automatically created and updated by PersistentTableArray or DataStore operations.

The statement PTblCost[trow1] reads a complete table with instance parameter trow1 from the database, and returns an in-memory table of type MatrixTable:

MatrixTable CostTbl = PTblCost[trow1];

You can view an element (instance) of a persistent table array PTblCost as follows:

MatrixTable.View_MatrixTable(PTblCost[trow1], "Cost Table, Peru - 2008");

Cost Table, instance: Peru-2008
Note that each attribute pair (country and year) identifies a single table instance. In this example we have only two attributes, but a table row as instance key of a persistent table array can have as many attributes as you need.

Following statement writes the in-memory table CostTbl into database together with the instance parameter trow1:

PTblCost[trow1] = CostTbl;

If a table with the same field structure and instance parameter already exists in the database it will be overwritten (unless it is locked, see DataStore methods) just like in ordinary arrays.

Persistent Table Array and DataStore

Methods of DataStore class are responsible for all database operations for reading or writing complete tables.

A persistent table array assignment like PTblCost[trow1] = CostTblPeru2008 triggers following method of the DataStore class:

PTblCost.dataStore.WriteTableInstance(CostTblPeru2008, 
    PTblCost.TableName, trow1);

The WriteTableInstance() method of DataStore above writes the complete table CostTblPeru2008 into the database together with the instance information trow1 handling all required database operations behind the scenes.

Persistent Table Array takes this level of abstraction and simplification one step further: All database read or write operations are executed automatically during the ordinary use of table arrays.

Why are table arrays needed?

Table arrays are useful in cases in which we need to make calculations with certain set of input tables to obtain the desired set of output tables.

As an example, consider the price calculation below with in-memory tables:

using FinaquantProtos;
 
PTblPrice =
    MatrixTable.MultiplySelectedKeyFigures(PTblCost, PTblMargin + 1,
    InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");

Assume you need to calculate product prices for every year with different margin and cost tables. That is, the field structure of margin and cost tables remain the same, but their entries and values may vary from year to year.

Let’s add another dimension to the equation: You have different margin and cost tables for each year and country.

Following code example demonstrates how you can calculate and store price tables for each country and year. You can find this demo function and more in the MS Visual Studio project file FinaquantProtosStarter (see page finaquant® protos for free download).

Demo function: Calculate price tables for each country and year
// Calculate price tables for each year and country with Persistent Table Arrays
using FinaquantProtos;
 
public static void Persistent_Table_Array()
{
// required variables
MetaData md;
MatrixTable CostTable, MarginTable1, MarginTable2;
 
// call helper to create test tables
Create_Cost_and_Margin_tables(out md, out CostTable, out MarginTable1, out MarginTable2);
 
// Create separate tables for each country and year
MatrixTable CostTblPeru2008 = CostTable + 0.08;     // add 0.08 to all key figures of CostTable
MatrixTable MarginTblPeru2008 = MarginTable1 + 0.0008;
 
MatrixTable CostTblPeru2012 = CostTable + 0.12;
MatrixTable MarginTblPeru2012 = MarginTable1 + 0.0012;
 
MatrixTable CostTblBolivia2010 = CostTable + 0.10;
MatrixTable MarginTblBolivia2010 = MarginTable1 + 0.0010; 
 
// view tables
MatrixTable.View_MatrixTable(CostTblPeru2008, "CostTblPeru2008");
MatrixTable.View_MatrixTable(MarginTblPeru2008, "MarginTblPeru2008");
 
// add new fields to MetaData
MetaData.AddNewField(md, "country", FieldType.TextAttribute);
MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
 
// define row field structure as key for table instance
// Note: TableInstance = PersistentTableArray[InstanceKey]
var RowFields = TextVector.CreateVectorWithElements("country", "year");
TableFields InstanceRowFields = TableFields.CreateTableFields(RowFields, md);
 
// Database connection strings: replace them with your own connection strings on your computer
// You need to install ODBC driver for MySQL on your computer (32 bit version)
// see: http://dev.mysql.com/downloads/connector/odbc/ - mysql-connector-odbc-5.2.4-win32
 
// MS SQL
string ConnStr1 = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=finaquantdb;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
 
// MySQL
string ConnStr2 = @"Driver={MySQL ODBC 5.2w Driver};Server=127.0.0.1;Database=finaquantdb;uid=root;pwd=";
 
string[] Connections = { ConnStr1, ConnStr2};
 
// database software for each connection string
string[] DataBaseSW = {"MS SQL", "MySQL"};
 
// Data providers for each connection string
string[] DataProviders = { "System.Data.SqlClient", "System.Data.Odbc"};
 
//****************************************************************
// Calculate product prices for each country and year
//   and store each table instance separately
//
// Repeat operations for each connection string
//****************************************************************
 
Console.WriteLine("Calculate product prices for each country and year");
 
// Repeat same operations for each connection string
for (int i = 0; i < Connections.Length; i++)
{
	Console.WriteLine("Database Software: " + DataBaseSW[i] + " *******************");
	Console.WriteLine("Database Provider: " + DataProviders[i]);
	Console.WriteLine("Connection String: " + Connections[i]);
 
	// TEST
	System.Diagnostics.Debug.WriteLine("Database Software: " + DataBaseSW[i] + " *******************");
 
	// declare persistent table arrays
	PersistentTableArray PTblCost = null;
	PersistentTableArray PTblMargin = null;
	PersistentTableArray PTblPrice = null;
 
	// initiate DataStore for database connection
	DataStore datstore = new DataStore(Provider: DataProviders[i]); 
 
	try
	{
		// open database connection
		datstore.OpenConnection(Connections[i]);
 
		// initiate "persistent table array" objects
		PTblCost = new PersistentTableArray(TableName: "costs", dstore: datstore);
		PTblMargin = new PersistentTableArray(TableName: "margins", dstore: datstore);
		PTblPrice = new PersistentTableArray(TableName: "prices", dstore: datstore);
 
		// Create TableRow objects as instance keys for tables
		TableRow trow1 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2008);
		TableRow trow2 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Peru", 2012);
		TableRow trow3 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Bolivia", 2010);
		TableRow trow4 = TableRow.CreateTableRowWithElements(InstanceRowFields, "Bolivia", 2012);
 
		// store each table instance separately into database (yes, just by assigning!)
		PTblCost[trow1] = CostTblPeru2008;      // store CostTblPeru2008 with instance information in trow1
		PTblCost[trow2] = CostTblPeru2012;
		PTblCost[trow3] = CostTblBolivia2010;
 
		PTblMargin[trow1] = MarginTblPeru2008;
		PTblMargin[trow2] = MarginTblPeru2012;
		PTblMargin[trow3] = MarginTblBolivia2010;
 
		// now check database to see if these tables are written
 
		// read table instances from database
		// "PTblCost[trow1]" reads table named "costs" with instance information in trow1
		MatrixTable.View_MatrixTable(PTblCost[trow1], "Cost Table, Peru - 2008");
		MatrixTable.View_MatrixTable(PTblCost[trow2], "Cost Table, Peru - 2012");
		MatrixTable.View_MatrixTable(PTblMargin[trow1], "Margin Table, Peru - 2008");
 
		MatrixTable.View_MatrixTable(PTblCost[trow3], "Cost Table, Bolivia - 2010");
		MatrixTable.View_MatrixTable(PTblMargin[trow3], "Margin Table, Bolivia - 2010");
 
		// calculate and store price tables for each instance (country-year)
		PTblPrice[trow1] =
			MatrixTable.MultiplySelectedKeyFigures(PTblCost[trow1], PTblMargin[trow1] + 1,
			InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
 
		PTblPrice[trow2] =
			MatrixTable.MultiplySelectedKeyFigures(PTblCost[trow2], PTblMargin[trow2] + 1,
			InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
 
		PTblPrice[trow3] =
			MatrixTable.MultiplySelectedKeyFigures(PTblCost[trow3], PTblMargin[trow3] + 1,
			InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
 
		// overwrite a table instance with itself
		PTblPrice[trow3] = PTblPrice[trow3];
 
		// coply a table instance to another one
		PTblPrice[trow4] = PTblPrice[trow3];
 
		// read price tables from database
		MatrixTable.View_MatrixTable(PTblPrice[trow1], "Price Table, Peru - 2008");
		MatrixTable.View_MatrixTable(PTblPrice[trow2], "Price Table, Peru - 2012");
		MatrixTable.View_MatrixTable(PTblPrice[trow3], "Price Table, Bolivia - 2010");
		MatrixTable.View_MatrixTable(PTblPrice[trow4], "Price Table, Bolivia - 2012 (copy of Bolivia-2010)");
	}
	catch (Exception ex)
	{
		Console.WriteLine("Error in Case 1: " + ex.Message);
	}
	finally
	{
		Console.WriteLine("Close database connection ...");
 
		datstore.CloseConnection();
	}
 
	// wait a bit before showing message box
	System.Threading.Thread.Sleep(1000);
	MessageBox.Show("Please close all table viewers and press OK to continue ...");
}   // for i
}
There are lots of similar applications in real life where you need to make instance calculations with selected set of input tables. Examples include financial planning (quarterly, yearly, for each region), dealer commissions (monthly, quarterly, yearly, for each product or contract group), estimations or forecasts and so on. In most cases, the required input data are selected with ad-hoc, customized or parameterized SQL statements.

Persistent Table Array provides a framework for easily reading and writing complete tables associated with a calculation instance (without needing to care for the underlying database operations) as a calculation engine based on table functions.

Note that no method of the classes PersistentTableArray or DataStore will let you manipulate the entries of individual table instances. Table instances are read, written, overwritten or deleted as a whole in compliance with the philosophy of table functions.

Conclusions

Remember how we define a calculation engine based on table functions (video: What is a calculation engine? Comparisons with excel sheets (video)):

Table function: Functions with in-memory tables as input and output parameters

  1. An extensive library of general-purpose table functions (like standard formulas in excel)
  2. User-defined functions and formulas must be possible (like user-defined formulas in excel)
  3. Ability to store multiple instances of tables in a generic database

Up to release 1.03 finaquant® protos already had the features 1 and 2 as listed above. But the last feature 3 was the missing link for a full-featured table calculation engine.

The new classes Persistent Table Array and DataStore with the new release 1.04 completes this missing link.

Persistent Table Array is also the necessary infrastructure of Calculation Nodes and Networks because every worker ant (as calculation node) should be able to read/write tables from/to a database.

Related articles

Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
Share
This entry was posted in Calculation engine and tagged , , . Bookmark the permalink.

Leave a Reply