This article provides instructions for using the Report Data Warehouse Schema spreadsheet.
- Datto Autotask PSA
You can download the Report Data Warehouse Schema spreadsheet from this link.
The Excel spreadsheet has three columns: View Name, Column Name, and Data Type.
What are the views, columns, and data types?
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, and so forth.
Column Name is similar to the actual field in Autotask. Many fields will have an ID value and 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 the SQL world, this is referred to as 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.
How to find columns contained in a view
- Scan the View Name column to locate the view you're looking for in alphabetical 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.
- At the Find and Replace dialog, in the Find what: field, enter the name of the view or a keyword 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. Expand the dialog box as needed.
- Scan the list of rows until you find the first instance of the view you need, and then click to go to that row.
- The columns in the view appear in the column to the right. Find the column data type in the far right column.
How to find which views contain a specific column
- Click the arrow in the Column B header to open the Sort menu.
- Click to select Sort A to Z or Sort Z to A.
- After the columns sort, search Column B for the column name in alphabetical 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, and then select Find.
- In the Find and Replace dialog, in the Find what: field, enter the column name to search, or a keyword 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, and 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 appear in the Views column to the left.
Figure 2: Report overview (click to enlarge)