Datto Autotask PSA: Using the Report Data Warehouse Schema spreadsheet

Follow

Topic

This article provides instructions for using the Report Data Warehouse Schema spreadsheet.

Environment

  • Datto Autotask PSA

Description

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.

View names, column names, 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, 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.

How to find columns contained in a view

  1. 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.
  2. To search, click the Column A header to select the column.
  3. On the left side of the Excel ribbon, click the magnifying glass for Find & Select, then select Find.
  4. 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.
  5. 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.
  6. Scan the list of rows until you find the first instance of the view you need, and then click to go to that row.
  7. The columns in the view appear in the column to the far right of the window.

mceclip0.pngFigure 1: Locating views (click to enlarge)

How to find which views contain a specific column

  1. Click the arrow in the Column B header to open the Sort menu.
  2. Click to select Sort A to Z or Sort Z to A.
  3. 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.
  4. On the left side of the Excel ribbon, click the magnifying glass for Find & Select, then select Find.
  5. In the Find and Replace dialog, in the Find what: field, enter the column name to search, or a keyword in the column name.
  6. 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.
  7. 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.

mceclip1.pngFigure 2: Report overview (click to enlarge)

​Additional Resources


Was this article helpful?

0 out of 0 found this helpful

You must sign in before voting on this article.

Want to talk about it? Have a feature request?

Head on over to our Community Forum or get live help.

Datto Homepage