Forum Discussion

goldenarm253's avatar
goldenarm253
Frequent Visitor
1 year ago
Solved

Is it Normal for a Semantic Model to Have Over 50+ Tables

Hello Community, We currently use a vendor-created semantic model for our financial reporting. This model contains over 50 tables, including multiple fact tables and numerous dimensions like departm...
data engineering
  • govindarajan_d's avatar
    govindarajan_d
    1 year ago

    Hi goldenarm253,

     

    Based on my experience,

    1. 50+ tables is not typically ideal for a semantic model unless lot of KPIs are dependent on all the tables and the data modelling is done in a good way(No complex relationships). Are you clubbing different business use cases in the sameSM? If so, there is also a possibility of exposing data that is not needed by specific persons who use this model. You would also have a hard time applying RLS. If the model will be used for self-service, the number of objects in current model can overwhelm the business user. 

     

    2. I would say consolidate the dimensions based on your requirement. Even with 50 tables, I don't think all columns would have been used. You can think about junk dimension if only 1-2 columns are used across each dimension table. You can also use Measure Killer to know how much of the model objects is actually being used. 

     

    3. Ideally, the semantic model would be designed based on the audience and the intended purpose. This would then help you to think about the level of granularity you need to have in the SM. Take into consideration the KPIs that will be created but don't create a model specific to those KPIs alone. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi goldenarm253,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

 

I suggest you to Focus on performance and user-friendliness rather than size.

1. Is it typical for semantic models to include 50+ tables?
Yes, Large models are often seen in financial reporting as they need to cover various dimensions and fact tables to support a wide range of reporting needs.

 

2. Should you simplify the model?
Combine less frequently used, detailed dimensions or create summary tables to streamline the model and improve efficiency.

 

3. Best practices for managing vendor-created models:
Identify and remove unused tables, columns, and measures that may have been included, and avoid direct filtering in Power BI, as it can impact performance. Instead, implement filtering within measures to maintain efficiency.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Regards,

Vinay Pabbu