Slowly Changing Dimensions
来源:互联网 发布:淘宝客推广软件 编辑:程序博客网 时间:2024/05/17 22:32
Slowly Changing Dimensions (SCDs) are dimensions that have data that slowly changes. The SCD editor offers
the simplest method of building the data flow for the SCD outputs. In the SCD editor, you can map columns, select
surrogate key columns, and set column change attributes through combining SCD types.
The below figure illustrates an example of the SCD editor.
SCD keys
You must choose one or more source keys columns from the incoming data to ensure its unicity.
You must set one surrogate key column in the dimension table and map it to an input column in the source table.
The value of the surrogate key links a record in the source to a record in the dimension table. The editor uses
this mapping to locate the record in the dimension table and to determine whether a record is new or changing.
The surrogate key is typically the primary key in the source, but it can be an alternate key as long as it uniquely
identifies a record and its value does not change.
Source keys: Drag one or more columns from the Unused panel to the Source keys panel to be used as the key(s)
that ensure the unicity of the incoming data.
Surrogate keys: Set the column where the generated surrogate key will be stored. A surrogate key can be generated
based on a method selected on the Creation list.
Creation: Select any of the below methods to be used for the key generation:
• Auto increment: auto-incremental key.
• Input field: key is provided in an input field.
When selected, you can drag the appropriate field from the Unused panel to the complement field.
Routine: from the complement field, you can press Ctrl+ Space to display the autocompletion list and select
the appropriate routine.
• Table max +1: the maximum value from the SCD table is incremented to create a surrogate key.
• DB Sequence: from the complement field, you can enter the name of the existing database sequence that will
automatically increment the column indicated in the name field.
Combining SCD types
The Slowly Changing Dimensions support four types of changes: Type 0 through Type 3. You can apply any of
the SCD types to any column in a source table by a simple drag-and-drop operation.
• Type 0: is not used frequently. Some dimension data may be overwritten and other may stay unchanged over
time. This is most appropriate when no effort has been made to deal with the changing dimension issues.
• Type 1: no history is kept in the database. New data overwrites old data. Use this type if tracking changes is
not necessary. this is most appropriate when correcting certain typos, for example the spelling of a name.
• Type2: the whole history is stored in the database. This type tracks historical data by inserting a new record in
the dimensional table with a separate key each time a change is made. This is most appropriate to track updates,
for example.
SCD Type 2 principle lies in the fact that a new record is added to the SCD table when changes are detected on
the columns defined. Note that although several changes may be made to the same record on various columns
defined as SCD Type 2, only one additional line tracks these changes in the SCD table.
The SCD schema in this type should include SCD-specific extra columns that hold standard log information
such as:
-start: adds a column to your SCD schema to hold the start date. You can select one of the input schema columns
as a start date in the SCD table.
-end: adds a column to your SCD schema to hold the end date value for a record. When the record is currently
active, the end date is NULL or you can select Fixed Year Value and fill in a fictive year to avoid having a
null value in the end date field.
-version: adds a column to your SCD schema to hold the version number of the record.
-active: adds a column to your SCD schema to hold the true or false status value. this column helps to easily
spot the active record.
• Type 3: only the information about a previous value of a dimension is written into the database. This type tracks
changes using separate columns. This is most appropriate to track only the previous value of a changing column.
- Slowly Changing Dimensions
- Implementing Slowly Changing Dimensions
- 缓慢变化维--Slowly Changing Dimensions
- How to handle Slowly Changing Dimensions (SCDs) in data model design?
- Slowly Changing Dimension
- BW:建模:Slowly Changing Dimension
- CHECK DQ Issue in Slowly Changing Dimension
- 渐变维度(Slowly Changing Dimension)及其处理方法
- BW:Read Master Data:如何规避Slowly Changing Dimension
- 缓慢变化维----slowly changing dimension 处理 办法
- 渐变维度(Slowly Changing Dimension)及其处理方法
- 理解缓慢变化维(Slowly Changing Dimension)
- dimensions
- [数据仓库--ETL]渐变维度(Slowly Changing Dimension)及其处理方法
- [数据仓库--ETL]渐变维度(Slowly Changing Dimension)及其处理方法
- 数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计
- 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计
- 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计
- KVO的学习
- Eclipse反编译插件
- multisim安捷伦仪器使用
- OTcl与C++,NS2是如何识别并调用执行协议的
- 插件资料
- Slowly Changing Dimensions
- 谈谈父窗口和所有者窗口
- 单片机-状态工作,循环工作模式
- Android适配器Adapter学习
- android PreferenceActivity EditTextPreference 输入校验
- 学习和安装 skyeye
- PAM语法
- 修改文件编码格式
- QTP使用技巧