Star Schema Cardinality and Storage

Cardinality of a Star Schema

• A common relationship cardinality is one-to-many, or its inverse, many-to-one
• In a star schema, the "one" side corresponds to a dimension table
• The "many" side corresponds to a fact table
• For calculations, a retention period of 5 years is used in subsequent examples
• In an example, the number of products is 1,000 rows, the days are 365/year rows, the number of salesperson is 10,000 rows, and the number of customers is 1,000,000 rows
• An example total row total comes to 1,000 x (365 x 5) x 10,000 x 1,000,000
• In total the rows come to = 1.825 x 10^16 rows

Storage Sizing of a Star Schema

• The previous definition of a common relationship cardinality is one-to-many, or its inverse, many-to-one
• The "one" side is always a dimension table, while the "many" side is always a fact table.
• Data rention period of 5 years is used in further calculations
• The number of products is 1,000 rows, the number of days are 365/year rows, and the number of salespeople is 10,000 rows in this eample
• The customer number is 1,000,000 rows, which equates to 262 bytes to 250 MB (approx.)
• The calculation of Total Rows (Fact Table) = 1,000 x (365 x 5) x 10,000 x 1,000,000
• The total rows = 1.825 x 10^16 rows
• The individual storage sizes can be calcluated: customer_id (PK) to INT = 4 bytes, customer_name to VARCHAR(50) = 50 bytes, email to VARCHAR(100) = 100 bytes, city to VARCHAR(50) = 50 bytes, country to VARCHAR(50) = 50 bytes, signup_date to DATE = 8 bytes for a "Total row size = 4 + 50 + 100 + 50 + 50 + 8 = 262 bytes per record/row"

Storage Sizing Exercise

• An exercise prompts you to calculate the storage of one more table
• The fact table size should be computed
• In this example # Product = 1 000 rows, # Day = 365/year rows, # Salesperson = 10 000 rows, # Customer = 1 000 000 rows x 262 bytes = 250 MB (approx.)
• Example Total Rows (Fact Table) = 1 000 x (365 x 5) x 10 000 x 1 000 000
• The example Total Rows (Fact Table) = 1,825 x 10^16 rows

Fact Tables

• It is rare to find a subject area fully described by a single fact table
• It's impossible for a single fact table to cover an entire enterprise
• Multiple fact tables are necessary in almost every practical application
• Dimensional designs generally include a single fact table for each process under study
• Designs allow each process to be analyzed individually, avoiding complications from a single fact table covering multiple processes

Determining How Many Fact Tables

• For a pair of facts, determine if the facts occur simultaneously and if the facts are available at the same level of detail (or grain)
• If the answer to either question is no, the facts represent different processes

Facts with Different Timing

• When two or more facts describe events at different times, they describe different processes
• Analysis is hampered if facts are placed in a single fact table
• Separate fact tables allows processes to be studied more easily

Facts Analysis

• Quantity Ordered should be analyzed by date, customer, and product
• Quantity Shipped should be analyzed by date, customer, and product

Fact Tables Solutions

• One solution is to select product_key, sum(quantity_shipped) from sales_facts, group by product_key and having sum(quantity_shipped) > 0
• Another solution is to record only one generic fact
• An alternative dimension will determine whether it represents an order or a shippment
• This approach solves the zero-valued row problem, because each row is either an order or a shipment.

Separate Fact Tables

• Each fact table shares the common dimensions day, product, and customer
• Each stores only the appropriate facts which means there is no need to record extraneous zeros
• It is now possible to study the individual process without additional bother

Hierarchies and Drilling

• It is possible to describe a dimension table as a series of parent-child relationships among groups of attributes.
• Days make up months, months fall into quarters, and quarters fall into years

Drill Down Analysis

• Analysis is talked about as the process of "drilling into data."
• The word drill connotes digging deeper into something
• In a dimensional context, that something is a fact
• A generic concept of drilling is simply the addition of dimensional detail

Attribute Hierarchies

• Attribute hierarchies are best documented graphically
• This example includes important information like the names for each level of the hierarchies and attributes present at each level
• One-to-many relationships exist between instances of each level
• Drilldown analysis is achieved through the addition of dimensional detail

Cubes of Data

• Attribute hierarchy provides a shorthand vocabulary to define the grain of each cube
• A cube may be defined to hold "order dollars at the brand (product) and quarter (day) levels.
• Lining up all the hierarchies is useful for a given star on a single diagram, and draw a horizontal line to define the aggregation level represented by a cube

Snowflakes

• A star schema consists of a fact table and a group of dimension tables, which each dimension table joined directly to the fact table.
• When relationships between dimension attributes are made explicit in a dimensional design, it becomes a snowflake

Normalization

• Snowflaking a dimension = Normalization, following the design of operational systems
• Normalization is not useful in an analytic setting.
• Normalization is suited to the usage profile of an operational system, which supports a wide variety of simultaneous transactions that can modify data
• An analytic database does not share this usage pattern, so it does not call for this level of normalization.
• The added complexity is not necessary to guarantee data integrity and it also detracts from usability, complicates ETL, and may negatively impact performance.