Database-driven library system

www.cskl.de

• Characteristics of database-driven library system

Key-Benefits of CADSTAR 12.1

KEY-BENEFITS

– Increased speed when

searching for parts

– You can edit/save a single

part (instead of having to

edit/save the whole Parts

Library)

– No Parts index required

– Easier integration with

other PLM systems

– And much more…

• Database requirements

– The database, used to store the

CADSTAR Parts Library data, can be

any ODBC compliant database which

provides the following capabilities:

– SQL: 1999 – Transactions

– A suitable OLE ODBC drive being available

– Examples of suitable Database

Management Systems (DBMS) are:

– Microsoft Access 2007 (MDB file) – Microsoft SQL Server 2005

www.cskl.de

•

Integration with PLM / Corporate Databases

– Zuken does not provide any functionality to allow the integration of the

CADSTAR Parts Library database with other PLM systems or corporate

databases as part of the standard CADSTAR product

– Any such integration may use the standard features available with the

chosen DBMS to populate the CADSTAR database (in correct structure as

defined by schema) from any other source required. It is the responsibility

of the user to organise and implement this

– However, such integrations as this may be the subject of a specific

consultancy project, at a cost to be determined in each individual case

– A database schema is provided as an SQL file with the CADSTAR 12.1

install for users to be able to create a suitable empty database and

providing the information you need in order to be able to create or edit this

data from an external source (i.e. another database)

Key-Features Targeted for CADSTAR 12.1

13.10.2011 3

www.cskl.de

Only the set up of all components ensures

superior results

13.10.2011

+

+

+

=

Full HD TV HDMI cable Blue ray player Blue ray disc Brillant full HD picture

CADSTAR ODBC interface MRP-Link Database High Performance PCB - evelopment

+

+

+

=

www.cskl.de _{Page 5}

ODBC – An overview

ODBC

(

O

pen

D

atabase

C

onnectivity)…

… is an interface for the

database access

… is used primarily

under Windows

… is the most important manufacturer

independent standard

…

serves the communication

of database systems

… makes

DBMS

independent data banking connections possible

… uses

SQL query language

www.cskl.de

Why to use ODBC for CADSTAR?

The currently used file based system will still be available for CADSTAR.

• If the library contents has to be updated very often, a database management system (DBMS) should be used to maintain the data.

• In favour of which database scheme the editor finally decides, all concepts have

different simple information storage systems, where each has its own advantages. So the application developer doesn't have to take care of the physical storage of the data any longer.

• Over recent years there has been significant progress in database technology, which opened up opportunities for increased quality and efficiency etc.

• Recognising the shift in the market to this approach Zuken is in the process of

creating an ODBC interface for CADSTAR that will enable access to CADSTAR library Part data that can be stored in databases like MS - SQLServer, Oracle etc.

• By allowing all this data to be stored in a database rather than on a disk, the whole data flow process becomes much quicker and is more versatile.

www.cskl.de _{Page 7}

Is ODBC an advantage for CADSTAR ?

CADSTAR is a combination of using simple binary files and a series of flat text files.

Advantage:

CADSTAR is total ownership of the structure and the contains of the data and even the whole maintaining is controlled itself.

Disadvantage:

CADSTAR needs a lot of developer resources, because basic function like: Storing, Access and consistent routines. The amount of necessary data is continually growing up. Today, the user needs normally only less than 3000 parts.

Attention:

Behind 10.000 parts the overall performance breaks more and more down, which occurs using PLM-, MRP- or SAP-Systems.

www.cskl.de

The ODBC architecture has four

components

Application

Driver Manger

Driver Driver Driver

Data Source Data Source Data Source

Excel Access SQL-Server

Application - Spreadsheet, Word processor, Data Access &

CADSTAR development language etc. Performs processing by passing SQL Statements and receiving results from the ODBC Driver Manager.

Driver Manager - a Dynamic Link Library, loads drivers on

behalf of an application.

Driver:

A Dynamic Link Library, that processes ODBC function calls received from the Driver Manager. submitting the resultant SQL requests to a specific data source and returns results to the application. If necessary, the driver modifies an

application's request so that the request conforms to syntax supported by the associated DBMS.

Data source:

Consists of a DBMS, the operating system the DBMS runs on and the network (if any) used to access the DBMS.

The Driver Manager and Driver appear to an application as one unit to process ODBC function calls.

www.cskl.de _{Page 9}

File- or DBMS-Based Drivers

