Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
A data model is a logical structure that incorporates the various tables present within a database, and the relationships between them. In essence, it outlines the connections present between the datasets existing in a particular database, thus illustrating how these datasets are stored and processed within the system.
Astera Data Stack allows its users to create a data model from scratch using the entities available in the toolbox, or to generate a data model for an already existing database using the reverse engineer option. Moreover, you can also create and edit entity relationships, modify entity layouts, and add and modify indexes using this functionality.
Here is a sample data model that has been reverse engineered from an existing database.
The boxes represent the tables present in the database and the lines connecting these boxes are an indicator of the relationships between these tables.
Astera Data Stack contains a built-in data modeler that you can use to create a data model from scratch or by reverse engineering an existing database. In this article, we will cover a detailed overview of the data modeler’s user interface.
Let’s assume that we’ve created this data model by reverse engineering an existing database:
The data modeler contains a dedicated toolbar that provides an extensive list of options.
You will find the following options or shortcut icons on the toolbar, from left to right.
Undo: Allows you to undo an action that you’ve performed on the data modeler.
Redo: Allows you to redo an action that you’ve negated through the Undo option.
Auto Layout Diagram: Arranges the entities in a model to improve its visual representation.
Zoom: A dropdown menu that allows you to zoom in or out of a model.
Auto-Size All: Adjusts the size of each entity object.
Show Alias Labels: Displays the parent and child alias on each relationship link in the model.
Show Verb Labels: Displays the parent-to-child and child-to-parent verbs, if assigned by the user, on the relationship link. You can assign these verbs to each relationship via the Edit Relationship window.
Show Data Types: Displays the data types of each field in an entity. This option is only applicable if one of the following options has already been selected: View Entity and all Elements or View Entity and Keys.
View at an Entity Level: Reverts to the default setting of displaying only entity names on the data modeler.
View Entity and all Elements: Displays all of the elements or fields in each entity present in the model.
View Entity and Keys: Displays all of the primary and foreign keys in each entity present in the model.
Add Self Reference: Allows you to create a self-reference relationship within an entity. To learn more about how you can use this option, click here.
Link entities to create non-identifying relationships: Allows you to create non-identifying relationships between entities. To learn more about how you can use this option, click here.
Link entities to create identifying relationships: Allows you to create identifying relationships between entities. To learn more about how you can use this option, click here.
Pointer: Reverts back to the default pointer in case you had selected one of the options used to link entities and create relationships.
Infer Relationships from Field Names: Automatically assumes and creates relationships based on common field names between entities.
Denormalize Entities: Enables you to denormalize or combine two entities in the model.
Deploy Data Model: Enables you to deploy a data model. For more information on deploying a data model, click here.
Change Database Connection Info: Allows you to change the database connection of the model via a Database Connection window.
Reverse engineer an existing database: Allows you to create a data model by reverse engineering an existing database. To learn more about the process of reverse engineering, click here.
Generate DDL Script: Allows you to create a DDL script from a data model. This option is also called Forward Engineering.
The toolbox includes a few objects which can be used as part of a model.
You can drag and drop any of these objects onto the data modeler.
Annotation: Provides a textbox for you to add a comment on the model.
Date Dimension: A non-editable date dimension entity that can be used as part of a dimensional model.
Entity: An editable general entity that can be added to an existing data model or used to create a data model from scratch. To learn about general entities, click here.
Time Dimension: A non-editable time dimension entity that can be used as part of a dimensional model. For more information on date and time dimensions, click here.
The data model browser provides a single window for you to edit the data model and its components without having to look through the entire model and access the entities directly.
You can expand each entity to view the elements contained in it.
Moreover, you can right-click on an entity name and choose to go to the actual item in the data model.
The other options available in the context menu are the same as those that appear when you right-click on an entity in the data model.
At the very top of the data model browser, a few options/icons are available.
Sort Entities Alphabetically: Sorts the entity names in an alphabetical order. This option is selected by default.
Group Entities by Type: Creates separate nodes for each entity type. In this case, there’s only going be one node, General, if this option is selected.
Expand All: Expands all of the nodes in the data model browser.
Collapse All: Collapses all of the nodes in the data model browser.
Delete this object from the data model: Deletes the selected object from the data model.
You can use the search bar underneath these options to search for any specific entity or field in the model.
At the bottom of the data modeler, you’ll notice a tab titled Main and an icon that represents the Add New View option.
The Main tab contains a view of the complete data model. If you wish to add a new view consisting of a few specified entities, click on the Add New View icon. A blank designer will appear on the screen. Here, you can create a new view by adding entities from the existing model, which can be done in two ways.
Drag and drop the entities of your choice from the data model browser.
Right-click on the designer and select Add Existing Entities from the context menu.
The following pop-up window will appear on the screen:
Here, you can select the entities you want to include in the new view. In this case, we’ll select People, Invoices, and InvoiceLines.
Once you’re done selecting the entities, click OK.
A new view consisting of the selected entities will appear on the screen.
Using the tabs at the bottom, you can switch between different views of the same model.
This concludes our walkthrough of the data modeler’s user interface.
Reverse engineering is the process of constructing a data model from an existing database. When you reverse engineer a database in Astera Data Stack, it creates a logical structure that incorporates the tables in the database, and the relationships between them. In essence, It synchronizes the data model with the database and its entities.
There are two ways to reverse engineer a database in Astera Data Stack. One is from the toolbar on the data model designer and the other one is from the main menu.
Let’s explore each, one by one.
On the toolbar of the data model designer, there is an icon for reverse engineering.
On the main menu bar of Astera Data Stack, there is an option for Data Model > Reverse Engineer.
Once you select either of these two options, a Reverse Engineering wizard will open on the screen.
In case you haven’t established a database connection earlier, specify a connection from within the Reverse Engineering wizard.
Click on the ellipses in front of the Database Connection option. A Database Connection screen will open.
On this screen, select the data provider for the database and fill in the required credentials. Once done, click OK.
There is a list of data providers that Astera Data Stack supports.
Note: In this case, we will select SQL Server as our database provider.
Now, all the tables in the database have been listed as Entities on the Reverse Engineering wizard.
The following information is available for each entity:
Exist? - Identifies if the entity already exists in the model
Type - Specifies the type of the entity in the database (Example: Table)
Schema - Represents the schema that the entity belongs to in the database
Name - Name of the entity in the database
Select the entities that you want to reverse engineer in the data model.
Note: In this case, we are selecting all the listed entities.
On the Reverse Engineering wizard, there is a Search option that can be used when there are numerous listed entities and specific entities are to be reverse engineered from the database.
There is also an option to filter out listed entities for each information type provided. Upon selecting the filter, it would open a new wizard where you can specify filter options.
Once you’ve selected the entities for reverse engineering, click OK.
When the process is complete, the selected entities from the database will appear on the data model designer.
This concludes our discussion on reverse engineering an existing database in Astera Data Stack.
An entity relationship represents the association between two tables that exist within a database. For example, a table containing employee data is linked to one containing sales data in the sense that each sale is made by an employee. Each relationship is denoted by a foreign key, which is an attribute that refers to the primary key of the parent entity. In a data model, these relationships are visualized through links connecting related entities.
In this article, we will discuss how you can create and edit relationships between the entities present in a data model in Astera Data Stack.
Here, we have a couple of entities that have been reverse engineered from an existing database. The entity named Employee is the parent entity and the one named Sale is the child entity.
To edit a relationship, right-click on the relationship link and select Edit from the context menu.
The Edit Relationship configuration window will appear.
Using this window, you can modify the properties of the relationship that exists between these two entities.
From the Relationship Type dropdown menu, you can choose a relationship type from two options: Identifying and Non-Identifying.
Non-identifying: A relationship where the primary key of the parent entity is included in the child entity but not as part of the primary key.
Identifying: A relationship where the primary key of the parent entity is included in the primary key of the child entity.
The relationship between Sale and Employee will remain non-identifying. You can also change the relationship type by right-clicking on the relationship link, hovering over the Relationship Type option, and selecting the appropriate choice.
Using the Parent Alias, Child Alias, Constraint Name, Parent to Child Verb and Child to Parent Verb text boxes, you can define certain attributes of the relationship.
Parent Alias: A user-defined alternative name for the parent entity.
Child Alias: A user-defined alternative name for the child entity.
Constraint Name: A user-defined name that represents the rules describing this relationship.
Parent to Child Verb: A user-defined phrase that describes the dynamics of the parent to child relationship.
Child to Parent Verb: A user-defined phrase that describes the dynamics of the child to parent relationship.
By checking or unchecking the Mandatory checkbox, you can define whether the relationship is mandatory or optional, respectively.
A mandatory relationship is one that requires the parent entity to participate in the relationship. On the other hand, an optional one is where participation is not compulsory. Once the mandatory or optional nature of a relationship has been defined, it is further indicated by the Allows Null column in the Layout Builder of the child entity. If the relationship is mandatory, the Allows Null checkbox for the foreign key that represents the relationship between the entities is automatically unchecked.
You can also define the mandatory or optional nature of a relationship by right-clicking on the relationship link and checking or unchecking the Mandatory option.
The table at the bottom of the Edit Relationship screen contains a non-editable column representing the Parent Field and a corresponding dropdown menu for the Foreign Key(s).
The Parent Field is the primary key of the parent entity and the Foreign Key(s) is a corresponding key that represents the relationship in the child entity.
This is how you can create a non-identifying relationship between two entities:
Click on the Link entities to create non-identifying relationships icon in the data model menu bar.
Drag a relationship link from the parent entity and drop it onto the child entity.
This is how you can create an identifying relationship between two entities:
1. Click on the Link entities to create identifying relationships icon in the data model menu bar.
2. Drag a relationship link from the parent entity and drop it onto the child entity.
A self-reference relationship denotes that a foreign key exists within the same entity as the parent field. This is how you can create one:
Click on the entity that fulfils the requirements of a self-reference relationship. This will enable the Add Self Reference icon in the menu bar.
Click on the Add Self Reference icon.
You’ll notice that a self-relationship link will have been created to and from the entity in question.
This concludes our discussion on creating and editing relationships.
Virtual relationships are used to link related entities based on a common attribute which helps in querying data and generating useful insights.
Consider entities that do not have relationships between them in the database. To derive insights from entities, we may need certain relationships in the data model while ensuring that the schema of the database remains the same.
To solve this problem, Astera Data Stack offers virtual relationships that help us create relationships between entities and leverage the benefits of relationships without forward engineering them to the database. Virtual relationships have similar characteristics as physical relationships, but they are not present in the database.
Consider an example where Customers and Orders tables are present in a database but are not connected with each other.
A virtual relationship could be created between the entities using a common attribute such as the CustomerID. Virtual relationships between two entities can be marked as virtual using any of the following steps
Right-click the relationship and select Virtual from the context menu.
Double-click the relationship to open the Edit Relationship window and mark the Virtual checkbox. Click OK to save the changes and close the window.
Open the Relationship Manager window either from the toolbar or using the short cut key, Ctrl + Shift+ E.
Now, select the relationship and mark the Virtual checkbox. Click OK to close the window.
A virtual relationship between the Customers and Orders entities has been created successfully. This virtual relationship will not show any changes when applying diff nor will it give any verification errors when verifying for Read and Write Deployment.
A primary key is used to uniquely identify each record in an entity and may comprise of a single or multiple attributes of that entity.
Consider a database consisting of a schema that has an entity without a primary key. After reverse engineering it to a data model, we may need to have a primary key in it. To fulfill this need of having a primary key in the data model without making changes in the schema of the database, Astera Data Stack provides you with a feature of marking Virtual Primary Keys in entities.
These Virtual Primary Keys can be used to create virtual relationships with other entities which can further help users in designing ETL pipelines and deriving useful insights from their data.
Consider a database with the following entities: Customers, Orders, and Invoices. The Orders entity has details of orders placed by a customer, while the Invoices entity contains information about invoices for all orders.
As per the business logic, the Orders entity can have a relationship with the Invoices entity. However, due to the missing primary key in the Orders entity, we cannot create this relationship. Therefore, a Virtual Primary Key can be utilized to create a virtual relationship between these two entities.
To create a Virtual Primary Key in the entity, right-click the entity header and select Properties from the context menu or double-click the entity header.
On the Entity Properties page, check Virtual Primary Key checkbox.
Now, click Next. In the Layout Builder window, mark the existing field(s) as Primary Key.
Once done, click OK to close the window.
The Virtual Primary Key has been created for the Orders entity, and this change will not be forward engineered to the database.
Note that marking a Virtual Primary Key does not show any changes when applying a diff script, nor does it show any verification errors when verifying the data model for Read and Write Deployment.
Now, a virtual relationship can be created between the Orders and Invoices entities.
NOTE:
Entities with Virtual Primary Keys can only have outgoing relationships that are marked as virtual.
The primary key constraints for entities with Virtual Primary Keys are not forward engineered to the database. Hence, when the entity with a Virtual Primary Key is recreated in the database due to any changes in the properties or field names, it might lose its existing primary key constraints, if any are present in the database.
The Change Field Properties feature in Astera Data Stack allows users to modify field datatypes of multiple fields at once.
This feature is essential for data professionals tasked with adjusting field datatype properties of an entity in a data model, such as data types, DB types, length, and scale. The Change Field Properties functionality ensures that users do not have to modify each field individually.
For our use case, we have an entity called Customers in a data model, with the following field layout:
The following changes need to be made to all string datatype fields:
Change the dB types from ‘NVARCHAR’ to ‘VARCHAR’, if their length is between ‘10’ & ‘1000’, and change the length of newly modified ‘VARCHAR’ fields to ‘300’
Change the dB types from ‘NVARCHAR’ to ‘CHAR’ if their length is less than ‘10’, and change the length of newly modified ‘CHAR’ fields to ‘6’
Change the dB types from ‘NVARCHAR’ to ‘NCHAR’ if their length is ‘10’, while the length should remain unchanged.
1. To make the aforementioned changes, right-click on the Customers entity header and select the Change Field Properties option from the context menu.
2. The Change Field Properties window will open. In the Find section select the source fields’ properties (datatype, dbtype, length and scale) which you want to replace.
3. Next, in the Replace with section, select the required field properties which you want to replace the source fields’ properties with, as shown below:
4. For the first change in dB Type mentioned above, the following changes must be made in the Change Field Properties window:
5. Once done, Click OK to save the change and close the window.
6. For the next change, open the Change Field Properties window again, and make the following changes:
7. Once again, Click OK to save the changes and close the window.
8. For the final required change, open the Change Field Properties window again, and make the following changes:
Note: You can use the Scale and Scale Between options in the Find section in the same manner as the Length and Length Between options.
Click OK to save the changes and close the window.
Now, open the Customer Entity’s field layout. You will see that all required changes have been made successfully.
Similarly, this can be done with other datatypes and dB types as well.
In Astera Data Stack, a general entity represents a read-only table. In essence, it represents a table that could contain readable data but does not require any data to be written to it. All entities are marked as general entities by default when a database is reverse engineered unless you change their type manually. Moreover, you can also add new general entities to your data model via the toolbox.
In this article, we will be discussing the properties of a general entity in a data model.
Here, we have a general entity named Customer, which is part of a sample data model that we’ve created by reverse engineering an existing database. To learn about reverse engineering, click here.
You can access the properties of an entity in three ways:
Double-click on the entity header.
Right-click on the entity header and select Properties from the context menu.
Right-click on the entity name in the Data Model Browser and select Properties from the context menu.
On performing one of these actions, a configuration window will appear on your screen. You can use this window to modify the properties of the entity.
The first screen of the configuration window is the Entity Properties screen. Here, you can see and change some general information regarding the table.
Table Name: Using this text box, you can change the name of the table.
Schema: Using this text box, you can change the schema of the table.
Entity Type: From this dropdown menu, you can select an entity type from the following options.
Definition: Here, you can enter an optional description for the table.
The following options are available in the Entity Type dropdown menu:
General: When this type is selected, the entity layout will not contain Fact Field Type options or SCD (Slowly Changing Dimensions) Field Type options. Therefore, the entity will represent a general table.
Fact: When this type is selected, the entity layout will contain Fact Role options. Therefore, the entity will represent a fact table. To learn about fact tables, click here.
Dimension: When this type is selected, the entity layout will contain Dimension Role options. Therefore, the entity will represent a dimension table. To learn about dimension tables, click here.
Fact and dimension tables are an integral part of dimensional modeling. For more information on dimensional models, click here.
Click Next to move to the Layout Builder screen.
Here, you can modify the layout of the table by adding or removing fields.
To remove a field, right-click on it and select Delete from the context menu.
To add a new field, click on the empty row at the bottom, enter a field name, and specify its properties.
You can move a selected field up or down the table layout using the up and down arrow icons at the top of the screen.
Once you’ve modified the entity layout according to your requirements, click Next.
On the Data Model Entity Indexes screen, you can create indexes to increase the speed of data retrieval based on a field or a set of fields.
In this case, the Index List contains a pre-existing index. This is the primary key index that was already present in the database from which this entity was reverse engineered.
To add a new index to the Index List, click on the Add Index icon on the top left of the screen.
To delete an existing index, click on the Delete Index icon.
Note: The Delete Index icon is disabled when you select the primary key index.
You can also auto-generate indexes based on field characteristics using the Auto-Generate Indexes icon. This icon is only enabled for fact and dimension entities.
Once you click on the Add New Index icon, a new index with a default name is added to the Index list. You can customize this index using the Edit Index options.
Name: Using this text box, you can change the index name.
Using the provided checkboxes, you can specify the index type.
Clustered: A clustered index specifies the order in which records are physically stored in a table. We can only have one clustered index per entity.
Unique: A unique index ensures that two or more rows of data do not contain the same value.
Unique Constraint: A unique constraint has the same function as a unique index. When a unique constraint is created for a column, a corresponding unique index is also created for that column. Therefore, when you check this option, the Unique option is automatically checked and disabled.
Column Store: A column store index uses a columnar data format to retrieve data.
Using the dropdown menu under the Columns section of the screen, you can specify the key column(s) of the index.
If you wish to include any non-key columns to the index for further optimization, you can utilize the dropdown menu available under the Include Columns section of the screen.
Once you’ve created your desired index or indexes, click OK to exit the configuration window.
This concludes our discussion on general entity properties in a data model.
Working with complex data models might get difficult when dealing with various relationships within the model.
Astera Data Stack allows users to make changes to multiple relationships simultaneously, while also allowing users to track these changes, using the Relationship Manager.
The Relationship manager lets you:
Search and filter relationships
Change relationship types
Rename aliases, verbs, and constraints
Change foreign key field(s) of the relationship.
To open the relationship manager, select the Manage all Relationships in Data Model icon from the toolbar, or use the shortcut key, Shift+Alt+E.
The FormRelationshipManager wizard will open, containing a list of all relationships in the data model.
Here, relationships can be filtered and/or searched based on the parent entity, child entity, parent alias, child alias, etc.
For example, to find all outgoing relationships from an entity, a filter can be applied.
To do this, select the dropdown arrow icon located next to the “From” header, and check the checkbox of the entity to be filtered.
Click OK to close the drop-down menu.
Now, only relationships with the previously specified entities, that are also parent entities, will be displayed.
Similarly, if we want to find all relationships ”From” and “To” an entity, or if we want to search a relationship with a specific keyword in its parent entity name, child entity name, parent alias, child alias or even in constraint, we can use the search bar to search for the specific keyword and all relationships fulfilling the criteria will be displayed.
When any relationship from the list is selected, it will be highlighted in the data model and all information regarding the selected relationship will appear in the wizard. This information can be modified as desired by the user.
After making any changes, click OK to implement these changes in the data model.
A data model needs to be checked for any possible errors or warnings before it can be forward engineered to a target database or deployed to the server for usage. In Astera Data Stack, you can use the verification option to perform this verification.
To verify a data model, click on the Start Verification icon in the toolbar.
If the model is erroneous, the Verify window will display a list of error and warning messages. If not, it will indicate that the model has been successfully verified and contains no errors. Let’s verify the following model as an example.
This particular model contains no errors or warnings. Upon clicking the Start Verification option, the Verify window will show that it has been verified successfully.
When you expand the Start Verification option, you’ll see that there are two types of verification.
This is the default option for the Start Verification button.
When the Verify for Read and Write Deployment option is selected, the Verify window shows whether a model is ready to be deployed. In case there are any errors in the model, you’ll have to fix them before you can deploy it.
Forward Engineer refers to mirroring any changes you’ve made to a data model into a target database.
When the Verify for Forward Engineer option is selected, the Verify window shows whether the changes made to model are ready to be reflected into the database. In case there are any errors in the model, you’ll have to fix them before you can forward engineer it.
Here are some common errors that you may encounter when verifying a data model:
In this case, we have a data model that contains one entity named Orders.
This entity does not contain a primary key, as indicated by its layout.
On verifying for read and write deployment, the Verify window will display the following errors:
The first error indicates that there is no primary key field in the Orders entity.
The second error indicates that the field OrderID in the entity layout is the primary key within the database table but not within the data model.
Solution: Go to the entity layout and select the Primary Key checkbox for the OrderID field.
Click OK. The model can now be verified successfully.
In this case, we have a data model that contains two entities, Shippers and Orders, that are related to each other.
Here is the layout of the Orders entity.
You’ll notice that the data type for the ShipVia field is Decimal and the db type is NUMERIC. In actuality, these should be Integer and INT, respectively.
On verifying for read and write deployment, the Verify window will display the following errors:
The first two errors indicate that the data type for the ShipVia field in the Orders entity does not match with its data type in the database.
The third error indicates that the data type for ShipVia does not match with its corresponding field in the Shippers entity, which is the parent entity in this case.
Solution: Go to the layout of the Orders entity and change the data type of ShipVia to Integer and change its db type to INT. You can do so using the dropdown menus in the Data Type and Db Type columns.
Click OK. The model can now be verified successfully.
In this case, we have a data model that contains one entity named Orders.
This entity contains two clustered indexes. However, you cannot have more than one clustered index within an entity.
On verifying for forward engineering, the Verify window will display the following error:
The error simply states that you cannot have more than one clustered index in the entity.
Solution: Go to the Data Model Entity Indexes screen and delete one of the clustered indexes using the Delete Index icon.
Click OK. The model can now be verified successfully.
This concludes our discussion on verifying a data model.S
Forward Engineering is the process of generating a database schema from a physical model. It enables the synchronization of the data model with the database when physical changes have been made to the data model, such as adding, removing, or changing entities, fields, indexes, physical names, and relationships. It also employs all the changes that are made in the data model while restructuring it. While it executes physical changes, logical changes are not reflected in forward engineering.
To forward engineer a data model, click on the forward engineering icon in the toolbar of the data model designer. By default, this option generates a DDL script when selected.
When you click the arrow next to the Forward Engineer icon, a drop-down menu appears, that shows the four options to forward engineer the data model.
These options are:
Apply DDL Script
Apply Diff Script
Generate DDL Script
Generate Diff Script
Let’s consider the following data model to explore each of these forward engineering options one by one.
This data model represents a simple star schema, with one Fact and four Dimensions.
The Apply DDL Script option generates an SQL Script for the entire data model schema and automatically executes it on the specified database.
Specify the database connection where you want to execute the script. To do that, click on the Change Database Connection Info icon in the toolbar. Alternatively, press Shift+Alt+C to open the Database Connection window.
Specify the database connection information and click OK.
Select the Apply DDL Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F5.
The Apply Scripts window shows all the operations that will be applied to the database once the script is executed. It gives the summary of the following:
Operation
Object Name
Type
View all the changes and click Execute.
The script will be executed, and the operations will be performed on the specified database. The results of the transaction can be seen in the Output window.
Note: If the Output window does not appear, go to View > Output in the main menu. Alternatively, you can press Ctrl+Alt+O to open the Output window.
A Diff Script is a SQL script that reflects any differences between the data model and the corresponding database. The Apply Diff Script option generates a Diff Script for all the differences between the data model and the corresponding database and automatically executes it on the specified database.
Specify the database connection where you want to execute the script.
Select the Apply Diff Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F1.
If you select Apply Diff Script and there are no differences between the data model and the corresponding database, a message that ‘No changes were detected’ will be shown.
Let’s add a new Dimension to the data model and link it to the Fact with a non-identifying relationship. The data model now looks like this.
Select the Apply Diff Script option after making the changes specified above.
The Apply Scripts window shows all the changes that were made to the data model. In this case, it shows that a new dimension has been added. View all the changes and click Execute.
The script will be executed, and the changes will be made to the specified database. The results of the transaction can be seen in the Output window.
The Generate DDL Script option generates an SQL Script for the entire data model schema. It does not execute the script automatically. However, the generated script can be executed in the database.
To generate a DDL script for the data model, select Generate DDL Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F2.
You can also go to Data Model > Generate DDL Script from the main menu.
In the Save window that shows up, navigate to the desired directory, provide a name for the DDL script, and select Save.
The DDL script has been generated and saved. You can execute this script in the database server if required.
The Generate Diff Script option generates a Diff Script for all the differences between the the data model and the corresponding database. It does not execute the script automatically. However, the generated script can be executed in the database.
To generate a Diff Script for the data model, select Generate Diff Script option from the Forward Engineer option dropdown menu. Alternatively, press Shift+Alt+F3.
In the Save window that shows up, navigate to the desired directory, provide a name for the Diff script, and select Save.
The Diff script has been generated and saved. You can execute this script in the database if required.
Note: If you select Generate Diff Script and there are no differences between the data model and the corresponding database, a message stating that ‘No changes were detected’ will be shown.
Each of the four forward engineering options can also be applied to a single entity or a set of entities selected. Select the entity or the set of entities that you want to forward engineer and select one of the four forward engineering options. The script will be generated and/or applied only for the selected entities.
This concludes our discussion on forward engineering a data model in Astera Data Stack.
When you open a new data model in Astera Data Stack, you’re provided with an empty canvas referred to as the data modeler. Here, you can either reverse engineer an existing database or create a model from scratch. To learn about reverse engineering, click .
In this article, we’ll show you how to create a data model from scratch.
In the data model section of the toolbox, you’ll notice an object named Entity.
You can drag and drop this object onto the data modeler to add new entities to it.
Here, we’ve added two new entities to the empty model.
To access the properties of an entity, right-click on it and select Properties from the context menu.
A configuration window will appear. On the Entity Properties screen, you can:
Update the name of the table via the Table Name textbox.
Fill in the Schema textbox to specify the schema of the entity.
Change the entity type via the Entity Type dropdown menu.
Add an optional description for the table via the Definition textbox.
Here is a look at the Entity Properties screen of one of the entities in our model, after the required modifications have been made.
As you can see, we’ve changed the table name to Orders and specified the schema of the table, Sales. Once you’ve made the required changes, click Next.
On the Layout Builder screen, you can create elements for the entity by specifying their name, data type, and other characteristics, by filling out the empty rows.
Here is what the layout looks like after we’ve added one field to the newly added Orders entity.
Similarly, you can create all of the fields that are relevant to a particular table. Once that is done, click Next.
On the Entity Indexes screen, you can add new indexes to the entity through the Add Index icon.
Once you’ve added new indexes, if any, click OK to close the configuration window. You have now configured a new entity from scratch.
In our model, we’ve specified the general properties and layout of both of the newly added entities. This is what the model looks like so far:
Once you’ve added the entities that you want to include in the model, and specified their schema and layout, you can create relationships between these entities. Using the icons in the toolbar, you can create:
Non-Identifying Relationships
Identifying Relationships
Self-Reference Relationships.
In this case, we’ve created a non-identifying relationship between the two entities and self-reference relationships within each entity.
The final step in creating a data model from scratch is to forward engineer the newly created entities and relationships to a database.
Use the Create DDL Script icon to build a script for the model.
Using the generated DDL script, populate an empty database in your selected database server with the contents of the model.
Once that is done, establish a connection to that database via the Change Database Connection Info icon in the DWB toolbar.
In this case, we’ve made the database on an SQL server.
You can test the connection using the Test Connection button. Once it is successful, click OK. The model is now ready to be verified and deployed.
This concludes our discussion on creating a data model from scratch in Astera Data Stack.
In this article, we’ll talk about how you can access the built-in data modeler in Astera Data Stack.
You can open a data model in Astera Data Stack through the main menu, the main toolbar, and also through Project Explorer.
Let’s explore each of these options one by one.
You can also open a data model directly using a Windows shortcut.
On the main menu bar, go to File > New > Data Model.
There is an option to open a data model directly from the main toolbar.
When you create a Data Warehousing project, it will appear in the Project Explorer.
To open a new data model from the Project Explorer, right click on Data Models and select Add New Data Model.
Another way to open a data model through the Project Explorer is by right-clicking on Data Models and selecting Add New Item. There, you would find an option by the name of DataModel.
Select DataModel, name your data model and click Add.
A new data model will be added to your project folder and will open on the designer.
This concludes our discussion on the various ways of opening a data model.
Read and Write Deployment refers to publishing the data model to the server for usage in ETL processes or for data visualization through a built-in OData service. For more information on deployment in Astera Data Stack, click .
For a detailed overview of entity properties, click .
To learn more about how you can create entity relationships in Astera Data Stack, click .
