How to Prevent Table Drops in the License Subject-Area of Analytics
- Product: Alma
- Product Version: Analytics (OBI 11)
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Analytics draws its information from different tables, which store different information related to each Analytics Subject Area.
A common data warehousing practice is to store related but different data on different tables; for example, data related to a customer's id, name, and address might be stored on a different table then the data related to information regarding a purchase such as an item id, price, purchase date, etc.
Ancillary tables such as these can be considered Dimension Tables; they may individually store parts of the data desired for an Analytics Report, but may "drop" results when two Dimension Tables are referenced without Analytics also drawing information from the Primary Key or Fact Table.
Analytics reports that use criteria that reference the Fact Table are going to be the most "accurate" because the primary information stored in the Subject Area "lives" in the Fact Table.
In layman's terms, think of the Fact Table as a bridge that "joins" the Dimension Tables in a way that prevents a drop of data that would normally occur otherwise.
If an Analytics report does not reference the Fact Table, it might experience a loss of data when including different criteria fields.
From the Alma homepage, choose "Design Analytics" in the Analytics menu.
Click on the "New" drop down menu and select "Analysis". Select "Licenses" as the subject area.
On "Criteria" tab, select "License Header" table. Under this table click on "License Name", "License Begins", and "License Ends" fields to add these fields to the "Selected Columns" area for the report. Run the report by clicking on the "Results" tab. View the report, which populates all selected data fields.
Return to "Criteria" tab. Click on the "License Modification Date" table and select the "License Modification Date" field, to add this field to the report. Re-run the report by navigating to the "Results" tab.
Observe that in the updated report, the "License Modification Date" field has been populated with data, but that the data from the other fields is missing.
Here are Three Workarounds that can Help Ensure that License Subject-Area Analytics Reports do not Drop Results:
1. Adding a Measure from the Fact by Adding a Column
Run a report with additional fields such as License Id or Vendor name, which both reference the Fact Table of this Subject Area. The data should populate as expected:
If these criteria columns are not desired in a report, they can be added and then filtered out to still obtain the same result.
2. Adding a Measure from the Fact Table by Adding a Column (and filtering the Criteria Column Out)
Add a column that references the Fact Table. Then right click the criteria column to be excluded, and select "Exclude Column".
By adding and excluding the criteria column that references the Fact Table, the desired results should still appear without experiencing a table drop.
Adding a measure from the Fact by Adding a Filter
1. Create a Report in Analytics. In this example, the same report which had a drop after adding the "License Modification Date" field will be used.
3. Add a new filter to the report, using 'More Columns, and selecting a measure from the Fact Table.
4. Set the Operator of the filter to 'is greater than or equal to', and the Value as 0. This will include the column in the report, while filtering the results of the column out of the report.
5. The Report Criteria now has the desired Criteria selected, with the applied filter referencing the Fact Table.
It is important to note that this Knowledge Article applies only to the License Subject Area of Analytics; each Subject Area is unique and might experience similar Table Drops due to how the data is stored in different tables.
If you encounter one of these drops and would like assistance in circumventing it or determining why it occurred, please open a case in the Support Portal.
- Article last edited: 01-May-2020