Tilda JSON Syntax: View Formulas

<-- View Syntax

Formulas

If you can get simple logic implemented in the database, then you should for a variety of reasons, in particular, centralization of logic and performance. An example of that are alculations you need to be performed on data that can be embedded in views instead of living somewhere else (e.g., application code, APIs or ETL logic). Surprisingly, the threshold where such logic becomes too complicated to be managed in the database is pretty high!

Formulas in Tilda are very powerful and offer many advantages:

• Package nuggets of calculations as part of a view definition
• Fully exploit the power of your underlying database functionality
• Centralize calculations in the database for reuse across any client
• Removes logic from more complex system such as ETL, APIs or application code
• Help from the Tilda compiler in managing complexity including validation and composition.
• Self documenation as in, the code is the documentation and the documentation is the code.
• Automated migration

A typical definition looks like the following:

 ,"formulaColumns":[
     { "name": "isPassed"
      ,"type": "INTEGER"
      ,"formula":[ 
          "formCountCorrect >= 2"
        ]
      ,"title": "Test Passed"
      ,"description":[
          "Whether the test was passed or not by answering at least 2 out of the 3 questions."
        ]
     }
    ,{ "name": "wasChallenging"
      ,"type": "INTEGER"
      ,"formula":[ 
          "    isPassed=1"
         ,"AND testAnswerCountCorrect < testCount*3/2"
        ]
      ,"title": "Test Was Challenging"
      ,"description":[
          "Whether the test was challenging in that:<LI>"
         ,"  <LI>it was passed,</LI>"
         ,"  <LI>and overall, less than a third of answers across all tests were answered correctly.</LI>"
         ,"</UL>"
        ]
      ,"values":[
           { "value":"0", "description":"The test was not challenging" }
          ,{ "value":"1", "description":"The test was challenging" }
        ]
     }
   ]

The elements are:

• name: The mandatory name of the formula that will result in a column definition, so the regular Tilda Naming Convention applies.
• measure: true|false, optional, false by default. Whether the formula is considered a measure. If specified to true, some additional meta-data will be generated to support querying measure information at the database level.
• formula: An array of string defining the formula in plain SQL as per any syntax your database allows as part of a view column definition. Note that columns referenced do not need to be quoted.
• title: The mandatory title for the formula.
• description: The mandatory array of strings for a possibly multi-line description of the formula.
• values: An optional list of values defining the expected outcomes of the formula if enumerated, i.e., a count for example is a value, but a flag often is null, 0 or 1, which means its values are explicitly enumated. This is optional and is used to support documentation where a formula's possible values can be enumerated.

🎈 NOTE: A formula's description can include HTML tags for formatting in the final generated HTML documentation. Tilda doesn't check if that HTML is valid or not.

🎈 NOTE: A formula's implementation is not explicitly checked by Tilda at build-time (during Gen) since it's open to any syntax supported by the underlying database. As such, it is only checked during Migration when the formula is incorporated into the view and the view is declared in the database. Tilda defines a few common database utility functions (see Common Helper Database Functions).

🎈 NOTE: Most formulas we have seen are somewhat simple, like the examples above. That being said, it's possible to write a formula that makes a view significantly more expensive, for example, something calling a stored procedure that runs multiple querries, or even a non-optimized sub-select. As always, check your performance for the generated view and optimize as needed.

🎈 NOTE: Formulas are composable, meaning that one formula can reuse another formula. Tilda will check for referential loops, e.g., formula F1 reuses formula F2 which reuses F1.