Overview: CWSerenade 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 CWSerenade 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 CWSerenade enabling you to extract the key data (that is, the tables and fields that MICROS has identified for inclusion in the data warehouse)
• the staging tables you populate in the CWSerenade database 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
For more information: See the CWData Reference Guide.
In this topic:
• Concepts
How is a data warehouse different from the CWSerenade 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 CWSerenade includes the customer number, but not the customer name, which is stored in the Customer table. Similarly, the customer’s phone numbers are stored in yet a different table. 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 tables 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 CWSerenade.
The process you use to populate the data warehouse with the transactional and supporting data in CWSerenade involves:
1. In CWSerenade, performing an initial load. The initial load extracts the supporting, customer, and order-related information from the CWSerenade tables to the DW Staging tables in the CWSerenade database. Only key information is extracted for CWData.
2. Transferring the information in the DW Staging tables in the CWSerenade database to the tables in the cwi_staging database on the CWData server.
3. Transforming the data and propagating it into the tables in the 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 CWSerenade to the cwi_warehouse database on a periodic basis:
• Capture transactions in the DW Capture Transaction table for subsequent extraction.
• Periodically, extract the transactional information and other supporting information, and populate the DW Staging tables. Typically, this is part of your daily process.
• Transfer the information in the DW staging tables in the CWSerenade database 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. At defined intervals, you can extract data from the CWData database to populate the CWAnalytics database, where you can use an analysis tool, such as Microsoft Data Analyzer, to query the data.
Flow of data: This flowchart displays the flow of data from the CWSerenade 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 CWSerenade 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 and summary tables.
• 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.
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. The UPC code is right-justified and zero filled. |
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.
The relational model of the cwi_warehouse database are 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 table 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 tables are used by CWSerenade to collect key information for download to CWData. Each table’s name and description begins with the letters DW; for example, the DW Accounting Period table (DWACTP) captures information on CWSerenade accounting periods. Transaction information, such as orders or updates to customer records, are temporarily stored in the DW Transaction table. You run a periodic extraction to populate these tables, 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.
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.
