Fact Tables

Fact tables are really summary tables. From a SQL perspective, it is a group by of the regular ARM tables. This makes the tables smaller and faster to generate reports from. ARM groups the rows in the regular ARM table (detail table), so one row in the fact table represent many rows in an ARM table. Updating the fact tables could be seen as a pre-processing step to make the ARM reports faster. One example of this grouping is that ARM groups on date. While the detail tables have the time down to the second, the fact tables only show the date.

EFT database tables can grow very large. Fact tables are used to update EFT's tables every day and keep the database current. A check box on the Server > Logs tab, Refresh facts table daily, is selected by default.

  • This feature is only available if the ARM database is managed in SQL Server. It is not available with Oracle databases.

  • When the fact tables are enabled, a warning message explains that database performance can be affected temporarily while the tables are refreshed.

  • The fact tables are updated as part of its hard-coded nightly cleanup routine (not the Cleanup Event Rule). There is no logging or reporting on the nightly fact tables cleanup.

  • Upon new install, the initial fact table creation stored procedures are run, unless those tables already exist, and then will back-fill the fact tables with historical data

  • The nightly import process logs in the ARM logger when it starts the job, with TRACE. If calling the DBUtility.exe fails, it will log an ERROR.

Each night, EFT imports data into its fact tables. Those tables are used to populate the following reports, all of which represent inbound behavior:

  • Executive Summary Report

  • Traffic - Average Transfer Rates by User

  • Traffic - Connections Summary

  • Traffic - Datewise-hourly Bytes Transferred

  • Traffic - Datewise-IPwise Bytes Transferred

  • Traffic - IPWise Connections (Summary)

  • Traffic - Monthwise-IPwise Bytes Transferred

  • Traffic - Most Active IPs - Connections

  • Traffic - Most Active IPs - Data Transferred

  • Traffic - Most Active Users - Connections

  • Traffic - Most Active Users - Data Transferred (original report done)

  • Traffic - Protocolwise Connections (Summary)

  • Traffic - Sitewise-Hourly by User