Sample Header Ad - 728x90

Have I converted this DB table to the correct number of dimension tables for a star schema correctly?

0 votes
0 answers
15 views
I'm trying to replace a pretty complicated query for a dashboard. We currently use this system where we track practices, phone numbers and start/end dates partially in an excel spreadsheet and also in a database. Whenever we want to update any of the info, like which practice uses which number during which time period, it is a very involved process that includes checking numbers and practices in excel to make sure that nothing is duplicated, etc. If we want to change something so that practices share a line we have to go into the db and combine the names, etc. I think the entire process could be simplified if we used a star schema instead of the system that we currently have. We currently have a database table with the following schema | Group Practice | Practice | TransferType | DialedNumber | DigitsDialed | StartDate | EndDate| I think the table should be broken out into the following dimensions: * Practice | PK | Group Practice | Practice | * Phone Number | PK | DialedNumber | DigitsDialed | * TransferType | PK| TransferType | * Calendar Table There would be a fact table at the center that would track "Transfer Volume by Practice". A phone number can have one or more transfer types for one or more practices simultaneously. For example, (123)456-7890 could be transfertype1 for practice A from Jan 1, 2024 - Feb 1, 2024. It could then be used as transfertype2 for practice B from Feb 2, 2024 - Mar 1, 2024. Or Number (234)567-8901 could be transfertype 2 for practice C from Jan 1, 2024 - Feb 1, 2024 and it could simultaneously be transfertype 3 for practice D from Jan 1, 2024 - Mar 1, 2024. I've read textbook definitions of how to set up a star schema but I want to make sure that I'm doing this correctly. Do my dimensions make sense? Would this capture the "slowly changing dimensions" in the data?
Asked by j.jerrod.taylor (101 rep)
Oct 18, 2024, 05:11 PM