AdHoc Connectivity to ADW Sources in ABI
Introduction
Anterra Data Warehouse (ADW) and AdHoc Reporting are optional add-ons for the Anterra Platform. You can add AdHoc access for new users in the Admin -> User Administration -> User Administration area in AnterraBI. Only company administrators can edit users in AnterraBI. Please refer to the following knowledgebase article for directions.
Steps to connect your ADW database to your AdHoc reporting using AnterraBI
1. Open AnterraBI and go to the Home Selection and then select Anterra AdHoc and then AnterraBI AdHoc Authoring
- Go to Datasets, then click New Dataset
3. Select the ADW Data Source - Scroll down to From Existing Data Sources - Click on ADW-[YourCustomerName]
Key schemas:
- dbo: general data
- anterra: raw system tables
- qs: ready-made views for reporting
- sftp: uploaded files (only applies if your company uses SFTP to upload data)
- others: company-specific schemas
Select a Table (Recommended) - For most users, the easiest and quickest method to start working with ADW data is to select a table directly without writing SQL.
1. Navigate to a schema like `qs`.
2. Browse the list of available tables (e.g., `qs.JSTransactions`, `qs.GL`).
3. Click on the table you want to use.
4. Click the “Select” button.
5. Choose either“Directly query your data” or “Import to SPICE” based on your needs. See the Choose Import Method section below for additional details. For most datasets (< 10,000 rows) we recommend starting with “Directly query your data”
6. Click “Edit/Preview data” to review and clean the dataset.
7. Name your dataset at the top left, then click Save and Publish at the top right.
8. Name your dataset at the top left, then click Save and Publish at the top right.
The above method is recommended for most users who want to explore data visually without writing code.
If you need more control or want to filter/join data in specific ways, you can write a SQL query instead:
1. Select “Use custom SQL” from the dataset creation options.
2. Enter your query, we recommend copying and pasting from SQL Server Management Studio.
3. Click “Edit/Preview data” to test the result.
4. Name the dataset and save it as described above.
This approach is powerful for advanced users who want to fine-tune the data extraction.
Choose Import Method: SPICE vs Direct Query
When prompted, you’ll have two options:
- Import to SPICE: fast performance, but requires scheduling updates.
- Direct Query: real-time data access, but may be slower.
Recommendation: Use Direct Query for small data or one-time analysis; use SPICE for dashboards and performance.
Clean Up / Prepare Your Data
1. Exclude sensitive info (e.g., SSNs)
2. Rename fields (e.g., row_create_date → Created Date)
3. Group fields to make them easier to use and more organized by adding them to folders
4. Set or change data types (e.g., City, Country)
5. Filter rows - Sometimes you want to always have data excluded from your dataset
6. Create calculated fields using AWS functions
Examples:
- Show the number of days (or hours, minutes, etc) between two dates
- Concatenate multiple fields (First Name + Last Name)
- Various advanced windowed functions are possible as well (e.g. total balance as of a given date)
Working with AdHoc Functions
When preparing your dataset, you can create calculated fields using built-in AdHoc (QuickSight) functions. A full list of available functions can be found here.
To explore available functions:
1. In the data preparation screen, click “Add calculated field”.
2. On the right-hand panel, scroll through the function list (Date, Math, String, etc.).
3. Hover over a function to see a description and input format.
4. Click “Learn more” at the bottom to visit the AWS QuickSight documentation site.
Example: Calculate the number of days between two dates
Use the `dateDiff` function as follows to calculate the number of days between an order date and delivery date:
`dateDiff({order_date}, {delivered_date}, 'DD')`
This returns the number of days between the order and delivery dates.
Share the Dataset with Other Authors
The best way to make a dataset accessible to other authors in your company is to place it in the folder named after your customer.
1. After saving your dataset, click to the right of your dataset and choose Add to folder
2. Select your company’s name under Shared Folders
3. Any user with author permissions in your company will automatically get access.
Recommended: Use the shared folder method for all general-purpose datasets.
Advanced Option: Limit Dataset Access to Specific Individuals
If you want to restrict access to a specific dataset:
1. Go to “Manage Permissions”.
2. Manually enter individual email addresses of users who should have access. (Note: users must have logged in at least once.)
3. Alternatively, create a subfolder under your customer folder (e.g., “Executives”) and move the dataset there.
If you want to restrict access to a specific dataset, note that:
Subfolders inherit permissions from their parent folders. You can grant extra access, but you cannot deny access inherited from the parent folder.
If you want a dataset to be available only to a limited group:
1. Create a top-level folder (not a subfolder).
2. Move the dataset into that new top-level folder.
3. Manually manage which users have access to that folder using email-based permissions.
This gives you full control over who can and cannot view the dataset.
Final Tips
- Avoid using “Visualize” before data prep – always clean the dataset first.
- Always mask or exclude PII fields if not necessary.
- Save datasets with clear names (e.g., “JC Actuals YTD”).
- Use folders to manage access more easily across teams.
Related Articles
Publishing AdHoc Dashboards to Anterra Menus
Making AdHoc Analysis Available to Viewers In Anterra's AdHoc (powered by Amazon QuickSight), only dashboards are available to viewers. Creation of a dashboard starts by creating analysis which uses one or more datasets to present information, then ...Accessing Anterra Data Warehouse (ADW) to sync Sage 300 CRE data securely and access in Power BI or SQL Server Management Studio (SSMS)
Prerequisites If using SQL Server Management Studio, we recommend you have the latest version of the software from Microsoft before beginning this guide. It is possible to have multiple versions of SSMS installed at the same time. You can download ...Can't log into ABI/New User setup
Your AnterraBI login email may be blocked by your company’s email filters. If you don’t see our email check your junk email folder, if it is not there simply go to https://anterracloudbi.com Click on the link “Forgot Your Password” and enter your ...ABI Forecasting / Excel Export 2024 R2 Release
New Features and Improvements: Enhanced Excel Export Functionality Improved handling of Excel exports from persisted grids throughout the platform Increased performance for all users Larger exports now supported (up to 10 minutes of processing time) ...Building Adhoc (AWS QuickSight) Video Tutorials
Video Tutorials Components Column Sorting Custom Sorting Parameters Types of Charts Guage Chart Donut Chart Tables / PivotTables Sorting Dual Axis Line Chart Themes Titles General Custom Charts Custom Visual Content Custom Branding in E-Mail Reports ...