0

I'm working on a project for school and I'm new to the database design so I would appreciate any advice to learn the best practices.

I was giving a data source for a bank and my assignment is to create OLTP then DW. My OLTP is as follows

enter image description here

now I'm trying to populate these to the dimension tables but I was told that having the LoanNumber like this is redundant and also I can't have this column in all of the fact and dim tables for the next step. My questions are:

  1. As far as I know, is that I need a matching column to do look up to or use the MERGE statement on to move the data to the DW. So what should I do in this case?

  2. If I take off the LoanNumber column how would I join between these tables? should I replace it with and ID columns for each table, would that be a good thing?

the dim tables that I have enter image description here the fact tables enter image description here

Big_Dwarf90
  • 77
  • 7
  • What dimensions are you trying to build? – billinkc Oct 18 '20 at 17:08
  • @billinkc I updated the post to include how I have the dim and fact tables so far – Big_Dwarf90 Oct 18 '20 at 18:42
  • 1
    The design of an OLTP system is very different from a data warehouse. Your current dimension tables are just your OLTP tables with a surrogate key and some auditing "stuff" Your fact tables aren't great (Loan number shouldn't be in there as you're not aggregating or slicing by it - directly at least. The loan_status column in the loan table should be pulled out into either its own dimension or a "junk dimension" Issue_d should be using the same shared date table that drives datekey. `Acc_now_delinq` smells like it's an attribute of the loan and should be on that table – billinkc Oct 19 '20 at 03:43

0 Answers0