The Data Warehouse schema

Autotask provides an Excel spreadsheet that contains a list of all views, all columns in each view, and the data type of each column.

NOTE  To view a list of all views included in the Report Data Warehouse Data Model along with a brief description of each view, refer to Report Data Warehouse view descriptions.

Using the Report Data Warehouse Schema

This Excel spreadsheet has 3 columns, View Name, Column Name, and Data Type.

NOTE  The Warehouse_Last_Load views show the last time data was gathered to create a view (Backup_Taken) and when the data view was last refreshed (Last_Load).

  • View Name is similar to the entity described in Autotask. There may be several views that collectively would provide a complete picture of that object in Autotask. For example, the view wh_account contains a column account_id, which could join with other views that contain additional information such as wh_account_contact, wh_account_invoice_email_template_preference, wh_account_udf, etc.
  • Column Name is similar to the actual field in Autotask. Many fields have an ID value, then a corresponding definition elsewhere for the name of that value. For example, a view may contain a column name account_id but not the name of the account. So you could use the value for account_id to link back to the view wh_account which would contain the column account_name. In SQL, this is called a table join.
  • Data Type represents the type of value contained in that field. For example, int is short for integer, datetime means the field would use a standardized date formatting.
  1. Scan the View Name column to locate the view you're looking for in alphabetic order or

Use the Excel Find option to search the column.

  1. To search, click the Column A header to select the column.
  2. On the left side of the Excel ribbon, click the magnifying glass for Find & Select and then select Find.
  3. In the Find and Replace dialog, in the Find what: field, enter the name of the view, or a key word in the name of the view.
  4. Click Find All.

A list of all rows that contain the values entered in the find field opens in the lower part of Find and Replace Dialog.

NOTE  Expand the dialog box as needed.

  1. Scan the list of rows until you find the first instance of the view you need, then click to go to that row.
  2. The columns in the view are listed in the column to the right. Find the column data type in the far right column.
  1. Click the arrow in the Column B header to open the Sort menu.
  1. Click to select Sort A to Z or Sort Z to A.
  2. After the columns sort, search column B for the column name in alphabetic order, or...

Move the cursor over the Column B header and when you see the down arrow, click to select the column.

  1. On the left side of the Excel ribbon, click the magnifying glass for Find & Select, then select Find.
  2. In the Find and Replace dialog, in the Find what: field, enter the column name to search, or a key word in the column name.
  3. Click Find All.

A list of all rows that contain the values entered in the find field opens in the bottom of the Find and Replace Dialog.

Expand the dialog box as needed.

  1. Scan the list of rows until you find the first instance of the column name you need, then click to go to that row.

There is a row for each instance of the column in the Report Data Warehouse and the views that contain the column are listed in the Views column to the left.