June 29, 2023 at 2:48 pm
Let's say the main fact table stores financial agreements, along with various attributes and measures, like the lender, the amount loaned, the amount still owed, etc....
We have a fairly well working DW whereby the one and only fact table models the above.
But for some financial agreements, the agreement isn't a monetary loan but an asset finance agreement, so the agreement will also have assets relating to it. For example such an agreement might be for 10 office printers, or 5 mercedes sprinter vans, etc. Or just for 1 photocopier.
How can i best model the storage of the 0 or more assets per financial agreement?
Having a Dimension.Asset would do it, but I'd need multiple columns in the fact table to accommodate however many assets that may be. I don't really want the unknown number of columns relating to Dimension.Asset.
What's the best Kimball/star schema methodology for this?
June 30, 2023 at 12:08 pm
Response duplicated for some reason...please see the other response.
June 30, 2023 at 12:09 pm
In my opinion, create a dimension for Financial Agreements. The dimension would contain all descriptive attributes of the agreement. Create a separate dimension for Asset, which can either have the granularity of "Asset Type" (i.e. one record for each type of asset) or a record for each asset on a financial agreement (if there are any).
With the above dimensions, the grain of your fact table would change to a record for each asset contained within the agreement. If it's a monetary only agreement there will be one fact record pointing to the default record of the Asset dimension, and if there are assets there would a a row for each asset (or asset type depending on the grain of our dimension).
This is all just a theoretical answer of course. Without knowing more about the data and good examples of reporting requirements it is difficult to give you better direction in terms of the ideal design.
July 3, 2023 at 9:45 am
Thank you for the reply and understanding my design puzzle. I understand what you've suggested, but i think that would then end up doubling/tripling/X-ling the various fact measures i have for an agreement when it has 2 or more assets....?
July 3, 2023 at 10:04 am
Sure, you're adding rows to the fact table but ultimately it will keep the fact table narrow and you're aggregating a single fact attribute.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply