How to find out where an ARC field or measure comes from in Aleph

• Article Type: General
• Product: Aleph
• Product Version: 18.01

Description:
In ARC, How is this measure calculated : Circulation and Cash >Loan Analysis > Measures > No of Loans

I did a report in ARC to find the most-circulated items of a library collection, using the Circulation and Cash model since it contains a Measure called "No of Loans". This report was vastly different from one I did using SQL to query the XXX50.Z30 table, specifically the Z30_NO_LOANS field.

Second question : Is the Z30_NO_LOANS field mapped anywhere in ARC?

Resolution:
You can find the origin of just about any ARC field by using the following process:

From ARC, connect to Report Studio. Once there, select the field you want (the No of Loans Measure, in this case) into a report by itself. Then, between "Insertable Objects" (on the left) and your query, there are three tabs. The selected one is usually "Page Explorer". Hover over the second one down, which is "Query Explorer" and select "Query" from the expand box that opens to the right. Then, in the left lower pane (labelled "Properties - Query"), in the "Data" section, there is "Generated SQL/MDX". Select the box to the right of that, which will give you an ellipsis button. Click that to see the SQL for your query. If you have only one field selected in the query, it will show you the source field from which that field is drawn. In this case, this shows that the "No of Loans" measure is taken from DWH_FACT_LOANS.LOAN_QTY, calculated over the rest of the query.

You can then look at the document called, "MRR to DWH Mapping" to see where this field comes from. In this case, it says that the LOAN_QTY field is hardcoded as a "1" for all records. The rest of the fields in this table come from the MRR_LOANS table, which is part of the extract stage of the ARC ETL. You can then look at the document called, "ALEPH to MRR Mapping for ARC 2.0.0". This tells you that most of the fields in the MRR_LOANS table come from the Z36 and Z36H records. If you do a search for Z30_NO_LOANS, you will find that this field is not listed anywhere in this document, which means it is not part of the ARC extract.

So, my interpretation of the information above is that the DWH_FACT_LOANS table has one record per Z36/Z36H record, each of which counts as one loan. This should definitely reflect all loans that have taken place since you have migrated to Aleph. Also, I would guess that active loans from your previous system would have been added as Z36 records at the time of migration. However, I would guess that any loans that were registered in a previous system were not brought in as Z36H records. Thus, the number of loans would not count what occurred in previous systems. The Z30_NO_LOANS field probably does contain a count of historical loans, and thus would not correlate with the numbers drawn from a simple count of Z36/Z36H records.

Since the Z30_NO_LOANS field does not appear to be part of the ARC extract, there is no way to use ARC to get at this historical data. You would either have to create separate reports for this data or do all of the calculations outside of ARC.


Additional keywords: Aleph Reporting Center, ARC 2.0, SQL, source

• Article last edited: 10/8/2013