This is the first in a 3 part series titled “Database? Why?”
Database. Just seeing the word can make people want to call a consultant. Luckily, I know where you can find one. (You can’t blame a guy for trying, right?) While DriveWorks provides a tremendous amount of rule driving functionality, when it comes to crunching raw data, the undisputed ruler of that domain is the database. Well, I’m here to spill the secrets and give you the basic concepts that you need to leverage Microsoft SQL Server (aka SQL Server aka MS SQL) for your DriveWorks data.
For those that are scared of databases, realize that databases are just grown up spreadsheets. They have worksheets (called “tables”), they have columns (called “columns”) and they have rows (called “rows”). In fact, a lot of folks work with their Microsoft SQL Server databases using Excel (check out how). As a fan of professional cycling, I won’t say that a database is just a spreadsheet on…well… anything unnatural, but it definitely is more powerful for handling tables of data. Spreadsheets are great at doing calculations and pretty pictures, but databases are great at holding, handling, and aggregating large amounts of tabular data.
Not Just Any Database…
You can store data in lookup tables and in group tables within DriveWorks and use all of your table functions (GetTableValue, FilterTable, ListAll, and so on). But when it comes to large amounts of data or cross-referencing between tables, MS SQL does it faster. DriveWorks recognizes this, focuses on other valuable functionality and gives us QueryData, QueryDataValues, DBExecute and a Microsoft SQL Server output document. That’s pretty much all we need.
The benefits of using SQL Server over DriveWorks tables lie in three situations. First, if you want to share data with an outside system, then MS SQL is the ideal way to do that. Almost every system that you will use to manage data in your company today runs on or is designed to talk to a database. From MRP to ERP to SFA to MES to PDM, they all use databases. And having one database talk to another database requires a few more steps. Whether you want DriveWorks to consume data from another system, or you want DriveWorks to provide data to another system, having a standalone Microsoft SQL Server database that both systems can access is an ideal mechanism for that communication.
Databases Support Simultaneous Users and Complex Sets of Data
Unlike spreadsheets, databases are designed for multiple simultaneous users. When you have volatile data that is constantly being updated, like a quote history that each DriveWorks specification will log, or new part numbers being created, you can’t afford to have users get denied access or be forced to wait their turn or have their requests overlap. MS SQL takes care of that through a transactional approach.
Which brings us to our last scenario, managing large or complex sets of data. When you start a DriveWorks specification, the project file is copied to the specification folder. If that project contains a lot of tables with a lot of rows and a lot of columns then you’re going to wait for all of that data to be copied, every time. Storing that information in MS SQL not only boosts your startup performance, but it will save your disc space and prevent the information from being duplicated and becoming out-of-sync.
Strong Searching Capabilities
Microsoft SQL Server uses all manner of tricks to allow you to search within and across tables remarkably fast. Large datasets are no problem for MS SQL. And SQL Server can very quickly resolve complex table relations which require values to be looked up in table one to get a value to be looked up in tables two and three which return the values that you need to get the real values that you need by combining results from tables four and five. This is no problem for MS SQL, and it can do it surprisingly fast.
The bottom line is that when it comes to large amounts of data, data with relationships across tables or data that needs to be shared amongst DriveWorks users or with outside systems, Microsoft SQL Server provides the fastest and most logical way to use DriveWorks. Data tables can be stored in SQL Server and searched and sorted in the same way that you would pull data from a table or list within DriveWorks. But that data can be larger, more complex, more dynamic, and more available than information within DriveWorks. And SQL provides even more tools to manage all of that information. We’ll have to get into those tools next time.