OLAP with table functions

Table-valued functions in finaquant .net libraries

OLAP with table functions

Postby quincy » 02 Nov 2013, 10:40

Hi
How can OLAP operations like slice & dice, drill-down etc can be executed with table functions of finaquant?
Regards
quincy
 
Posts: 2
Joined: 02 Nov 2013, 10:38

Table Functions for Filtering and Aggregation

Postby finaquant » 06 Nov 2013, 19:23

OLAP operations like slice & dice, drill-down etc. are related with filtering and aggregation operations on data tables.

Accordingly, you may use filtering and aggregation functions of finaquant's .NET libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial) for the same purpose.

You may find examples (demo functions) in the respective Visual Studio file FinaquantProtosStarter or FinaquantProtosCalcs:
Image

See C# code examples below:

AGGREGATION
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. //***********************************************
  2. // short aggregation demo
  3. //***********************************************
  4.  
  5. // obtain table costs(category, product, year)
  6. MatrixTable CostTable = MatrixTable.PartitionColumn(tbl1,
  7.     TextVector.CreateVectorWithElements("category", "product", "year", "costs"));
  8. MatrixTable.View_MatrixTable(CostTable, "COSTS per product, category and year");
  9.  
  10. // get aggregated table costs per category: costs(category)
  11.  
  12. // get subtable costs per category
  13. tblr = MatrixTable.PartitionColumn(CostTable,
  14.     TextVector.CreateVectorWithElements("category", "costs"));
  15. MatrixTable.View_MatrixTable(tblr, "COSTS per category, before aggregation with SUM");
  16.  
  17. // aggregate subtable costs per category
  18. tblr = MatrixTable.AggregateAllKeyFigures(tblr, null);
  19. MatrixTable.View_MatrixTable(tblr, "COSTS per category, after aggregation with SUM");
  20.  
  21. // aggregate input key figure costs w.r.t. reference attribute "category"
  22. // ... to produce a new output key figure "costs_per_category"
  23. tblr = MatrixTable.AggregateSelectedKeyFigure_B(CostTable,
  24.     RefAttributes: TextVector.CreateVectorWithElements("category"),
  25.     InputKeyFigName: "costs", OutputKeyFigName: "costs_per_category",
  26.     AggrOpt: AggregateOption.nSum);
  27.  
  28. MatrixTable.View_MatrixTable(tblr, "Table with new key figure costs_per_category");
GeSHi ©


FILTERING
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // ****************************************************************
  2. // Filtering rows with a condition table
  3. // ****************************************************************
  4.  
  5. // initiate variables
  6. MatrixTable FilteredTable;
  7.  
  8. // create condition table
  9. TableFields CondTableFields = TableFields.CreateEmptyTableFields(md);
  10. TableFields.AddNewField(CondTableFields, "family");
  11. TableFields.AddNewField(CondTableFields, "birth_year");
  12.  
  13. MatrixTable CondTable = MatrixTable.CreateTableWithElements_A(CondTableFields,
  14.     "mammals", 0,       // all years              
  15.     "ALL", 2005,        // all families          
  16.     "Fish", 2008
  17.     );
  18.  
  19. // view table
  20. MatrixTable.View_MatrixTable(CondTable, "Filtering with condition table: Condition table");
  21.  
  22. // CASE 1: Include (select) mathched rows: ExcludeMatchedRows = false
  23. FilteredTable = MatrixTable.FilterTableA(AnimalsTable, CondTable,
  24.     ExcludeMatchedRows: false, JokerMatchesAllvalues: true, TextJoker: "ALL", NumJoker: 0);
  25.  
  26. // view resultant filtered table
  27. MatrixTable.View_MatrixTable(FilteredTable, "Filtering with condition table (include): Output table");
  28.  
  29. // CASE 2: Exclude (deselect) mathched rows: ExcludeMatchedRows = true
  30. FilteredTable = MatrixTable.FilterTableA(AnimalsTable, CondTable,
  31.     ExcludeMatchedRows: true, JokerMatchesAllvalues: true, TextJoker: "ALL", NumJoker: 0);
  32.  
  33. // view resultant filtered table
  34. MatrixTable.View_MatrixTable(FilteredTable, "Filtering with condition table (exclude): Output table");
GeSHi ©
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron