Data Modeling Question - Mixed Granularity

m_perreault — Sat, 16 Nov 2024 21:21:55 GMT

I have two tables of mixed Granularity and mixed date range and am struggling to figure out the best way to model them.

Table 1 is a Transactions table. This Table has a Transaction Date which can be any date and has data back to 2010. See below for all Transactions by Owner = A ID = 1A. Note there are Months When there are no Transactions

Owner	ID	TransactionDate
A	1A	1/1/2010
A	1A	3/2/2011
A	1A	6/16/2012
A	1A	7/20/2013
A	1A	4/29/2014
A	1A	10/30/2015
A	1A	12/4/2016
A	1A	5/5/2017
A	1A	6/6/2018

My second tables is a "Positions Table". The Positions table has a Month End Date and goes back to 2015 Below is an example of Positions for Owner = A and ID = 1A

Owner	ID	MonthEndDate	Value
A	1A	1/1/2013	1
A	1A	2/1/2013	2
A	1A	3/1/2013	1
A	1A	4/1/2013	3
A	1A	5/1/2013	4
A	1A	6/1/2013	5
A	1A	7/1/2013	6

I want to Transactions by Owner & ID & MonthEnd(TransactionDate) to its Owner & ID & MonthEndDate in the Positions table. I am having issues because of the mixed Date Ranges between the tables.

If I select an ID from the Positions table I only get its Transactions going back to 2013.

I thought about creating a Concatenated Fact table but I then ran into the issue of if I select a TransactionDate, I wont see the value from Positions for the Month End of that Transaction.

Any suggestions on how best model this would be much appreciated!!!

Thanks,

Mark

topic Data Modeling Question - Mixed Granularity in App Development

Data Modeling Question - Mixed Granularity