Cre­at­ing semantic mod­els in Power BI empowers users to build their own reports whilst ensur­ing stand­ard­ised data and seam­less con­nectiv­ity to con­sist­ent data sources. Semantic mod­els provide a solid found­a­tion for report­ing work­flows by defin­ing key met­rics, apply­ing updates post-deploy­ment, and man­aging secur­ity roles. Designed with reusab­il­ity in mind, they sim­plify tasks for both tech­nical teams and busi­ness users.

How­ever, a fre­quent request – and chal­lenge – is to com­bine mul­tiple semantic mod­els into a single, uni­fied one. For instance, busi­ness users may have been gen­er­at­ing some reports from a Recruit­ment semantic model and oth­ers from an Employee Per­form­ance model, but when they want to merge these into a single object to con­sol­id­ate their report­ing cap­ab­il­it­ies, the pro­cess gets tricky and needs care­ful planning.

When design­ing a semantic model for self-ser­vice report­ing, the focus is on defin­ing a spe­cific busi­ness area and cre­at­ing a schema that cap­tures its events and data, unlike design­ing a report from scratch. While com­bin­ing two dis­tinct areas into a single semantic model can enable users to gen­er­ate more com­plex and integ­rated reports, this approach comes at a price.

Dis­ad­vant­ages of Com­bin­ing Mul­tiple Semantic Mod­els Into One

  1. Usab­il­ity: Con­trary to what we may think, mer­ging mul­tiple busi­ness areas into a single semantic model will reduce its usab­il­ity. Let’s think about the audi­ence. A model that integ­rates two busi­ness areas will only be access­ible to users that have both the required know­ledge and per­mis­sions for both sets of data. The more areas we com­bine, the nar­rower the poten­tial audi­ence becomes. On top of that, if access is gran­ted to users for just one of the areas, Object Level Secur­ity (OLS) must be applied, again increas­ing the model’s com­plex­ity and mak­ing it harder to maintain.
  2. Own­er­ship: As we men­tioned above, com­bin­ing mul­tiple areas reduces the audi­ence size, and we need to con­sider that the owner of the Power BI model must be part of this smal­ler group. From a tech­nical per­spect­ive, fewer developers are likely to have the expert­ise to cre­ate, test, and main­tain such a com­pre­hens­ive model. This means we will likely have mul­tiple con­trib­ut­ors work­ing on the same Power BI solu­tion, but none of them will be able to see the entire picture.
  3. Design: Usu­ally, when we cre­ate a semantic model, we try to keep it simple and use a star schema. How­ever, when com­bin­ing mul­tiple mod­els the star schema no longer applies, and rela­tion­ships between tables become harder to man­age. Although we can use the DAX func­tion USERELATIONSHIP() for spe­cific meas­ures, it won’t pre­vent the model from becom­ing more com­plex, and any future modi­fic­a­tions will become increas­ingly dif­fi­cult to implement.
  4. Size: From a busi­ness point of view, a com­bined semantic model may poten­tially have hun­dreds of fields to choose from when users want to build a report. Sim­ilar field names can cre­ate con­fu­sion, and while adding field descrip­tions can help, the res­ult­ing model would still mean extra effort for users to cre­ate reports. And from a tech­nical point of view, the lar­ger num­ber of tables and rows increases the model size, which leads to longer refresh times if we use Import mode.
  5. Depend­en­cies: There’s a greater risk of depend­ency issues with com­bined mod­els. For instance, a refresh fail­ure in one source table—perhaps tied to a small busi­ness area—can cas­cade, ren­der­ing the entire model inac­cess­ible. This can lead to situ­ations where, for example, we can­not ana­lyse Employee Per­form­ance data because a table from Recruit­ment did not load prop­erly, and such inter­de­pend­en­cies are dif­fi­cult to jus­tify to stakeholders.
  6. Dif­fer­ent refresh times: Dif­fer­ent busi­ness areas often have dif­fer­ent require­ments for data fresh­ness and refresh fre­quency. With dif­fer­ent refresh sched­ules for each area, busi­ness users might struggle to determ­ine which data­sets are up-to-date.

A Few Alternatives

Des­pite the dis­ad­vant­ages we’ve dis­cussed, there are cases where a busi­ness needs an ana­lysis cov­er­ing mul­tiple areas. In such scen­arios, rely­ing on Power BI’s self-ser­vice cap­ab­il­it­ies may not be the optimal solu­tion. Instead of attempt­ing to cre­ate a uni­fied semantic model for a broad audience—many of whom may not be Power BI experts—it is often more prac­tical to develop an ad hoc Power BI report with a ded­ic­ated semantic model. By keep­ing the report and the self-ser­vice model as sep­ar­ate objects, we can ensure that the pur­poses of both deliv­er­ables remain dis­tinct and sig­ni­fic­antly mit­ig­ate the chal­lenges we’ve out­lined in this blog post.

If the object­ive of com­bin­ing areas is to offer an over­all view to a wider audi­ence whilst keep­ing the areas inde­pend­ent, it might be a good option to cre­ate indi­vidual reports for each area and com­bine them into a Power BI dash­board. This approach, together with Row Level Secur­ity in the under­ly­ing semantic mod­els, offers users a uni­fied view of mul­tiple areas. How­ever, it’s import­ant to note that they will not be able to cross data between models.

In con­clu­sion, Power BI is a ver­sat­ile and power­ful tool that offers busi­ness users the abil­ity to cre­ate their own reports. How­ever, we need to main­tain clean, well-struc­tured solu­tions to ensure that the model has long-term usab­il­ity and adoption.

If you need assist­ance with Power BI Semantic Mod­els, optim­ising your data strategy, or imple­ment­ing best prac­tices, we’re here to help! Con­tact us now to see how we can help you to unlock the full poten­tial of your Power BI environment.