使用ssis包进行数据仓储和加载数据
我是数据仓库设计的新手,据我所知,我创建了一个数据仓库设计。
对于这个数据库,我创建了 4 个维度和 1 个事实表。
- 对于所有维度,我没有保留主键约束。
- 对于每个维度都有一个唯一的列。
- 有了这个独特的列,SSIS 包中才实现了 SCD。
- 对于事实表,通过使用查询直接从源数据库移植数据。
任何人都可以就上述几点提出建议吗?
是否需要进行任何修改或者这是正确的吗?
I am new to Data warehousing design, I have created a data warehouse design as of my knowledge.
For this database I have created 4 dimensions and 1 fact table.
- For all dimensions i didn't kept primary key constraints.
- For each dimension having one unique column.
- with that unique column only SCD is implemented in SSIS package.
- For fact table directly data porting from source database by using query.
Can any one suggest on above mentioned points.
Any modifications have to do or is this correct?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
维度有自己的自动递增键,所以如果您的意思是您没有从源系统复制该键,那是可以的。
不知道这意味着什么。维度确实具有唯一标识“维度对象”(客户、商店、产品)的业务键,但列没有具有唯一约束。对于 SCD 类型 1,它恰好是唯一的,但这是一种特殊情况。
所以,您正在 SSIS 中使用 SCD — 很好。
错误——至少我是这么理解的。加载事实表时,您必须根据业务键从每个维度查找主键。
看一下此示例。
Dimensions have their own auto-incrementing key, so if you mean that you did not copy the key from the source system that is OK.
Not sure what this means. Dimensions do have the business key which uniquely identifies a "dimension object" (customer, store, product), but the column does not have an unique constraint. For SCD type 1 it happens to be unique, but that is a special case.
So, you are using the SCD in SSIS -- fine.
Wrong -- at least the way I understand this. When loading fact tables, you have to look-up primary keys from each dimension based on the business key.
Take a look at this example.