Enterprise Information – Star Snowflake ERP EAI

Star and Snowflake Schemas

In relational implementation, the dimensional designs are mapped to a relational set of tables. You can implement the design into following two methods:

  • Star Schema
  • Snowflake Schema

What Is a Star Schema?

A star schema model can be depicted as a simple star: a central table contains fact data and multiple tables radiate out from it, connected by the primary and foreign keys of the database. In a star schema implementation, Warehouse Builder stores the dimension data in a single table or view for all the dimension levels.

 

A star schema database of products

Star Schema (Prod_to_dwh.gif)

Author: BennyPJ

Source

This image is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported license.

 

 

Star and Snowflake Schemas Cont.

In relational implementation, the dimensional designs are mapped to a relational set of tables. You can implement the design into following two methods:

  • Star Schema
  • Snowflake Schema

What Is a Snowflake Schema?

The snowflake schema represents a dimensional model which is also composed of a central fact table and a set of constituent dimension tables which are further normalized into sub-dimension tables. In a snowflake schema implementation, Warehouse Builder uses more than one table or view to store the dimension data. Separate database tables or views store data pertaining to each level in the dimension.

A sales database with a snowflake schema. Several tables are shown and connected

Snowflake Schema (Snowflake-schema-example.png)

Author: SqlPac

Source

This image is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported license.

Attribution: SqlPac at English Wikipedia

 

When do you use Snowflake Schema Implementation?

Ralph Kimball, the data warehousing guru, proposes three cases where snowflake implementation is not only acceptable but is also the key to a successful design:

  • Large customer dimensions where, for example, 80 percent of the fact table measurements involve anonymous visitors about whom you collect little detail, and 20 percent involve reliably registered customers about whom you collect much detailed data by tracking many dimensions.
  • Financial product dimensions for banks, brokerage houses, and insurance companies, because each of the individual products has a host of special attributes not shared by other products.
  • Multienterprise calendar dimensions because each organization has idiosyncratic fiscal periods, seasons, and holidays.

Ralph Kimball recommends that in most of the other cases, star schemas are a better solution. Although redundancy is reduced in a normalized snowflake, more joins are required. Kimball usually advises that it is not a good idea to expose end users to a physical snowflake design, because it almost always compromises understandability and performance.

 

The fact table relationship, and communication / joining of the products table, and sales tables

 

Data Warehouse schema of adventureworks example (2008)

Samples and Sample Databases at:

https://CodePlex.com/SqlServerSamples

Production database with product tables, and product sub-tables all joined by shared fields (keys).

 

Char Vs. Varchar

 

The CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section C.10.4, “Limits on Table Column Count and Row Size”.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column’s maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.8, “Server SQL Modes”.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. ForCHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).

 

 

Information Systems Vary by Scope

Personal Information System

  • Use by single individual
  • Simple Procedures
  • Easy to learn
  • Documentation light
  • Timing controlled by subscriber
  • Little or No Workflow

 

Workgroup Information System (Departmental)

  • Facilitate  group activities
  • Shared Access
  • Common Procedures (Across group)
  • Requires group training, documentation
  • Versions – all change together
  • Issues resolved within group
  • Results may impact other parts of an organization

 

Enterprise Information System

  • Span an entire organization
  • Multi User access
  • Large numbers of users (000’s)
  • Formal Procedures
  • Audit trails
  • Group Rights and Permissions for Access (role based)
  • Documentation light
  • Change usually a large project – Change Management
  • May be subject to Manufacturers versioning, can impact related systems (e.g. office)
  • Workflow commonly available

 

Inter Enterprise Information System

  • Shared by multiple Organizations
  • May be Industry or channel based
  • Can have 000,000’s users
  • Common procedures
  • Issues/changes require inter-organizational cooperation
  • Versions – all change together
  • Usually UG’s form, boards representing multiple orgs.
  • Results may impact other parts of an organization

 

Enterprise Information Systems – CRM, ERP, EAI

Contact Management vs CRM

  • Contact Management keeps contact info
    • May be Personal or workgroup
    • Focus on managing contact data, appointments
    • May have proprietary Data structures
  • CRM has process imbedded
    • Track Metrics
    • May update from other corporate systems ( Order History, Payments, etc.)
    • State Machine – Funnel
      • In general, a state machine is any device that stores the status of something at a given time and can operate on input to change the status and/or cause an action.
    • Formal Database – SQL
    • Inherent processes for managing all customer interactions (Customer Service?)

 

A table showing the sales and marketing department and their applications
Department Application
Sales and Marketing
  • Lead generation
  • Lead tracking
  • Customer management
  • Sales forecasting
  • Product and brand management

 

Enterprise Application Integration (EAI)

 

  • Suite of software to integrate existing applications together
  • Connects system “islands” via new software layer
  • Enables existing applications to communicate and share data
  • Leverages existing systems, leaving functional applications as is, but providding integration top layer
  • Enables a gradual move to ERP

 

 

EAI Automatically Makes Data Conversions Among Different Systems

 

Virtual Integrated Database

 

ERP – Enterprise Resource Planning

  • Database
  • Suite of Applications
  • Set of processes for consolidating operations
  • Modules for functions with common data structure – Avoid Silos
  • Expensive lengthy implementations almost always involve process transformation.
  • Industry Templates
  • Configurable to a degree

 

Applications relating to the ERP database.
The applications relating to the ERP database are: Sales, Accounting, HR, Inventory, Manufacturing, Solicitation and Lead management, customer support, Relationship management.

 

Enterprise Resource Planning

ERP star diagram.
ERP made up of: Purchasing, manufacturing, finance, dashboards, time and projects, distribution, SRM and sales, customer web portal.

Title: Accounting Software Singapore ERP

Author: accounting software

Source: Flickr

License: Attribution-NonCommercial-ShareAlike 2.0 Generic (CC BY-NC-SA 2.0)

ERP diagram.
ERP with elements: supply chain, machining, engineering, service, accounting, sales and marketing, human resources, quality management.
ERP diagram.
Accounting, supply chain, sales and marketing, HR, quality management, service, engineering, and management. Modules can be derives from service, engineering, and management.
In-depth text diagram of modules derived from ERP
Sales – Implements functions of ordder placement, order scheduling, shipping, and invoicing. Procurement (SRM) – Maximize cost savings with support for the end-to-end procurement and logistics processes. Production (PLM) – Helps in planning and optimizing the manufacturing capacity and material resources. It is evolved from the MRP. Distribution (SCM) – Control warehouse processes and manage movements in the warehouse and repost faster to challenges and changes in supply and demand. Accounting – Automate any financial operation while ensuring regulatory compliance and gaining real-time insight into overall performance. Human Resource – Maintain a complete employee database and to optimally utilize of all employees. Corporate performance and governance – aims to streamline and gain greater control of the corporate services. Customer Services (CRM) Capture and maintain customer relationships, facilitate the use of customer experiences and evaluate thew knowledge management. The inner circle of the diagram has 3 other modules listed. Business Intelligence – Analyze data an convert to information. E-commerce – Focus on external strategies. Enterprise asset management – efficiently and sustainably manage the entire asset lifecycle, improve asset usage and cute costs with powerful analytics

Title: ERP II Modules

Author: Shing Hin Yeung

Source

License: Creative Commons Attribution-Share Alike 3.0 Unported

 

 

Business intellifence systems dashboards and data visualizations
A diagram meant to provide a visual aid to seeing the business intelligence systems dashboards and data visualizations. The base of the structure is made up of IT platform components, IT personnel and services, and a transaction processing systems (ODS). Stemming from the ODS are the ERP systems. Enterprise resource planning systems: Marketing, Sales / CRM, Production/Operations management, procurement and inventory, Human Resources, Accounting and Finance.
IS that Function Among Multiple Organizations

 

Flowchart diagram of an organization.
At the center of the diagram is your organization. Suppliers get orders and payment online from your organization. Your organization gains knowledge and information of the supplier. Suppliers ship physical materials to your organization via the supply chain management network. Businesses and customers use the internet and your organizations web portal to order and pay for products. Digital products available immediately to customers (customer relationship management), and businesses. Physical products (if ordered) are also shipped to customers and businesses.

License

Icon for the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Information and Organizations (IST 301) Copyright © by Bill Meyerowitz is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.

Share This Book