ETL_2009微软BI培训.ppt

Dimension Design Different Dimension Relationships Regular Dimension Relationship Reference Dimension Relationship Fact Dimension Relationship Role Playing Dimension Parent-Child Hierarchy Regular Dimension Relationships A traditional star schema design The Primary Key in the dimension table joins directly to Foreign Key in the fact table. Reference Dimension Relationships Snowflake schema A Reference dimension using columns from multiple tables, or the dimension table links a dimension that is directly linked to the fact table. Role Playing Dimension It is used in a cube more than one time, each time for a different purpose. Each role-playing dimension is joined to a fact table on a different foreign key. Example, you might add a Time dimension to a cube three times to track the times that products are ordered, products are shipped, Orders are due.. Parent-Child Hierarchy A parent-child hierarchy is a hierarchy in a standard dimension that contains a parent attribute. A parent attribute describes a self-join, within the same dimension table. Example: Employee Hierarchy Slowly Changing Dimension Some attribute values may change over time. Two basic techniques: Type 1 change Type 2 change Slowly Changing Dimension – Type 1 A Type 1 change, is to simply overwrite the old value with the new one. Slowly Changing Dimension – Type 2 You create a new dimension row with the new value and a new surrogate key, and mark the old row or timestamp as no longer in effect The fact table will use the new surrogate key to link new fact measurements Calculated Member A Calculated Members is a member of a dimension or a measure group that is defined based on a MDX expression. The value for the member is calculated at runtime. The result values are not stored in the disk. Calculated Member Properties Named Set A named set is a MDX expression that returns a set of dimension members. You can define named sets and save them as part of the cube definition. It allows