DWH Modelling Tool — Visual Paradigm Using In Trendyol

Hello, I am Gizem from Trendyol and working as a DWH & BI Analyst. Today we will talk about the process of deciding on a modeling tool, Visual Paradigm setup and usage with useful tips.

In Trendyol Data Warehouse (DWH) team, one of our responsibilities is modeling and enriching data from different source systems.

For the following reasons, we decided to use modeling tool.

• Table Relation: Our colleagues working in other departments can write SQL using DWH tables. They often ask us “how can I join tables to get the data I want”. They are really right, sometimes we even forget our table relationships. We have wide DWH structure.
• Team Onboarding: When new team members come to our team, we want to easily show them our DWH model.
• Impact Analysis: We want to calculate estimated effort for impact analysis. Modeling tools help us to see the lineage.

How did we decide to use Visual Paradigm?

In our culture, if we decide to use a new tool we always get together with volunteer team members and use decision-making tools.

So we started to search for a modeling tool.

Decision-making: Scorecard

• Determining on the features that should be in the modeling tool.
• Assigning a coefficient to features according to their importance.
• Finding out alternative modeling tools.
• Performing POC (Proof of Content) according to predetermined features.
• Assigning a coefficient to each tool according to predefined features.
• Multiplying by the coefficient to get the result.

Here you can find the scorecard example below.

Assigning a coefficient to features (Figure -1):

Assigning a coefficient to each tool and multiplying by the coefficient (Figure -2):

Note for scorecard:

For example API/Synch support feature;

1. The coefficient of teammates was averaged. (Figure 1)
2. Multiplied by the coefficient of each tool. (Figure 2)
3. The same process is carried out for all features and a subtotal is taken.

Visual Paradigm has a really great score compared to other tools because it meets the features that are most important to us.

These features are;

• Plugin support
• Import/export our modeling diagrams
• GDPR management
• Version control and team collaboration
• Flexibility and user friendly

We finally got results, but this is only half the way :)

After we got the results, we explained and showed the scorecard, the Visual Paradigm, in fact the whole process to the whole team. The whole team started using the demo version.

Thanks to Visual Paradigm for supporting us with the demo license!

(Here you can find demo request link: https://www.visual-paradigm.com/demo/request.jsp )

After a while we sent a survey to the team. We got a positive response from most of the team.

Purchase and Installation Process

We contacted them and easily purchased from their Customer Service Center.

We purchased a floating license and chose to pay annually to receive upgrades and maintenance. Floating licenses include 2 standard, 10 modeler and 1 server license. Licenses’ keys are reusable and each team member can use the same key.

(You can find more detailed information about the licenses on the website: https://www.visual-paradigm.com/shop/vp.jsp?license=perpetual )

We installed client, server, licenses and upgraded versions from the Customer Service Center.

Our development team set up the server then we created the project and folder structure.

Usage Process

Model structure: We have two base model folders and ERD diagrams.

• One of ERD diagrams contains master view entities which means DWH table’s without relation.
• The other ERD diagram contains DWH models with relations drawn by auxiliary views.

Master view entity: We recommend using master view entities.

• If the master view changes, all auxiliary views change automatically.
• Also you can easily make impact analysis by clicking master views and seeing all models where the auxiliary views are used.

Plugin: Visual Paradigm doesn’t support reverse engine for Big Query but they support Open Api. Therefore we use a plugin developed in Java language after parsing our DWH tables to Json formatting.

Master entities can be imported automatically by using plugins.

Here you can see our Plug In below. (Figure 3)

We have two options for importing our tables.

1. One of them is to import just one table. We use Gsheet for our table’ s source to target mapping. We added Plug In to the Gsheet to parse our DWH tables to Json formatting. We can copy the json format and paste it into the import table screen.
2. The other one is to import the table as a batch. We are using the Visual Paradigm Request plugin in the Gsheet plugin we’ve already created. After adding the main entities to the queue, we create them in bulk.

Here you can see our Gsheet plug in below. (Figure 4)

Teamwork client: Each teammate can work on their own personal branch. At the end of their work, they can merge their branch with the main branch (trunk). Teammates can also see each other’s branches.

Version control: History can be viewed through the teamwork client or visual history. If a wrong version has been switched to, the old version can be reverted from these two areas. We use branches for version control. Our analysis process also includes drawing the ERD diagram of the model we created. We give the number of our analysis Jira task to the relevant branch. We also note branch numbers to Jira tasks. The reviewer reviews the entire analysis process, including the ERD. After all reviews are complete, we merge from branch to trunk. So we get the final version.

Forum site: When a problem is encountered, solutions to similar problems can be found or asked through the forum. ( https://forums.visual-paradigm.com/ )

Useful tips

• When establishing relations in ERD diagrams, the primary key — foreign key relation is automatically assigned. If automatic assignment is not preferred, generic connectors can be used.
• Column-to-column relationships between two tables can be locked. You can see the Figure below (Figure 5)

• You can proceed as follows to view entities in the appropriate format. (Figure 6)

What is next?

• Integrating plugin to Gsheet because we kept all json formatting tables in Gsheet.
• Sharing ERD’s with all company via PostMania. We are planning to add ERD’s PostMania link to Data Catalog for each table. PostMania has a chat feature via Server.
• Asking our team for feedback on Visual Paradigm usage.

If you want to be part of a team that tries new technologies and want to experience a new challenge every day, come to us.

Trendyol Tech