What is the difference in kf_answer_stats and ans_stats tables for Knowledge Advanced?

Environment:

Oracle B2C Service Knowledge Advanced

Issue:

I am building a custom report that includes statistics on the answers on my site. The kf_answer_stats and ans_stats tables are both available, but it is unclear which table is the correct choice for my business needs.

Resolution:

The two tables aggregate data differently:

• The ans_stats table only has hourly data
• The kf_answer_stats table has windows of hourly, daily, monthly, and yearly data that is rolled up, i.e. aggregated over specific time periods.

The hourly and daily data for kf_answer_stats is removed after 36 hours and the value of KF_ACTIONABLE_DATA_PURGE_DAYS respectively. The window values of 0, 1, 2, and 3 correspond to hourly, daily, monthly, and yearly rollups (note that hourly data is not reportable). Less granular rollup data includes the aggregates of the more granular data (for example, a yearly rollup value includes all of the monthly values). Daily windows are only removed per the value of KF_ACTIONABLE_DATA_PURGE_DAYS after the monthly rollups have been performed.

The hourly data for ans_stats is not removed by default unless the associated answer is also deleted from the database. If you believe your ans_stats table is large enough to be causing performance problems, please submit a service request to Technical Support with a valid business reason for why this table should be truncated.

Ultimately, the kf_answer_stats data is more useful, eventhough it eventually purges its daily counts, because of all of the extra data it contains. Keep in mind the kf_answer_stats window column is important when interpreting this data and has values of 1 - Daily, 2 - Monthly, and 3 - Yearly

• In the calculation of ans_stats.solved_* and kf_answer_stats.rated_*, the ans_stats also recognizes answer ratings (action = "AnswerFeedback")
• Note that the stat_date values for ans_stats are different than the kf_answer_stats values.
  •  ans_stats records the actual hour the rating took place: e.g. '2019-04-19 05:00:00'
  •  kf_answer_stats only shows the daily rollup time, which is the top of the day (adjusted for site time zone): e.g. '2019-04-19 00:00:00
• For every set of rows in the ans_stats table that share a month, there is a row in kf_answer_stats; however, the daily and yearly rollup values really don't have a corresponding piece of data in the ans_stats table.