Course Data:
The data used in this course is from Orion Star Sports & Outdoors, which is a fictitious retail company selling sports and outdoor products has retail stores in many countries throughout the world sells products in retail stores, through catalog mail orders, and over the Internet utilizes the Orion Star Club to track purchases, enabling analyses of buying patterns and providing a basis for CRM activities and targeted product offerings.
The Orion Star organization hierarchy has several levels:
Country company department group.
Information stored for each employee includes:
employee ID job title, hire date, termination date, address, gender, salary.
Each product has a cost and sales price. Price change history is maintained by recording start and end dates for each price.
All prices are in U.S. dollars.
Orion Star has approximately 100,000 customers spread across many countries. Customer address information is stored in a number of tables and includes street city state postal code country.
This data is controlled by means of pointers (ID columns), as is often seen where geographical data is gathered from official sources that are maintained externally to the organization. This approach facilitates changes to information such as postal codes and street names.
Most of the approximately 750,000 orders are sales to Orion Star Club members for whom customer information is recorded.
Each order contains one or more order lines (one line per product purchased).
All of the Orion Star products have a pointer to a supplier. There are 64 suppliers, based in different countries, but only one supplier per product.
>>There are many people and groups at Orion Star who use SAS software, including information systems business users management and C-level executives.
>>The Information Systems Department has built a data warehouse by extracting data from Online Transactional Processing Systems (OLTP) and Enterprise Resource Planning (ERP) systems, as well as data from external sources.
>>The business users use the data warehouse
Management and C-level executives use the SAS Business Intelligence tools to access the information stored in the data warehouse.
The Orion Star data is organized two ways:
Normalized (relational) model: Represents the extracts from OLTP, ERP, and other systems. This data can be transformed, cleansed, and loaded into a data warehouse.
Dimensional model: Represents a data warehouse that consists of a star schema design. This data is well suited for use in business intelligence applications and for ad hoc querying and reporting.
The normalized data model contains a number of Start_Date and End_Date columns that are used to ensure an accurate history of changes. Therefore, an item can exist more than once in a particular table but only once within a particular time interval
The Product and Organization tables are organized hierarchically. They use product-level and organization-level ID’s to determine the placement of an entity within the appropriate hierarchy.
The dimensional data model is the result of an ETL (Extract, Transform, and Load) process and represents one possible data mart. The dimensional model is a star schema with a fact table that contains orders. The dimension tables represent data for time, geography, customers, products, suppliers, and employees.
The remainder of this course uses the SAS BI Client Tools in a case study scenario to build the Orion Star Marketing Data Mart by
Once the Marketing Data Mart is built, it is used to
Vinod M is a Big data expert writer at Mindmajix and contributes in-depth articles on various Big Data Technologies. He also has experience in writing for Docker, Hadoop, Microservices, Commvault, and few BI tools. You can be in touch with him via LinkedIn and Twitter.