Overview of Orion Star Data - SAS BI

Orion Star Data

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.

Orion Star – Organization

The Orion Star organization hierarchy has several levels:
Country company department group.

Orion Star – Employees

Information stored for each employee includes:
employee ID job title, hire date, termination date, address, gender, salary.

Orion Star – Merchandise

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 – Customers

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.

Orion Star – Orders

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).

Orion Star – Suppliers

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.

Orion Star Information Consumers

>>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

• to create data marts
• to perform advanced analyses
• for querying and reporting.

Management and C-level executives use the SAS Business Intelligence tools to access the information stored in the data warehouse.

Orion Star – Data Models

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 (Relational) Data Model

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

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.

Case Study Scenario

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

• Defining data libraries
• Registering source tables
• Defining target tables
• Creating ETL jobs
• Creating OLAP cubes

Case Study Scenario

Once the Marketing Data Mart is built, it is used to

• Create stored processes
• View and analyze data
• Create information maps
• Create reports
• Use the information delivery portal

Case Study Scenario Flowchart