Datawarehouse Export Definitions

Datawarehouse Export Definitions

Module: Reports
Path: Reports > Processing Reports > Data Warehouse Export 
Topic: Data Warehouse Export 
Version(s):  5.1 and up
Editions: All


What Is The Data Warehouse Export?

The Data warehouse Export and the Flattened Export are both very valuable reports in OrderLogix. The reports allow you to retrieve data in a variety of combinations, some of which may not be available from other default reports. Both reports allow you to select from multiple date filters, item statuses, and product values, as well as allowing you to select exactly which columns you would like displayed. 
You can setup default columns to be displayed when creating new Data Warehouse and Flattened Exports by reviewing the article "How to Set Default Columns for the Datawarehouse Export and the Flattened Export"

Setup

Navigate to Reports > Processing Reports > Data Warehouse Export


Tool Bar

Utilizing the buttons on the tool bar  you can:
Go back to the previous screen 
Print 
Minimize Tax Account module 

Schedule


Review Automated Schedule and Process Alerts for more information on how to set this section up.

Show Schedules



Show Upcoming

Review Show Upcoming for more information on details found here.


Configuration

Selection

Directly under the Configuration > Selection tab you will see a drop down with the options of Data Warehouse Export or Flattened Data Warehouse Export.
Confirm Data Warehouse Export is selected.


Selection > Columns

Review Column Definitions for more precise information on what details will be pulled with the selected column.


Single arrows move highlighted column(s) to the other side
Double arrows move ALL columns to the other side

To select a column to be generated on the report:
  1. Highlight the column name from the Available Columns list 
    1. click the right single arrow to move it to the Selected Columns list
  2. To select multiple columns click on the first column desired and press and hold the CTRL button on your keyboard while selecting additional columns
    1. click the right single arrow to move it to the Selected Columns list
  3. To move all of the columns from Available Columns to Selected Columns click the right double arrows
To move columns out of the Selected Columns list repeat the steps above and click the left facing arrow(s).

Selection > Date Filters



Select the type of date to filter the returned results by.
  1. Order Date - date the order was placed
  2. System Entry Date - date the order entered OLX
  3. Payment Date - date payment was made on the order; approved or declined
  4. Ship/Export Date - date order was shipped or exported
  5. Hold Date - date on hold until for items in regular hold status
  6. Auto Date - next ship date of autoship (continuity/subscription)
  7. Last Item Status Update - date of when the item status changed 
Select the date range:
  1. No Date Filter
  2. User Selected
    1. Fiscal Week - the accounting period
    2. Date Range - day to start looking at data and day to stop looking at data
      1. Click on calendar  icon for easier selection of date(s) needed
  3. Last Week (S) - Date Range automatically set
  4. Last Week (M) - Date Range automatically set
  5. This Quarter - Date Range automatically set
  6. Last Year - Date Range automatically set
  7. This Week (S) - Date Range automatically set
  8. This Week (M) - Date Range automatically set
  9. This Month - Date Range automatically set
  10. This Week To Date (S) - Date Range automatically set
  11. This Week To Date (M) - Date Range automatically set
  12. This Month To Date - Date Range automatically set
  13. Tomorrow - Date Range automatically set
  14. Now to end of week (S) - Date Range automatically set
  15. Now to end of week (M) - Date Range automatically set
  16. Now to end of month - Date Range automatically set
  17. Now to end of year - Date Range automatically set
  18. Last Quarter - Date Range automatically set
  19. This Quarter To Date - Date Range automatically set
  20. Year to Date - Date Range automatically set

Selection > Item Status



Click on the box beside the item status(es) to select for filtering records displayed in report.


Selection > Products


Single arrows move highlighted Product(s) to the other side
Double arrows move ALL Products to the other side

To select a column to be generated on the report:
  1. Highlight the Product name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Products click on the first Product desired and press and hold the CTRL button on your keyboard while selecting additional Products
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Products from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).

Selection > Client


Single arrows move highlighted Client(s) to the other side
Double arrows move ALL  Clients to the other side

