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

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.
Datawarehouse Export Column Definitions

FOR ORDERS


autoseq
If the order is part of an continuity sequence then the cycle number, otherwise blank
AutoShipDate
blank
BillAddr1
Bill to address for the order
BillAddr2
Bill to address 2 for the order
BillCity
Bill to city for the order
BillFName
Bill to first name for the order
BillLName
Bill to last name for the order
BillPhone
Bill to phone number for the order
BillState
Bill to state for the order
BillZip
Bill to postal code for the order
CampaignName
Campaign of the source of the order
cctype
When the payment is a credit card then the credit card type, otherwise blank
checktype
When the payment is 'telecheck' then 1, when 'check' then 2, when 'money order' then 3 otherwise blank
Client
Client of the product
custemail
Customer email address
custnum
Customer ID of the customer
CustStatus
The current customer status
datecreated
The system entry date of the order
dateordered
The order date of the order, initial order date for continuity generated orders
DateProc
The last payment transaction date for the item, can be approved or declined
DateShip
The shipment date for the item
DayCreated
The difference in days between the minimum order date in OLX and datecreated + 1
DayOrdered
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
DayShipped
The difference in days between the minimum order date in OLX and DateShip + 1
dnis
DNIS of the source of the order
enteredby
Employee number of the employee who took the order
ExtSKU
The external sku of the item
firstn
First name of the employee who took the order
Gender
Gender of the customer, if available
hold
When the item is in regular hold status then 3 otherwise 1
holdstatus
When the item is in regular hold status with a release date then 'on hold until' release date, otherwise blank
importordernumber
The import id of the order if available
iscredit
0 if the item is a debit item, 1 if the item is a credit item
ItemCommissionTo
Employee number of the commission to employee of the item
itemid
OrderDetail ID of the item
lastn
Last name of the employee who took the order
MerchantTransactionNumber
Merchant transaction number for the last payment transaction of the item
NumDeclines
The number of declined transactions for the item
orderid
Order ID of the order
orderno
Web order number of the order
orderstatus
blank
original
blank
originalsoldby
Employee number of the employee who took the call for the order
paymentmethod
Current payment method for the order
PayProcAcct
Name of the payment processing account used in the last payment transaction for the item, if available
price
Quantity * UnitPrice of the item, negative when it is a credit item
prodcode
Product name of the item
proddesc
Product description of the item
prodgroup
Product group of the item
prodofferid
Product offer id of the item
productoffer
Product offer name of the item
prom
Media type of the source of the order
promocode
Promo code of the order
qty
Quantity of the item, negative if a credit item
Script
Script of the source of the order
shipping
Shipping of the item, negative if a credit item
shipstate
Ship to state of the order
shipzip
Ship to postal code of the order
soldby
Employee number of the sold by employee of the item
status
Item status of the item
tax
Tax of the item, negative if a credit item
team
Team of the employee who took the order
total
(Quantity * UnitPrice) + Shipping + Tax of the item, negative if a credit item
type
Item type of the item
unitcount
Unit count of the item
validationhold
If the item is in invalid status then 1, otherwise 0
voiceprintid
The recording id of the order if available

FOR PENDING AUTOSHIPMENTS


autoseq
Times already shipped + 1 for the continuity 
AutoShipDate
Next ship date of the continuity 
BillAddr1
Bill to address of the continuity profile
BillAddr2
Bill to address 2 of the continuity profile
BillCity
Bill to city of the continuity profile
BillFName
Bill to first name of the continuity profile
BillLName
Bill to last name of the continuity profile
BillPhone
Master customer record phone number
BillState
Bill to state of the continuity profile
BillZip
Bill to postal code of the continuity profile
CampaignName
Campaign of the source of the original order
cctype
When the payment method of the continuity profile is a credit card then the credit card type, otherwise blank
checktype
When the payment method of the continuity profile is 'telecheck' then 1, when 'check' then 2, when 'money order' then 3 otherwise blank
Client
Client of the product of the continuity item to be generated
custemail
Customer email address
custnum
Customer ID of the customer
CustStatus
The current customer status
datecreated
blank
dateordered
The initial order date
DateProc
blank
DateShip
blank
DayCreated
blank
DayOrdered
The difference in days between the minimum order date in OLX and the base order date + 1
DayProcessed
blank
DayShipped
blank
dnis
DNIS of the source of the original order
enteredby
Employee number of the employee who entered the original order
ExtSKU
The external sku of the continuity item to be generated
firstn
First name of the employee who entered the original order
Gender
Gender of the customer, if available
hold
blank
holdstatus
blank
importordernumber
blank
iscredit
0
ItemCommissionTo
Employee number of the commission to employee for the continuity 
itemid
blank
lastn
Last name of the employee who entered the original order
MerchantTransactionNumber
blank
NumDeclines
0
orderid
blank
orderno
blank
orderstatus
blank
original
blank
originalsoldby
The employee who took the call for the original order
paymentmethod
The payment method of the continuity profile
PayProcAcct
blank
price
Quantity * UnitPrice of the continuity  item to be generated
prodcode
Product name of the continuity item to be generated
proddesc
Product description of the continuity item to be generated
prodgroup
Product group of the continuity item to be generated
prodofferid
Product offer ID of the continuity item to be generated
productoffer
Product offer name of the continuity  item to be generated
prom
The media type of the source of the base order
promocode
Promo code of the original order
qty
Quantity of the continuity item to be generated
Script
The script of the source of the base order
shipping
Shipping of the continuity item to be generated
shipstate
Ship to state of the continuity profile
shipzip
Ship to postal code of the continuity profile
soldby
Employee number of the employee who sold the continuity 
status
When the continuity is stopped or on old then 'Cancelled continuity ' otherwise 'Pending Autoshipment'
tax
0
team
Team of the employee who entered the original order
total
(Quantity * UnitPrice) + Shipping + Rush Fee for the continuity item to be generated
type
The item type of the base item
unitcount
Unit count of the continuity item to be generated
validationhold
blank
voiceprintid
blank

FILTERS

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.          

GROUPING 

No
    • 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 ...
    • 5.1.36 - Make Client Filter a Required Field for DW Exports

      Version: 5.1 Patch: 036 Title: Make Client Filter a Required Field for DW Exports Application Category: Reports; Other Patch Type: Bug Description: Data Warehouse & Flattened Data Warehouse exports need to have clients selected in order for client ...
    • 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 ...