World Wide Importers Model

The World Wide Importers dataset serves as a comprehensive example for business data, developed primarily for demonstrating SQL Server capabilities. This rich dataset represents a fictional company, World Wide Importers, specializing in the wholesale and distribution of various products globally. It includes detailed records spanning several tables, encompassing sales, purchasing, stock, people, customers, suppliers, and logistics. The dataset is designed to showcase a wide range of queries and data manipulation scenarios, from basic CRUD operations to complex analytical tasks, thus providing a realistic environment for testing, development, training, and benchmarking database tools and techniques.

Demonstrated Model Features

1. Multi-fact model
2. Calculated Columns

Supported Data Platforms

1. Snowflake
2. Databricks

Data Model Overview

Data Loading Instructions

How to Connect to Snowflake Tutorial Data

The tutorial data for the sample models is available for free in the Snowflake Marketplace. To get access to the tutorial data in the Snowflake Marketplace:

1. Go to the Snowflake Marketplace: In the Snowflake console, click on "Data Products" and then click on the "Marketplace" link.

2. Find the "AtScale Tutorials" data product: In the search bar, type in "AtScale" and select the "AtScale Tutorials" data product.

3. Connect to the AtScale Tutorials Data Product: On the right side of the screen, click on the "Get" button.

4. Name Your Database: Click on the down arrow on the "Options" accordion control and enter atscale_tutorial_data in the "Database" field and assign the proper access role. Click the "Get" button.

How to Connect to Databricks Tutorial Data

The tutorial data for the sample models is available for free in the Databricks Marketplace. To get access to the tutorial data in the Databricks Marketplace:

1. Go to the Databricks Marketplace: In the Databricks workspace console, Click on "Data Products" and then click on the "Marketplace" link.

2. Find the "AtScale Tutorials" data product: In the search bar, type in "AtScale" and select the "AtScale Tutorials" data product.

3. Connect to the AtScale Tutorials Data Product: On the right side of the screen, click on the "Get instant access" button.

4. Accept the Terms and Conditions: Check the terms and conditions box and click on the "Get instance access" button.

5. View your data: Click on the "Open" button to see your shared data.

6. Rename your catalog: Click on the atscale_inc_atscale_tutorials catalog in the "Shared" section, click on the vertical "..." menu on the righthand side of the screen and choose the "Rename" menu option. Type in atscale_tutorial_data in the edit box and click on the "Save" button.

How to Load to Tutorial Data into BigQuery

1. Create a project named atscale-tutorial-data in BigQuery
2. In the BigQuery console for the atscale-tutorial-data project, run the following DDL scripts in this directory:
   1. load-as-adventure.sql
   2. load-ww-importers.sql
   3. load-tpcds.sql

Creating an AtScale Connection to your Own Data

1. Go to AtScale Settings: In AtScale Design Center, click on the "Settings" dropdown by clicking on the AtScale logo icon (upper left).

2. Create a Data Warehouse: In Settings, click on the "Data Warehouse" option (top left) and click on the icon (right) for the data platform you wish to connect (i.e. BigQuery, Snowflake, Databricks, etc.).

2. Enter Data Warehouse Information: In the Data Warehouse property panel, enter your information and click on the "Apply" button. Note that you may need to create a new database and schema to hold AtScale's aggregate tables.

3. Create a Data Warehouse Connection: After creating the data warehouse, create a connection by clicking on the down arrow on the data warehouse your just created and click on the "Add Connection +" button.

4. Enter Data Warehouse Connection Information: In the Data Warehouse Connection property panel, enter your information and click on the "Test" button to make sure that the connection information is valid. Then click on the "Apply" button.

SPECIAL NOTE for a Databricks: Fill in the "Extra JDBC Flags" field with the folowing information you can get from your data wareouse/cluster's "Connection Details" tab in the Databricks console: transportMode=http;ssl=1;AuthMech=3;httpPath=<YOUR HTTP PATH>. Also, for the "Username" field enter a value of "token" and enter your Databricks Personal Access Token into the "Password" field.

5. Change your SML Connection: Go back to the Repo Browser by clicking on the AtScale icon (upper left corner) and update each model's connection YML file's as_connection property to to same string (i.e. "Snowflake") you entered into "External connection ID" field in the Data Warehouse Connection property panel in step 3. Make sure to click on the "Save File" button at the bottom of the text editor.

SPECIAL NOTE for BigQuery: You may also need to change the database property to atscale-tutorial-data (note the dashes rather than underscores).

6. Deploy your catalog (see above).

Additional Resources

Quick Start Video

How to connect to Snowflake Video

How to connect to Databricks Video

How to connect to BigQuery Video