Guidelines when integrating data

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > Best practices and implementation guidelines > Best practices in Data Model >

Guidelines when integrating data

Overview

The following section lists guidelines to consider when using (or planning to use) either of Bizagi features known as Data Virtualization or Data Replication.

Refer to the basic guidelines which apply as best practices to any project, and to advanced guidelines if customizing the Data Virtualization or Data Replication (when including bespoke code).

 

 

Basic guidelines

These basic guidelines will help you plan accordingly.

 

1. Proper analysis and design in early stages

It is strongly recommended to keep in mind any constraints, integration possibilities and proper design for modeling data (in analysis and design phases of your project implementation).

As Good practice, during your Data Modeling, you would need to consider which Entities and attributes would be integrated with an existing data source, and this includes:

Defining the relationships between Virtual and Replicated entities, as well as other entities in your model.

Using the 3rd normal form in your model (a.k.a. Normalization) to avoid duplication of information while keeping your relationships clear across your process.

 

2. Always map your attributes and Bizagi relationships (consider references to compound keys)

It is strictly required that you map Bizagi attributes to external columns of your external source bearing in mind:

Which ones you will be using exactly.

What type of information underlies in there (e.g, is it Unicode, its length, primary keys and constraints, etc).

Using filters if applicable.

Considering the relationship attributes which are created in Bizagi.

Note that these attributes belonging to Bizagi will also need to be mapped.

For more information and guide about this configuration, refer to the How to configure Virtualization example, in which you may also see how to do this when a reference to a compound key is included.

 

3. Explicit ports (applies when integrating to an SQL Server data source)

When using SQL Server as an external data source, it is strongly recommended that the database's instance configuration has an explicit TCP port:

 

 

SQLConfiguration06_TCPProperties

 

 

Using an explicit TCP port follows best practices (instead of the TCP Dynamic Ports alternative).

 

 

4. Virtualization working in Production

Take into account that once a virtual entity is operational in a Production environment, you may not convert it into a non-virtual entity.

 

Once an external data source (registered as a system in Bizagi) has been deployed to a Production Environment, it will be not possible to edit its settings so that it is not used by virtual entities.

This means ensuring which tables should and which ones should not be virtual, and this must be defined previously to deploying the Processes and using them into production.

In case you do need to stop using Data Virtualization for your project, you will need to create a new process version and ensure that your process data model considers an alternate entity (which is not set as virtual).

In a similar way, you may not convert a non-virtual entity into a virtual entity once it is set up in a Production environment, and so you will need a new entity.

 

Proper cautions need to be considered for existing values and business keys.

 

 

5. Entities names

If you are integrating data sources having really long names (in its tables or columns), acknowledge that in Bizagi you will end up having a data structure mapped to your source, supporting the long name at the source, but having in Bizagi a name truncated.

This is so because Oracle puts a 26-character limit to names of data structures, and in addition to this, with Data Virtualization or Data Replication Bizagi will add up a suffix.

Recall too that this applies for object's names (mainly for internal use) and not for display names (which should be the ones end users refer to).

 

note_pin

