Fourth Shift SQL Dataview Types

Microsoft SQL Server uses T-SQL query language to send instructions to SQL:

Using SELECT T-SQL to extract Item information
The UPDATE statement can be used in Fourth Shift with extreme care and knowledge. It is easy to corrupt Fourth Shift if key fields are updated
Can't use DELETE and INSERT T-SQL statements with Fourth Shift because of internal indexing requirements 

DVUD Equivalents

In the early days of Fourth Shift SQL customers where encouraged to use SQL by producing data view definition(DVUD) equivalents has SQL data views. This was primarily due to the data export module(EXPM) being the primary way to extract information from Fourth Shift:
Item_ReferenceData has an equivalent DVUD item-reference data
70% DVUD have data view Equivalent
Not optimized for SQL Usage

Standard ODBC Tables

Fourth Shift had a ODBC solution with the previous database manager MDBS titanium database. The standard ODBC views where used for report and SQL data view equivalents were developed.A utility called Viewgen was developed to convert ODBC views to SQL data views.
Fstable.sql translated SQL Data views
Utility called Viewgen used for translation process
Not optimized for SQL Usage 

Business Foundation Views

A library of SQL data views exposing how data is arranged in the underlying tables. These data views are optimized and extremely fast. The Business Foundation SQL data views are the current recommended way of extracting information from Fourth Shift
Each table in FS Database schema has an equivalent SQL data view
Field names are in English
Exposes internal FS table indexing for usage by data views
Optimized for SQL Usage and extremely fast

About Business Foundation Views

There are 2 versions of business foundation views:

Business Foundation views Phase 1: Fourth Shift V7.2 excludes historical and multi-level bill views
Business Foundation views Phase 2: Fourth Shift V7.3 includes historical and multi-level bill views.Fourth Shift historical information is stored in the Mfg_TransHis table with all field information stored in a SQL field separated by tildas(~). A set of SQL functions where developed to extract historical information for these new historical extracts

What are the business foundation views

Prefix FS_ for SQL Data view names
Historical SQL data views are prefixed with FS_History
Fields have English names
Index fields have Key in the field name (use these fields for linking to other data views or tables)

Using Business Foundation Views

It is recommended to develop your own library of business SQL data views using the business foundation views. Use a sensible naming notation for example: GJDV_Orderbook_CustomerOrders. The index fields have key in the field name and are used to link Business Foundation data views in your custom SQL data views.
 
Creating a business SQL data view for order book.Firstly review the Business foundation views that will be useful in your order book SQL data view: Open Microsoft Enterprise manager. Move to Fourth Shift database FSDB and open data views. Scroll down until you reach data views with a prefix of FS_, these are the Business Foundation data views. Right click on SQL data views and select open all rows to review information. After a review the following Business Foundation data views will be used:
 
FS_COHeader: Customer Order header
FS_COLine: Customer Order line details
FS_Customer: Customer data
FS_Item: Item data
 
Right click on views category in Fourth Shift database and select new view to open the SQL query builder. Right click on diagram pane (top) and select show tables. It is possible to add SQL tables or data views to a SQL data view. Select the views tab and select the business foundation views already identified. You can select multiple selections by pressing control and click(one at time) or shift and click(select all in range).

Scroll to the end of list of fields for each table to review key index fields.It should be obvious which key field to use to join the data views:

FS_COHeader use COHeaderKey field to link to equivalent field in FS_COLine
FS_Customer use CustomerKey field to link to equivalent field in FS_COHeader
FS_Item use ItemKey field to link to equivalent field in FS_COLine
 
To link tables in query builder simply select key field and keep finger on left click select equivalent key field in table to join. A line should appear between tables representing a table join. There are different types of join by right clicking you can select all rows in source table or all rows in destination table. The default option is display rows where there is a match in each table.

Now select the fields you require which should be relatively easy now all fields names are very descriptive

On Close of SQL query builder you will be asked to save the data view, use a naming convention for your business SQL data view library. To open you SQL data view. Firstly right click Views in Fourth Shift database and refresh. Then scroll until you locate you SQL data view, right click and select all rows to review information
 
NOTE: It is presumed that the Fourth Shift client is installed on this PC

Fourth Shift Business Foundation Dataviews Advanced Topics 

Get help with your Fourth Shift business foundation dataviews requirements

Data retrieval Optimisation

Fourth Shift Reporting
​
Advanced SQL Business Data View Library