A data model is a representation of real-world entities and associations between them in graphical form. Astera Data Warehouse Builder allows you to design data models from scratch as well as modify the reverse engineered data models from existing databases. A new data model can be created by dragging and dropping entities from the toolbox, and creating relationships between them. Once the data model has been designed, it can be forward engineered to a database.
Create a New Project
It is recommended to use a data model as a part of a project. Click on the Project tab, choose New, and click on Data Warehousing Project. First, create a folder where you want to save the project and then specify the project’s name.
As soon as you click Save, you will see a project explorer with all the relevant folders. Right click on Data Models folder and choose Add New Data Model.
Reverse Engineer a Data Model
You can point to an already existing database by using the reverse engineering feature. The entities that you see on the layout in the image below have been reverse engineered after specifying the connection info on pressing the Reverse Engineer an Existing Database toolbar command. Once the data model has been reverse engineered, you can modify it according to your requirements. You may make changes in the design, for example add new entities, add or delete a field, change the Db Type, mark a field identity to autogenerate the values, etc. You can even modify the relationships and indexing.
Use Toolbox to Add Different Entity Types
You can add entities by dragging and dropping them on to the document window from the toolbox. There are six different types of entities that you can use in your data model. They are shown in the image below.
Rename the Entities
Rename an entity from the context menu after right clicking the entity.
Create a Layout
Initially when you add relevant entities, the layout is empty. You can create an entity layout by clicking on Properties.
Before creating a layout, you will have to give the table a desired name and specify the schema. Then click on the Next tab.
In the layout screen, you can manually enter all the fields, their data types, Db Types, and mark the fields as primary key, foreign key, identity, specify default values, etc. You can create as many fields as you want.
After creating the layout, you can also create and modify indexes on the key fields as well as non-key fields to optimize the search operation and query performance. You can also use the auto-generate indexes option and if there are any relevant fields on which there should be an index, Centerprise will add it automatically.
After you have created the layouts and added the indexes, you can now create relationships between entities. Select the option Link nodes to create reference relationships in data model from the toolbar, click on the parent entity, and stretch the node to the child entity. When the popup opens, you can enter the fields on which the relationship will be built. Centerprise will auto-suggest the primary/unique and foreign key fields, if there are matching field names in the two entities. The parent entity’s field should always be primary or unique. Furthermore, it must be ensured that the Db type of the Primary/Unique Keys and the Foreign Keys is same.
Once you are done designing the data model, you should always verify the data model.
Forward Engineer the Data Model to a Database
You will now have to specify the connection info so that you can migrate the changes to the appropriate database.
After performing this step, you can now make use of Forward engineering an existing database feature. This command will allow you to propagate all the changes that you have made to the model to the specified database connection. You can forward engineer all entities or just the selected ones.
After clicking on the Forward Engineer button, an Apply Scripts pop up will open. The pop-up grid will inform you of all the changes that will be propagated to the database.
Click on Execute and all the changes that have been done to the entities in the data model will be saved in the database.