StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to create history fact table?
text
Body
copied!<p>I have some entities in my Data Warehouse: </p> <ol> <li><p><strong>Person</strong> - with attributes personId, dateFrom, dateTo, and others those can be changed, e.g. last name, birth date and so on - slowly changing dimension</p></li> <li><p><strong>Document</strong> - documentId, number, type</p></li> <li><p><strong>Address</strong> - addressId, city, street, house, flat</p></li> </ol> <p>The relations between (Person and Document) is One-To-Many and (Person and Address) is Many-To-Many.</p> <p>My target is to create history fact table that can answer us following questions:</p> <ol> <li>What persons with what documents lived at defined address on defined date?</li> </ol> <p>2, What history of residents does defined address have on defined interval of time?</p> <p>This is not only for what DW is designed, but I think it is the hardest thing in DW's design.</p> <p>For example, Miss Brown with personId=1, documents with documentId=1 and documentId=2 had been lived at address with addressId=1 since 01/01/2005 to 02/02/2010 and then moved to addressId=2 where has been lived since 02/03/2010 to current date (NULL?). But she had changed last name to Mrs Green since 04/05/2006 and her first document with documentId=1 to documentId=3 since 06/07/2007. Mr Black with personId=2, documentId=4 has been lived at addressId=1 since 02/03/2010 to current date.</p> <p>The expected result on our query for question 2 where addressId=1, and time interval is since 01/01/2000 to now, must be like:</p> <p>Rows:</p> <pre><code>last_name="Brown", documentId=1, dateFrom=01/01/2005, dateTo=04/04/2006 last_name="Brown", documentId=2, dateFrom=01/01/2005, dateTo=04/04/2006 last_name="Green", documentId=1, dateFrom=04/05/2006, dateTo=06/06/2007 last_name="Green", documentId=2, dateFrom=04/05/2006, dateTo=06/06/2007 last_name="Green", documentId=2, dateFrom=06/07/2007, dateTo=02/01/2010 last_name="Green", documentId=3, dateFrom=06/07/2007, dateTo=02/01/2010 last_name="Black", documentId=4, dateFrom=02/03/2010, dateTo=NULL </code></pre> <p>I had an idea to create fact table with composite key (personId, documentId, addressId, dateFrom) but I have no idea how to load this table and then get that expected result with this structure.</p> <p><strong>I will be pleased for any help!</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload