SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DM Modeling Suggestion

DM Modeling Suggestion

SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 316
Hi everyone,

I have some question regarding DM design. I am currently working on a clinical DM. Ultimate goal is to build a SSAS cube and run report from our clinical DM.

Business Scenario is fairly simple: Patient can have multiple hospital visits, and for each visit, patient could have multiple transactions for lab, medicine and procedures.

So far these are the dimension we have:

- Patient
- Procedure
- Lab
- Medicine
- Procedure
- Geography
- Flag Field Junk Dimension

We currently have one fact table. Its granularity is at transaction level. Our per visit level information is also in the fact table as repeating column value.

FK_Paitient / FK_Lab / FK_Medicine / FK_Procedure / 6 Lab measure attributes / 5 Medicine measure attributes (5) / 3 Procedure measure attributes (3) / 8 Visit Level Attributes

Each fact line contains lab result/applied medicines/performed procedures during patient visit to our hospital. So, if patient has 7 transactions during visit, we will have 7 lines in fact tables. Since all transaction's attributes are merged, not attribute columns have some values. Some will have null values depending on the data sources (if data source is from medicine, only the medicine related attribute columns will have data and other will be filled in with null value, if data source is from lab then lab attributes will be filled in and other attributes will be filled in with null).

However, since all transaction gets generated based on patient visits, all visit level attributes information are all populated and repeated 7 times. We were trying to stay in star schema, and we thought if we were to write a report from the current design DM, we would set the data scope based on patient visiting date, and show all tied transactions.

Since this was our first attempt, we realized our design is flawed, and this is where we are stuck and not sure what would be best practice or solution...

1. Is it okay to break fact table into 4? Instead of one giant fact table, create fact table for visit level, lab transactions, medicine transactions, procedure transactions. Each our data scope is determined based on patient visit date, so fact tables will have visit_date_key and some minimal visit level key values.

2. In our analysis scenario, all of analysis is done on visit level. Lab/medicine/procedure transactions are required for report to show some detailed information. (to show MDs what lab tests are done and results for lab tests etc.) We are bring these data for drill through/down purpose only. When we roll up these values, it really means nothing. We can only derive factless(?) fact type of measure from these transaction data (i.e. total number of medicine administered during patient visit). In this situation, is it better to create visit dimension rather than visit fact, and try to derive measure from visit dimension and combine three transaction (medicine, lab, procedure) into one fact table?

3. If we break the fact tables into 4. What would be the proper way to link visit fact to transaction fact tables? It's 1-M from visit to lab/medicine/procedures. Only common dimension for these 4 fact tables would be patient, and visit fact table will act like a dimension...

Any suggestion would be greatly appreciated!


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum