Astera Data Stack
Version 9
Version 9
  • Welcome to Astera Data Stack Documentation
  • Release Notes
    • Astera 9.0 - Release Notes
  • Setting Up
    • System Requirements
    • Product Architecture
    • Installing Client and Server Applications
    • Connecting to a Astera Server using Lean Client
    • How to Connect to a Different Astera Server from the Lean Client
    • How to Set up a Server Certificate (.pfx) File in a New Environment
    • How to Build a Cluster Database and Create a Repository
    • How to Login from Lean Client
    • Licensing Model in Astera 9
    • User Roles and Access Control
    • Offline Activation of Astera Data Stack
  • Dataflows
    • Sources
      • Data Providers and File Formats Supported in Astera Data Stack
      • Setting Up Sources
      • Excel Workbook Source
      • COBOL File Source
      • Database Table Source
      • Delimited File Source
      • File System Items Source
      • Fixed Length File Source
      • Email Source
      • Report Source
      • SQL Query Source
      • XML/JSON File Source
      • PDF Form Source
    • Transformations
      • Introducing Transformations
      • Aggregate Transformation
      • Constant Value Transformation
      • Denormalize Transformation
      • Distinct Transformation
      • Expression Transformation
      • Filter Transformation
      • Join Transformation
      • List Lookup Transformation
      • Merge Transformation
      • Normalize Transformation
      • Passthru Transformation
      • Reconcile Transformation
      • Route Transformation
      • Sequence Generator
      • Sort Transformation
      • Sources as Transformations
      • Subflow Transformation
      • Switch Transformation
      • Tree Join Transformation
      • Tree Transform
      • Union Transformation
      • Data Cleanse Transformation
      • File Lookup Transformation
      • SQL Statement Lookup
      • Database Lookup
    • Destinations
      • Setting Up Destinations
      • Database Table Destination
      • Delimited File Destination
      • Excel Workbook Destination
      • Fixed Length File Destination
      • SQL Statement Destination
      • XML File Destination
    • Data Logging and Profiling
      • Creating Data Profile
      • Creating Field Profile
      • Data Quality Mode
      • Using Data Quality Rules in Astera
      • Record Level Log
    • Database Write Strategies
      • Data Driven
      • Source Diff Processor
      • Database Diff Processor
      • Dimension Loader - Database Write
    • Text Processors
      • Delimited Parser
      • Delimited Serializer
      • Language Parser
      • Fixed Length Parser
      • Fixed Length Serializer
      • XML/JSON Parser
      • XML JSON Serializer
    • Visualizations
      • Basic Plots
      • Distribution Plots
  • Workflows
    • What are Workflows?
    • Creating Workflows in Astera
    • Decision Task
    • EDI Acknowledgment Task
    • File System Task
    • File Transfer Task
    • OR Task
    • Run Dataflow Task
    • Run Program Task
    • Run SQL File Task
    • Run SQL Script Task
    • Run Workflow Task
    • Send Mail Task
    • Workflows with a Dynamic Destination Path
    • Customizing Workflows With Parameters
    • GPG-Integrated File Decryption in Astera
  • Subflows
    • Using Subflows in Astera
  • DATA MODEL
    • Creating a Data Warehousing Project
    • Data Models
      • Introducing Data Models
      • Opening a New Data Model
      • Data Modeler - UI Walkthrough
      • Reverse Engineering an Existing Database
      • Creating a Data Model from Scratch
      • General Entity Properties
      • Creating and Editing Relationships
      • Forward Engineering
      • Verifying a Data Model
    • Dimensional Modelling
      • Introducing Dimensional Models
      • Converting a Data Model to a Dimensional Model
      • Fact Entities
      • Dimension Entities
      • Date and Time Dimension
      • Verifying a Dimensional Model
    • Documentation
      • Generating Technical and Business Documentation for Data Models
      • Lineage and Impact Analysis
    • Deployment and Usage
      • Deploying a Data Model
      • Validate Metadata and Data Integrity
      • Using Astera Data Models in ETL Pipelines
      • Connecting an Astera Data Model to a Third Party Visualization Tool
  • Functions
    • Introducing Function Transformations
    • Custom Functions
    • Logical
      • Coalesce (Any value1, Any value2)
      • IsNotNull (AnyValue)
      • IsRealNumber (AnyValue)
      • IsValidSqlDate (Date)
      • IsDate (AnyValue)
      • If (Boolean)
      • If (DateTime)
      • If (Double)
      • Exists
      • If (Int64)
      • If (String)
      • IsDate (str, strformat)
      • IsInteger (AnyValue)
      • IsNullOrWhitespace (StringValue)
      • IsNullorEmpty (StringValue)
      • IsNull (AnyValue)
      • IsNumeric (AnyValue)
    • Conversion
      • GetDateComponents (DateWithOffset)
      • ParseDate (Formats, Str)
      • GetDateComponents (Date)
      • HexToInteger (Any Value)
      • ToInteger (Any value)
      • ToDecimal (Any value)
      • ToReal (Any value)
      • ToDate (String dateStr)
      • TryParseDate (String, UnknownDate)
      • ToString (Any value)
      • ToString (DateValue)
      • ToString (Any data, String format)
    • Math
      • Abs (Double)
      • Abs (Decimal)
      • Ceiling (Real)
      • Ceiling(Decimal)
      • Floor (Decimal)
      • Floor (Real)
      • Max (Decimal)
      • Max (Date)
      • Min (Decimal)
      • Min (Date)
      • Max (Real)
      • Max (Integer)
      • Min (Real)
      • Pow (BaseExponent)
      • Min (Integer)
      • RandomReal (Int)
      • Round (Real)
      • Round (Real Integer)
      • Round (Decimal Integer)
      • Round (Decimal)
    • Financial
      • DDB
      • FV
      • IPmt
      • IPmt (FV)
      • Pmt
      • Pmt (FV)
      • PPmt
      • PPmt (FV)
      • PV (FV)
      • Rate
      • Rate (FV)
      • SLN
      • SYD
    • String
      • Center (String)
      • Chr (IntAscii)
      • Asc (String)
      • AddCDATAEnvelope
      • Concatenate (String)
      • ContainsAnyChar (String)
      • Contains (String)
      • Compact (String)
      • Find (Int64)
      • EndsWith (String)
      • FindIntStart (Int32)
      • Extract (String)
      • GetFindCount (Int64)
      • FindLast (Int64)
      • GetDigits (String)
      • GetLineFeed
      • Insert (String)
      • IsAlpha
      • GetToken
      • IndexOf
      • IsBlank
      • IsLower
      • IsUpper
      • IsSubstringOf
      • Length (String)
      • LeftOf (String)
      • Left (String)
      • IsValidName
      • Mid (String)
      • PadLeft
      • Mid (String Chars)
      • LSplit (String)
      • PadRight
      • ReplaceAllSpecialCharsWithSpace
      • RemoveChars (String str, StringCharsToRemove)
      • ReplaceLast
      • RightAlign
      • Reverse
      • Right (String)
      • RSplit (String)
      • SplitStringMultipleRecords
      • SplitStringMultipleRecords (2 Separators)
      • SplitString (3 separators)
      • SplitString
      • SplitStringMultipleRecords (3 Separators)
      • Trim
      • SubString (NoOfChars)
      • StripHtml
      • Trim (Start)
      • TrimExtraMiddleSpace
      • TrimEnd
      • PascalCaseWithSpace (String str)
      • Trim (String str)
      • ToLower(String str)
      • ToProper(String str)
      • ToUpper (String str)
      • Substring (String str, Integer startAt)
      • StartsWith (String str, String value)
      • RemoveAt (String str, Integer startAt, Integer noofChars)
      • Proper (String str)
      • Repeat (String str, Integer count)
      • ReplaceAll (String str, String lookFor, String replaceWith)
      • ReplaceFirst (String str, String lookFor, String replaceWith)
      • RightOf (String str, String lookFor)
      • RemoveChars (String str, String charsToRemove)
      • SplitString (String str, String separator1, String separator2)
    • Date Time
      • AddMinutes (DateTime)
      • AddDays (DateTimeOffset)
      • AddDays (DateTime)
      • AddHours (DateTime)
      • AddSeconds (DateTime)
      • AddMonths (DateTime)
      • AddMonths (DateTimeOffset)
      • AddMinutes (DateTimeOffset)
      • AddSeconds (DateTimeOffset)
      • AddYears (DateTimeOffset)
      • AddYears (DateTime)
      • Age (DateTime)
      • Age (DateTimeOffset)
      • CharToSeconds (Str)
      • DateDifferenceDays (DateTimeOffset)
      • DateDifferenceDays (DateTime)
      • DateDifferenceHours (DateTimeOffset)
      • DateDifferenceHours (DateTime)
      • DateDifferenceMonths (DateTimeOffset)
      • DateDifferenceMonths (DateTime)
      • DatePart (DateTimeOffset)
      • DatePart (DateTime)
      • DateDifferenceYears (DateTimeOffset)
      • DateDifferenceYears (DateTime)
      • Month (DateTime)
      • Month (DateTimeOffset)
      • Now
      • Quarter (DateTime)
      • Quarter (DateTimeOffset)
      • Second (DateTime)
      • Second (DateTimeOffset)
      • SecondsToChar (String)
      • TimeToInteger (DateTime)
      • TimeToInteger (DateTimeOffset)
      • ToDate Date (DateTime)
      • ToDate DateTime (DateTime)
      • ToDateString (DateTime)
      • ToDateTimeOffset-Date (DateTimeOffset)
      • ToDate DateTime (DateTimeOffset)
      • ToDateString (DateTimeOffset)
      • Today
      • ToLocal (DateTime)
      • ToJulianDate (DateTime)
      • ToJulianDayNumber (DateTime)
      • ToTicks (Date dateTime)
      • ToTicks (DateTimeWithOffset dateTime)
      • ToUnixEpoc (Date dateTime)
      • ToUtc (Date dateTime)
      • UnixTimeStampToDateTime (Real unixTimeStamp)
      • UtcNow ()
      • Week (Date dateTime)
      • Week (DateTimeWithOffset dateTime)
      • Year (Date dateTime)
      • Year (DateTimeWithOffset dateTime)
      • DateToJulian (Date dateTime, Integer length)
      • DateTimeOffsetUtcNow ()
      • DateTimeOffsetNow ()
      • Day (DateTimeWithOffset dateTime)
      • Day (Date dateTime)
      • DayOfWeekStr (DateTimeWithOffset dateTime)
      • DayOfWeek (DateTimeWithOffset dateTime)
      • DayOfWeek (Date dateTime)
      • DateToJulian (DateTimeWithOffset dateTime, Integer length)
      • DayOfWeekStr (Date dateTime)
      • FromJulianDate (Real julianDate)
      • DayOfYear (Date dateTime)
      • DaysInMonth(Integer year, Integer month)
      • DayOfYear (DateTimeWithOffset dateTime)
      • FromUnixEpoc
      • FromJulianDayNumber (Integer julianDayNumber)
      • FromTicksUtc(Integer ticks)
      • FromTicksLocal(Integer ticks)
      • Hour (Date dateTime)
      • Hour (DateTimeWithOffset dateTime)
      • Minute (Date dateTime)
      • JulianToDate (String julianDate)
      • Minute (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (DateTimeWithOffset dateTime)
      • DateToIntegerYYYYMMDD (Date dateTime)
    • Files
      • AppendTextToFile (String filePath, String text)
      • CopyFile (String sourceFilePath, String destFilePath, Boolean overWrite)
      • CreateDateTime (String filePath)
      • DeleteFile (String filePath)
      • DirectoryExists (String filePath)
      • FileExists (String filePath)
      • FileLength (String filePath)
      • FileLineCount (String filePath)
      • GetDirectory (String filePath)
      • GetEDIFileMetaData (String filePath)
      • GetExcelWorksheets (String excelFilePath)
      • GetFileExtension (String filePath)
      • GetFileInfo (String filePath)
      • GetFileName (String filePath)
      • GetFileNameWithoutExtension (String filePath)
      • LastUpdateDateTime (String filePath)
      • MoveFile (String filePath, String newDirectory)
      • ReadFileBytes (String filePath)
      • ReadFileFirstLine (String filePath)
      • ReadFileText (String filePath)
      • ReadFileText (String filePath, String codePage)
      • WriteBytesToFile (String filePath, ByteArray bytes)
      • WriteTextToFile (String filePath, String text)
    • Date Time With Offset
      • ToDateTimeOffsetFromDateTime (dateTime String)
      • ToUtc (DateTimeWithOffset)
      • ToDateTimeOffsetFromDateTime
      • ToDateTimeOffset (String dateTimeOffsetStr)
      • ToDateTimeFromDateTimeOffset
    • GUID
      • NewGuid
    • Encoding
      • ToBytes
      • FromBytes
      • UrlEncode
      • UrlDecode
    • Regular Expressions
      • ReplaceRegEx
      • ReplaceRegEx (Integer StartAt)
    • TimeSpan
      • Minutes
      • Hours
      • Days
      • Milliseconds
    • Matching
      • Soundex
      • DoubleMetaphone
      • RefinedSoundex
  • Report Model
    • User Guide
      • Report Model Tutorial
    • Report Model Interface
      • Report Options
      • Report Browser
      • Data Regions in Report Models
      • Region Properties Panel
      • Pattern Properties
      • Field Properties Panel
    • Use Cases
      • Applying Pattern to Line
      • Auto Creating Data Regions and Fields
      • Auto Parsing
      • Connecting to Cloud Storage
      • Creating Multi Column Data Regions
      • Defining Region End Type as Specific Text and Regular Expression
      • Defining the Start Position of Data Fields
      • Floating Patterns and Floating Fields
      • How To Work With PDF Scaling Factor in a Report Model
      • Line Count
      • Pattern Count
      • Pattern is a Regular Expression
      • Using Comma Separated Values to Define Start Position
    • Auto Generate Layout (Beta)
      • Setting Up AGL in Astera
      • UI Walkthrough Auto Generation of Layout, Fields and Table
      • Using Auto Generation Layout, Auto Create Fields, and Auto Create Table (Beta)
    • Exporting Options
      • Exporting a Report Model
      • Exporting Report Model to A Dataflow
    • Miscellaneous
      • Importing Monarch Models
      • Microsoft Word and Rich Text Format Support
      • Working With Problematic PDF Files
  • API Flows
    • API Consumption
      • Consume
        • REST Connection
        • Making API Calls with the REST Client Object in Astera
        • REST API Browser
        • Method Operations
        • Pagination
      • Authorize
        • Open APIs - Configuration Details
        • Authorizing Facebook APIs in Astera
        • Authorizing Astera's Server APIs
        • Authorizing Avaza APIs in Astera
        • Authorizing Square API in Astera
        • Authorizing ActiveCampaign API in Astera
        • Authorizing QuickBooks’ API in Astera
        • Accessing Astera's Server APIs Through a Third Party Tool
        • Astera's Server API Documentation
  • Project Management
    • Project Management
      • Deployment
      • Server Monitoring and Job Management
      • Connecting to Source Control
      • Astera Project and Project Explorer
    • Job Scheduling
      • Scheduling Jobs on the Server
      • Job Monitor
  • Use Cases
    • End-to-End Use Cases
      • Data Integration
        • Using Astera Data Stack to Create and Orchestrate an ETL Process for Partner Onboarding
      • Data Warehousing
        • Building a Data Warehouse - A Step By Step Approach
      • Data Extraction
        • Reusing The Extraction Template for Similar Layout Files
  • Connectors
    • Setting Up IBM DB2/iSeries Connectivity in Astera
    • Connecting to SAP HANA Database
    • Connecting to MariaDB Database
    • Connecting to Salesforce Database
    • Connecting to Salesforce - Legacy Database
    • Connecting to Vertica Database
    • Connecting to Snowflake Database
    • Connecting to Amazon Redshift Database
    • Connecting to Amazon Aurora Database
    • Connecting to Google Cloud SQL in Astera
    • Connecting to MySQL Database
    • Connecting to PostgreSQL in Astera
    • Connecting to Netezza Database
    • Connecting to Oracle Database
    • Connecting to Microsoft Azure Databases
    • Connecting to Amazon RDS Databases
  • Miscellaneous
    • Using Dynamic Layout Template Mapping in Astera
    • Synonym Dictionary File
    • SmartMatch Feature
    • Role Based Access Control in Astera
    • Updating Your License in Astera
    • Using Output Variables in Astera
    • Connection Vault
    • Safe Mode
    • Using the Data Source Browser in Astera
    • Pushdown Mode
    • Cloud Deployment
      • Deploying Astera on Microsoft Azure Cloud
      • Deploying Astera on Oracle Cloud
      • Deploying Astera on Amazon Web Services
    • Context Information
  • Best Practices
    • Overview of Cardinality in Data Modeling
    • Cardinality Errors FAQs
    • Astera Best Practices - Dataflows
Powered by GitBook

© Copyright 2025, Astera Software

On this page
  • Video
  • Overview
  • Layout Builder
  • Dimension Role
  • Related Dimension Field
  • Adding Surrogate Key and Row Identifier Fields
  1. DATA MODEL
  2. Dimensional Modelling

Dimension Entities

PreviousFact EntitiesNextDate and Time Dimension

Last updated 1 year ago

In a dimensional data model, a dimension entity represents a table that contains descriptive information regarding a fact. Each attribute in a dimension table provides context to the numeric information present in the fact table. In a star schema, each dimension entity is related to the fact entity.

Video

Overview

Let’s assume that we have a sample dimensional model that looks like this:

The Customer, City, Stock_Item, and Employee entities in this model represent dimension tables. The Sale entity in the center represents the fact table.

In this article, we will examine the layout of the Customer entity to learn how you can configure dimension entities in Astera Data Stack.

Layout Builder

To open the properties of an entity, right-click on it and select Properties from the context menu. Alternatively, you can double-click on the entity object.

On the first screen, you can view and edit some general information regarding the table, including its name, schema, and type.

Click Next to proceed to the Layout Builder screen.

Dimension Role

The Dimension Role column in the layout builder provides a dropdown menu for you to assign a dimension-related role to each field present in the table. These roles mostly constitute Slowly Changing Dimension types and other SCD functions.

  • Insert Only: Stores a field value that will not be updated, regardless of any updates made to it. Otherwise known as SCD0.

  • Business Key: Holds the key that is normally used to identify records in the table.

  • SCD1 – Update: Stores an SCD value which will be updated if modified. The storage of historical data is not considered.

  • SCD2 – Update and Insert: Stores an SCD value which is expected to change over time. A new record is added each time the value changes. The validity of each record is indicated through additional fields.

  • Current Record Designator: Stores the Active Value if the record has the current version of the SCD value. Otherwise, it stores the Inactive Value. Active Value and Inactive Value should be entered in the appropriate cells in the grid next to the Current Record Designator.

  • Surrogate Key: Designates the field holding an extra system generated key that identifies versions of the SCD value with the same business key.

  • Effective Date: Stores the date at which the SCD value in a record became valid.

  • Expiration Date: Stores the date at which the SCD value in a record became invalid.

  • Version Number: Stores the version number of the SCD value.

  • Previous Surrogate Key: Stores the surrogate key of the previous version of the record.

  • SCD3 – Current Value: Stores the current value of an SCD Type 3 field.

  • SCD3 – Previous Value: Stores the previous value of an SCD Type 3 field.

  • SCD6 – Current Value: Stores the current value of an SCD Type 6 field.

  • SCD6 – Historical Value: Stores a historical value (not necessarily the previous value) of an SCD Type 6 field.

Placeholder Dimension: Caters to late arriving dimensions and early arriving facts.

In this dimension table, we have the following fields:

  • Customer_Key: Surrogate Key

  • WWI_Customer_ID: Business Key

  • Customer: SCD2 – Update and Insert

  • Bill_to_Customer: Insert Only

  • Valid_To: Expiration Date

Related Dimension Field

The Related Dimension Field column only appears in the layout builder when the Show Options for SCD3 and SCD6 option on the Entity Properties screen is checked.

Once you’ve assigned the SCD3 or SCD6 role to a field, the dropdown menu in the Related Dimension Field column will provide a list of fields that have been assigned the SCD3 - Previous Value or the SCD6 - Historical Value dimension role. From here, you can choose a field to store the previous value or historical value, respectively. This functionality is particularly useful in cases where you need to use more than one set of SCD3 or SCD6 values.

Adding Surrogate Key and Row Identifier Fields

In this case, the Customer dimension entity already contains fields that can be assigned the surrogate key and row identifier dimension roles. However, if your entity layout does not contain such fields, you can create them via the options that appear in the context menu when you right-click on the entity.

Note: In this case, the Add Surrogate Key option is disabled because the layout already contains a surrogate key field.

If you wish to add a row identifier to the layout or change the row identifier already present in the layout, click on the Add Row Identifier option. A pop-up window will allow you to choose from the list of row identifiers that are available in the product.

Once you’ve chosen an option from the list, you can name the new field using the textbox at the bottom of the window. Click OK to close the window and add the field to the dimension entity’s layout.

This concludes our discussion on dimension entities.

A configuration window will appear. This window provides the same options as it does for a general entity, the only additions being the Dimension Role and Related Dimension Field columns on the Layout Builder screen. To learn about general entity properties, click .

here