Blended Applications
Everyone may have a favorite database, but only in marketing does anyone argue that theirs is best. It is rather like arguing whether a screw or a nail is better — each have benefits in specific circumstances.
Most software developers choose their favorite DBMS and then incorporate work-arounds or third-party solutions to compensate for any weaknesses in that DBMS. The trick is to know the strengths of each DBMS and data filing system, use the system that is best suited for each kind of data, and then blend the access to the data.
A SQL database is understood by many. There are a lot of new programmers coming out of school that use and like SQL. And there are a lot of tools that have been developed for SQL, such as SQL Reporting Services. SQL is a two dimensional database structure that is extremely fast at processing transactions where everything you need to know is stored in that transaction.
Utilizing a MultiValue three dimensional database structure provides increased performance when accessing large amounts of data that would require multiple table joins in a two dimensional data structures such as SQL.
MultiValue databases lend themselves to business logic and rules and are extremely flexible. I heard it said that MultiValue developers think in terms of the business problem, while SQL developers often think in terms of how the data is stored, or what tables are needed. What format works best depends on the data to be stored and how it's going to be retrieved and viewed.
The reality today is that even an entirely self-contained MultiValue ERP software package will need to access other databases, if for no other reason than that their users may want to view data in third-party commercial, off-the-shelf data viewers such as Crystal Reports or Microsoft Excel. The MultiValue database vendors are diligently adding tools to make that access easier, but the design and considerations of how and when to access another database is still being ironed out by the application software vendors.
I recently had the opportunity to see a long standing ERP application that has been upgraded to use the best features of both SQL and their MultiValue database so, with their permission, I am going to discuss the design considerations that went into their product.
Their time and expense application requires people in many different locations, both on site and remote, to be able to enter time and expenses charged to specific projects. Security constraints at the remote locations require that the application be accessed by computers or workstations that do not allow for installation of third-party software clients. To solve this problem, a remote user interface was written in .NET and placed on the Web.
The company has created their own controller so that a .NET programmer can just call an object, which is a subroutine that resides in the MultiValue database. All data fields are mapped to indicate which dictionary fields in the MultiValue database correspond to what SQL data sets. When the time record data is entered, it is passed to the MultiValue subroutine which creates the appropriate MultiValue record or records and then sends the insert or update to the SQL database. The MultiValue record stores all the SQL primary keys, and the transactions can be controlled by multivalued dates.
In the MultiValue application, the user can retrieve an employee record and see all the time card transactions on one screen. By clicking on a single date, another window opens (written in .NET) that displays the SQL detail. At the bottom of that .NET screen they have a button to view the data displayed in a generated Crystal report.
When payroll calculations are done, the MultiValue database already has all the information by pay date in each employee record, so the calculation is done with MultiValue Basic and is very fast.
With this approach, data that is desired for reporting is generated at the same time that the transaction is entered, as well as creating a multivalue list or index of SQL keys. Since the MultiValue DBMS controls what records are written, record locking can be handled programmatically, and therefore the two databases remain synchronized. Determining how the data is going to be retrieved and viewed defines what data is stored in each database.
This is similar to the effort required in designing a data warehouse. You have to know what data you are going to want to use. MultiValue developers, in the habit of thinking in terms of the business process, are ideally suited for this kind of design.
With SQL, when a select statement is issued with joins, the tables are all searched and then a temporary table is created combining all the records selected. By creating multivalue indexes or lists in the MultiValue database of primary SQL keys that might be desired for all complex SQL joins, only one read is required.
The primary keys are passed to SQL, and SQL creates the temporary table from those keys. There is no delay for the select and no need to develop complex joins in SQL. The selection is extremely fast and accurate, and the ability to drill down or audit the records being retrieved is possible. (Working in an SQL database and developing complex joins, then verifying that the SQL statement did indeed select the right records, can be frustrating to say the least.)
With the blended approach discussed here, a .NET programmer need only be told to add a new field to the screen, and pass the data; the MultiValue designer determines where the data needs to reside and adds it to the data map. No additional complex SQL selects need to be written. This keeps the MultiValue application fast and easy to modify and the number of personnel required to maintain it to a minimum.
Screens can be developed with either .NET or the MultiValue providers' tools for web browsers. I am told that screen development is still faster with the MultiValue tools, so it is great for doing mock-ups. On the other hand, there are a lot of .NET programmers available. And .NET screens can easily access other Microsoft tools.
The time and expense application I mentioned earlier allows the user to attach scanned images of their expense receipts to their expense report. If the screen designs are kept consistent, the user can't tell which tool is used, or which database is being accessed. Indeed, some screens may access both.
MultiValue application developers have the advantage of years of experience understanding business problems and the ability to quickly modify or enhance an application. Even though two databases may be used, a blended approach can still maintain the integrity of a unified central database.
Access to both databases can be controlled for security purposes, and all reports will be working with the same data. This interface can be seamless and real time rather than bolting other tools, modules, or applications to the ERP application. It saves time and eliminates multiple steps to move data between applications.
Large companies may already have separate databases, and MultiValue providers today do provide tools to talk to other databases. But I believe the real competitive advantage for the application providers will be to use both SQL and MultiValue together to provide a complete solution.
As always, planning, an understanding of the business rules, an appreciation for ease of use for the end users, and standardization is the hard part. The technology is available. Applications that embrace the best features of multiple DBMS are available and are successfully competing against any database that is trying to stand-alone.
I wish to thank Obvio Software for the time they spent explaining their design to me. I think they hit the nail on the head. Though I'm not arguing that one database is best, I believe in the versatility and creativity of MultiValue application developers, and I remain optimistic about the future of all MultiValue applications.
