Hi,
I am a newbie to data modeling and I am trying to create a star schema with my data which is as follows:
I have a project table and a process step table from a SQL database that have a 1:n relationship.
The project table contains a few date columns (start date, due date, end date).
The process step table also has a few date columns and planned and actual working hours.
In addition I have a date dimension table.
My problem is that I would like to do calculations on both tables using the date table. At the same time I want to filter the process step table by columns from the project table.
In order to achieve this I am considering to create two product tables, one containing the project key and date columns only (acting as a fact table), the other one containing the columns required for filtering (acting as a dimension table for both the project fact table and the process step table).
This way I would have two fact tables without a relationship sharing two dimension tables (date and product).
Is this appropriate or is there a better way to handle this scenario?
My first approach was to flatten the project and process steps into one table. But then I could get misleading calculations I am afraid (e.g. average duration of a project would have a weight implied by the number of process steps per project which is varying).
