Information Systems:ASW Analyzer
Overview
Note: The terminology regarding 'Analyzer' is likely inaccurate, but I'll update it later. Skip to this section if you just want to fix something. -norwizzle (talk)
Analyzer is an ASW module providing ‘data warehouse’ functionality to ASW. It can be described as a system of programs and files that maintain summary statistics of various natures (e.g. inventory and accounting stats). For example, consider the common task within ASW of querying the A/R transactions for a customer for a given period. It would be inefficient for the system to work directly with the A/R transaction file each time, first having to filter for only that customer's transactions and then having to perform a sum on the amounts. It would be more efficient to query a file that already had summarized A/R transactions by customer. Analyzer maintains such a file. As Analyzer is part of ASW, the updating of Analyzer files is internal and automatic (e.g. as a transaction is added to the G/L, the summary levels that pertain to G/L transactions are also updated).
Summary Levels
There are two types of summary files – Base summaries and Custom summaries. ‘Base’ summaries are what shipped with ASW, thus they are summarized by ASW enquiry fields (e.g. Main account, Sub account etc.). Custom Summary levels are ones defined by uniPHARM, based on parameters for which we want to produce summary reports (i.e. customer,item class). See ASW Analyzer Appendix for more information.
Troubleshooting Analyzer Problems
It is rare for the base summary levels become out of balance with the transactions. It is more common for the custom summary levels - particularly 111, 113, and 115 - to become out of balance with the base summary levels.
Users become aware of imbalances when their reports don't agree. For example, the A/R Reconciliation List prints both the total of G/L account for A/R, and the total of all customer balances. Both these come from Analyser. The A/R Analysis report shows the total of (and detail when requested) of accounts receivable transactions. All three of these totals should be the same.
The A/P Reconciliation List prints both the total of G/L account for A/P, and the total of all vendor balances. Both these come from Analyser. The A/P Analysis report shows the total of (and detail when requested) of accounts payable transactions. All three of these totals should be the same.
Report Writer (which uses the Analyser files) is used to print trial balances and income statements.
If any of these are out of balance, it could be because there is something wrong with the Analyser files. I think that the problem is usually that the various custom summary levels have gotten out of sync – which isn’t surprising; it does happen when Analyser has to look at every single transaction and decide which totals to add it to. As long as we catch it soon enough, it is easy enough to fix.
Other problems are rarer. Several times we have had two records in ANOSTK with different key fields, but with the same level code and reference key. I’m pretty sure this was caused by doing a rebuild at the same time as transactions were be added to Analyser. This hasn’t happened since I started doing rebuilds on Saturday.
Something that just happened recently was that there were two records in ANOBAL with the same level code, year, balance type, and reference key. This didn’t cause any problems with any of the reports, as they were all summarising records. The problem became apparent when the opening balances (ANOBOB) were built for the following year; that process only read one of the records. This was difficult to find, as the problem was first noticed in 201501, but actually happened in 201401.
There are a couple of ways to check for problems. Go to - InfoNet / I.T. / Validate Analyser Files.
A message will show when there are duplicate keys in ANOSTK. This can happen if a rebuild is run while there is activity on the system – the transaction update and the rebuild can generate the same key fields. If this has happened, the validation program will display, in red, ‘Duplicate keys in ANOSTK. See UP1480BFVA/ANOSTKDUP.’ The way to correct this is to rebuild the summary level that has the duplicate. This must be done on a Saturday.
Key fiscal year into ‘from’ and ‘to’; select a ‘Summary type’ and press Search. If there is more than one balance type shown, as in Sales, select one. For each period shown, the value should be the same for all levels. Note that there can be discrepancies in the current period, as it is active.
Summary type Balance type A/P all A/R all Product Sales all G/L 301 Sales 001
There can be some differences in A/P transactions (level TRN) because of the way preliminary transactions work.
The product sales summary files are in even dollars only, so there can be rounding differences.
The G/L amounts should all be zero except for summary level 010, which is a sub set of the G/L accounts.
This summary file, and the check for duplicates, is redone every morning.
Rebuild
All Base summary levels that have the same balance type as one of the System Base Levels being rebuilt are deleted from ANOBOB and ANOBAL. Changed so that they are only deleted, or cleared, starting with the period being rebuilt.
System Base Levels (start with ‘A’), in the requested group (see file ANOBTD), for the requested period and later, are cleared from ANOBOB and ANOBAL.
The selected System Base Levels are rebuilt from transactions (SRODTA, SROLTA, SROLOGGL).
All deleted Summary Base Levels are recalculated. Changed to only recalculate periods starting with the period being rebuilt.
When summary base levels are recalculated, ANLR664 is called to find an existing summary level from which to build.
Issues
-Even if only the last month is being rebuilt, summary levels from 2005 are cleared and rebuilt. In October 2012 this was changed to only clear summary levels for the period being rebuilt and after. See ANLR811, 812, 818, 819, and 851.
-When A/R (balance type 311), or A/P (balance type 321) are rebuilt, some sales summaries are cleared because they include the same balance types.
-it takes all day to rebuild the summary base levels, and while they are being rebuilt, analyser reports and WebSmart programs that use the analyser files will be incorrect.
- Do not do a rebuild while there is activity, because if summary records are added by activity during the rebuild, there will be duplicate keys in ANOSTK. The office and warehouse are closed Saturday, so that is the time to do it.
Rebuild System Base Levels
When to do –
A/R Analysis List not balanced to A/R Reconciliation List
A/P Analysis List not balanced to A/P Reconciliation List
G/L balances disagree with totals in SROLOGGL or SROPST
What to do
Sign on as FINADMIN.
Go into ASW.
Key in GO STCBTM (Balance Types Definition Tasks) and press enter.
Selection option 9 – Rebuild balances from transactions
*PL/480B* Rebuild balances - selection 17/21/07 17:17:55 ANLD45001
-----------------------------------------------------------------------
Rebuild From period Description
Y 200812 A/P Supplier balances
N 200602 A/R Customer balances
N 200304 G/L balances
N 200801 Sales balances
These are the only balances that we are interested in.
Key a ‘Y’ in from of the type you want to rebuild, and the number of the period that is incorrect, then press enter.
*PL/480B* Rebuild balances - selection 3/21/07 17:21:41 ANLD45004
Select balance types
-----------------------------------------------------------------------
Type Description Alias
321 Supp system currency amounts SUPSYS
322 Supp system currency amounts/transaction currency SUPSYSTRN
323 Supp transaction currency amounts SUPTRN
324 Supp primary currency amounts SUPPRI
325 Supp VAT system currency amounts SUPVATSYS
326 Supp VAT transaction currency amounts SUPVATTRN
327 Supp turnover system currency amounts SUPTOSYS
328 Supp turnover transaction currency amounts SUPTOTRN
You will then see a list of types. (Note that all of these are not active in our system. To see which ones are, go to ‘System balance type maintenance’. So far, only 311 for A/R, 321 for A/P and 301, 303, 304 for G/L, and 001, 002, 003, 004, 009 for Sales are active.) Select the one you want to rebuild, and press enter. Press enter again to run immediately.
Rebuild Custom Summary Level
Sign on as FINADMIN using our favourite password (unity). Open ASW by typing 'ASW'.
Go to Summary Level Definition Tasks (GO STCSLM). Select option 3 - Reorganise summary level definitions, and select level.
This will select a system base level, or existing summary level, that matches the keys you selected, and summarize it. If you are rebuilding several levels, redo the most complex level first, as the rebuild tries to use an existing summary level as a base. It will only use the base level if it has to.
- if an analyser rebuild fails, check here to see if summary levels have to be manually rebuilt.
Potential timing issue
Currently, we are under the impression that custom summary levels can be rebuilt while there is user activity on the system (as opposed to base, which should only be done when no users are on the system). In fact, we have done so successfully numerous times already. However, an issue was experienced whereby custom summary levels were rebuilt on a Sunday (and interestingly, prior to any user activity), but led to Analyser files not being updated that entire day (i.e. none of the day's transactions were reflected in Analyser files). There is thus perhaps a theory that custom summary rebuilds should occur after users have already signed on. This has yet to be confirmed however; the issue may have been caused by other factors.
Add Custom Summary Level
Sign on as FINADMIN.
Go to Summary Level Definition Maintenance (either menu STCSLM, or select table maintenance program). F6 to add, key in code and description, and press enter. Select which keys to use (go into each one to see the possibilities).
From menu GO STCSLM selection option 3 - Reorganise summary level definitions, and reorganize level just created.
This will select a system base level that matches the keys you selected, and summarize it. If you are rebuilding several levels, redo the most complex level first, as the rebuild tries to use an existing summary level as a base. It will only use the base level if it has to.
A rebuild will not build a new summary base level, as the rebuild only builds the same summary base levels as it deletes.
- if an analyser rebuild fails, check here to see if summary levels have to be manually rebuilt. Can this be used to manually correct a single summary level? - yes