Calculating N of visits for each caller

[razmochaev]

Hi everyone. Been struggling for a long time with the following task.

I have two fact tables:

1. factLeads, containing data for incoming calls and lead source. Some of the callers are our existing clients.

2. factVisits, containing data for actual visits to our clinic. 

I also have a bridge table that stores associations with phone numbers and client IDs. One client can have many phone numbers and one phone number can belong to many clients.

I need to measure, for each call, the number of visits conducted during the next 30 days after the call.

As far as I can see it, the solution is based on the following steps:

1. For any given time period get the list of phone number

2. Get the client IDs from bridge table who have these phone numbers associated with them.

3. Filter the factVisits table with those client IDs.

Here's a sample file 

I will be very grateful for any suggestions.

[v-zhangti]

Hi, @razmochaev 

Can you provide simple PBIX files instead of Excel files? What do you expect the output to look like and can you show it with pictures?

Best Regards

[lbendlin]

That's not a bridge table, that is a dimension - in this case the phone number. Bridge tables would have arrows in two directions, at least on one side.

Try combining the dimLeadsPhones and dimClients tables.

[razmochaev]

Hi @lbendlin 

Sorry for bad quality screenshot of the data model, I've replaced it with a better one, clearly showing that bridgeClientsPhones is a bridge table connecting dimClients and dimLeadsPhones, as a client can have many phone numbers and a phone number can be attributed to many clients in our database.

[daXtreme]

"Bridge tables are dimensional tables needed to address the many to many relationships between facts and dimensions OR dimensions and multi-valued attributes you may come across when modeling your star schema."

[lbendlin]

That is an insufficient definition. A bridge allows the flow of something ( in this case filters) in one or both directions. In your data model no filter flow is possible in either direction.

[daXtreme]

Simply put, a bridge table is any table that relates one entity to another in order to define the many-to-many relationship between the entities. This is the most general definition of a bridge table. It has nothing to do with any filters, any models, any directions and so on. Such a bridge table will have at least 2 columns in which the keys to the said entities will be placed.

And therefore the table that @razmochaev calls "a bridge table" is indeed a correctly defined bridge table.

What is a bridge table in data warehouse? – idswater.com