CoginitiScript Macros

Introduction to CoginitiScript

In SQL, macros are pre-written SQL codes that can be called and executed repeatedly to simplify complex queries. CoginitiScript Macro has the same idea, its is a reusable piece of code. When you call a macro in a query, its content is substituted inline prior to query execution.

They are handy for simplifying repetitive or complex queries, reducing the amount of code that needs to be written, and making it easier to maintain and update queries over time. By defining macros, you can create reusable code that can be used throughout your codebase, making it easier to develop and manage complex queries.

Suppose you have a CASE expression to get a country group using country code. It might look like this:

SELECT
CASE
WHEN country IN ('US', 'CA') THEN 'NA'
WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU'
WHEN country IN ('AU') THEN country
ELSE 'Other'
END AS country_group,
....
FROM fact_sales;
GROUP BY country_group;

Instead of copy-pasting it whenever you need it, you can create a macro for this expression and reference it. Using a macro will avoid changing the logic for these expressions in all places where they are being used, for example.

How to create a macro

Macros are defined using an #+macro command.

#+macro countryGroup(countryField) 
#+meta { 
  :doc "Given a name of the country column returns a CASE statement to get a country group." 
} 
#+begin 
 CASE 
    WHEN {{ countryField }} IN ('US', 'CA') THEN 'NA' 
    WHEN {{ countryField }} IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU' 
    WHEN {{ countryField }} IN ('AU') THEN {{ countryField }} 
  ELSE 'Other' 
 END 
#+end

This code defines a macro named countryGroup, which accepts a parameter countryField that represents the name of a column in a SQL table that contains country values. The macro returns a CASE statement that will group countries into different categories.

You can use the “#+meta” command to add metadata and the “:doc” parameter for documentation.

Here’s an example of using the macro above:

SELECT 
 {{ countryGroup(country="country") }} AS country_group, 
 sum(revenue) AS revenue 
FROM fact_sales 
GROUP BY country_group; 

Note that CoginitiScript will expand countryGroup into the content of the macro body, and, before the execution, the SQL will be like the example below.

SELECT 
 CASE 
   WHEN country IN ('US', 'CA') THEN 'NA' 
   WHEN country IN ('GB', 'FR', 'DE', 'IT', 'PL', 'SE') THEN 'EU' 
   WHEN country IN ('AU') THEN country 
   ELSE 'Other' 
 END AS country_group, 
 sum(revenue) AS revenue 
FROM fact_sales 
GROUP BY country_group; 

Later on, to modify the logic of this expression, you’ll only need to update the macro.