Data Model - Multiple tables with Start and End Dates

[WBscooby]

Hi

I am hoping for some advice on how best to model my data.

I have the following key tables (simplified examples due to the privacy nature of the data):

Date Calendar
Client Table: Client ID, Client Name etc

Client Episode: Client ID, Epidsode ID, Episode Start Date, Episode End Date

Client Addresses: Client Address, Address ID, Address Start Date, Address End Date, Address Type

A client can have multiple episodes.
A client can have multiple address lines.

I think my Client Episode Table is probably my FACT table as I will be querying this by end date to find out various measures regarding the client at the end date. Most of these measures will be text facts rather than calculated metrics.

Example questions would be find all client epidodes ending on a given date and return the address of the clients at that point in time.

My issues is that due to the format of the data provided to me, I have several event tables with start and end dates that I need to query by client episode end date. I am hoping for advice on how best to model the data for efficiency as it is a large database. Would it be more efficient to

a) use power query to merge each of the event tables to the client episode table by episode end date and essentially create a large flat table

b) relate all event tables to the episode table via a bridge table - concerns being this may create lots of bridge tables and not all clients will have an applicable event. I also assume this will require some quite technical measures to filter correctly in power bi

c) Another way that I've not considered! Examples/ resources I can look at? 

I would really appreciate any advice before I get started!
Thank you

[WBscooby]

Hi

Thank you for your response! I apologise, I don't think I've been very clear.

There is only one Episode table but I have multiple events tables. A better way of describing may be as event history e.g.

1) Address - This is address history. A client can have multiple addresses with a start and end date if they move
2) School history - A client can have multiple rows if they move schools, all with a start and end date
 Etc

All of the event tables have start and end dates but also have different columns attached to them so I'm not sure I should append them?

Thank you



 

[zenisekd]

You don't describe Events table in the previous model description...

Date Calendar
Client Table: Client ID, Client Name etc

Client Episode: Client ID, Epidsode ID, Episode Start Date, Episode End Date

Client Addresses: Client Address, Address ID, Address Start Date, Address End Date, Address 

Hard to give an option without seeing the event tables and knowing the meaning. You can either append and merge with episodes, which will give you more rows, but less columns or merge events to episodes, in which case you end up with a lot more columns... 

With the dates, you could later:
a) multiple start and end date tables 
b) one dat table, and use inactive relationships, which you will activate later on in DAX measures (using USERELATIONSHIP)...

[WBscooby]

Hi

Sorry, I think I'm being unclear in my description. It may be better to describe the tables as containing history, so the Address table is Address history. I have several tables that contain rows of data with a start and end date for each client but they are all quite distinct tables so I cannot append them. I

For each of the tables, I need to find the events that are 'open' at the episode end date for each client. 

I have tried to create a sample file to demonstrate as the data I am working with is very sensitive.

Thank you

SampleFile.pbix

[zenisekd]

Hi, 
Client Episode is a Fact table
Client table and Client address and Date table are Dimension tables.

If you have multiple client episode tables, then I would suggest you append them in Power Query. To have just one large fact table (add a column, which would specify, what type of episode table it was, so you can query by it as well).

You mention event table, but you do not specify it..?

Finally, if you were to calculate something by the start and end date, you can refer to this video:
https://www.youtube.com/watch?v=8ozINFvuiUg&ab_channel=EnterpriseDNA. 

Kudos and Mark as solution appreciated 🙂