1. Executive summary
This application was developed to demonstrate capability of synchronizing Tekla Structures Models data to a commercial database such as MS SQL server or Oracle and being accessible via internet. Having model date stored in a SQL database allows one:
- To share model information remotely through web application and browser type application;
- To have more developers (e.g. web developers) developing applications with model information;
This solution was not available on Tekla Extranet. If you are interested in obtaining the program or need advise on developing your own tool please feel free to contact me.
See demo videos on YouTube
2. Introduction
We had received requests from clients who want to have a solution where they can exchange Tekla Model information with a relational database. Although our XML export seems pretty sufficient to serve this demand but most clients still want to have a dynamic exchange capability, which is tailored to their in-house solution, rather plain XML files interchange. Clients from different segment all present similar interest for such a database solution including Steel, Precast Concrete and Contractor segments. Although ways of using a database are slightly varied from client to client, it still motivates to have a general solution for all instances and possibly specialization for each later.
This project and prototype is meant to be an interface or platform for more Tekla users to create their own extended application by using Tekla Structures. It is not an end-product because clients will use it in different ways. A database more likely will serve as an intermediate tier for data exchange, especially for web-based applications. In our TSRELDB (Tekla Structures External Relational Database Synchronization Toolkit) prototype, we actually demonstrate how one can write Tekla model information to various external databases such as Microsoft SQL Server, Access or a plain XML file. Relational database is engine and backbone that drives the entire eCommerce, financing and ERP/MRP/MIS bushiness.
2.1 DB Evolution and Trend in a Nutshell
It is good to have some basic knowledge about database prior to start. The following paragraph provides a brief background in the database evolution and history. There are two common misconceptions that need to be clarified:
The first misconception of database is directly thinking a commercialized relational database such as MS SQL Server or Oracle as the de facto term of 'Database'. In fact, database is a generic term for simple data storage. Commercial database e.g. MS SQL Server and Oracle are known as Relational Database (RELDB) where there exists another type of databases such as Object-Oriented Database (OODB) or a hybrid database system such as IBM DB2. Tekla Structures also contains a native database which is half relational and half object-oriented.
Database is simply a storage component where you need a management system on-top to manage information and transactions. This is known as Database Management System (e.g. RELDBMS or OODBMS). It is fair to say that part of Tekla Structures role is to perform a role as a DBMS of Tekla internal database (i.e. DB1).
Underlying database in Tekla Structures is, one of many instances, of a proprietary database system. It is hard to classify whether it is purely a RELDB or OODB since it accommodates features from both sides. This is similar to IBM DB2 database, a hybrid database system. RELDB and OODBMS were competing intensively in the 80's. Most OODBs truned into a nitch market after big companies such as Microsoft and Oracle have successfully conquered the database market in late 90's. This led to a fact that 1) SQL becomes the de-facto interface to access a relation database and 2) most business, corporations, and enterprise databases are developed in RELDB. In fact, RELDB is very good to handle business and financial data where OODB is good in managing engineering and mechanical data for example geometry.
It is clear that RELDB will still be the mainstream database solutions in the next decade. There is a trend where most RELDB are blending OODB features back into the RELDB (e.g. conforming to SQL 3 standard) because the success of OO programming language and hierarchical information such as XML.
In short term, it seems unavoidable to have solution to integrate Tekla Structures with a RELDB since it has been widely used in most web-based eCommerce systems and incorporated in many ERP systems today.
The second misconception is not to think database separately to its front-end applications. Most MS Access users have this dilemma because its database is totally tied with its front-end functionality. It is the front-end application which defines the character and behavior of a product but not the database.
 |
| Figure 1evolution of database in the last 3 decades |
2.2 Customer Demands & Motivation
We have received several requests from customers to exchange model information in a relation database fashion e.g. a portable database file such as MS SQL Serve Express or MS Access file. Basically, demands are classifiable into two categories:
- A solution and data exchange interface through a relational database that can easily exchange information with client's ERP/MRP/MIS or eCommerce systems. A B2B integration is highly demanded.
- A platform for many extended applications, mainly web-based applications that interact with a linked output database. This is more likely to be used in a B2C integration.
3. Scope and Plan
Scope of TSRELDB aims to support an intermediate platform to integrate Tekla Structures with commercial database. We have less interest to customize front-end applications for clients at this moment. TSRELDB is considered an interface where clients can easily employ and tailor their front-end applications. There are more web/database application developers who can develop 3rd party applications without having expertise in data structures of TS .NET API. In other words, application development will become more easy and available to developers who know simply SQL but not our .NET API. This hopefully can increase 3rd party developments for a variety front-end/extended applications with Tekla Structures, and also reducing learning curve on Tekla Structures .NET API.
Since database solution is closely tied to World Wide Web, it actually opens alternative ways to integrate Tekla with our customers' and partners' in-house systems, triggering migration from file-base to dynamic database information interchange. Implication includes rethinking and reinventing possible business models in a long term.
We will discuss several use cases and potential integration methods to show how clients can utilize their benefit on incorporating with TSRELDB. In summary, the following list simply defines things that are included or excluded from TSRELDB.
Things are included:
- A simple server side application that synchronize TS Model Data to a database medium, e.g. a database or XML file
- A late-binding like data schema that consists with four tables, 'Project', 'Transactions', 'Objects' & 'Properties'.
Things are excluded:
- Any programmability on the database side for example stored procedures, views, functions, etc. It assumes that customers will customize this programmability by themselves;
- No trigger, index or other optimization have been added to the database schema;
- Tekla users who want to work collaboratively can use our multi-users feature on a domain network. The TSRELDB has no intention to replace this existing and good mechanism. It aims to support another type of client-server collaboration;
3.1 Potential Usage & Used Cases
MS .NET framework obtains great features to 1easy application development in web-based database driven application. Strength of MS .NET framework has been shown in many directions; especially in 1) incorporate with relational database integration and deploying 2) web-based. RELDB apparently serves as the backbone of most eCommerce Enterprises and ERP/MRP/MIS solutions.
Possible usages are described under following categories:
1) Different Client-Server configurations
2) Integration with client's EPR/MRP/MIS and eCommerce Solutions
3) Interface to various handhold devices e.g. mobile phone, tablet PC, palm pilot, etc.
4) Other applications
3.1.1 Client-Server Configuration
There are several ways to configure usage of a database. Section 3.1.1.1, 3.1.1.2 and 3.1.1.3 shows different configuration of using the TSRELDB. Implication of configuration is really to see how TS is involved in various configurations.
3.1.1.1 Tekla Structures on both Server and Clients
 |
