Skip to content

Latest commit

 

History

History
117 lines (65 loc) · 6.77 KB

README.md

File metadata and controls

117 lines (65 loc) · 6.77 KB
Raw

Internet Sales Model

The Internet Sales dataset is adapted from the Microsoft AdventureWorks tutorial database which simulates a manufacturing company that sells bicycles and bicycle accessories, including product inventory, sales orders, employee information, and customer data. It serves as a comprehensive example for demonstrating data warehousing, star schemas, reporting, data integration, and analytics practices.

Demonstrated Model Features

  1. Single fact model
  2. Calculated Columns
  3. Time-relative calculations
  4. Query Dataset
  5. Row-level security

Supported Data Platforms

  1. Snowflake
  2. Databricks

Data Model Overview

Internet Sales Model

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.

Snowflake Marketplace Page

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

Snowflake Marketplace Search

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

Snowflake Marketplace AtScale Page

  1. 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.

Snowflake Marketplace Get

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.

Databricks Marketplace Page

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

Databricks Marketplace Search

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

Databricks Marketplace AtScale Page

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

Databricks Marketplace Get

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

Databricks Marketplace View

  1. 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.

Databricks Marketplace Rename

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).

AtScale-Design-Center-Settings

  1. 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.).

AtScale Design Center Add Connection

  1. 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.

AtScale Design Center Add DW Properties

  1. 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.

AtScale Design Center Add DW Connection

  1. 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.

AtScale Design Center DW Connection Properties

  1. 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).

AtScale Design Center Connection Object

  1. 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