Help designing star schema

[AvgMoJoe]

Relational DB report writer swinging into dimensional modeling, so please be patient.

Essentially I am creating a Fact Table which holds the Numerator and Denominator for various healthcare Measures per Physician.
Each Measure has a Goal and a Score.
This will serve to populate a Scorecard which each Physician will receive. (In the report, we'll compare the % from Num/Den to the Goal and Score appropriately.)
A Physician has the usual demographic info (Name, Practice Name, etc.) and a Primary Specialty.

Modeling this seems relatively simple as I read up on Dimensions and Facts. The tricky part, which is causing me to stumble, is that there are different types of Scorecards. The Scorecard a Physician receives is dependent upon their Primary Specialty. The Scorecard itself has a subset of Measures.

So it's this relationship between Physician, Scorecard, and Measure that's throwing me off.
Physician to Scorecard is many to one. Scorecard to Measure is many to many.
While we may store some Measurements for some Physicians, they won't be part of the Scorecard report.

Before running into this, I imagined a Dimension for each of the following Quarter, Physician, Measure around the Fact Table for Measurements (Num and Denom).
Now I need help working in the Scorecard with its relationships in mind. Maybe I'm over-thinking, maybe I haven't come across the complexity in my reading, but any help would be appreciated!

Thank you in advance!
Joe

 

[johnherman]

Scorecard is a dimension. Just as in the relational world, the dimensional world sometimes needs relation/bridge tables to handle many to many relationships. So, one fact table. Dimensions: Physician, Scorecard, Measures. And just at a glanse, four bridge tables: Physician-Scorecard, Physician-Measures, Scorecard-Measures, and Physician-Scorecard-Measures. You may be able to get by without one of the bridge tables. Perhaps.

Then you mentioned Quarter. Which is a time dimension. So, now decide if you want Type I,II, or III for the dimension changes. That is, do you need to track the changes for Physician, Scorecard, and Measures. For instance, if a Physician gains or loses a specialty or changes practice. Also need to decide if any changes to the bridge/relation tables *outside* of the quarterly reporting cycle are important, or if a snapshot at the quarter is sufficient.

General Fact table
Measurement ID (unique, surrogate)
Measurement Value
Measure (FK)
Quarter (FK)
Physician (FK)
Scorecard (FK)

hope this helps


==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright