I was wondering if somebody could shed some light on the below.
I am currently working on building a kimball data warehouse within the financial sector specifically within the pensions industry.
Currently we are working to integrate the business process of valuations on a scheme.
The requirement is to store all valuations regardless of the product in a single FACT table for reporting. There are many different type of products which a pension can hold (Portfolio, Securities, Property etc) , so we have decided to go down the route of creating supertype and subtype dimensions. There will be one supertype for product which will contain common fields and then a subtype dimension for each product which will contain further detail.
The issue we are currently having is that a security can be held within a portfolio , but on the flip side the portfolio might not hold any investments but still contain a value (may be down to how we store the underlying data).
We do not want to create a single valuation line within the fact table for the portfolio if it has underlying investments we would just expect to show the underlying investments but somehow tie this back to the porfolio. If the porfolio has no underlying investments that we know of we would expect to store a line within the FACT table with just the value of the portfolio which would key directly to the product table.
Does anyone have any suggestions on this?
Here is a structure of how the data is held in the source system.
Here is my proposed design with all of the investment dimensions inter-changeable and the product dimension being core , however this falls over because there is no link between the underlying investment holding and the portfolio.
Updated model with Portfolio Key within Fact
