IRIS AP data structures

What exactly do you need to know?

-- Jason Richards

Exchequer was created a long time ago and has a structure that reflects the limitations of Windows way back in the 90's or maybe earlier......

The Pervasive Data has complex data structures because most of the currency/floating point fields are stored as split integers which make for more accurate floating point accuracy. As a result most of the currency or floating point values are not readily accessible by ODBC. Even with ODBC you need a licence from Iris to access the data as they ship Exchequer with limited DDF files (think 4 figures for this). Even with ODBC the data is useless unless you can convert it.

Oh and all the dates are stored as text fields in an order not easily convertible to Date Time format!

You can access the values via the SDK. The reality is that it is slow.

Exchequer does not use traditional or 'normal' database methods to access data within the application. It is amazingly quick within the application and as a result the drill down capabilities of Exchequer are the love of many an accounting department and a credit to Exchequer developers.

The fact is that the original Exchequer developers were rather clever and 'invented' what is one of the most open and flexible accounting packages in the world. Their method of using Pervasive as a data store and creating their own method to search data was really years ahead of anyone else.

However, it does come at a price. Access to the data outside of Exchequer is complex or if you want to link data across other business systems.  Complex reporting is slow and painful even with Crystal Reports or similar.

The actual tables are very simple. All transactions are in two big tables there is no separation except by filtering, it gets complicated in ODBC because many of the tables don't have primary key fields. Exchequer does not need them always and so they are not there. It is also a nightmare to figure out how to query the data via ODBC and painfully slow.

There are some strange things like all the customers being in the suppliers table and all the suppliers in the customer’s tables and further lots of duplication of fields for the text indexing to work.

To any 'normal' dba it all looks like spaghetti and so it is outside of Exchequer.

Most Exchequer users that I have come across love it and hate it at the same time. The love is the flexible, open accounting system and the innovative drill down and visibility. The hate is the poor reporting performance and integration with other systems and some of the niggles that Iris never quite get around to fixing.

I spend most of my working day writing software to access exchequer data and run fast reports and queries.

So what is your interest in learning more?

 Very interesting... 

Is it the same DB structure for the MSSQL version?

Olof

I have no experience of working with the MSSQL version. What  I do know is that the MSSQL version has an emulation layer.

This is not new to IRIS as Iris financials now works on the same basis.

So as to not re-write the whole application from the ground up, emulation was chosen as the quickest route to the MSSQL market.

I suspect therfore that the MSSQL version will have 'normal' data types. It is hampered only by the performance of the emulation. Which is significant on large datasets.

-- Jason Richards

We are going to dig into the MSSQL version today and see how bad it is...

We are currently using Syspro from which it has been very simple to knock up simple web applications through which customers and suppliers check stock, order history etc.... 

Will let the forum know what it looks like!

Rich

To update everyone, the MSSQL DB structure is not too bad, pretty logical on the whole, in a few hours we successfully build statements to return:

• total of all orders for account ex.VAT ignoring all currency for the current company year
• total of all orders for account ex.VAT converting all transactions to GBP for the current company year
• total of all margin for all orders for account 
• Get the last 50 orders for account with transaction date, values in native currency, currency, your/our references ex. VAT converting all transactions to GBP for the current company year
• top 10 customers YTD based on sales orders with all transactions converted to GBP at their transaction rate
• top 10 customers YTD based on sales order transactional margin with all transactions converted to GBP at their transaction rate

What strikes me however is the extremely poor attention to detail throughout the program. Layout wise it could not be worse, nothing lines up properly or scales and the colours are terrible. Worst of all the features are inconsistent from one screen to another, in one screen the search dialogue box returns results while in the other it just crashes out – according to support this is intended…

Overall feel is that it was designed as an internal proof of concept from the late 90’s. I can’t imagine why they don’t just load a modern Delphi design pack and spend a few hours getting the interface to all line up, in less than a working day it would make the software feel so much more profeshional.

Next we are going to tackle Ecommerce inbound EDI, has anyone any experience bringing in orders from Magento?

Kind regards

Olof

We were aware of the emulation layer issue, and had steered away from the MS SQL version of Exchequer for that reason.

I'm guessing though the emulation layer only applies when you are accessing the database via the Exchequer client, and that you are then left with what is essentially an openly available SQL database that can be directly queried from other software such as Excel or Crystal Reports? 

Humm, Iris never mentioned the abstraction layer during pre-sales meetings. Going with something like Pervasive just wouldn’t have been an option however.. We use Postgres or MySQL and MsSQL where we have to.

 Yes we are interacting with the data natively through MsSQL queries even the most complicated one which is taking each transaction line in the entire system, normalising it to base currency, calculating margin, summing against a customer and returning the customers with the top ten gross margins returns its result instantly (sub 0.01 seconds). We build most of our reporting direct in SQL queries and then build little ASP gadgets that we embed into our CRM to form a series of general and account sensitive dashboards. 

Exchequer doesn't look at the accounting model as ledgers but looks at everything more in the way of documents.

CUSTSUPP - Customer and Supplier Records (static data with some totals)

Documents (or sometimes TransactionHeader) - has all the invoice, payments, orders, journal header information (and the VAT declared on the VAT return.

Document Lines (TransactionLines) - has all the nominal distribution and description lines on the transaction. You have to look at this for details like the serial number used, cost of goods sold etc etc.

There's a surprising amount of information in the Visual Report Writer help pages that details the tables that you can apply directly against the SQL tables (Pervasive only, I've not seen MSMSQL). Also, there should be an ODBC help file that helps you translate the numbers into something sensible (instead of the Pascal Reals) and gives further information - it's how I learnt.

Support should be able to point you in the right direction.

Rich