If your data source (its table name or columns' names) has blank spaces, then you will need to ensure you configure and map the source by escaping the name with the appropriate characters to handle blank spaces in that database engine (e.g using [] in SQL Server).

 

6. Use optimization parameters when needed

Whenever you query information from an external source through Data Virtualization, Bizagi will use an optimization parameter that creates separate, and on-demand queries for information residing in text type columns having more than 100 characters. (i.e, varchar(101), varchar2(200), etc).

 

Therefore, you may modify this parameter so that you can improve performance on certain scenarios.

To use it, add the following key to your web.config file:

<add key="AttribMaxLengthThreshold" value="[column_length]"/>

 

Notice that you will edit the [column_length] value with the length of the string you want Bizagi to consider at once in the main query.

In this way, your virtual attribute of more than 100 characters does not require an additional query.

 

 

7. Appropriate primary key data types

Recall that in order to use Data Virtualization or Data Replication, it is required that you external source has a primary key definition.

When defining the primary keys in your external tables, recall that it is not recommended to rely on string data types for this purpose (e.g varchar, text, etc).

Primary keys indexing and performance is affected when using such data types and should be avoided.

 

 

8. Using Views when applicable

Bizagi Data Virtualization and Data Replication supports views.

You may use views when applicable, as long as these views can guarantee uniqueness in their records (i.e, there is a column, or set of columns, which ensure this and which can be taken as a business key).

 

note_pin

Usually, views are meant for read-only purposes.

 

However, keep in mind that when using a view in which you want to update information from Bizagi (and when this view is built from more than 1 source table), the database engine by itself may have some restrictions.

For instance, in SQL Server, such type of views will only allow you to update information in the base table (for more information, refer to https://msdn.microsoft.com/en-us/library/ms187956.aspx).

 

9. Virtual Entities having virtual and non-virtual attributes when needed

It is important to acknowledge that if you have both virtual and non-virtual (local) attributes in a Virtual entity, you need to pay special attention on how you design your forms and business rules in general.

Take into account that a mixed model should be only used when needed, because having both types of attributes would imply that you should control from its very design, so that you don't run into performance issues.

 

Performance issues may arise given that filters of information should consider if the attributes to filter by, are virtual or are non-virtual.It is not recommended to allow or build filters which combine both types of attributes in these situations.

 

In case you consider that the Process' data model requirements would need additional attributes for a virtual Entity and if you wish to avoid potential performance issues, then these can be created in a separate Entity (which has an Entity reference attribute to the virtual Entity).

In the following data model example of a Bizagi Process, the additional attributes "Observations" and "Total Accounts" for a customer are created in another entity ("Customer_AdditionalInfo") which references the virtual Customer entity:

 

 

HT_VirtualizationRelatedEntities26_Allattributes

 

 

note_pin

The importance of this guideline is focused on how Data Virtualization performs searches and synchronization for records in virtual Entities (which is done directly at the data source).

Hence in a mixed model, queries and filters ran at the source could not be optimal.

 

10. Include within the Data Virtualization or Replication, those entities which are referenced by virtual entities

It is important that you design your data model bearing in mind that you should always include within the Data Virtualization or Replication, any entities which are referenced by attributes in entities which are virtual or replicated (strongly recommended).

This applies as well recursively, for those entities added if in turn they reference other entities.

 

 

11. Replicate first if virtual Entities relate to replicated Entities

When having a mixed model where you use both Data Virtualization and Data Replication, and you have a virtual Entity which has a reference attribute to a replicated Entity, make sure that replicated records (those at the Parameter Entity) have been previously synchronized.

For this, you may set periodical executions of Replication accordingly.

 

 

Advanced guidelines

These advanced guidelines apply when using Custom Virtualization, since you will be overriding the default methods in Bizagi by including your own code.

 

 

1. Data access and avoiding locks

It is possible to access different data sources through Custom Virtualization by using any connection and transport mechanism (ODBC, OLEDB, Client Access, HTTP, Sockets, etc.). What it is important, is to follow good connection practices to your source (and other special considerations) in order to manage adequately the connections and to avoid locks.

 

2. Performance

When developing a custom implementation for a different data source than Oracle or SQL Server, take into account performance variables.

Access to Bizagi's database from the Virtualization class is not allowed, and the least number of accessing connections to the source database is the best strategy to reach maximum performance.

 

3. CRUD implementation considering Data Types

When using Custom Virtualization, the external data source must expose mechanisms for: select, insert, update and delete entity instances (by using functions, stored procedures, Web Services etc.).

 

The data types in the external data source should be as similar as possible (ideally equal) to the Bizagi data types.

If the data types cannot be mapped naturally to the Bizagi data types then the Virtualization interface must implement these mappings.

 

This means that if necessary, data mapping and data type validations should be performed as well manually, and at the Virtualization interface.

This is done in order to evaluate possible transformations from Bizagi to the external data source.