To select a column to be generated on the report:
  1. Highlight the Client name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Clients click on the first Client desired and press and hold the CTRL button on your keyboard while selecting additional Clients
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Clients from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).


Selection > Campaign


Single arrows move highlighted Campaign(s) to the other side
Double arrows move ALL  Campaigns to the other side

To select a column to be generated on the report:
  1. Highlight the Campaign name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Campaigns click on the first Campaigns desired and press and hold the CTRL button on your keyboard while selecting additional Campaigns
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Campaigns from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).


Selection > Script


Single arrows move highlighted Script(s) to the other side
Double arrows move ALL  Scripts to the other side

To select a column to be generated on the report:
  1. Highlight the Script name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Scripts click on the first Script desired and press and hold the CTRL button on your keyboard while selecting additional Scripts
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Scripts from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).


Selection > Media/Promo


Single arrows move highlighted Media(s) to the other side
Double arrows move ALL  Media to the other side

To select a column to be generated on the report:
  1. Highlight the Media name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Media click on the first Media desired and press and hold the CTRL button on your keyboard while selecting additional Media
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Media from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).


Selection > Employee


Single arrows move highlighted Employee(s) to the other side
Double arrows move ALL  Employee to the other side

To select a column to be generated on the report:
  1. Highlight the Employee name from the Available list 
    1. click the right single arrow to move it to the Selected list
  2. To select multiple Employee click on the first Employee desired and press and hold the CTRL button on your keyboard while selecting additional Employee
    1. click the right single arrow to move it to the Selected list
  3. To move all of the Employee from Available to Selected click the right double arrows
To move columns out of the Selected list repeat the steps above and click the left facing arrow(s).


Selection > Other


Enter specific criteria to filter returned records by

Other Criteria
  1. Order #
  2. Auto Seq#
  3. Ship Charge Profile
  4. Location 
  5. DNIS
  6. Ship State
Payment Method Criteria
  1. None
  2. American Express
  3. Check
  4. Discover
  5. Invoice
  6. Mastercard
  7. Money Order
  8. Telecheck
  9. Visa
Continuity Type
  1. All Autoshipments
  2. Pending Autoshipments
  3. Cancelled Autoshipments
  4. Held Autoshipments
Please keep in mind autoshipments that are put on hold do NOT automatically release. They must be manually released for processing to continue.


Delivery

You can find more information for the Delivery tab with this article:

Delivery > None



When Delivery Method is set to None the export will run and provide a link to download the file from the job history. The downloadable link requires zip password to access.

The Data Warehouse Export and the flattened Data Warehouse export are just that, “exports”.  They are not standard reports delivered through the SQL Server Reporting Services platform like all our other reports.  Those 2 integration modules perform custom dynamic SQL queries and produce output based on configuration values and data.  We originally put them under the reports menu as an easy way to give power users access to them without having to give them access to the processing menu.

The reason these modules require zip is because of 2 things:

  1. The potential size of the file being downloaded/transferred.
  2. The actual data in the file (Customer names and addresses) is becoming more and more important to treat as sensitive data (ie.. GDPR and PII).

 

Delivery > FTP - SFTP (SSH) - FTPS (SSL)


  1. Delivery Method - select if you will be using FTP, SFTP, or FTPS
  2. Server Address - IP address of server to send the file to that has been whitelisted by Orderlogix
  3. Server Port - port on the IP address listed above
  4. Folder - directory or path for dropping of the files
  5. User Name - same as is being used on the above listed folder
  6. Password - same as is being used on the above listed folder
You will need to make sure that you have requested a firewall rule be made for any IP address outside of Orderlogix your system needs to communicate with.

Delivery > Email

Selecting the Email Delivery Method will display an email template that will send along with the results of the job log.

  1. Delivery Method - select Email
  2. From Address - enter an email that has already been setup in Email Profiles 
  3. Email Address - enter the email you want the job log to send to
  4. CC - add any additional emails here
  5. Subject - enter a subject line to let the recipient know what results they are receiving 
  6. Message - enter the body of the email here with any additional information you would the recipient to have



Encryption

Clicking on the Encryption tab allows you to select which method you would like the results to be encrypted with.


Clicking on the drop down displays 2 options for encryption:

  1. Zip - ZIP supports a simple password-based symmetric encryption system.
  2. PGP - Pretty Good Privacy (PGP) is an encryption program that provides cryptographic privacy and authentication for data communication

ZIP


  1. Encryption Method - select Zip
  2. File Name - designated file name
  3. File Password - this is the same password that must be used when opening the file
  4. Confirm Password - exact match to entry in File Password field above
You'll want to read "Using Zip Encryption & Decryption in OrderLogix" for additional information

PGP


  1. Encryption Method - select PGP
  2. File Name - designated file name
  3. PGP Key Option - drop down will display PGP Keys that have already been setup
OrderLogix supports both file extensions of: .txt or a .asc for the PGP files.



Definitions

This report is for orders in OLX and any continuity orders that are going to be generated from the orders. It is one item per line.

Columns

COLUMN
FOR ORDERS
FOR PENDING AUTOSHIPMENTS
AdCopy
"Ad Copy" from the media schedule
"Ad Copy" from the media schedule
autoseq
The cycle number if the order is part of a continuity sequence, otherwise blank
Times already shipped + 1 for the continuity 
AutoShipDate
blank - only for upcoming continuity orders that are yet to be created
Next ship date of the continuity within the date range selected on filter for orders not yet created.
BillAddr1
Bill to address for the order
Bill to address of the continuity profile
BillAddr2
Bill to address 2 for the order
Bill to address 2 of the continuity profile
BillCity
Bill to city for the order
Bill to city of the continuity profile
BillFName
Bill to first name for the order
Bill to first name of the continuity profile
BillLName
Bill to last name for the order
Bill to last name of the continuity profile
BillPhone
Bill to phone number for the order
Master customer record phone number
BillState
Bill to state for the order
Bill to state of the continuity profile
BillZip
Bill to postal code for the order
Bill to postal code of the continuity profile
CampaignName
Campaign of the source of the order
Campaign of the source of the original order
cctype
When the payment is a credit card then the credit card type, otherwise blank
When the payment method of the continuity profile is a credit card then the credit card type, otherwise blank
checktype
  1. When the payment is 'telecheck' then 1,
  2. When 'check' then 2,
  3. When 'money order' then 3
  4. Otherwise blank
  1. When the payment method of the continuity profile is 'telecheck' then 1,
  2. When 'check' then 2,
  3. When 'money order' then 3
  4. Otherwise blank
Client
Client of the product
Client of the product of the continuity item to be generated
custemail
Customer email address
Customer email address
custnum
Customer ID of the customer
Customer ID of the customer
Custom1
Custom field on the order
Custom field on the original order
Custom2
Custom field on the order
Custom field on the original order
Custom3
Custom field on the order
Custom field on the original order
Custom4
Custom field on the order
Custom field on the original order
Custom5
Custom field on the order
Custom field on the original order
CustStatus
The current customer status
blank - order has not been created yet
datecreated
The system entry date of the order
blank - order has not been created yet
dateordered
The order date of the order, initial order date for continuity generated orders
The initial order date
DateProc
The last payment transaction date for the item, can be approved or declined
blank - order has not been created yet
DateShip
The shipment date for the item
blank - order has not been created yet
DayAutoCancel
The difference in days between the minimum order date in OLX and datecancelled + 1
The difference in days between the minimum order date in OLX and datecancelled + 1
DayCreated
The difference in days between the minimum order date in OLX and datecreated + 1
blank - order has not been created yet
DayCustomerMin
minimum orderdate for that customerId with day 1 to be 12/31/1999
minimum orderdate for that customerId with day 1 to be 12/31/1999
DayOrdered
The difference in days between the minimum order date in OLX and dateordered + 1
The difference in days between the minimum order date in OLX and dateordered + 1
DayProcessed
The difference in days between the minimum order date in OLX and DateProc + 1
blank - order has not been created yet
DayShipped
The difference in days between the minimum order date in OLX and DateShip + 1
blank - order has not been created yet
dnis
DNIS of the source of the order
DNIS of the source of the original order
enteredby
Employee number of the employee who took the order
Employee number of the employee who entered the original order
ExtSKU
The external sku of the item
The external sku of the continuity item to be generated
firstn
First name of the employee who took the order
First name of the employee who entered the original order
Gender
Gender of the customer, if available
Gender of the customer, if available
hold
  1. When the item is in regular hold status then 3
  2. Otherwise 1
0 - order has not been created yet
holdstatus
  1. When the item is in regular hold status with a release date then 'on hold until' release date,
  2. Otherwise blank
blank - order has not been created yet
importordernumber
The import id of the order if available
blank  - order has not been created yet
iscredit
  1. 0 if the item is a debit item,
  2. 1 if the item is a credit item
0 - order has not been created yet
ItemCommissionTo
Employee number of the commission to employee of the item
Employee number of the commission to employee for the continuity 
itemid
OrderDetail ID of the item
0 - order has not been created yet
LastModified
Not including system processes, the last time the order was modified through manual process
blank - order has not been created yet
lastn
Last name of the employee who took the order
Last name of the employee who entered the original order
MediaFormat
"Media Format" from the media schedule
"Media Format" from the media schedule
MediaStartDate
  1. Start Date/Time from the Media Schedule
  2. Defaults to 1/1/1900  12:00:00 AM
  1. Start Date/Time from the Media Schedule
  2. Defaults to 1/1/1900  12:00:00 AM
MediaStartTime
  1. Start Date/Time from the Media Schedule
  2. Defaults to 1/1/1900  12:00:00 AM
  1. Start Date/Time from the Media Schedule
  2. Defaults to 1/1/1900  12:00:00 AM
MerchantTransactionNumber
Merchant transaction number for the last payment transaction of the item
blank - order has not been created yet
NoSolicit
  1. True - customer record is checked,
  2. False - customer record is unchecked
  1. True - customer record is checked,
  2. False - customer record is unchecked
NumDeclines
The number of declined transactions for the item
0 - order has not been created yet
orderid
Order ID of the order
0 - order has not been created yet
orderno
Web order number of the order
blank - order has not been created yet
orderstatus

blank - order has not been created yet
OrderTime
  1. The time of the order date
  2. hh:mm:ss of manually entered orders;
  3. imported orders will display as 00:00:00
blank - order has not been created yet
original


originalsoldby
Employee number of the employee who took the call for the order
The employee who took the call for the original order
paymentmethod
Current payment method for the order
The payment method of the continuity profile
PayProcAcct
Name of the payment processing account used in the last payment transaction for the item, if available
blank - order has not been created yet
price
Quantity * UnitPrice of the item, negative when it is a credit item
Quantity * UnitPrice of the continuity item to be generated
prodcode
Product name of the item
Product name of the continuity item to be generated
proddesc
Product description of the item
Product description of the continuity item to be generated
prodgroup
Product group of the item
Product group of the continuity item to be generated
prodofferid
Product offer id of the item
Product offer ID of the continuity item to be generated
productoffer
Product offer name of the item
Product offer name of the continuity item to be generated
prom
Media type of the source of the order
The media type of the source of the base order
promocode
Promo code of the order
Promo code of the original order
qty
Quantity of the item, negative if a credit item
Quantity of the continuity item to be generated
resultText
  1. response from payment processor regarding sales transaction 
  2. blank for auth only or no payment transaction
blank - order has not been created yet
Script
Script of the source of the order
The script of the source of the base order
ShipAddr1
Ship to address of the order
Ship to address of the continuity profile
ShipAddr2
Ship to address2 of the order
Ship to address2 of the continuity profile
ShipCity
Ship to city of the order
Ship to city of the continuity profile
ShipFName
Ship to first name of the order
Ship to first name of the continuity profile
ShipLName
Ship to last name of the order
Ship to last name of the continuity profile
shipping
Shipping of the item, negative if a credit item
Shipping of the continuity item to be generated
shipstate
Ship to state of the order
Ship to state of the continuity profile
shipTransID
  1. Shipment transaction ID assigned during shipping export or when using mark shipped button from order detail screen
0 - order has not been created yet
shipzip
Ship to postal code of the order
Ship to postal code of the continuity profile
soldby
Employee number of the sold by employee of the item
Employee number of the employee who sold the continuity on original order
StationName
"Station Name" from the media schedule
"Station Name" from the media schedule of original order
status
Item status of the item
  1. When the continuity is stopped or on old then 'Cancelled continuity '
  2. otherwise 'Pending Autoshipment'
tax
Tax of the item, negative if a credit item
0 - order has not been created yet
team
Team of the employee who took the order
Team of the employee who entered the original order
total
(Quantity * UnitPrice) + Shipping + Tax of the item, negative if a credit item
(Quantity * UnitPrice) + Shipping + Rush Fee for the continuity item to be generated
TrackingNumber
  1. Blank - if item has not been ship confirmed
  2. Tracking number for the order
blank - order has not been created yet
type
Item type of the item
The item type of the base item
unitcost
Unit cost set on the base item
Unit cost set on the base item
unitcount
Unit count of the item
Unit count of the continuity item to be generated
validationhold
  1. If the item is in invalid status then 1,
  2. otherwise 0
blank - order has not been created yet
voicelogid
The recording id of the order if available - voiceprintid
blank - order has not been created yet
WarehouseLocation
Warehouse assigned to the item in its current state
blank - order has not been created yet

Filters

Filter Type
Definition
Order Date Range
Order date of the order
System Entry Date Range
System entry date of the order
Payment Date Range
Payment date of the order, can be approved or declined
Ship/Export Date Range
Ship date of the order
Hold Date Range
Hold date of items that are in regular hold status
Auto Date Range
Next ship date of continuity items
Payment Method
The payment method of the order
Single Order #
The order ID
Single Auto Seq. #
The cycle number of continuity generated orders
Product Code
The product offer name of the item
Single DNIS
The DNIS of the source of the order
Single Ship State
The ship state of the order
Single Source Client
The client of the source of the order
Single Source Campaign
The campaign of the source of the order
Single Script
The script of the source of the order
Single Product Group
The product group of the item
Single Media Type
The media type of the source of the order
Single Employee
The employee who entered the order
Single Team
The team of the employee who entered the order
Item Status
The status of the item
Item Type
The type of the item
Showing last item status in date range
This is only when a payment date range is selected. The status of the item will be the last status in the selected date range instead of the current status.    


Copyright 2019
Revised 8.26.2024




    • Related Articles

    • 5.1.105 - Datawarehouse Fixes

      Version: 5.1 Patch: 105 Title: Datawarehouse Fixes Application Category: Other; Reports Patch Type: Bug Description: Fixed issues: - Could not access relative date selections when attempting to schedule Datawarehouse export - Datawarehouse scheduled ...

    • 5.1.178 - Add Warehouse Location to DW Export

      Version: 5.1 Patch: 178 Title: Add Warehouse Location to DW Export Application Category: Other; Reports Patch Type: Feature Description: Added warehouse location to list of available columns on the Datawarehouse export Articles: Datawarehouse Export ...

    • Flattened Export Definitions

      Module: Reports Path: Reports > Processing Reports > Flattened Export  Topic: Flattened Export  Version(s):  5.1 and up Editions: All What Is The Flattened Export? The Data warehouse Export and the Flattened Export are both very valuable reports in ...

    • Continuity Projection Report Definitions

      Module: Reports Path: Reports > Processing Reports > Continuity Projection Report Topic: Continuity Projection Report Version(s): 5.1 and up Editions: All What Is This report displays estimated autoship statistics expected to be shipped in the ...

    • Returns Report Definitions

      Module: Reports Path: Reports > Processing Reports > Returns Report Topic: Returns Report Version(s): 5.1 and up Editions: All What Is Returns Report Returns Report displays the returns for auto-generated (continuity/subscription) and ...