---
title: "OneStrata Data Warehouse"
canonical: "https://hub.freewheel.tv/space/OS/113672265/OneStrata%20Data%20Warehouse"
format: markdown
---
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: Option Description Requirements 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 Option Connection Options Unload 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. The  COPY INTO <location>  command for unloading data into a cloud storage location. Tables This section provides information about the tables in the OneStrata Data Warehouse: Schema Table Description DIMENSION AD_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. DIMENSION AD_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. DIMENSION AGENCY Contains information about your Agency records. An Agency represents an advertising agency, and is the highest level entity within the business structure. DIMENSION BILLING_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. DIMENSION BUSINESS_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. DIMENSION CAMPAIGN Contains information about your media campaigns. DIMENSION CLIENT Contains information about your Client records. A Client represents the advertiser that an Agency or Business Unit is planning and buying media for. DIMENSION CLIENT_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. DIMENSION COST_CATEGORY Contains information about your cost categories. A cost category represents a way that an agency might structure costs for billing or reporting. FACT DELIVERY Contains information about your delivered units and unit types. For committed units and unit types, see the  MEDIA_LINE_METRICS  view. DIMENSION ENTERPRISE Contains information about your OneStrata enterprise. DIMENSION ESTIMATE 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,  DIMENSION EXCHANGE_RATE Contains information about your Exchange Rate records. An Exchange Rate record defines the rate at which one currency is converted to another currency. DIMENSION FLIGHT_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. DIMENSION MEDIA Contains information about your Media Types. A Media Type represent an agency's custom media types, for example  Social  or  Programmatic . FACT MEDIA_LINE_METRICS Contains information about your committed units and unit types. For delivered units and unit types, see the  DELIVERY  view. FACT ORDER_COST Contains information about your order costs, at the billing period level. DIMENSION ORDER_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. DIMENSION ORDER_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). DIMENSION PAYEE 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). DIMENSION PREFERRED_VENDOR Contains information about your Preferred Vendors. DIMENSION PRODUCT Contains information about your Product records. A Product represents a Client's product, activity, or brand. DIMENSION PURCHASE_ORDER Contains information about your purchase orders. DIMENSION ROLE_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. DIMENSION SUPPLIER 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). DIMENSION TAX Contains information about your Tax records. A Tax record defines a client tax rate or vendor tax rate. DIMENSION TERMS_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. DIMENSION USER 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: Schema Table Primary Key Foreign Key(s) Example DIMENSION AD_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
 DIMENSION AD_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
 DIMENSION AGENCY AGENCY_KEY AGENCY_PARENT_KEY, ENTERPRISE_KEY DIMENSION.AGENCY A
INNER JOIN 
DIMENSION.BUSINESS_UNIT BU
	ON A.AGENCY_KEY = BU.AGENCY_KEY DIMENSION BILLING_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
 DIMENSION BUSINESS_UNIT BUSINESS_UNIT_KEY ENTERPRISE_KEY DIMENSION.BUSINESS_UNIT BU
INNER JOIN 
DIMENSION.CLIENT C
	ON BU.BUSINESS_UNIT_KEY = C.BUSINESS_UNIT_KEY
 DIMENSION CAMPAIGN 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
 DIMENSION CLIENT CLIENT_KEY ENTERPRISE_KEY DIMENSION. CLIENT C
INNER JOIN 
DIMENSION.PRODUCT P
	ON C.CLIENT_KEY = P.CLIENT_KEY
 DIMENSION CLIENT_PASSBACK CLIENT_PASSBACK_KEY CLIENT_KEY, ENTERPRISE_KEY DIMENSION.CLIENT C
LEFT JOIN 
DIMENSION.CLIENT_PASSBACK CP
	ON C.CLIENT_KEY = CP.CLIENT_KEY
 DIMENSION CONTRACT 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
 DIMENSION COST_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
 FACT DELIVERY 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
 DIMENSION ENTERPRISE ENTERPRISE_KEY DIMENSION.ENTERPRISE E
INNER JOIN 
DIMENSION.AGENCY A
	ON E.ENTERPRISE_KEY = A.ENTERPRISE_KEY DIMENSION ESTIMATE ESTIMATE_KEY ENTERPRISE_KEY, MEDIA_KEY, PRODUCT_KEY DIMENSION EXCHANGE_RATE EXCHANGE_RATE_KEY ENTERPRISE_KEY DIMENSION FLIGHT_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
 DIMENSION MEDIA MEDIA_KEY ENTERPRISE_KEY DIMENSION.CAMPAIGN C
INNER JOIN 
DIMENSION.MEDIA M
	ON M.CAMPAIGN_KEY = C.CAMPAIGN_KEY
 FACT MEDIA_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
 FACT ORDER_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
 DIMENSION ORDER_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
 DIMENSION ORDER_PARTNER ORDER_PARTNER_KEY ENTERPRISE_KEY DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.ORDER_PARTNER OP
	ON OP.ORDER_PARTNER_KEY = OD.ORDER_PARTNER_KEY
 DIMENSION PAYEE PAYEE_KEY ENTERPRISE_KEY DIMENSION.ORDER_DETAIL OD
LEFT JOIN 
DIMENSION.PAYEE P
	ON P.PAYEE_KEY = OD.PAYEE_KEY DIMENSION PREFERRED_VENDOR PREFERRED_VENDOR_KEY ENTERPRISE_KEY, MEDIA_KEY, PAYEE_KEY, SUPPLIER_KEY DIMENSION PRODUCT PRODUCT_KEY CLIENT_KEY DIMENSION.PRODUCT P
INNER JOIN 
DIMENSION.ESTIMATE E
	ON P.PRODUCT_KEY = E.PRODUCT_KEY
 DIMENSION PURCHASE_ORDER PURCHASE_ORDER_KEY CLIENT_KEY DIMENSION ROLE_PERMISSION ROLE_PERMISSION_KEY ENTERPRISE_KEY DIMENSION SUPPLIER 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
 DIMENSION TAX 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
 DIMENSION TERMS_AND_CONDITION TERMS_AND_CONDITION_KEY AGENCY_KEY, BUSINESS_UNIT_KEY, CLIENT_KEY, ENTERPRISE_KEY, MEDIA_KEY, ORDER_PARTNER_KEY DIMENSION USER USER_KEY ENTERPRISE_KEY