Skip to main content

Creating and Using Macros

Getting Started​

Step 1: Load Sample Metadata​

  1. Navigate to the BimlFlex sample collection

  2. Select the "08 - MSSQL DataMart ADF Solution" sample

  3. Load the sample metadata into your environment

    Load Sample Metadata

Step 2: Access the Macros Interface​

  1. Once the sample metadata is loaded, navigate to the Macros section

  2. Review the existing macros included in the sample (these serve as examples for implementation)

    Macros Overview

Creating a String Expression Macro​

Step 3: Create a New Macro​

  1. Click on the option to add a new string macro
  2. Enter a name for your macro (e.g., "null to demo")
  3. Add a description (optional)
  4. Add comments (optional)
  5. Enter the SQL expression:
ISNULL(@@this, 'DEMO')
  1. Save the macro
Add Macro
note

The @@this placeholder is context-aware and will automatically be replaced with the column name where the macro is applied. This eliminates the need to manually specify column names.

Applying Macros to Columns​

Step 4: Apply Column-Level Macro​

  1. Navigate to your target table (e.g., Customer table view)
  2. Select the Columns section
  3. Choose the column where you want to apply the macro (e.g., "LastName")
  4. In the expression field, type {{ (two open curly brackets)
  5. Select your macro from the dropdown list
  6. Save the column configuration
Assign Column Macro

Creating Join Statement Macros​

Step 5: Create a Join Macro​

  1. Create a new macro
  2. Name it descriptively (e.g., "product-category")
  3. Paste your join statement SQL code:
INNER JOIN [awlt].[ProductCategory] spc
	ON	sp.[ProductCategory_BK] = spc.[ProductCategory_BK]
INNER JOIN [awlt].[ProductModel] spm
	ON	sp.[ProductModel_BK] = spm.[ProductModel_BK]
  1. Save the macro
note

Macros can be nested within other macros, allowing you to build complex, reusable logic structures.

Step 6: Apply Join Macro to Table​

  1. Return to your target table
  2. Navigate to the Join SQL field
  3. Type {{ (two open curly brackets)
  4. Select "product-category" from the dropdown
  5. Save the Object

Use Cases and Best Practices​

Common Applications​

  • Default date logic: Create macros for frequently used date calculations
  • Environment-specific WHERE clauses: Define standard filtering logic
  • Standardized NULL handling: Implement consistent NULL replacement across tables
  • Reusable join patterns: Store complex join logic for repeated use

Best Practices​

  1. Use descriptive names for macros to ensure clarity
  2. Document macro purposes in the description field
  3. Test macros on a single column/table before widespread implementation
  4. Leverage nested macros for complex logic when appropriate
  5. Review generated code after build to verify correct injection

Conclusion​

BimlFlex macros provide a powerful way to create reusable, maintainable SQL logic across your data integration projects. By implementing macros, you can:

  • Reduce code duplication
  • Ensure consistency across implementations
  • Simplify maintenance and updates
  • Improve development efficiency