AnsweredAssumed Answered

CLI Data Extract Missing Foreign Key Data

Question asked by Paul Johnson on Jul 1, 2019
Latest reply on Jul 1, 2019 by Paul Johnson

I'm going to post this to the CLI extract Github issues site as well, but I wanted to post it here too in case someone else is having the same issue. 

 

I have set up the CLI data extraction successfully.  This includes setting up the CLI extract (see Github site) which pulls down our data (as dimension and fact tables) into a set of flat text files.  I have successfully set up a SQL Server database along with all foreign keys and relationships between those dimension and fact tables.  Finally, I set up an SSIS process which reads those downloaded flat files and loads the tables in the SQL Server database.

 

This works wonderfully - EXCEPT for a small detail (which really isn't small) - there are some fact tables that are getting pulled down that contain foreign key data that does not exist in our dimension tables.  This is not good.  At this point, we are pretty new, and do a "truncation and load" of all of our tables.  I know this will need to change down the road as we get larger.  But for now, I simply get all of the data for each load.  I have the sequence of how to load the dimension tables and they all load fine.  However, when the fact tables (details) are being loaded, some of the loads fail because it is trying to put data into the fact tables that doesn't correspond to data in the dimension tables.

 

One example is the table "Wiki_page_fact" is bringing down data that the usr_id (user id) does not exist in the usr_dim table.  Null values for this are OK, but when it is bringing down data that doesn't exist, that is a problem.  

 

To get this data to load, I've disabled my FK relationship enforcement.  Overall, I have 20 tables that I had to disable the FK enforcement to get loaded.  Most (but not all) of them are for user ids that are not in the usr_dim table.  

 

Is anyone else seeing this?  If so, any solutions other than disabling the FK relationships??

 

Any help/direction is appreciated!!

 

FYI...the tables I've disabled FK enforcement for are as follows (just showing the script):

 

-- Disabled Fact Table FKs

ALTER TABLE [cvs].[assign_overr_usr_roll_fact]
NOCHECK CONSTRAINT [FK_usr_assign_overr_usr_roll_fact];
GO

ALTER TABLE [cvs].[conference_partic_fact]
NOCHECK CONSTRAINT FK_usr_conference_partic_fact;
GO

ALTER TABLE cvs.course_ui_nav_item_fact
NOCHECK CONSTRAINT FK_course_ui_canvas_nav_course_ui_nav_item_fact
GO

ALTER TABLE cvs.discussion_entry_fact
NOCHECK CONSTRAINT FK_topic_editor_discussion_entry_fact
go

ALTER TABLE cvs.discussion_topic_fact
NOCHECK CONSTRAINT FK_usr_discussion_topic_fact
GO

ALTER TABLE cvs.discussion_topic_fact
NOCHECK CONSTRAINT FK_editor_discussion_topic_fact
go
ALTER TABLE cvs.enrollment_fact
NOCHECK CONSTRAINT FK_usr_enrollment_fact
GO

ALTER TABLE cvs.file_fact
NOCHECK CONSTRAINT FK_uploader_file_fact
GO

ALTER TABLE cvs.file_fact
NOCHECK CONSTRAINT FK_folder_file_fact
GO
ALTER TABLE cvs.module_compl_req_fact
NOCHECK CONSTRAINT FK_discussion_topic_editor_module_compl_req_fact
go

ALTER TABLE cvs.module_compl_req_fact
NOCHECK CONSTRAINT FK_usr_module_compl_req_fact
go
ALTER TABLE cvs.module_item_fact
NOCHECK CONSTRAINT FK_discussion_topic_editor_module_item_fact
GO

ALTER TABLE cvs.module_item_fact
NOCHECK CONSTRAINT FK_usr_module_item_fact
GO
ALTER TABLE cvs.pseudonym_fact
NOCHECK CONSTRAINT FK_usr_pseudonym_fact
GO

ALTER TABLE cvs.quiz_quest_answer_fact
NOCHECK CONSTRAINT FK_assessment_quest_quiz_quest_answer_fact
GO

ALTER TABLE cvs.quiz_quest_answer_fact
NOCHECK CONSTRAINT FK_quiz_quest_group_quiz_quest_answer_fact
GO
ALTER TABLE cvs.quiz_quest_fact
NOCHECK CONSTRAINT FK_assessment_quest_quiz_quest_fact
GO

ALTER TABLE cvs.quiz_quest_fact
NOCHECK CONSTRAINT FK_quiz_quest_group_quiz_quest_fact
GO
ALTER TABLE cvs.submis_file_fact
NOCHECK CONSTRAINT FK_submis_file_submis_file_fact
GO
ALTER TABLE cvs.wiki_page_fact
NOCHECK CONSTRAINT FK_usr_wiki_page_fact
GO

Outcomes