Creating Scripts
Planning your import
Salesmatrix was originally designed to cater for the business transaction
‘Customer A buys Product B from Sales Channel C in Period D’
with values for the sale value, the sale cost and the units of product sold analysed.
This transaction while wide-ranging does not cover all aspects of the analysis requirements of all business. Hence we have designed Salesmatrix to cater for other business transactions. For example:
Client A has Staff Person B perform Work type C in Period D.
Customer A buys a component of Product-Group B from Sales Channel C in Period D.
Customer from Postcode A buys Product B etc.
Or even analysing purchases.
Supplier A provides Product B to Location C in Period D.
The import wizard and the stored scripts provide the ability to change modify the imported data to suit any of the above scenarios and of course any others where there are 4 key dimensions.
The Script files (*.SMD) files are text documents that store the rules for importing data. All dimensions of the import can be modified in these scripts and the details of what the contents are is outlined in the section Using Scripts to Automate Imports
On each of the 4 data pages in the import wizard is the option check-box ‘Use Custom Script?’ If data fields are entered into the Links screen, clicking this button will display the current SQL select statement that is to be sent to the ODBC driver and then to the accounting software database.
Once the custom scripts entry area is shown and has text in it, this text is not modified until it is cleared completely. If changes have been made to the linking fields, the query must be refreshed by completely deleting the custom query text in the entry area. Recheck the ‘Use Custom Script’ box and the modified custom script will be displayed.
The only limitations to the complexity of the selection statement are the limitations of the particular ODBC driver that is used to query the accounting data. If the driver supports multiple joins and union statements etc, then they can be employed when selecting datasets into Salesmatrix.
Default column names.
To enable Salesmatrix to discern where each column of data returned is mapped to, the default script use an ‘AS’ clause to name the column returned.
The following table lists the AS clause names used by Salesmatrix.
Transaction Selection:
- CCUSTID
Field
that links to Customer ID .
- CSMANID
Field that links to Sales Channel ID.
- DTRANDATE
Transaction Date Field.
- CPRODID
Field that links to Product ID.
- NVALUE
Value of Transaction.
- NUNITS No
of Units in Transaction.
- NCOST
Cost of Transaction.
Customer Selection:
- CID
Unique ID for Customer.
- CNAME
Customer description.
- CCAT1
First Analysis Category for Customer.
- CCAT2, CCAT3, CCAT4, CCAT5
Second etc Analysis Categories for Customer.
Product Selection:
- CID
Unique ID for Product.
- CNAME
Product description.
- PCAT1
First Analysis Category for Product.
- PCAT2
Second Analysis Category for Product.
Sales Person/Channel Selection:
- CID
Unique ID for Sales Person.
- CNAME
Sales Person description.
- SCAT1
First Analysis Category for Sales Person.
- SCAT2
Second Analysis Category for Sales Person.
What to do if the data doesn’t import?
Once a script has been written or modified, very often there are issues with the data. During the import you may see a dialog ‘Errors occurred during Loading. Continue?’ This is a sign that one or more of the four selection scripts did not complete correctly.
Checking the data returned may give some clues as to where the issue lies.
If no value data comes into Salesmatrix there are some steps you can take to check where the issue is.
- Check to see if there are transactions in the
accounting database for the date range entered.
- After the import go to the File Properties menu item. That will show various information regarding the import including details of the SQL used during the import process. If this screen is unable to be viewed, Salesmatrix writes a file to disk named
‘SQL.log’. This is a list of the selection statements and the errors
that are returned. This may give you an idea of where the errors are
originating.
- You can test the SQL in a database query tool like MS-Query or MS Access to determine whether the query is correctly returning data.
Copyright
|