October 15, 2015 at 2:53 am
Hi,
Im pretty new to BI modelling and have been assigned a project to create a BI solution for a Patient Administration System (millions of rows). There are no sales or additive information in the system. Basically all of the Measures will be Counts. Im finding it difficult to locate good examples/tutorials of the model I require as most BI implementations seem to be based around Sales.
If anyone could help with my modelling problem I would be very grateful.
Tables (columns excluded for brevity) within the implementation are
Patient (
Key -> Surrogate Key
PatientId -> Source System Id
Name -> Text
AgeKey -> FK to Age Dimension Table
NationalityKey -> FK to Nationality Dimension Table
DateOfBirthKey -> FK to Date Dimension Table
GenderKey -> FK to Gender Dimension Table
etc.
)
Referral (
Key -> Surrogate Key
ReferralId -> Source System Id
PatientKey -> FK to PatientTable
ReferredAtDateKey -> FK to Date Dimension Table
PriorityKey -> FK to Priority Dimension Table
SpecialtyKey -> FK to Specialty Dimension Table
etc
)
WaitingList(
Key -> Surrogate Key
WaitingListId -> Source System Id
ReferralKey -> FK to ReferralTable
AddedToListDateKey -> FK to Date Dimension Table
RemovedFromListDateKey -> FK to Date Dimension Table
StatusKey -> FK to Waiting List Status Table
Position -> int
etc
)
I need to be able to build reports/charts around Patients, Referrals and Waiting Lists so I am using Views to create my Fact tables in SSAS. Examples are:
FactPatient (
Key -> Surrogate Key
PatientId -> Source System Id
Name -> Text
AgeKey -> FK to Age Dimension Table
NationalityKey -> FK to Nationality Dimension Table
DateOfBirthKey -> FK to Date Dimension Table
GenderKey -> FK to Gender Dimension Table
etc.
)
FactReferral (
Key -> Surrogate Key
ReferralId -> Source System Id
-- Patient
Name -> Text
AgeKey -> FK to Age Dimension Table
NationalityKey -> FK to Nationality Dimension Table
DateOfBirthKey -> FK to Date Dimension Table
GenderKey -> FK to Gender Dimension Table
etc.
-- Referral
ReferredAtDateKey -> FK to Date Dimension Table
PriorityKey -> FK to Priority Dimension Table
SpecialtyKey -> FK to Specialty Dimension Table
etc
)
FactWaitingList(
Key -> Surrogate Key
WaitingListId -> Source System Id
-- Patient
Name -> Text
AgeKey -> FK to Age Dimension Table
NationalityKey -> FK to Nationality Dimension Table
DateOfBirthKey -> FK to Date Dimension Table
GenderKey -> FK to Gender Dimension Table
etc
-- Referral
ReferredAtDateKey -> FK to Date Dimension Table
PriorityKey -> FK to Priority Dimension Table
SpecialtyKey -> FK to Specialty Dimension Table
etc
-- Waiting List
AddedToListDateKey -> FK to Date Dimension Table
RemovedFromListDateKey -> FK to Date Dimension Table
StatusKey -> FK to Waiting List Status Table
Position -> int
etc
)
As you can see I am attempting to keep to the Star schema here as I am including the Foreign Keys/Relationships to Dimension tables for related objects e.g. FactReferral has all of the FactPatient relationships.
Is this the right way to model this? Or should I be using a Snowflake schema with FactReferral just having a relationship to FactPatient?
Should I create an extra denormalized Dimension for Patient with no relationships and then create a relationship between it and FactReferral? e.g.
DimPatient (
Key -> Surrogate Key
PatientId -> Source System Id
Name -> Text
Age-> int
Nationality -> Text
DateOfBirth -> Date
GenderKey -> Text
etc.
)
FactReferral (
Key -> Surrogate Key
ReferralId -> Source System Id
-- Patient
PatientKey -> FK to Patient Dimension Table
-- Referral
ReferredAtDateKey -> FK to Date Dimension Table
PriorityKey -> FK to Priority Dimension Table
SpecialtyKey -> FK to Specialty Dimension Table
etc
)
Ultimately Im not really sure how to model the one-to-many relationship tree. There are other tables that are related to Referral such as Assessment and Attendance that I have excluded from my description but I have the same problem with.
Any help or suggestions would be most welcome.
Thanks
October 15, 2015 at 4:15 am
I think you're on the right lines 🙂
I don't think that you need a "FactPatient" table in your underlying data since it looks like it's entirely dimensional. Rather, when it comes to creating your cube, use the Patient dimension table to create a measure group that has a factual relationship to the patient dimension (for counts and such). Then your FactReferral and FactWaitingList tables will only need to have 1 foreign key to the Patient dimension (and not all of the patient-specific ones such as NationalityKey etc.).
I hope that helps steer you along 🙂
October 15, 2015 at 6:30 am
Thanks very much for your help. This set me on the right path 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply