Use * syntax for faster search

Table of Contents


Overview

The OneStrata Data Warehouse contains the media and financial data of a OneStrata enterprise, helping agencies manage their data more efficiently.

Hosted in Snowflake, the OneStrata Data Warehouse has the following key benefits:

  • Centralized data repository and data analytics platform for OneStrata Digital.
  • Data can be queried directly within Snowflake through a Snowflake worksheet.
  • Data can be be easily exported to business intelligence tools like Tableau and Power BI.

Data Access

Whenever possible, we recommend using your own Snowflake account through Secure Data Sharing instead of using a reader account, since it gives you more control over your data:

  • You can use your own company's security workflows, like SSO or private keys.
  • You can directly insert, update, and perform other data manipulation operations on your data, like writing to S3.

 There are two ways to access your OneStrata Data Warehouse data in Snowflake:

OptionDescriptionRequirements

Secure Data Sharing

Recommended

Your data is shared with your existing Snowflake account.

Secure Data Sharing:

With Secure Data Sharing, no actual data is copied or transferred between accounts. All sharing uses Snowflake’s services layer and metadata store. Shared data does not take up any storage in a consumer account and therefore does not contribute to the consumer’s monthly data storage charges.

  • You must have your own Snowflake account.
  • Your Snowflake account must be in the same region as the OneStrata data warehouse containing your data:
    • AWS us-east-1
    • AWS eu-central-1

To use Secure Data Sharing, contact your FreeWheel representative.

Reader Account

Your data is available through a Snowflake reader account.

A reader account is primarily intended for querying and viewing, and cannot perform certain data manipulation tasks like uploading new data or modifying existing data.

Manage reader accounts:

A reader account enables data consumers to access and query data shared by the provider of the account, with no setup or usage costs for the consumer, and no requirements for the consumer to sign a licensing agreement with Snowflake.

To get a reader account, contact your FreeWheel representative.

Data Connection and Unloading

Depending on how you access the OneStrata Data Warehouse, there are different ways to connect to Snowflake and unload (bulk export) data from Snowflake.

Access OptionConnection OptionsUnload Options
Secure Data Sharing
  • The Snowflake web application.
  • A Snowflake driver for languages and APIs such as Go, C#, Python, and ODBC.
  • The COPY INTO <location> command for unloading data into a cloud storage location.
  • A Snowflake storage integration for unloading data to Amazon S3, Google Cloud Storage, or Microsoft Azure.
Reader Account
  • The Snowflake web application.

Tables

This section provides information about the tables in the OneStrata Data Warehouse:

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

The table is being loaded. Please wait for a bit ...

SchemaTableDescription
DIMENSIONAD_PLACEMENT

Contains information about your Ad Placements.

An Ad Placement contains the external ad serving details of an Ad Unit; an Ad Placement is created when an Ad Unit is assigned an external Ad Server provider.

DIMENSIONAD_UNIT

Contains information about your Ad Units and Media Lines.

A Media Line defines the unit information of a media activity purchase. An Ad Unit is more granular than a Media Line, and defines the format and dimensions of an individual media placement.

DIMENSIONAGENCY

Contains information about your Agency records.

An Agency represents an advertising agency, and is the highest level entity within the business structure.

DIMENSIONBILLING_PERIOD

Contains information about your billing periods.

A billing period defines the committed financial details (the amount that a vendor should be paid or that a client should be billed) for a given Cost Line, for a given period of time (typically a calendar month).

All Cost Lines have at least one billing period.

DIMENSIONBUSINESS_UNIT

Contains information about your Business Unit records.

A Business Unit represents an office, branch, or subdivision within an Agency, depending on the operating structure or financial reporting needs of the Agency.

DIMENSIONCAMPAIGN

Contains information about your media campaigns.

DIMENSIONCLIENT

Contains information about your Client records.

A Client represents the advertiser that an Agency or Business Unit is planning and buying media for.

DIMENSIONCLIENT_PASSBACK

Contains information about your Client Passback records.

A Client Passback record defines the decimal percentage of the vendor discount that is passed on to the Client.

DIMENSION

CONTRACT

Contains information about your Contract records.

A Contract is a record that links one Payee to one or more Order Partners to one or more Suppliers.

DIMENSIONCOST_CATEGORY

Contains information about your cost categories.

A cost category represents a way that an agency might structure costs for billing or reporting.

FACTDELIVERY

Contains information about your delivered units and unit types.

For committed units and unit types, see the MEDIA_LINE_METRICS view.

DIMENSIONENTERPRISEContains information about your OneStrata enterprise.
DIMENSIONESTIMATE

Contains information about your estimates.

An estimate provides a way of grouping financial orders and defines a budget associated with a given combination of Agency, Business Unit, Client, Product, and Media Type. Multiple campaigns can be linked to the same estimate, and multiple estimates can be linked to the same campaign, 

DIMENSIONEXCHANGE_RATE

Contains information about your Exchange Rate records.

An Exchange Rate record defines the rate at which one currency is converted to another currency.

DIMENSIONFLIGHT_PERIOD

Contains information about your flight periods.

A flight period defines the period of time that a media activity purchase runs for, as well as the number of units purchased during that time frame.

DIMENSIONMEDIA

Contains information about your Media Types.

A Media Type represent an agency's custom media types, for example Social or Programmatic.

FACTMEDIA_LINE_METRICS

Contains information about your committed units and unit types.

For delivered units and unit types, see the DELIVERY view.

FACTORDER_COST

Contains information about your order costs, at the billing period level.

DIMENSIONORDER_DETAIL

Contains information about your orders, at the Cost Line level.

An order is a formal request to purchase media activity from a given Order Partner.

DIMENSIONORDER_PARTNER

Contains information about your Order Partner records.

An Order Partner represents an entity that processes orders and is part of the vendor structure (Payee, Order Partner, and Supplier).

DIMENSIONPAYEE

Contains information about your Payee records.

A Payee represents an entity that is paid for media activity and is part of the vendor structure (Payee, Order Partner, and Supplier).

DIMENSIONPREFERRED_VENDORContains information about your Preferred Vendors.
DIMENSIONPRODUCT

Contains information about your Product records.

A Product represents a Client's product, activity, or brand.

DIMENSIONPURCHASE_ORDER

Contains information about your purchase orders.

DIMENSIONROLE_PERMISSION

Contains information about your User Role records.

A User Role record defines the parts of the OneStrata platform that a user assigned that user role can view or edit.

DIMENSIONSUPPLIER

Contains information about your Supplier records.

A Supplier represents an entity that runs media activity and is part of the vendor structure (Payee, Order Partner, and Supplier).

DIMENSIONTAX

Contains information about your Tax records.

A Tax record defines a client tax rate or vendor tax rate.

DIMENSIONTERMS_AND_CONDITION

Contains information about your Terms & Conditions records.

A Terms & Conditions record defines a set of terms and conditions that applies to an order.

DIMENSIONUSER

Contains information about your User records.

A User record defines a user in your enterprise.

Entity Relationship Diagram

This entity relationship diagram illustrates the relationships between the tables in the OneStrata Data Warehouse, where green tables are DIMENSION tables and purple tables are FACT tables:

Join Keys

This section contains important notes about foreign keys:

  • The MEDIA_KEYS columns in the COST_CATEGORY and SUPPLIER tables are arrays of MEDIA_KEY values.
    • It is recommended to flatten the MEDIA_KEYS column before joining other tables on the MEDIA_KEY column.
  • The ESTIMATE_KEYS column in the CAMPAIGN table is an array of ESTIMATE_KEY values.
    • It is recommended to flatten the ESTIMATE_KEYS column before joining other tables on the ESTIMATE_KEY column.

The following table lists the key columns that can be used to join tables in the OneStrata Data Warehouse:

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

The table is being loaded. Please wait for a bit ...

SchemaTablePrimary KeyForeign Key(s)Example
DIMENSIONAD_PLACEMENT

AD_PLACEMENT_KEY

AD_UNIT_KEY,

ORDER_KEY

DIMENSION.AD_UNIT AU
LEFT JOIN 
DIMENSION.AD_PLACEMENT AP
	ON AU.AD_UNIT_KEY = AP.AD_UNIT_KEY
WHERE
	AP.AD_UNIT_KEY IS NOT NULL


DIMENSIONAD_UNIT

AD_UNIT_KEY

MEDIA_LINE_KEY,

ORDER_KEY

DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.AD_UNIT AU
	ON OD.ORDER_KEY = AU.ORDER_KEY


DIMENSIONAGENCYAGENCY_KEY

AGENCY_PARENT_KEY,

ENTERPRISE_KEY

DIMENSION.AGENCY A
INNER JOIN 
DIMENSION.BUSINESS_UNIT BU
	ON A.AGENCY_KEY = BU.AGENCY_KEY


DIMENSIONBILLING_PERIOD

BILLING_PERIOD_KEY

ENTERPRISE_KEY,

ESTIMATE_KEY,

ORDER_KEY,

PURCHASE_ORDER_KEY

DIMENSION.ORDER_DETAIL OD
INNER JOIN 
DIMENSION.BILLING_PERIOD BP
    ON OD.ORDER_KEY = BP.ORDER_KEY


DIMENSIONBUSINESS_UNITBUSINESS_UNIT_KEYENTERPRISE_KEY
DIMENSION.BUSINESS_UNIT BU
INNER JOIN 
DIMENSION.CLIENT C
	ON BU.BUSINESS_UNIT_KEY = C.BUSINESS_UNIT_KEY


DIMENSIONCAMPAIGN

CAMPAIGN_KEY

CLIENT_KEY,

ENTERPRISE_KEY,

ESTIMATE_KEYS,

MEDIA_KEY

DIMENSION.AGENCY A
INNER JOIN 
DIMENSION.BUSINESS_UNIT BU
	ON A.AGENCY_KEY = BU. AGENCY_KEY
INNER JOIN
DIMENSION.CLIENT CL
	ON BU.BUSINESS_UNIT_KEY = CL.BUSINESS_UNIT_KEY
INNER JOIN
DIMENSION.CAMPAIGN C
	ON C.CLIENT_KEY = CL.CLIENT_KEY
LEFT JOIN 
DIMENSION.MEDIA M
	ON C.MEDIA_KEY = M.MEDIA_KEY


DIMENSIONCLIENTCLIENT_KEYENTERPRISE_KEY
DIMENSION. CLIENT C
INNER JOIN 
DIMENSION.PRODUCT P
	ON C.CLIENT_KEY = P.CLIENT_KEY


DIMENSIONCLIENT_PASSBACK

CLIENT_PASSBACK_KEY

CLIENT_KEY,

ENTERPRISE_KEY

DIMENSION.CLIENT C
LEFT JOIN 
DIMENSION.CLIENT_PASSBACK CP
	ON C.CLIENT_KEY = CP.CLIENT_KEY


DIMENSIONCONTRACT

CONTRACT_KEY

ENTERPRISE_KEY,

ORDER_PARTNER_KEY,

SUPPLIER_KEY,

PAYEE_KEY

DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.PAYEE P
	ON P.PAYEE_KEY = OD.PAYEE_KEY
LEFT JOIN 
DIMENSION.ORDER_PARTNER OP
	ON OP.ORDER_PARTNER_KEY = OD.ORDER_PARTNER_KEY
LEFT JOIN 
DIMENSION.SUPPLIER S
	ON S.SUPPLIER_KEY = OD.SUPPLIER_KEY
LEFT JOIN
DIMENSION.CONTRACT C
	ON C.PAYEE_KEY = P.PAYEE_KEY
	AND C.ORDER_PARTNER_KEY = OP.ORDER_PARTNER_KEY
	AND C.SUPPLIER_KEY = S.SUPPLIER_KEY


DIMENSIONCOST_CATEGORY

COST_CATEGORY_KEY

ENTERPRISE_KEY,

MEDIA_KEYS

WITH CTE_COSTCATEGORY AS (
SELECT s_flat.value as MEDIA_KEY, s.*
	FROM DIMENSION.COST_CATEGORY s,
	LATERAL FLATTEN(input => s.MEDIA_KEYS) s_flat
	WHERE IS_ACTIVE IS NULL
)
SELECT *
FROM DIMENSION.CAMPAIGN C
INNER JOIN 
DIMENSION.ORDER_DETAIL OD
	ON C.CAMPAIGN_KEY = OD.CAMPAIGN_KEY
LEFT JOIN 
DIMENSION.MEDIA M
	ON C.MEDIA_KEY = M.MEDIA_KEY
LEFT JOIN 
CTE_COSTCATEGORY CC
	ON CC.COST_CATEGORY_KEY = OD.COST_CATEGORY_KEY
	AND CC.MEDIA_KEY = M.MEDIA_KEY


FACTDELIVERY

DELIVERY_KEY

AGENCY_KEY,

CLIENT_KEY,

ENTERPRISE_KEY,

ORDER_KEY,

PLACEMENT_KEY,

SUPPLIER_KEY

DIMENSION.ORDER_DETAIL OD
INNER JOIN 
DIMENSION.BILLING_PERIOD BP
	ON OD.ORDER_KEY = BP.ORDER_KEY
INNER JOIN 
DIMENSION.AD_UNIT AU
	ON OD.ORDER_KEY = AU.ORDER_KEY
LEFT JOIN 
FACT.DELIVERY D
	AND D.ORDER_KEY = BP.ORDER_KEY
	AND D.START_DATE >= BP.START_DATE AND D.END_DATE <= BP.END_DATE
	AND D.ORDER_KEY = AU.ORDER_KEY
	AND D.AD_UNIT_KEY = AU.AD_UNIT_KEY


DIMENSIONENTERPRISEENTERPRISE_KEY
DIMENSION.ENTERPRISE E
INNER JOIN 
DIMENSION.AGENCY A
	ON E.ENTERPRISE_KEY = A.ENTERPRISE_KEY


DIMENSIONESTIMATE

ESTIMATE_KEY

ENTERPRISE_KEY,

MEDIA_KEY,

PRODUCT_KEY


DIMENSIONEXCHANGE_RATE

EXCHANGE_RATE_KEY

ENTERPRISE_KEY


DIMENSIONFLIGHT_PERIOD

FLIGHT_PERIOD_KEY

AD_UNIT_KEY,

ORDER_KEY

DIMENSION.AD_UNIT AU
INNER JOIN 
DIMENSION.FLIGHT_PERIOD FP
	ON FP.AD_UNIT_KEY = AU.AD_UNIT_KEY


DIMENSIONMEDIAMEDIA_KEYENTERPRISE_KEY
DIMENSION.CAMPAIGN C
INNER JOIN 
DIMENSION.MEDIA M
	ON M.CAMPAIGN_KEY = C.CAMPAIGN_KEY


FACTMEDIA_LINE_METRICS

MEDIA_LINE_METRICS_KEY

MEDIA_LINE_KEY,

ORDER_KEY

DIMENSION.ORDER_DETAIL OD
LEFT OUTER JOIN 
DIMENSION.AD_UNIT AU
	ON OD.ORDER_KEY = AU.ORDER_KEY
LEFT JOIN 
FACT.MEDIA_LINE_METRICS MM
	AND AU.ORDER_KEY = MM.ORDER_KEY
	AND AU.MEDIA_LINE_KEY = MM.MEDIA_LINE_KEY


FACTORDER_COSTS

ORDER_COST_KEY

AGENCY_KEY,

BILLING_PERIOD_KEY,

CAMPAIGN_KEY,

CLIENT_KEY,

CLIENT_TAX_SET_UP_KEY,

COMMISSION_KEY,

COST_CATEGORY_KEY,

ORDER_KEY,

ORDER_PARTNER_KEY,

PAYEE_KEY,

SUPPLIER_KEY,

VENDOR_TAX_SET_UP_KEY

DIMENSION.AGENCY A
INNER JOIN 
DIMENSION.BUSINESS_UNIT BU
	ON A.AGENCY_KEY = BU. AGENCY_KEY
INNER JOIN
DIMENSION.CLIENT CL
	ON BU.BUSINESS_UNIT_KEY = CL.BUSINESS_UNIT_KEY
LEFT JOIN 
DIMENSION.PRODUCT P
	ON CL.CLIENT_KEY = P.CLIENT_KEY
LEFT JOIN 
DIMENSION.ESTIMATE E
	ON P.PRODUCT_KEY = E.PRODUCT_KEY
INNER JOIN
DIMENSION.CAMPAIGN C
	ON C.CLIENT_KEY = CL.CLIENT_KEY
LEFT JOIN 
DIMENSION.MEDIA M
	ON C.MEDIA_KEY = M.MEDIA_KEY
INNER JOIN 
DIMENSION. ORDER_DETAIL OD
	ON C.CAMPAIGN_KEY = OD.CAMPAIGN_KEY
AND E.ESTIMATE_KEY = OD.ESTIMATE_KEY
INNER JOIN 
DIMENSION.BILLING_PERIOD BP
	ON OD.ORDER_KEY = BP.ORDER_KEY
INNER JOIN
FACT.ORDER_COST OC
	ON OC.BILLING_PERIOD_KEY = BP.BILLING_PERIOD_KEY


DIMENSIONORDER_DETAIL

ORDER_KEY

AGENCY_KEY,

CAMPAIGN_KEY,

CLIENT_KEY,

COST_CATEGORY_KEY,

ESTIMATE_KEY,

MEDIA_KEY,

ORDER_PARTNER_KEY,

PAYEE_KEY,

SUPPLIER_KEY

DIMENSION.AGENCY A
INNER JOIN 
DIMENSION.BUSINESS_UNIT BU
	ON A.AGENCY_KEY = BU. AGENCY_KEY
INNER JOIN
DIMENSION.CLIENT CL
	ON BU.BUSINESS_UNIT_KEY = CL.BUSINESS_UNIT_KEY
LEFT JOIN 
DIMENSION.PRODUCT P
	ON CL.CLIENT_KEY = P.CLIENT_KEY
LEFT JOIN
DIMENSION.ESTIMATE E
	ON P.PRODUCT_KEY = E.PRODUCT_KEY
INNER JOIN
DIMENSION.CAMPAIGN C
	ON C.CLIENT_KEY = CL.CLIENT_KEY
LEFT JOIN 
DIMENSION.MEDIA M
	ON C.MEDIA_KEY = M.MEDIA_KEY
INNER JOIN 
DIMENSION. ORDER_DETAIL OD
	ON C.CAMPAIGN_KEY = OD.CAMPAIGN_KEY
	AND E.ESTIMATE_KEY = OD.ESTIMATE_KEY


DIMENSIONORDER_PARTNERORDER_PARTNER_KEYENTERPRISE_KEY
DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.ORDER_PARTNER OP
	ON OP.ORDER_PARTNER_KEY = OD.ORDER_PARTNER_KEY


DIMENSIONPAYEEPAYEE_KEYENTERPRISE_KEY
DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.PAYEE P
	ON P.PAYEE_KEY = OD.PAYEE_KEY


DIMENSIONPREFERRED_VENDORPREFERRED_VENDOR_KEY

ENTERPRISE_KEY,

MEDIA_KEY,

PAYEE_KEY,

SUPPLIER_KEY


DIMENSIONPRODUCTPRODUCT_KEYCLIENT_KEY
DIMENSION.PRODUCT P
INNER JOIN 
DIMENSION.ESTIMATE E
	ON P.PRODUCT_KEY = E.PRODUCT_KEY


DIMENSIONPURCHASE_ORDER

PURCHASE_ORDER_KEY

CLIENT_KEY

DIMENSIONROLE_PERMISSION

ROLE_PERMISSION_KEY

ENTERPRISE_KEY

DIMENSIONSUPPLIER

SUPPLIER_KEY

ENTERPRISE_KEY,

MEDIA_KEYS

WITH CTE_SUPPLIER AS (
SELECT s_flat.value as MEDIA_KEY, s.*
	FROM DIMENSION.SUPPLIER s,
	LATERAL FLATTEN(input => s.MEDIA_KEYS) s_flat)
SELECT S.*
FROM 
DIMENSION.CAMPAIGN C
LEFT JOIN 
DIMENSION.MEDIA M
	ON C.MEDIA_KEY = M.MEDIA_KEY
INNER JOIN 
DIMENSION. ORDER_DETAIL OD
	ON C.CAMPAIGN_KEY = OD.CAMPAIGN_KEY
LEFT JOIN 
CTE_SUPPLIER S
	ON S.SUPPLIER_KEY = OD.SUPPLIER_KEY
	AND S.MEDIA_KEY = M.MEDIA_KEY


DIMENSIONTAX

TAX_KEY,

ENTERPRISE_KEY,

GROUP_KEY,

DIMENSION.PAYEE P
LEFT JOIN
DIMENSION.TAX T
	ON T.GROUP_KEY = P.TAX_GROUP_KEY
WHERE
	T.GROUP_KEY IS NOT NULL


DIMENSIONTERMS_AND_CONDITION

TERMS_AND_CONDITION_KEY

AGENCY_KEY,

BUSINESS_UNIT_KEY,

CLIENT_KEY,

ENTERPRISE_KEY,

MEDIA_KEY,

ORDER_PARTNER_KEY


DIMENSIONUSER

USER_KEY

ENTERPRISE_KEY




  • No labels
Provide feedback on this article
You are evaluating Refined.