Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help designing star schema

Help designing star schema

Help designing star schema

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!

RE: Help designing star schema

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close