Cognos 简明教程

Data Warehouse - Schemas

模式被定义为数据库逻辑描述，其中事实表和维度表以逻辑方式结合。数据仓库以星形、雪花形和事实星座模式维护。

A schema is defined as a logical description of database where fact and dimension tables are joined in a logical manner. Data Warehouse is maintained in the form of Star, Snow flakes, and Fact Constellation schema.

Star Schema

星形模式包含一个事实表和多个维度表。每个维度仅以一个维度表表示，并且它们未规范化。维度表包含一组属性。

A Star schema contains a fact table and multiple dimension tables. Each dimension is represented with only one-dimension table and they are not normalized. The Dimension table contains a set of attributes.

Characteristics

  1. In a Star schema, there is only one fact table and multiple dimension tables.

  2. In a Star schema, each dimension is represented by one-dimension table.

  3. Dimension tables are not normalized in a Star schema.

  4. Each Dimension table is joined to a key in a fact table.

下图显示了公司的销售数据，相对于四个维度，即时间、项目、分支和位置。

The following illustration shows the sales data of a company with respect to the four dimensions, namely Time, Item, Branch, and Location.

star schema

中心有一个事实表。它包含四个维度中的每个维度的键。事实表还包含属性（即销售额和销售单位）。

There is a fact table at the center. It contains the keys to each of four dimensions. The fact table also contains the attributes, namely dollars sold and units sold.

Note − 每个维度只有一个维度表，每个表都保存一组属性。例如，位置维度表包含属性集 {location_key, street, city, province_or_state,country}。此约束可能会导致数据冗余。

Note − Each dimension has only one-dimension table and each table holds a set of attributes. For example, the location dimension table contains the attribute set {location_key, street, city, province_or_state, country}. This constraint may cause data redundancy.

For example − “温哥华”和“维多利亚”这两个城市都在加拿大不列颠哥伦比亚省。此类城市的条目可能会导致沿属性 province_or_state 和 country 发生数据冗余。

For example − "Vancouver" and "Victoria" both the cities are in the Canadian province of British Columbia. The entries for such cities may cause data redundancy along the attributes province_or_state and country.

Snowflakes Schema

雪花模式中的一些维度表已规范化。规范化将数据拆分为其他表，如下所示。

Some dimension tables in the Snowflake schema are normalized. The normalization splits up the data into additional tables as shown in the following illustration.

snowflakes schema

与星形模式不同，雪花模式中的维度表已规范化。

Unlike in the Star schema, the dimension’s table in a snowflake schema are normalized.

For example − 星形模式中的项目维度表已规范化并拆分为两个维度表，即项目表和供应商表。现在项目维度表包含属性 item_key、item_name、类型、品牌和 supplier_key。

For example − The item dimension table in a star schema is normalized and split into two dimension tables, namely item and supplier table. Now the item dimension table contains the attributes item_key, item_name, type, brand, and supplier-key.

供应商键链接到供应商维度表。供应商维度表包含属性 supplier_key 和 supplier_type。

The supplier key is linked to the supplier dimension table. The supplier dimension table contains the attributes supplier_key and supplier_type.

Note − 由于 Snowflake 模式中的规范化，因此冗余减少，因此维护和节省存储空间变得容易。

Note − Due to the normalization in the Snowflake schema, the redundancy is reduced and therefore, it becomes easy to maintain and the save storage space.

Fact Constellation Schema (Galaxy Schema)

事实星座具有多个事实表。它也称为 Galaxy 模式。

A fact constellation has multiple fact tables. It is also known as a Galaxy Schema.

以下插图显示了两个事实表，即 Sales 和 Shipping −

The following illustration shows two fact tables, namely Sales and Shipping −

galaxy schema

Sales 事实表与星型模式中的相同。Shipping 事实表具有五个维度，即 item_key、time_key、shipper_key、from_location、to_location。Shipping 事实表还包含两个度量，即已售金额和已售单位。还可以共享事实表之间的维度表。

The sales fact table is the same as that in the Star Schema. The shipping fact table has five dimensions, namely item_key, time_key, shipper_key, from_location, to_location. The shipping fact table also contains two measures, namely dollars sold and units sold. It is also possible to share dimension tables between fact tables.

For example − 时间、项目和位置维度表在 sales 和 shipping 事实表之间共享。

For example − Time, item, and location dimension tables are shared between the sales and shipping fact table.