New Transformation

New Transformation Feature under the ETL Toolbar

Breadcrumb:
ETL → New Transformation

Overview

The New Transformation feature in AnalyticsCreator allows users to define business logic and calculations as part of the ETL (Extract, Transform, Load) process. This includes derived columns, expressions, filters, conditional logic, and column-level transformations applied to staging or data mart layers.

Transformations are not implemented manually in SQL but are captured as metadata definitions that are automatically translated into deployment code for Fabric SQL, Azure Data Factory (ADF), or other integration layers. This makes transformation logic consistent, auditable, and reusable across environments.

When and Why to Use the New Transformation Feature

• Use when you need to derive business logic at the data warehouse level
• Apply calculated columns to staging or data mart tables
• Implement transformations in a governed and metadata-driven way
• Prepare data for semantic models by handling formatting, flag logic, or business rules

How to Define a Transformation

1. Go to the ETL toolbar and click New Transformation.
2. Select the table where you want to apply a transformation (e.g., staging or fact table).
3. Choose Add Transformation or right-click on a column and select Edit Transformation.
4. Enter the logic using SQL expressions, constants, or case statements.
5. Specify:
   • Output Column Name
   • Expression / Formula
   • Data Type and Length
   • Order of Execution if multiple transformations apply
6. Save the transformation to update the metadata model.

Types of Supported Transformations

• Simple expressions: Column + constant, string manipulation, math functions
• Conditional logic: CASE WHEN statements for status flags or derived values
• Date logic: Extracting year, month, quarter from date fields
• Business rules: Custom calculation logic aligned to domain requirements
• Lookup logic: Join conditions or mappings to reference tables

How Transformations Work in AnalyticsCreator

All transformations are stored in AnalyticsCreator’s metadata repository and are automatically applied during model generation and deployment. This includes:

• Generated SQL for Fabric SQL Databases
• ADF pipeline expressions for ELT flows
• Auto-generated delta view logic for OneLake or Lakehouse scenarios

Because transformations are metadata-based, updates can be applied centrally without rewriting SQL scripts, ensuring that changes are reflected across all environments consistently.

Benefits of the Transform Feature

Limitations

• Complex multi-table joins may require staging views or pre-transformation logic
• Transformations are evaluated at design time—not interactively during runtime
• Not intended for row-level security logic—use semantic layer or access rules instead

Best Practices

• Use clear naming conventions for calculated columns (e.g., IsActiveFlag, RevenueGrowthPct)
• Document business logic behind each transformation for governance
• Group related transformations logically and order them for readability
• Use transformations in the staging layer to offload complex logic from semantic models

Final Notes

The Transform feature is essential for building trusted, maintainable, and scalable data warehouse logic inside AnalyticsCreator. It enables modeling teams to define calculations once and apply them consistently across deployment environments—without manual SQL or ETL scripting. Whether preparing a Kimball-style fact table or shaping data for Microsoft Fabric, the Transform screen keeps your logic governed, centralized, and automated.