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.
- Download the Report Data Warehouse Schema spreadsheet to find which columns each view contains.
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.
- 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.
- To search, click the Column A header to select the column.
- On the left side of the Excel ribbon, click the magnifying glass for Find & Select and then select Find.
- 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.
- 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.
- Click to select Sort A to Z or Sort Z to A.
- 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.
- On the left side of the Excel ribbon, click the magnifying glass for Find & Select, then select Find.
- 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.
- 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.
- 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.