Data Model: Multiple Lookup Tables connected through Bridge Table

[kalkhudary]

Hello everyone, 

I am working on a data model that includes multiple Lookup tables that need to speak to each other in both directions.

1 - Applications ( App Id, Status, Division, Purpose)

2 - Servers ( Server ID, Server Name, Status, Condition)

3 - Services ( Service ID, Service Name, Division, Rank)

4 - Subservices ( Subservice ID, Service ID, Subservice Name, Impact)

One Service has many subservices. One Subservice has many applications.One application can have many servers. 

The matrix tables that are in the dashboard should read properly if I click on any application or any server or any service or any subservice and show the details based on the selected name of any of the 4 options whatever the user needs to see an update about.

For example, If user selected Application Z, all servers, services and subservices under application Z need to show. If user selected Server A, the same should happen. If user wanted to view based on subservices and clicked on the Subservice X, he/she should see all Service Name, all applications and all servers that apply.

To fulfill this scenario, I found that the best way is to create a bridging table that connects the matches the IDs of each of the lookup tables together. Then, I have connected each of the tables with a one to many relationship. I have also made the relationship bidirectional for applications, servers, and subservices.

My question here is that I tested the scenario that I explained above to see if the tables are speaking to each other properly and It looked good however I still in doubt if this data model is proper and the relationships that I set will not mess up when the data volume will increase. Also, I am not sure how much this data model can be scalable for the future in case I need to add more fact tables.

Any ideas and opinions would be appreciated as I don't want to build the model and later figure out that it is not the best approach.

[amitchandak]

@kalkhudary , Ideally server should be you Fact. I am not sure how it connected with the application. 

Service and sub-service should combine and give on dimension and join with the server.

Calender should join with server

[kalkhudary]

@amitchandak The Applications are the major fact table in this scenario since it acts as a dependency to servers and subservices. 

I can combine service and subservice together and connect them to Applications however I was worried that it won't work properly with the bi-directional requirement. That is why I thought a bridging table will lessen the risk and make the bi-directional relationship more optimum.

This data model is the trickest and most challenging as the main requirement falls into reading data back and forth.

What do you think?

[Anonymous · Accepted solution]

Hi, @kalkhudary 

Your data model design sounds very well organized, especially by bridging the IDs of the individual lookup tables through a bridge table, which ensures bi-directional communication and consistency of the data.

Here's a brief overview of the semantic model size limitations:

• For Power BI Pro, the semantic models are capped at 1 GB.
• For Power BI Premium P1, the limit is 10 GB per model.
• For Power BI Premium F32, the limit is 10 GB per model.
• For Power BI Premium F64, the limit is 25 GB per model.

In addition, the maximum size of the semantic model can be up to 100 GB when using a Power BI Premium Per User (PPU) license.

These limits are for each individual semantic model, not the sum of all models. Therefore, if you have multiple models, the size limit for each model is independent.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.