File-based drivers are used with data sources such as ACCESS, which do not provide a stand-alone database engine for the driver to use. These drivers access the physical data directly and must implement a database engine to process SQL statements.

As a standard practice, the database engines in file-based drivers implement the subset of ODBC SQL defined by the minimum SQL conformance level.

Driver writers have to be aware that the driver architecture could affect whether an application can use the DBMS-specific SQL.

DBMS-based drivers are used with data sources such as Oracle or SQL Server to provide a stand-alone database engine for the driver to use. These drivers access the physical data through the stand-alone engine; that is, they submit SQL statements to and retrieve results from the engine.

DBMS-based drivers act as the client in a client/server configuration, where the data source acts as the server. In most cases, the client (driver) and server

(data source) reside on different machines. Although both could reside on the same machine running a multitasking operating system.

A third possibility is a gateway, which is placed between the driver and data source. A gateway is a piece of software that causes one DBMS to look like another. For example, applications written to use SQL Server can also access DB2 data through the MRP-Link

Gateway; this product causes DB2 to look like SQL Server.

www.cskl.de

Single-Tier Configuration

In a single-tier implementation the database is processed directly by the driver. The driver processes SQL statements and retrieves information from the database.

In a single tier driver network environment data access software resides on the PC.

This implies query resolution intelligence resides on the client.

- Application

- Driver Manager

- Driver includes data

access software

- Data Storage

- Application

- Driver Manager - Driver includes

data access software

- Data Storage

The following diagram shows two types of single-tier configurations like CADSTAR:

one stand alone one that uses a network

www.cskl.de _{Page 11}

Multiple-Tier Configuration

In a multiple-tier configuration, the driver sends SQL requests to a server that processes SQL requests. The application, driver, and Driver Manager reside on one system, typically called the client like CADSTAR. The database and the software that controls access to the database normally reside on another system and is typically called the server.

A variant of the multiple-tier configuration is a gateway architecture, where the driver passes SQL requests to a gateway process.

The gateway process sends the requests to the data source. The gateway in this scenario can be a piece of hardware or data access software in the form of a low level interface to foreign

databases provided by the RDBMS vendor. From the perspective of an application the following configurations are identical.

- Application - Driver Manager - Driver - Data access software - Data Storage - Application - Driver Manager - Driver - Data access software - Data Storage - Gateway Software

This implies that query resolution

intelligence resides on the server.

www.cskl.de

Multiple-Tier with Middleware

The application, driver and Driver Manager reside on one system, typically called the CADTSTAR client

Between client and server we could have

more powerful query builder like MRP-Link

This part optimizes, controls and manages the client request to one or several database systems.

It collaborates the results in a single answer to the clients.

The database and the software, which controls access to the database reside on another system, is typically called the database-server.

This implies query resolution intelligence resides on the middleware server.

www.cskl.de ₁₃

One Server and several databases

A

relational

DBMS can manage more databases

parallel.

It is working parallel to our application CADSTAR and

ensures the table structures for the relational database.

The normalization process, which takes care that no

redundant data storage is carried out and that

inconsistencies in the data stock are avoided, supports

the relational database design.

At the outline of a table you decide, which data may

contain the individual columns whether additional

plausibility checks shall take place at the storage and

which column as a key field obviously identifies a data

set.

www.cskl.de

Inside CADSTAR ODBC schema

This diagram shows the simple database schema for CADSTAR .

A lot of tables arrange around the “Parts” Table as central element. The Part definition’s table is of particular interest for the integration with all other PLM/MRP or ERP systems.

www.cskl.de

Typical Enterprise Company Installation

13.10.2011 15 Location Database  2.000-5.000 parts

www.cskl.de

Setup enterprise company installation

The required setup in CADSTAR to get access to different Library sources. It is only necessary to know the location of each library.

Depending on the database type we distinguish a single source pointer. Parts, SCM, PCB and Documentations Symbol are collected in a single directory.

Each ODBC Database has two references, on to the Parts and a second on to the binary file which includes the SCM, PCB and Documentations Symbol.

www.cskl.de _{17 13.10.2011}

Set up the references to the Library

location of the SQL-Server

Instead browsing

through a directory, the browse is through the SQL Sever List.

www.cskl.de

Set up the reference to the library location

at Microsoft Access

For a Access based library which is a file based library.

Locate with the browser the MDB file.

The Microsoft Jet 4.0 OLE Provider is to chose.

www.cskl.de

• Transfer CADSTAR Parts Library contents to a new database library

CADSTAR: Parts import function

www.cskl.de

CADSTAR: Find components easily

Find components easily & quickly. Without any

