Report Data Warehouse
About Report Data Warehouse
Overview
Report Data Warehouse is a contract-based service made available for the convenience of authorized Datto customers. It provides read-only access to critical parts of your organization's Autotask data.
Datto has designed the Report Data Warehouse to provide fast and efficient access to data; however, as in any reporting environment, the more complex the query, the longer it will take to run.
You must connect to Data Warehouse from a static IP address. Each address that attempts to access our Data Warehouse servers must be in an allowlisted status on our network. You may allowlist up to three IP addresses for access to Data Warehouse.
Why am I only allowed three IP addresses for Data Warehouse allowlisting?
Some of our customers need to use Data Warehouse heavily from various locations and computers. As a result, they might request to have more than three IP addresses allowlisted for access to the Data Warehouse.
At this time, we do not allow the allowlisting of more than three IP addresses because Data Warehouse is meant to be a repository for a customer's reporting infrastructure. The ideal use is that a single source (one computer) accesses the Data Warehouse to pull a fresh copy of the data down to the local environment. From there, users can run reports from as many or few machines as needed with queries of infinite size, all without impacting the Datto Autotask network.
We encourage our customers to develop a scalable approach for access; if you would like further guidance from our team, contact Datto Technical Support.
Access requires a unique username and password. Unauthorized use of the Report Data Warehouse and attempts to compromise Report Data Warehouse services are direct violations of your Datto contract.
The Data Warehouse does not provide direct access to the base Autotask database tables; instead it provides views that map to one or more tables. These views are designed to allow for more efficient retrieval of selected data most commonly used by Autotask customers for reporting purposes. By exposing views instead of the base tables, the Report Data Warehouse prevents the need to update your reports any time a change is made to the tables.
NOTE Although the views provide extensive access to the data in your Autotask database, they do not provide access to all data.
Any reporting software that can access Microsoft SQL Server views can use the Autotask Report Data Warehouse. Because a wide variety of these tools exist, and we cannot predict our customers' preferences, we cannot provide support or user guides for any third-party reporting utility. Datto Technical Support can help validate that Data Warehouse is working correctly and review any errors you receive to ensure that they are not the result of a product defect.
Many different development tools are available to work with SQL standards. Due to the wide variety of development tools available and the custom-tailored nature of queries to their client environments, we cannot help you create or troubleshoot your application code. If you'd like advanced insight into Data Warehouse and how to optimize its use for your organization, contact your Datto Account Executive.
We encourage you to grow your knowledge by reaching out to other developers via the Datto Community. If you're a seasoned Data Warehouse user, we encourage you to visit the Community and share your knowledge with others.
NOTE To share information with other users, we recommend that you subscribe to the PSA Forum on the Datto Community.
For more information, review the following topics:
- Report Data Warehouse view descriptions - This topic provides an alphabetic listing of all views included in the Report Data Warehouse data model along with a description of each view.
- The Data Warehouse schema - This topic describes the Report Data Warehouse Schema Excel spreadsheet. This spreadsheet report provides the View Names, Column Names, and column Data Type for all views in the Report Data Warehouse. The Excel sheet includes column filters so you can sort by column to search alphabetically for views or columns.
- Report Data Warehouse revision history- This topic provides a chronological list of updates made to the Report Data Warehouse within the last 12 months.
How to...
- To arrange access to the Report Data Warehouse, contact your Datto Sales Executive to set up a contract.
- After you sign the Report Data Warehouse contract, Client Services will contact you and request your public IP address to add to Autotask's security allow list. Datto allows a maximum of three IP addresses. Your public IP address must be added to the security allow list before Datto can enable access to Report Data Warehouse.
- Client Services will send you the following information:
- The zone-specific URL where your reporting tool can access the server
- A unique Report Data Warehouse username and password. Your usual Autotask login information will not provide access.
- For access, use port 1433.
IMPORTANT Because there are numerous reporting tools in use, Datto cannot provide specific instructions about how to configure reporting tool access to the server.
Each Report Data Warehouse database is refreshed every day beginning at 4 AM Eastern Time for North American customers and 4 PM Eastern Time for Global Customers from a backup of the customer's production database. Your database may be refreshed at that time, or up to 4 hours later. To see the time your database was refreshed on the previous load, you can run the following query:
SELECT * FROM warehouse_last_load
That query will return a Last_Load (the time the data refresh was completed) and a Backup_Taken (the time the data will be accurate up until for that refresh). The data returned in this table is the only indication for when it is safe to perform a refresh.
Be aware that when the database is being refreshed, the following will occur:
- Any currently-running queries will be aborted.
- Any connected user will be disconnected.
Refresh time depends on the size of the customer's database. Typical refresh time will be less than 10 minutes.
The Report Data Warehouse draws data directly from your Autotask database. The data returned uses the terminology found in the internal naming conventions used in Autotask code. The terminology that appears in the Autotask user interface is designed to enhance usability when completing tasks through the interface. This terminology has changed over time and sometimes the terminology that appears in the user interface is different from the terminology returned by the Report Data Warehouse.
For example, the user interface uses the term company to describe the entities that represent the different businesses and organizations with which the Autotask user has a business relationship. The database uses the term account to describe these entities.
Another frequently used term describes any billing item connected to a ticket, contract or project. The interface uses the term charge for these items. The database uses the term cost. But note that the term cost is used in both the interface and the database to describe the amount a vendor charges your company.
The following table lists the most commonly used terms that might differ between your Autotask interface and the data returned by the Report Data Warehouse.
Autotask User Interface | Report Data Warehouse | Additional Information |
---|---|---|
charge | cost | For billing items associated with a Ticket, Contract, or Project |
classification | key_account_icon | |
client portal | client_access | |
company to bill | bill_to_account | |
end date | through_date | |
first response actual time |
first_activity_date |
|
multiplier | factor | As in block hour factor |
promised fulfillment date | promised_fulfillment_date | |
promised fulfillment date | promised_due_date | |
quote | equote | This value represents one usage of the term quote |
The database collation setting controls the sort order for text fields. The Report Data Warehouse uses different collation settings for localized language versions so words are sorted properly based on the alphabet of the localized language. The following table lists the collation setting for each language version of Autotask.
Language | Collation Setting |
---|---|
English | SQL_Latin1_General_CP1_CI_AS |
German | SQL_Latin1_General_CP1_CI_AS |
Spanish | Spanish_Modern_CI_AS |