Dimensional Model Design

Question

I am use to Snowflake ER Model, decided to delve into Star Schema Dimensional Model (Facts & Dimension).

Background Hospital Data: Track Patient what issues (Comorbidities) a patient had prior a surgical procedure i.e Heart condition

Issue A Patient can have many Procedures, each procedure can have many issues (Patient is asked before every Procedure if they have any issues (Comorbidities) i.e. heart)

Design

• Procedure Fact table, track how many procedures they had, Date of procedure, their age at time the procedure, patient id …
• Comorbidities Bridge Table, to manage the 1 to many relationship between a Procedure and Comorbidity , this is used to populate the Comorbidities Fact Table (Each procedure can record more than 1 issue , i.e. Patient has heart condition, has blood condition ..)
• Comorbidities Fact Table, Tracks each Comorbidity (Patient issue) for each Procedure

I want to Calculate the % of Comorbidities for each Procedure

Is there a more efficient way of doing it, than what I am doing? I feel I am finding myself going down the rabbit hole and getting caught in a Snowflake ER Model design on top of a Star Schema

Calculation

With CTE As 
             (
               --PROCEDURES
              Select  
               [ProcedureSite] ,
               [ProcedureType], 
               SUM([NumOfProcedures])As[NumOfProcedures]
              From [Fact].[Procedures]P
               Inner Join [Dim].[Procedure]P1 On P1.[ProcedureID]= p.[ProcedureID]
              Group By 
               [ProcedureSite],
               [ProcedureType]

             )




--COMORBIT
     Select 
      P1.[ProcedureSite], 
         P1.[ProcedureType],
      [Comorbidity],
      Sum([NumOfComorbidities]) As [NumOfComorbidities],
      C2.[NumOfProcedures]
      From [Fact].[Comorbidity]C1
      Inner Join [Dim].[Procedure]P1 On P1.[ProcedureID]= C1.[ProcedureID]
      Inner Join [Dim].[Comorbidity]C On C1.[ComorbidityID] = C.[ComorbidityID]
      Left Join CTE C2 On P1.[ProcedureSite] = C2.[ProcedureSite] And P1.[ProcedureType] =C2.[ProcedureType]
      Group By P1.[ProcedureSite], 
      P1.[ProcedureType],[Comorbidity],[NumOfProcedures]
      Order By 
      P1.[ProcedureSite], 
      P1.[ProcedureType]

DB Setup

https://1drv.ms/u/s!Ao-FTTt3GMhriI1nLvNAEJRowR1jTQ?e=EunmNa