additional effort or cost.

In combination with an ODBC-compliant data source CADSTAR 12.1 provides a strong and fast customizable search engine allowing the user to quickly find a specific

component.

www.cskl.de

Comparison of local search times

13.10.2011 21

Binary files

2.576 parts

Reference dataset provided within Zuken CADSTAR Express 12.1

6,2 8,3

14,5

0,4

Text-file Access SQL MRP-Link

Search time [sec]

Binary files

14.766 parts

Reference dataset provided within Zuken CADSTAR 12.1

Binary files

4 parts

Reference dataset provided by CSK

Within local environments, the usage of MRP-Link could already save most of the search time…

Hardware: DELL M4300, 4GB RAM, Windows 7 64 Bit & CADSTAR 12.1 P1

Location Database

Enterprise Database Project Database

20,5

13,4 14,8

0,4

Text-file Access SQL MRP-Link

Search time [sec]

0,3 0,4 0,6 0,2

Text-file Access SQL MRP-Link

www.cskl.de

Comparison of global search times

Location Database 2.576 parts Enterprise Database 14.766 parts 30,4 17,7 27,8 _0,9

Text-file Access SQL MRP-Link

Search time [sec]

Project Database 4 parts

-98,5%

…when looking for parts globally in all implemented databases, MRP-Link could save up to 99% of search time

Hardware: DELL M4300, 4GB RAM, Windows 7 64 Bit, & CADSTAR 12.1 P1 all database running on the same machine

www.cskl.de

MRP-Link will communicate via the

SQL-native language directly

13.10.2011 23

Each middleware software has now the chance to use and introduce the best fit techniques depending on the current situation.

This Multiple-Tier implementation with the Middleware MRP-Link demonstrate this technique perfect.

Native SQL language direct on an SQL-Sever combined with direct online OLE-

communication forward to CADSTAR results in a unique performance.

It is well-known that ODBC interfaces _{are limiting the performance of data} bases and thus of CADSTAR as well The open structure is the foundation for any integration with PLM / Corporate Databases.

www.cskl.de

Each software will need its individual

interface to work with CADSTAR

13.10.2011 24

For each external System a complex Software interface is necessary which follow the data schema from CADSTAR.

In the real world are the CAD relevant information distributed in different software programs and storages.

?

?

?

?

?

?

?

?

www.cskl.de

MRP-Link will ensure this connectivity

13.10.2011 25

www.cskl.de

MRP-Link could be used as central element

of the whole material planning process

MRP-Link, the component list and BOM management system, comprises several powerful CAD manufacturer-neutral modules. An SQL database is used as the central element that ensures maximum flexibility for optimised and rapid integration into ERP/PLM systems

BOM management helps to identify part uses and component changes and therefore makes pre-planning the execution of redesigns or design changes easier.

The component selection enables you to quickly find components using technical features. These can then be inserted directly into the design thanks to "drag and drop". As a result, an impressive selection of components is available via attributes such as releases, usability and alternative components.

www.cskl.de

MRP-Link information's flow

13.10.2011 27 MRP-Link Part Information BOM‘s Upgraded BOM’s, Parts Information Parts Upgraded BOM‘s, Parts Information Upgraded BOM‘s, Parts Information

Other CAD

System

EDA

System

Other Data

System

ERP / MRP

Access

Excel

Data

System

www.cskl.de

Key benefits of the MRP-Link

 In view of its modular expansion options, the MRP-Link is flexible

and versatile in use

 Processing rules can be custom-controlled with the Constraint Manager and the user's execution rights.

 Cross-site data replication safeguards data consistency  The Parts Stock, the central element,can be expanded on a

customised basis with further modules

 The CADSTAR Attribute synchronising minimizes errors when creating designs

 The usage of CSV-files ensures connectivity with almost all used business applications

 The automatic column name translation systematic enables unlimited communication

www.cskl.de

CADSTAR Attribute synchronising

13.10.2011 29

The technical features from a managing ERP or PLM system are transferred with the MRP-Link system directly into the CADSTAR libraries

This minimizes errors when creating designs. The availability of alternative and

preferred components safeguards the use of the best components from both a

technical and commercial point of view

www.cskl.de

MRP-Link column name translation

systematic

External Source

Naming

Internal Naming

External Destination

Naming

13.10.2011 31 Struckbrook 49 D-24161 Altenholz Tel. + 49 (0) - 431- 32917- 0 Fax. + 49 (0) - 431- 32917- 26 Email: [email protected] Web: www.cskl.de