Use Cases for Database Queries

Use Cases for Database Queries

This section gives several example use cases, by category, from least complex to most complex. Anyone familiar with database programming can build on these use cases to query your reporting database and extract a wide variety of customized reports.

If you use a database that is sensitive to case differences in field and table names, ensure that you use the correct case when writing database queries. All field names are uppercase with some exceptions. These exceptions are listed in the following table:

Table NameLowercase Fields
RULESETEJBnameresourceConfiguration
TERMSETEJBname
USEREJBuserName
FLAGEJBdocumentevent_id
TERMFIELDVALUEEJBtermfield_idtermflag_id

User Information Use Cases

Acrolinx Analytics stores information about current users in the UserEJB table. The following use cases show how to find the number of users who have logged in and how often they are checking documents.

Find the Number of Users

Each time a new user logs in, a new user entry is created. Information about users can be used to associate client, host, and session information. To find the number of users who have ever logged in, count the number of rows in the UserEJB table.

SQL Query Syntax

SELECT Count(*) as Number_Of_Users FROM UserEJB

Result Example

NUMBER_OF_USERS1

Tables Queried

UserEJB

Get Frequent Users and Report the Number of Checks per Name and Client

Each time a user checks a document, an entry is created in the DocumentEventEjb table. To see the relative frequency of users who are performing document checks, join from UserEjb to DocumentEventEjb table and count the numbers of rows as Number_of_Checks grouped by the user name. By using a WHERE clause and the ORDER BY statement, we list the users who have performed successful checks in descending order from most frequent to least frequent user.

SQL Query Syntax

SELECT UserName, Count(*) as Number_of_Checks FROM UserEJB u
INNER JOIN DOCUMENTEVENTEJB de ON de.USER_ID=u.ID
WHERE de.CHECKCOMPLETEDSUCCESSFULLY=1
GROUP BY UserName
ORDER BY Number_of_Checks DESC;

Result Example

USERNAME NUMBER_OF_CHECKS
John Smith 47
Jane Smith 29

Tables Queried

UserEJB, DocumentEventEJB

User Statistics Use Cases

Acrolinx Analytics stores statistics in various tables (for example, the DocumentEJB StatisticsEJB , and DocumentEventEJB tables). The following use cases show how to find the number of documents checked, number of words checked, usage by Integration type, and usage by time.

Total Number of Documents

Each time a new document is checked, an entry is created in the DocumentEjb table. Count the rows in the DocumentEjb table to see the total number of checked documents. The second time (and succeeding times) a document is checked, no entry is added to theDocumentEjb table. Instead, the additional entries are made to the DocumentEventEjb table (see the section "Total Number of Checks").

SQL Query Syntax

SELECT Count(*) as Total_Checked_Documents FROM DOCUMENTEJB

Result Example

TOTAL_CHECKED_DOCUMENTS
3

Tables Queried 

DocumentEJB


Total number of Checks

A document can be checked more than one time. The second time (and succeeding times) a document is checked, no entry is added to the DocumentEjb table; entries are made only to the DocumentEventEjb table. Note the difference between these two tables. To get the total number of checks, query the DocumentEventEjb table.

SQL Query Syntax

SELECT Count(*) as Total_Checks FROM DOCUMENTEVENTEJB
Result Example
TOTAL_CHECKS
3

Tables Queried

DocumentEventEJB

Total Number of Words Checked

After each check, an entry is created in the StatisticsEJB table. Add the entries in the WordCount column of the StatisticsEJB table to see the total number of checked words.

SQL Query Syntax

SELECT SUM(WORDCOUNT) as Total_Words_Checked FROM STATISTICSEJB;
Result Example
TOTAL_WORDS_CHECKED
3,299

Tables Queried

StatisticsEJB

Usage by Integration Type

Each time a document is checked, an entry is created in the DocumentEventEJB table. Use the DocumentEventEJB ClientSessionEJB , and ClientEJB tables to see how many documents are associated with each application Integration type and version.

SQL Query Syntax 

SELECT Count(*) as Checked_Docs_Per_Client, Application, Clientversion FROM DOCUMENTEJB d
INNER JOIN DOCUMENTEVENTEJB de ON d.ID=de.DOCUMENT_ID
INNER JOIN CLIENTEJB c ON c.ID = de.CLIENT_ID
GROUP BY APPLICATION, CLIENTVERSION;

Result Example

CHECKED_DOCS_PER_CLIENT APPLICATION CLIENTVERSION
12 acrolinx IQ™ Batch Checker 1.2.0

Tables Queried

DocumentEJB, DocumentEventEJB, ClientEJB

Usage by Time

This query extracts the HOUR entries from the TIMESTARTED column in the DocumentEventEJB table. It provides the number of document checks started at a specific hour of the day. Use the following query to detect bottlenecks.

SQL Query Syntax

SELECT Count(*), EXTRACT(HOUR FROM de.TIMESTARTED) as HOUR FROM DOCUMENTEVENTEJB de
GROUP BY EXTRACT(HOUR FROM de.TIMESTARTED)
ORDER BY EXTRACT(HOUR FROM de.TIMESTARTED);
The SQL function for time extraction is platform-dependent. The proper syntax could be hour(de.TIMESTARTED) , depending on the platform.

To produce more detailed time-based usage statistics, add the weekday or month in the query.

Result Example

COUNT(*) HOUR
1 15
3 18
8 19
Tables Queried
DocumentEventEJB

Flag/Rule Statistics Use Cases

Acrolinx flags problems by type, shows which rules are being exercised, and shows the flags produced within each rule set. The following use cases demonstrate this capability.

Number of Problems Flagged by Type

Employ this use case to show where most of the problems with a document are occurring. Each time a flag occurs, an entry is created in the FlagEJB table. Count the entries grouped by the Name column of the FlagTypeEJB table, then display them grouped by name. Flag types include the following:

  • Style
  • Grammar
  • Spelling
  • Terminology
  • Term Candidates
  • Reuse

SQL Query Syntax

SELECT Count(*) as Total_Flag_Count, ft.name as Flag_Type FROM FLAGEJB f
INNER JOIN FLAGTYPEEJB ft ON f.TYPE_ID = ft.ID
GROUP BY ft.name;
Result Example
TOTAL_FLAG_COUNT FLAG_TYPE
11 term candidates
5 style
1 spelling
6 terminology


Tables Queried

FlagEJB, FlagTypeEJB

Which Rules are Flagging the Most

Employ this use case to show which rules are being invoked most frequently. Count the entries grouped by the Description column of the FlagEJB table and display them in descending order.

SQL Query Syntax

SELECT Count(*) as Count_Per_Rule, r.DESCRIPTION FROM FLAGEJB f
INNER JOIN RULEFLAGEJB rf ON f.RULEFLAG_ID = rf.ID
INNER JOIN RULEEJB r ON r.ID = rf. RULE_ID
GROUP BY r.DESCRIPTION ORDER BY Count_Per_Rule DESC;
Result Example 
COUNT_PER_RULE DESCRIPTION
3189 avoid_future_tense
1567 missing_space

Tables Queried

FlagEJB, RuleFlagEJB, RuleEJB

Flags per Rule Set

Employ this use case to show how many flags are being generated for a particular ruleset. Count the entries grouped by the Name column of the RuleSetEJB table.

SQL Query Syntax

SELECT count(*) as Flags_Per_Ruleset, rs.NAME FROM RULESETEJB rs
INNER JOIN CHECKCONFIGURATIONEJB cc ON cc.RULESET_ID = rs.ID
INNER JOIN DOCUMENTEVENTEJB de ON cc.ID = de.CHECKCONFIGURATION_ID
INNER JOIN FLAGEJB f ON f.DOCUMENTEVENT_ID = de.ID
GROUP BY rs.NAME


Result Example

FLAGS_PER_RULESET NAME
23 Demo
Tables Queried
RuleSetEJB, CheckConfigurationEJB, DocumentEventEJB, FlagEJB

Information Related to Specific Flag Types

Certain types of flags have extra information associated with them. The following tables contain additional information about reuse, term harvesting, and 'sentence too long' flags:

  • ReuseFlagEJB
  • TermHarvestingFlagEJB
  • SentenceTooLongFlagEJB

Join them with the associated FlagEJB by using their FLAG_ID field.

The "Table per Subclass" mapping strategy is used. Thus the following tables contain only the information that is not stored in their super class FlagEJB:
  • ReuseFlagEjb
  • TermHarvestingFlagEjb
  • SentenceTooLongFlagEjb.

Quality Report Use Cases

Acrolinx Analytics provides a measure of quality indicated by the overall monthly checking scores as well as the Acrolinx Score by flag type. The following use cases demonstrate this capability.

Monthly Trending of Acrolinx Scores

Employ this use case to show the monthly Acrolinx Scores. Average the entries in the CheckingScore column of the StatisticsEJB table. Then look in the DocumentEventEJB table to get the month and year of the successful checks. Next, output them grouped and ordered by month and year.

SQL Query Syntax 

SELECT avg(s.CHECKINGSCORE) as Average_Checking_Score, MONTHYEARTABLE.month_and_year FROM STATISTICSEJB s
INNER JOIN (SELECT de.STATISTICS_ID, to_char(de.TIMESTARTED, 'MM/YYYY') as Month_and_Year FROM DOCUMENTEVENTEJB de WHERE de.CHECKCOMPLETEDSUCCESSFULLY=1) MONTHYEARTABLE ON MONTHYEARTABLE.STATISTICS_ID=s.ID GROUP BY MONTHYEARTABLE.month_and_year ORDER BY MONTHYEARTABLE.month_and_year;
The SQL function for date extraction is platform-dependent. The proper syntax could differ on your platform.

Result Example

AVERAGE_CHECKING_SCORE MONTH_AND_YEAR
161 07/2009
1,251.4 08/2009
Tables Queried
StatisticsEJB, DocumentEventEJB

Acrolinx Score by Type

Employ this use case to show the Acrolinx Scores by flag type. Average the entries in the CheckingScore column of thTypeStatisticsEJB table. Then, output the results for each flag type.

SQL Query Syntax

SELECT avg(ts.CHECKINGSCORE), ft.NAME FROM TYPESTATISTICSEJB ts
INNER JOIN FLAGTYPEEJB ft ON ts.TYPE_ID = ft.ID
Result Example
AVG(TS.CHECKINGSCORE) NAME
0 grammar
475.33333 spelling
260 style
0 reuse
334.5 terminology

Tables Queried

TypeStatisticsEJB, FlagTypeEJB