帮助需要Dim和Fact数据加载 [英] Help need for Dim and Fact Data load

问题描述

我正在使用Dim Fact体系结构，其中Fact表与使用外键约束的Dim表具有外键关系.

和少量的暗表中的数据是新鲜的负担，
由于存在外键关系，因此无法直接截断表，因此需要删除外键约束.

我已经实现了以下解决方案:

在编写ADF管道以填充Dim和事实表中的数据时，
我已经编写了将数据填充到Dim和Fact表中的存储过程.

I am using the Dim Fact Architecture where the Fact table have foreign key relationship with Dim tables using Foreign Key constraints.

and data in few of the dim table is a fresh load,
as there is foreign key relationship we cannot truncate table directly, so we need to drop the foreign key constraints.

I have implemented the below solution:

As I am writing ADF pipeline to populate the data in Dim and Fact Table
I have written Stored Procedures for populating Data into Dim and Fact Tables.

因此，我先删除外键约束，然后按照维度SP的逻辑将数据加载到Dim表中. 然后在Fact Logic SP中再次创建外键约束
如果我使用正确的方法，请帮助我.

so i am dropping of the Foreign key constraints first and then loading the data into Dim table in logic of Dimension SP.
and then in Fact Logic SP I am again creating the Foreign key constraints 

Please help me if I am using the correct approach.

谢谢

Pankaj

推荐答案

嗨Pankaj，

Hi Pankaj,

请查看以下分布式表设计文档:在Azure SQL中设计分布式表的指南 数据仓库

Please take a look at the following distributed table design documentation: Guidance for designing distributed tables in Azure SQL Data Warehouse

您有两个选择，一个 循环分发.您需要标识一列可以围绕其设计解决方案的列.由于数据仓库中没有主-外键关系，因此上述分配策略是实现唯一行集的方式.

You have two options, a hash distibuted and round-robin distributed. You need to identify a column that you can design your solution around. Because there is no primary-foreign key relationship in Data Warehouse, the above distribution strategy is how you implement unique rowsets. 

还请查看以下最佳做法文档: 

Please also take a look at the following best practices documentation: Best practices for loading data into Azure SQL Data Warehouse.

我强烈建议您将数据加载到标记表中，然后运行第二项作业以将数据加载到生产表中.第二项工作可能是您的存储过程. ADF允许您自动执行加载过程，但是还有其他加载方法 可以让您启动SP的数据.请参阅:  Azure SQL数据仓库加载模式和策略

I highly suggest you load data to stagging tables and then run a second job to load the data to the production tables. This second job could be your Stored Proceedure. ADF allows you to automate the loading process but there are also other means for loading data that allow you to then kick-off your SP. Please see: 
Azure SQL Data Warehouse loading patterns and strategies

如果您还有其他问题，请告诉我们.

Please let us know if you have additional questions. 

这篇关于帮助需要Dim和Fact数据加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！