Main concept in this scenario is to maintain a centric 'master' model where distributed clients read and write data to the master model. This is redundant to the existing multi-users feature but probably consuming less resource in terms of the network bandwidth. It is possible to integrate TERELDB into Tekla Connect under this scenario. TSRELDB is not meant to serve process of collaborative design simultaneously.
3.1.1.2 Tekla Structures on Server only
The second scenario is to maintain similar master model in server where thin clients reside on client ends, for instance a web-browser client. This scenario is more adequate to management and not design tasks. In other words, it is good for visualization, e.g. RFI editing and other construction management tasks. Our prototype demonstration is implemented under this particular scenario.
 |
Figure 3 TS on Server side and not client side |
3.1.1.3 Tekla Structures on Clients only
 |
The third scenario is to have Tekla on multiple clients and none on the server side. It does not maintain a master Tekla model but a proxy that resides on the database server. Each client will need rapid synchronizations to update their attributes to the proxy model. The proxy model then synchronizes with all clients. This requires better merging capability and concurrency control on the database side.
3.1.2 ERP, MRP, MIS & eCommerce Integration
The most prominent use case of TSRELDB is to connect client's in-house database, in other words, establishing a link between two database systems. Figure 5 Illustrates this basic integration where B2B integration bridges two database systems. Front-end client is part of the ERP system that handles resources, supply chain, delivery and inventory managements. TSRELDB is an adequate solution for Tekla clients who are part of an ERP supply chain.
To derive meaningful and mandatory information from one system to another relies on a mapping process or known as views. There are professional mapping tools, for example the Microsoft BizTalk is a mapping middleware that allows interchange between databases. Altova, a a leading XML toolkit provider, has similar product for XML mapping named MapForce. Clients who want to adopt TSRELDB may need to derive their mapping scheme.
 |
3.2 Interface to diverse devices
TSRELDB provides an additional tier to Tekla Model by deploying a commercial relational database. It is very common and easy to write applications that operate on handhold devices and read data from a database. In spite of a web-browser client application, it is possible to write clients on diverse handhold devices such as Windows CS for cellular phones or tablet PC.
 |
3.2.1 Other Applications
Model Information Archive and Rollback
TSRELDB records each transaction time where it is possible to perform a rollback function. For example, write all properties and UDAs back to particular date and transaction.Alternative Change Management Storage
It is fairly easy to perform comparison on a large set of data in relational database. TSRELDB provides alternatives to one to tailored properties and attributes that will be caught in c round trip data exchange. In other words, one can define what is classified as a "change" in a more dynamic and expandable fashion without hard coded and persistent classifiers in current.3.2.2 Summary
Scenarios and usage listed above are basic examples of how to utilize TSRELDB. We believe more clients will be interested to link Tekla from to their in-house database solution through database integration. The required expertise of database and web programming is extremely lower then knowing Tekla .NET API. You may look at this database integration interface as a wrapper interface to Tekla .NET API.
3.2 Acknowledgement
Special thanks to Stacy Scopano and Andy Dickey from Tekla Inc.
4. Video
5. Glossary & Abbreviation
TS - Tekla Structures
RELDB - Relational Database
TSRELDB - Tekla Structures Relational Database Synchronization Toolkit
OO - Object-oriented
OODB - Object-oriented Database
RELDBMS - Relational Database Management System
OODBMS - Object-oriented Database management System
MS SQL Server - Microsoft SQL Server
SQL - Structured Query Language
SQL3 - SQL standard Version 3
ERP - Enterprise Resource Planning
MRP - Manufacturing Resource Planning
MIS - Manufacturing Information System
B2B - Business to Business
B2C - Business to Customer
XML - Extensible Makeup Language
LINQ - Language Integrated Query
