Overview: CWDirect retains comprehensive data on the transactions and other activities that make up your business. This wealth of data is essential to support a range of business processes, and it is structured in a way to facilitate extensive transaction processing; however, it can be cumbersome for you to select and analyze the information you need for strategic decision-making, especially if you need to consider the same body of information from multiple viewpoints. CWData provides a means to extract key information for your company, consolidating and transforming it so that you can perform powerful, efficient analysis, because it facilitates a multi-dimensional view of the data.
For example, the inquiries and reports available in CWDirect allow you to review the specifics of order activity for production purposes. You might use the data warehouse to monitor trends and tendencies in order activity because it enables you to view the same data from geographic, demographic, or seasonal perspectives.
What is CWData? CWData is MICROS’s business intelligence solution, consisting of:
• the processes in CWDirect enabling you to extract the key data (that is, the files and fields that MICROS has identified for inclusion in the data warehouse)
• the staging files you populate on the iSeries for transfer to the CWData Server
• the processes you use on the CWData Server to transform the data and populate the data warehouse
• the data warehouse, which holds the data for strategic analysis
• sample reports
For more information: See the CWData user reference.
In this chapter:
• Concepts
How is a data warehouse different from the CWDirect database? The data you need to collect and retain to run your business is operational in nature. For example, when you ship an order to a customer, the system retains the details regarding shipment and billing so that you can answer such questions as:
• When did we ship the order?
• Can we process a return or exchange against the order?
• What is the effect on inventory?
You can also ask questions that are more global or strategic in nature if you have a report or query designed to answer that specific question.
The information a data warehouse provides is strategic in nature. While many of the specifics of a customer’s or item’s history are retained, it is also possible to easily view the same body of information from multiple perspectives. You can use the data warehouse to analyze trends, store historical data, and get a snapshot of a company’s key performance indicators (KPI). For example, you can use the data warehouse to answer such questions as:
• Are we getting new customers from an unexpected geographical area?
• How many of the customers in this area are in a particular customer class?
• Is this class concentrated in particular zip codes?
Database design differences: Normalized database design tends to include less redundant information and is more efficient for transaction processing. For example, an order record in CWDirect includes the customer number, but not the customer name, which is stored in the Customer file. Similarly, the customer’s phone numbers are stored in yet a different file. Although this type of design supports quick transaction processing, it can make it cumbersome to construct sophisticated queries, as you may need to specify multiple files to include all relevant information for a single question. When this information is imported into a data warehouse, it can be transformed to best support powerful queries, rather than efficient transaction processing.
Greater efficiency in reporting: Another reason that CWData offers more efficient reporting is that key order activity information is already summarized in the data warehouse. For example, if you would like to generate a report of total dollars shipped by customer, this report can run more quickly in CWData because much of the information is already sub totaled. CWData also enables better efficiency because you are using the dedicated resource of the CWData server, rather than the production environment of CWDirect.
The process you use to populate the data warehouse with the transactional and supporting data in CWDirect involves:
1. In CWDirect, performing an initial load. The initial load extracts the supporting, customer, and order-related information from the CWDirect files to the DW Staging files on the iSeries. Only key information is extracted for CWData.
2. Transferring the information in the DW Staging files on the iSeries to the tables in the SQL Server cwi_staging database on the CWData server.
3. Transforming the data and propagating it into the tables in the SQL Server cwi_warehouse database. The data in the cwi_warehouse database represents the data warehouse.
4. Once you have populated the cwi_warehouse database, you can transfer updates to data in CWDirect to the cwi_warehouse database on a periodic basis:
• Capture transactions in the DW Transaction file for subsequent extraction.
• Periodically, extract the transactional information and other supporting information, and populate the DW Staging files. Typically, this is part of your daily process.
• Transfer the information in the DW staging files from the iSeries to the tables in the cwi_staging database on the CWData server.
• Transform the data and propagate it into the tables in the cwi_warehouse database.
5. In Microsoft SQL Server OLAP Manager, creating the cwi_warehouse database on your OLAP server so that you can view the analysis cubes. A cube is a set of data that is typically built from a subset of a data and is organized and summarized into a multidimensional structure using dimensions and summary data. A cube allows you to query data without writing complex queries. You can manipulate the various dimensions used to create the cube to get to the pre-calculated summary data, also called aggregations) that is created for each cube.
6. You can run reports and queries to review the data in the cwi_warehouse database. Normally, you would use a data presentation tool to query the data warehouse. For example, you might use:
• Cognos ImpromptuTM1: Create listings which summarize data and also permit you to “drill down” to the detail level. You can also create a prompt window to specify the selection criteria for a report.
• Cognos PowerPlayTM: When you create a report, you can specify the dimensions to include. Then, when you work with the report, you can easily view the data from various dimensions.
Important: CWData does not support special characters (for example: # , % * &). Before you transfer information from CWDirect to CWData, you should remove any special characters from your data.
Flowchart: This flowchart displays the flow of data from the CWDirect database to the data warehouse.
The CWData data warehouse consists of:
• tables in the cwi_warehouse database. These tables are populated by data from your CWDirect database.
• reference tables: contain the data that makes up the foundation of the cwi_warehouse database. These tables serve as the source input to dimension tables, summary tables, and multi-dimensional OLAP cube structures.
• dimension tables: contain data from reference tables which you can use to view the summarized data in the fact tables.
• fact tables: reference the dimensional tables, allowing you to view the data from different perspectives.
• star schemas: contain a combination of fact tables and dimension tables, providing multi-dimensional views of the data.
• analysis cubes: the main objects in online analytical processing (OLAP), providing you with access to data in the cwi_warehouse database. A cube is a set of data that is typically built from a subset of a data and is organized and summarized into a multidimensional structure using dimensions and summary data. A cube allows you to query data without writing complex queries. You can manipulate the various dimensions used to create the cube to get to the pre-calculated summary data that is created for each cube.
Reference tables contain the data that makes up the foundation of the cwi_warehouse database. The reference tables provided in CWData are described in the table below.
Reference Table |
Attributes |
accounting_period |
Contains accounting periods. |
add_reason_lkup |
Contains add reason codes. |
additional_charge_lkup |
Contains additional charge codes. |
ar_open_item |
Contains A/R open item numbers and history. |
ar_payment_detail |
Contains A/R payment history. |
ar_type |
Contains A/R type codes. |
bank_lkup |
Contains bank codes. |
business_line_lkup |
Contains business line codes. |
buyer_lkup |
Contains buyer codes. |
cancel_reason_lkup |
Contains cancel reason codes. |
company |
Contains company codes and addresses. |
correspondence_history |
Contains customer correspondence history. |
country_lkup |
Contains country codes. |
customer |
Contains customer numbers, names, addresses, and history. |
customer_affinity |
Contains customer affinity preferences. |
customer_billto |
Contains customer bill to numbers, names, addresses, and history. |
customer_catalog_request |
Contains customer catalog requests. |
customer_class_lkup |
Contains customer class codes. |
customer_email |
Contains customer email addresses. |
customer_entity |
Contains customer entity history. |
customer_individual |
Contains customer individual numbers and names. |
customer_membership |
Contains customer membership codes and history. |
customer_ownership |
Contains information on the products a customer owns or previously owned. |
customer_profile |
Contains customer profile codes. |
customer_shipto |
Contains customer ship to numbers, names, addresses, and history. |
customer_shipto_entity |
Contains customer ship to entity history. |
customer_warranty |
Contains customer warranty numbers. |
dispute_reason_lkup |
Contains dispute reason codes. |
division |
Contains division codes. |
ecommerce_category_lkup |
Contains ecommerce category codes. |
entity |
Contains entity numbers and addresses. |
exchange_reason_lkup |
Contains exchange reason codes. |
geographic_zone_lkup |
Contains geographic zone codes. |
geography |
Contains country codes, state codes, city names, postal codes. |
hazard_lkup |
Contains hazard codes. |
item |
Contains item numbers. |
item_attrib_value_lkup |
Contains item attribute values. |
item_attribute_assignment |
Contains item attribute types and values. |
item_attribute_lkup |
Contains item attribute codes. |
item_category_lkup |
Contains item category codes. |
item_class_lkup |
Contains item class codes. |
item_cycle_lkup |
Contains item cycle codes. |
item_ecommere_category |
Contains ecommerce categories. |
item_offer |
Contains item offer values. |
item_status_lkup |
Contains item status codes. |
item_transaction_activity |
Contains item transaction history. |
item_transaction_code_lkup |
Contains item transaction codes. |
item_trx_reason_lkup |
Contains item transaction reason codes. |
language_lkup |
Contains language codes. |
list_source_lkup |
Contains list source codes. |
location_class_lkup |
Contains location class codes. |
long_sku_class_lkup |
Contains long SKU class codes. |
long_sku_dept_lkup |
Contains long SKU department codes. |
long_sku_div_lkup |
Contains long SKU division codes, including the associated long SKU department code. |
mail_call_lkup |
Contains mail/call codes. |
manifest_audit |
Contains manifest history. |
offer |
Contains offer codes. |
order_additional_charge |
Contains order additional charges. |
order_detail |
Contains order detail information. |
order_detail_activity |
Contains order detail history. |
order_detail_activity_lkup |
Contains order line activity codes. |
order_header |
Contains order header information. |
order_payment |
Contains order payment information. |
order_payment_activity |
Contains order payment history. |
order_shipto |
Contains order ship to information. |
order_type_lkup |
Contains order type codes. |
parent_company_code_lkup |
Contains company codes. |
pay_type_lkup |
Contains pay type codes. |
prep_code_lkup |
Contains prep codes. |
price_override_reason_lkup |
Contains price override reason codes. |
profile_data_lkup |
Contains profile codes. |
promotion_lkup |
Contains promotion codes. |
recency_lkup |
Contains recency codes. |
return_reason_lkup |
Contains return reason codes. |
salesman_lkup |
Contains salesman numbers. |
season_lkup |
Contains season codes. |
shipper_lkup |
Contains shipper codes. |
sku |
Contains SKU codes. |
sku_cross_reference |
Contains SKU cross reference codes. |
sku_element_1_lkup |
Contains SKU element 1 codes. |
sku_element_2_lkup |
Contains SKU element 2 codes. |
sku_element_3_lkup |
Contains SKU element 3 codes. |
sku_offer |
Contains SKU offer values. |
sku_upc |
Contains SKU UPC codes. |
sold_out_lkup |
Contains soldout codes. |
source |
Contains source codes and history. |
source_category_lkup |
Contains source category codes. |
terms |
Contains terms codes. |
vendor |
Contains vendor numbers, names, addresses, and values. |
vendor_activity |
Contains purchase orders. |
warehouse |
Contains warehouse codes and addresses. |
Dimension tables provide different perspectives from which to view the data in the fact tables. The dimension tables provided in CWData are described in the table below:
Dimension Table |
Attributes |
add_reason_dim |
Contains data from the company and add_reason_lkup tables. • add reason code and description • company number and name |
buyer_dim |
Contains data from the company and buyer_lkup tables. • buyer code and name • company number and name |
calendar_time_period_dim |
Contains data from the time_period table. • calendar weekday • calendar week • calendar month • calendar quarter • calendar year |
cancel_reason_dim |
Contains data from the company and cancel_reason_lkup tables. • cancel reason code and description • company number and name |
company_dim |
Contains data from the company table. • company number and name • company address (street, city, state, postal code, country code) • scf code |
customer_dim |
Contains data from the customer and company tables. • customer number • customer company • customer name (prefix, first name, middle initial, last name, suffix) • customer address (street, apartment, city, state, postal code, country code) • scf code • email address • active since date • company number and name |
exchange_reason_dim |
Contains data from the company and exchange_reason_lkup tables. • company number and name • exchange reason code and description |
fiscal_time_period_dim |
Contains data from the time_period table. • time dimension • fiscal year • fiscal quarter • fiscal month • fiscal week |
geography_dim |
Contains data from the geography table. • country code and name • province or state code and name • scf code • postal code |
item_transaction_code_dim |
Contains data from the company and item_transaction_code_lkup tables. • transaction code and description • company number and name |
marketing_dim |
Contains data from the company, offer, source, and source_category_lkup tables. • source code and description • source type • source first order date and last order date • source mailing date • source drop code • source category code and description • source net circulation quantity • entity number, name, and address (street, city, state, postal code, scf code, country code) • division code and description • division bank code • company number and name |
offer_dim |
Contains data from the company, offer, source, and source_category_lkup tables. • source code and description • source type • source first order date and last order date • source mailing date • source drop code • source category code • source net circulation quantity • offer code and description • offer insert cost and publication cost • offer page count • offer start date and stop date • offer first order date • offer square inches • offer season code • offer currency code • offer default source code • offer internet offer flag • offer sort code • company number and name |
order_type_dim |
Contains data from the company and order_type_lkup tables. • order type code and description • company number and name |
product_dim |
Contains data from the sku, item_category_lkup, item_status_lkup, and long_sku_class_lkup tables. • sku code and description • sku elements 1, 2, and 3 • sku item category code and description • sku item status code and description • Item class • sku long sku (color, size, width, style, vendor) • sku long sku class number and description • sku standard cost • sku last purchase cost • sku average cost • sku lowest price • sku list price • sku first order date and last order date • sku unit of measure • sku second language description • sku retail reference number • item code and description • item entity number • item class code and description • item long sku department number and description • item long sku class code and description • Item category • item drop ship flag • item allow sku flag • item long sku (style, vendor) |
|
• item second language description • buyer code and name • item cycle code and description • item status code and description • item unit of measure • item vendor number and manufacturing vendor number • item location class and description • company number and name |
reason_dim |
Contains data from the company, exchange_reason_lkup, and return_reason_lkup tables. • reason code and description • company number and name |
return_reason_dim |
Contains data from the company and return_reason_lkup tables. • return reason code and description • company number and name |
vendor_dim |
Contains data from the company and vendor tables. • vendor number and name • vendor address (street, city, state, postal code, scf code, country code) • remit name and address • vendor type • company number and name |
warehouse_dim |
Contains data from the company and warehouse tables. • warehouse code and name • warehouse address (street, city, state, postal code, scf code, country code) • company number and name |
These tables serve as the summarized views of the data which optimize retrieval and allow you to make strategic business decisions.
Fact Table |
Attributes |
order_type_activity |
Contains order history information by company, order type, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
business_line_activity |
Contains order history information by company, business line, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
c_entity_activity |
Contains order history information by customer, entity and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
c_entity_itemclass_activity |
Contains order history information by customer, entity, item class, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
c_entity_longsku_activity |
Contains order history information by customer, entity, long SKU department, long SKU class, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
cs_entity_activity |
Contains order history information by customer ship to, entity, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
cs_entity_itemclass_activity |
Contains order history information by customer ship to, entity, item class, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
cs_entity_longsku_activity |
Contains order history information by customer ship to, entity, long SKU department, long SKU class, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
vendor_performance |
Contains PO history information by vendor and time period from the vendor_activity table: • PO dollar amount, number, and quantity • cancellations dollar amount and number • over receipt dollar amount, number, and quantity • under receipt dollar amount, number, and quantity • receipt dollar amount, number, and units • number of items late by due date, promise date, cancel date • charge backs dollar amount and units • invoiced POs dollar amount and number • non-invoiced POs dollar amount and number • disbursed amounts |
customer_class_activity |
Contains order history information by company, customer class, geography and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units |
purchase_history |
Contains PO history information by vendor, warehouse, item, offer, and time period from the vendor_activity table: • PO dollar amount, number, and quantity • cancellations dollar amount, number, and quantity • additional charge amount • receipt dollar amount and units • closed PO dollar amount and quantity • currency codes and conversion rates • vendor item • buyer code • item category • item class • long SKU department and class |
customer_purchasing_fact |
Contains order history information by customer, product, marketing, offer, geography, order type, and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts |
return_reason_fact |
Contains return history information by return reason, product, offer, and time period from the order_detail_activity table: • returns dollar amount, number, and units • cost of goods returned |
exchange_reason_fact |
Contains exchange history information by exchange reason, product, offer, and time period from the order_detail_activity table: exchanges dollar amount, number, and units |
cancel_reason_fact |
Contains cancellation history information by cancel reason, product, offer, and time period from the order_detail_activity table: cancellations dollar amount, number, and units |
add_reason_fact |
Contains add reason history information by add reason, product, offer, and time period from the order_detail_activity table: • orders dollar amount, number, and units • discount amounts |
offer_performance_fact |
Contains order history information by company, offer, product and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts |
entity_performance_fact |
Contains order history information by company, marketing and time period from the order_detail_activity table: • cancellations dollar amount, number, and units • exchanges dollar amount, number, and units • orders dollar amount, number, and units • returns dollar amount, number, and units • sales dollar amount, number, and units • soldouts dollar amount, number, and units • cost of goods returned • cost of goods sold • discount amounts |
inventory_tracking_fact |
Contains PO history information by buyer, product, warehouse, vendor and time period from the purchase history table: • cancellations dollar amount and quantity • purchases dollar amount and quantity • receipt dollar amount and quantity |
vendor_performance_fact |
Contains PO history information by company, vendor, geography, and time period from the vendor_activity table: • PO dollar amount, number, and quantity • cancellations dollar amount and quantity • over receipt dollar amount and quantity • under receipt dollar amount, number, and units • receipt dollar amount, number, and units • number of items late by due date, promise date, cancel date • charge back dollar amount and units • invoiced PO dollar amount and number • non-invoiced PO dollar amount and number • numbers and dollars of purchases • disbursed amounts |
backorder_tracking_fact |
Contains backorders history information by offer, vendor, product, and time period from the order_detail_activity table: • order numbers • original backorder quantities • cancellations quantity and units • orders quantity and units |
The star schema is a graphical representation of how the dimensions provide various views of the fact table. A star schema is a structure used to generate cubes.
Add reason tracking star schema: The add reason tracking star schema allows you to view order information from an offer, add reason, product, and chronological perspective.
Backorder tracking star schema: The backorder tracking star schema allows you to view backorder information from a vendor, customer, offer, product, and chronological perspective.
Cancel reason tracking star schema: The cancel reason tracking star schema allows you to view order cancellation information from an offer, cancellation reason, product, and chronological perspective.
Customer purchasing star schema: The customer purchasing star schema allows you to view customer purchasing information from a geographical, order type, customer, product, offer, marketing, and chronological perspective.
Entity performance star schema: The entity performance star schema allows you to view entity performance information from a marketing, company, and chronological perspective.
Exchange reason tracking star schema: The exchange reason tracking star schema allows you to view order exchange information from an offer, exchange reason, product, and chronological perspective.
Inventory tracking star schema: The inventory tracking star schema allows you to view inventory information from a vendor, buyer, product, warehouse, and chronological perspective.
Offer performance star schema: The offer performance star schema allows you to view offer performance information from an offer, company, product, and chronological perspective.
Return reason tracking star schema: The return reason tracking star schema allows you to view order return information from an offer, return reason, product, and chronological perspective.
Vendor performance star schema: The vendor performance star schema allows you to view vendor performance information from a vendor, company, geographical, and chronological perspective.
Analysis cubes are the main objects in online analytical processing (OLAP), which provides you with access to data in the cwi_warehouse database. A cube is a set of data that is typically built from a subset of a data and is organized and summarized into a multidimensional structure using dimensions and summary data.
A cube allows you to query data without writing complex queries. You can manipulate the various dimensions used to create the cube to get to the pre-calculated summary data, also called aggregations) that is created for each cube.
Each cube has a schema. A schema is a graphical representation of the tables joined to create the cube structure in the data warehouse. The cube draws data from these joined tables and combines the various attributes for data analysis. The central table in the schema is the fact table, the source of the cube’s statistical data. The dimension tables serve as a way to break the statistical data down into smaller more focused parts for analysis. Dimensions are an extremely useful tool because they allow you to ask questions at a high level and then expand the dimension hierarchy to get to the level of detail necessary.
Examples: These examples demonstrate how the Time Period dimension can be further broken down from the calendar year dimension into more specific, quarter, month, week, and day. This allows you to drill down to obtain the data necessary for analysis.
Example 1: In this example, you can view:
• fiscal year 2001: 93 orders were taken for a total of $2,700.81
• quarter 4, 2001: 37 orders were taken for a total of $1,128.25
• October of quarter 4, 2001: 35 orders were taken for a total of $1,089.31
• week 4 of October quarter 4, 2001: 16 orders were taken for a total of $499.68
• 2 orders were taken on Wednesday for $59.90
• 14 orders were taken on Friday for $438.78
Note: The quarters, months, weeks, and days are defined in the cwi_fiscal_period_configuration script. The default values for this script are: January = 01, Sunday = 1, first calendar week = 01.
Example 2: In this example, you can drill down into more than just the time dimension. You can view the data by:
• all companies
• for customers in the 017 SCF range
• for the mail order entity
• for item numbers 1090 - 7820
• for long SKU department 1015958
• for long SKU class
• for fiscal year 2001, quarter 4, last Monday in December
The analysis cubes delivered with CWData are:
• Add Reason Tracking
• Backorder Reason Tracking
• Cancel Reason Tracking
• Customer Purchasing
• Entity Performance
• Exchange Reason Tracking
• Inventory Tracking
• Item Offer
• Offer Performance
• Order Shipment
• Return Reason Tracking
• Vendor Performance
The relational model of the cwi_warehouse database is summarized in the following charts. These relationships make up the foundation of the data warehouse.
The common reference tables contain basic reference information that is used by the other tables in the cwi_warehouse database. The common reference tables are:
• time_period: additionally, the time period table references the order_payment_activity table, vendor_performance table, and purchase_history table
• warehouse: additionally, the warehouse table references the purchase_history table and item_transaction_activity table
• offer: additionally, the offer table references the purchase_history table, item_offer table, sku_offer table, and order_header table
• source
• company: additionally, the company table references the manifest_audit table, customer_billto table, order_header table, vendor table, item table, customer table, and ar_open_item table
• entity
• division
The accounts receivable tables are:
• ar_open_item: additionally, the ar open item table references the company table
• ar_payment_detail
The customer tables are:
• customer: additionally, the customer table references the company table and order header table
• customer_entity
• customer_individual
• customer_profile
• customer_membership
• customer_warranty
• customer_billto
• customer_shipto
• customer_shipto_entity
• customer_affinity
• customer_catalog_request
• customer_ownership
The inventory tables are:
• item: additionally, the item table references the company table
• item_offer: additionally, the item offer table references the offer table
• sku_offer: additionally, the sku offer table references the offer table
• sku: additionally, the sku table references the purchase history table
• item_transaction_activity: additionally, the item transaction activity table references the warehouse table
• sku_upc
• item_ecommerce_category
• item_attribute_assignment
The manifesting table is manifest_audit. Additionally, the manifest_audit table references the company table.
The order tables are:
• order_header: additionally, the order_header table references the customer table, offer table, and company table
• order_shipto
• order_additional_charge
• order_detail
• order_detail_activity
• order_payment
• order_payment_activity: additionally, the order_payment_activity table references the time_period table
The vendor tables are:
• vendor: additionally, the vendor table references the company table and vendor_activity table
• vendor_performance: additionally, the vendor_performance table references the time_period table
• purchase_history: additionally, the purchase_history table references the sku table, warehouse table, and offer table
Aggregation: Summarized facts, such as total dollars ordered, in a data warehouse.
Business intelligence describes the range of tools and technologies available to help you analyze data and make strategic business decisions.
Cubes are structures that include multi-dimensional data. A star schema is a structure used to generate cubes.
Data dictionary: Lists each file and field that makes up CWData, including how the business rules that govern how it is calculated.
Data mining consists of analyzing data to discover new relationships.
Data Warehouse staging files are used by CWDirect to collect key information for download to CWData. Each file’s name and description begins with the letters DW; for example, the DW Accounting Period file (DWACTP) captures information on CWDirect accounting periods. Transaction information, such as orders or updates to customer records, are temporarily stored in the DW Transaction file. You run a periodic extraction to populate these files, and then download them to the CWData server, where they are then added to the data warehouse.
Data warehouses are databases that serve as repositories of information for query and analysis.
Dimensions are structural attributes or members of a star schema, all of which are of a similar type in the user’s perception of the data. For example, months, quarters, and years, make up a time dimension.
Fact table: The table of information that makes up the center of a star schema.
File: A related collection of records in CWDirect. When referring to a collection of records in the CWData database, the term table is used.
Online analytical processing (OLAP) enables you to review information for the purpose of analysis and decision-making. The information is organized in such a way that you can view it from multiple dimensions. For example, you might want to view a segment of your customers from geographical, demographic, or order history perspectives.
Online transaction processing (OLTP) enables you to process, manage, and track the various types of transactions that make up your business.
Star schema: A data model made up of a single table of facts, such as number of units sold, unit price, or total sales dollars, which can be viewed from multiple dimensions. The star schema contains relationships, while the cube contains data.
Table: A related collection of records in the CWData database. When referring to a collection of records in the CWDirect database, the term file is used.
- These applications, and other applications referenced in this document, are trademarked or service-marked by their respective manufacturers.
| Part J: CWData | Contents | SCVs | Search | Glossary | Reports | XML | Index | Chapter 97: Working with the Data Warehouse Integration in CWDirect |
SO11_01 CWDirect 18.0 August 2015 OTN