Forum Discussion
Is it Normal for a Semantic Model to Have Over 50+ Tables
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.
I'd say:
- remove all the data you don't need
- denormalize dimension hierarchies into a single dimension table (avoid snowflakes, use star schema instead)
- fact tables: it depends on what fact granularity you need for reporting
Remove all data you don't need. Remove tables, columns and rows that you don't need.
The number of tables is not the only important thing. The types of relationships, and model layout (preferable start schema or multi-star schema) are important for performance.
Is the semantic model's storage mode Import Mode, DirectQuery or Direct Lake?