My facts are Tickets (1m rows) and Ticket Actions (0.6m rows), with various dimensions (showing 2 here for simplicity) - some common to both, some unique, e.g. Action Type relates only to Ticket Actions, but it is of interest to see the Ticket Type of the Ticket 'parent' of a Ticket Action.
I have requirements for measures at both Ticket and TicketAction granularity, mainly using date fields; e.g. Tickets open at a given date, TicketActions overdue.
I also need to allow users to drill from Tickets to TicketActions and allow for table visualisations that list Tickets and TicketActions.
I have modelled the data with separate fact tables, using a bridge table to allow drill-through from Tickets to Actions:
My question is:
Should I transform this into a Star Schema? i.e. flatten Tickets and TicketActions, duplicating the date fields in Tickets. Or continue with the bridge table. Or create 2 star schemas with dimensions for each. Or another way..
Very difficult to answer a question like this as we don't have domain knowledge of your data. However, the relationship between DIM_Tickets and FCT_Tickets is one-to-one and so you effectively have FCT_Tickets related directly to FCT_TicketActions which is not a star schema. Two fact tables should never be related and this makes me suspicious that this is probably a header/detail type scenario. If that is the case, then Alberto has a great video on how to model this here: https://www.youtube.com/watch?v=R8SaJ__UMHI