Analyses in more depth

We could create an analysis in "Requests" which contains the MMS Id identifiers for active physical item requests on books and issues along with the number of requests per MMS Id. We can use this analysis to create another analysis based on it in "Physical Items". This analysis would correctly list the physical items on which there are hold requests.

But, what about the number of hold requests, from the "Requests" subject area that we need to calculate the ratio. The number of hold requests is still not available as the "filtering based on the results of another analysis" mechanism only allows the use of one column, as a filter, the MMS Id. It is not actually including the MMS Id from the base analysis, only matching on it. What we do have available in the "Physical Items" subject area is the "Num of Requests (Physical Item)" and as we have seen it is a useful historical measure but not a measure of the number of active requests.

Set operations in OAS (Oracle Analytics Server) are for the union, intersection or subtraction of one set from another. The sets must contain the same data types. What we are trying to do is include different columns from different subject areas which may or may not have the same data types. So, set operations are not suitable in this context.

We have the mechanism for this. Remember that an analysis in two, or more, subject areas can contain measures from either subject area and that the descriptive data columns must come from shared dimensions.

This is straightforward to set up initially, so let’s do that by returning to an analysis from the "Physical Items" subject area we have already looked at "physical_items_1". To reduce the number of rows returned by the analysis, saving a bit of time, we have a filter on "Physical Items"."Physical Item Details"."Num of Requests (Physical Items)" saying that this measure must be greater than 0. That is to say there must have been at least 1 physical item request at some point past.

It is worth thinking a little about the appropriateness of this filter for the final version of the analysis that we are looking to develop. Do we wish to leave the filter in place when the number of rows have been reduced by other filter mechanisms that we will have used? We are not sure, based on the available documentation, whether this number is incremented as soon as there is a physical item request, when it has been accepted, or when it has been fulfilled. This is one of those situations where some help, or detailed knowledge of underlying data models, from Ex Libris may be required. If the measure is incremented only when fulfilled, and we leave that particular filter in place, then there is the danger that our analysis will miss currently active requests if there has been no previous requests at all on a particular item.

Considering "physical_items_1", this analysis has all the needed columns except for the number of patron physical item hold requests that are currently active. We can add the "Requests" subject area into the analysis and add the column for "Requests"."Request Measures"."# of requests". We could also restrict the MMS Id to "991003838989705181", the Leeds test data by adding a filter on MMS Id. You can load a copy of this to investigate.

Now taking a look at the results tab we can see that the number of requests "Num of Requests (Physical Items)" is 5. What does that mean? We can see from the requests' data analysis, restricted to "991003838989705181", that we ran previously, that there are 6 requests for that test record, 3 of which are active. Why the discrepancy? A detail of local processing? A question for Ex Libris perhaps? Pressing on, we have seen from the request data for this MMS Id that there are 3 active requests of the "Patron physical item request type". This is the number that we want in our physical items analysis, so we can use it to calculate the ratio of requests per copy.

How can we get just the number of active requests of the correct request type into this analysis? We can try to add a filter on "Requests"."Request Type"."Requests Type Description" and "Requests"."Request Status"."Request Status". But this will return no, or some data, but with some columns empty. This is because we are trying to include columns, or filter on them, in the analysis that are not from the measures or the shared dimensions. Remember that we can only use columns in a shared subject area analysis that are from the measures or are from the shared dimensions.

So, what we actually need is to filter the measure in the column "Requests"."Request Measures"."# of requests" to count only those which are active and of the correct type. Imagine this filtering is taking place on that column, from the "Requests" subject area, using filter columns entirely from "Requests". That is to say the filtering is not taking place in the joint subject area analysis, but only in "Requests" before it is used in the joint subject area analysis.

We can do this by editing the formula for the "# of requests" creating a custom measure that meets our requirements.

Go to the criteria tab and for the column menu for "# of requests" from the "Requests" subject area select the "Edit formula" option. Then select "Filter…​". Now the screen should look as in Figure 3.

Using the "Subject areas" panel find the "Active request flag" in "Request Details", double-click on it to add it as a filter for "# of requests". We will now have the usual filter dialogue box that will allow us to say that the request flag should be set to yes. Do this to have the screen dialogue look as in Figure 4.

Click "OK" and the column formula box will now contain the code:

Now taking a look at the results table shows that the "# of requests" column, with the filter, now has the value 3 for this particular physical item. We also want this "# number of requests" to be filtered to only be counting requests of the type "Patron physical item request".

We could go through the process again, navigating the menus and dialogues to create a filter of a filter and that will work fine. It’s also equivalent to delete the filter already there, reverting to just the measure, "# of requests" and repeat the process to replace the "filter expression" with a new filter that has two filters in the filter expression, one on the active request flag being yes and the other of the request type being a patron physical item request.

In many cases, however, it’s easy and simpler to edit the filter formula directly. As follows:

Edit the column formula again to reflect the additional filter. A cut and paste of the above code should work fine. While we are there, before clicking OK we can fix the now ungainly column heading by selecting "Custom Headings" and replacing the text in the "Column Heading" text box with "Active PI requests".

We can now create our ratio calculation column. There is no direct way to create a column from scratch, but we can select any old column from the subject area panel and replace its definition, including its headings, with what we require. We need to edit the column formula to be Requests/Copies. We need to change the column heading, again, to make it less ungainly. We will also need to change the default data format, using the column properties dialogue so that the ratio has a couple of decimal places.

So, to make all that clear, lets try using the analysis "physical_items_1" as the base for constructing this, as shown in the video High demand on physical items.

It’s possible, to some extent, to define and use variables within the Ex Libris implementation of OAS. For example, we might wish to prompt for a date range, and store the start date and the end date in variables. Then we can use those variables to display the date range as part of the analysis format or to use within filters in the analysis.

The variables we are interested in using are referred to as "Presentation Variables" within OAS. There is more information[mwug] available in Oracle’s documentation. There are other types of variable, but they do not appear to be generally available in the Ex Libris implementation, e.g. system variables and system presentation variables. It’s best to rely only on presentation variables that we ourselves create.

All analytics is going to be concerned with date & time as a context, often it is the primary one for any analysis. Data concerned with loans, requests, orders, courses, etc. is usually going to come with an implicit, question, "When is this data applicable?"

So, here are some date related tasks that we will wish to know how to do:

You will have noticed while looking at column formulas that there are "Calendar/Date" functions that can be applied to our data columns where they contain data corresponding to dates, time and timestamps. A timestamp is a date and time combination, e.g. "2007-04-01 19:45:25". We shall take a look at some of these functions and some basic usage.

A variable in OAS is referenced (or used) using the following syntax:

The default value part is optional but very useful. If you can imagine a computer program talking its way through a computation in English then the line above says …​. "Give me the value for "variable_name" if set, if it has not been set then give me the value for "default value" instead".

Let’s work through an example using variables and dates. Find and load the analysis "date_prompt_explore" in the "AAPL Training/indepth" folder. There is quite a bit going on in this analysis so let’s investigate in the video Date functions and prompts.

It’s often useful to be able to see aggregate numbers of events. Returning to the analysis "date_prompt_explore", how can we add a sub-total of loan numbers by days and a grand total of loan numbers? We need to format the container containing the table body of the analysis. Clicking on the edit pencil icon, , for the "Table" panel, while in the results tab will bring up the screen shown in Figure 5.

We are looking to apply totals for the "Columns and Measures" section. Look for the summation symbol, , in "Columns and Measures". As examples, click on the summation symbol for all the "Columns and Measures" and also for the specific column, "Day Name". In both cases select "After" from the menu. The screen will update in the preview panel and totals will be generated for each "Day Name" as well as a "Grand Total". When finished modifying the layout, or experimenting with it, be sure to click Done, or Revert for the table layout editor.

The video in Adding in totals and sub-totals shows this in action.

Now we can return to our high demand analysis, saved as "high_demand_reporting". Prompts have been added to restrict the date range to which the analysis applies and totals have been added. The video Dates prompts and totals in high demand reporting shows these additions.

OAS provides a wide range of graphs that can be added to an analysis. It will also attempt to suggest the best visualization depending on some sort of heuristics it must be applying to our analyses and data.

A visualization can be effective at communicating information, particularly trends over time. A visualization is of no use if the meaning of the data is not well understood and can be misleading.

Wikipedia expresses the concept of the pivot table well. It’s a table providing a summary view of the available data. You can think of all visualizations, whether a pivot table or graphical, as attempting to aid understanding by summarizing and simplifying a large data set to better show features or trends and to possibly make predictions.

In OAS a pivot table is regarded as being one of the available visualizations. If, using our high demand analysis as it currently exists you ask OAS to insert the recommended visualization it will use a pivot table. Which is sensible if you consider the content of the analysis so far, we are concentrating on finding items that match a particular requirement, that they are in high demand. It is not time series data. In this analysis we are not looking at a trend over time when a graphical visualization becomes more appropriate.

We shall look further into the available visualizations in the chapter on format and style.

Briefly here, in Adding a visualization, we look at adding some visualization so that you can start exploring and trying them out in your development and exploration.

Bins, or buckets are a mechanism for collecting together data items which share characteristics into a kind of container. They are collected together into bins, in the OAS parlance. We can illustrate how we might use bins in the context of a custom classification scheme. By custom, we mean a classification scheme that is not Library of Congress or Dewey . There is plenty of support in AAPL for the standard schemes. Your institution may do something different. For example, University of Lancaster uses BLISS and University of Leeds has a full custom classification scheme.

A custom classification scheme uses the "Permanent Call Number" data column as in "Physical Items"."Holding Details"."Permanent Call Number". Consider the screenshot Figure 6 from the University of Leeds. It shows an analysis showing the number of items associated with each distinct call number.

Consider the call numbers "All Souls Music BUR", "All Souls Music HAW", and "All Souls Music SMI". "BUR", "HAW", and "SMI" probably refer to the Author. For the purposes of the analysis we might wish to group them together into a home-made classification top level of "All Souls Music".

Let’s try this using bins. Follow along in the video Introduction to bins.

You can see that this can get complicated fairly quickly. Note that we have the "Save Column As" option which allows us to save our column bins formula definition as a reusable component. We can then reference it in a different analysis, also in the "Physical Items" subject area. We can see from the column definition that defining some bins generates SQL that the analysis uses. This opens up the option, useful along with "Save Column As" of keeping the SQL code outside OAS in a source code control environment and where the code can more easily edited, using your preferred text editor, than within OAS.

Code similar to that shown below could be created in text editor and then pasted into the "edit formula" definition panel in OAS. The column definition can then be saved and reused.

If there is a need for reporting against top line classification (or other local grouping requirements) it may be worth developing this concept further at your institution. Of course, bins are useful in many other areas as well.

When using an OAS analysis, we might like to have some element of interactivity. Perhaps we could click on a column heading to invoke an analysis in a different subject area using data from the analysis we are currently looking at. We might want to select a data item in a column and invoke an analysis that provides further information on that data object, or to link to that object in the online catalogue. We can add column interactions from the "Criteria" tab and editing "Column Properties  Interaction" to add "Action Links". "Action Links" are the mechanism whereby an action is specified for when a link defined for a column is clicked.

The video Adding interactions, using the analysis "high_demand_reporting_with_vis" shows an introduction to how this can work.

Note that the Primo search and the fulfilment history interactions may not necessarily work on your selected data items. For Primo, that item might be suppressed from a catalogue search and for the fulfilment history action there may be no fulfilment history to display.

Key Performance Indicators (KPIs) are described in the OAS Users Guide[mwug] as:

"KPIs are measurements that define and track specific business goals and objectives that often roll up into larger organizational strategies that require monitoring, improvement, and evaluation. KPIs have measurable values that usually vary with time, have targets to determine a score and performance status, include dimensions to allow for more specific analysis, and can be compared over time for trending purposes and to identify performance patterns."

So, a library goal might be that there should be no more than "n" patron physical item requests being made per day. Or, no more than a "p%" increase on the previous day. Possible reasoning might be that, if there is such a number of requests being made or such an increase then it needs to be noted. Perhaps to devote effort to making sure adequate copies are available or to steer requests towards available digital resources.

Given this information we can create a KPI to measure this. This KPI would then be able to be used within dashboards that we create. In the video Creating and using a KPI and watch-list we shall consider some, somewhat arbitrary KPIs, incorporate them into a KPI watch-list, a collection of KPIs, and then use it in a dashboard.