The preceding tables are all you need to store the usage, but to create effective dashboards of the actually usage, you will need additional data, for example, a date table, a table of time, a table of dashboards, pages, and of users.
The W_USAGE_TRACKING table is nearly identical to the capture table, except that we add extra fields. One of these fields is DATE_WID. This is so that the table can be linked to our standard W_DATE_D table. We add indexes to the table, on the date_wid field, and on the fields that will be filtered, such as USER_NAME and SAW_DASHBOARD. An additional task could be to create a day-level summary table that calculates performance measures for the day and will be joined to the W_DATE_D table, therefore enabling fast trend analysis.
The extra data tables listed previously are not installed into the Oracle BI schemas; you have to add them to your warehouse.
Oracle has provided a calendar table script and a time script that you have to run yourself. If you want to add more details about the dashboard and users, you will need to add these yourself manually.
To add the Oracle-provided tables and data in an MS SQL Server database, undertake the following steps:
- Navigate to the folder
oracle_homebibifoundationsamplesusagetracking. - Unzip the
.zipfiles that contain the tables and data scripts calledUsageTracking-datafiles.zip. - Open a connection in SQL Server Management Studio, and connect to your warehouse schema.
- Execute the file
SQLServer_create_nQ_Calendar.sql(from thesamplesusagetrackingfolder). - Execute the file
SQLServer_create_nQ_Clock.sql.This means the tables are now created so we now need to add some data to them.
- Execute the file
SQLServer_nQ_Calendar.sql. - Execute the file
SQLServer_nQ_Clock.sql.
You will now need to model your date and user tables together with the new warehouse-based usage tables in the repository.