PBI Tenant Architecture to support management, scalability, and data restrictions ( RLS )

[CoreyP]

Hey everyone,

I am really at a loss. We have been embarking on a large scale project to supply data and reporting solutions to the entire corporation. I had some time away from development and wanted to focus on structuring our PBI tenant in a way that would be organized, reduce scheduled refreshes, make management easier, implement dynamic RLS, and allow for scalability as we continue to develop solutions. So I created an organizational diagram showing the flow of data between workspaces, datasets, data models, and eventually reports. My goal here was to create different datasets/data models containing bucketed/related objects. These become our data source, importing from various databases and transactional systems. Here, we create our RLS roles and filter the tables based on the viewer's UPN. These sources are generic in the sense that they can be used for multiple reports or domains within the company. I then wanted to create a "contextual" data model, using any of the objects from any of the sources required to fulfill the requirements. In this model, we would establish all the relationships and create our measures. This data model would become the source for reports delivered to end users. These reports would simply have a live connection to the model and only display visuals. Additionally, we could promote these data models as certified or curated for use by citizen developers or other analysts in the organization. The vision here is that we would only need scheduled refreshes on those sources, separated so as to avoid an ever-increasing monolithic dataset/data model. In addition, we manage the RLS in one location, and everything just flows downstream accordingly. We tested the RLS with a basic representation of this structure, and it worked great. As we got everything set up and implemented, we began creating the contextual data model. It was there that we received the error message shown in the diagram when we attempted to create a relationship from a dimension table in one source, to multiple fact tables in another source. Some reading online has shown that this is not a recommended practice, and thus not supported. This is a bit disheartening because we felt we had a well-vetted tenant architecture which would work exactly as intended. So, now, it seems we are back at square one. 

Can anyone recommend a best practice, or alternative way of accomplishing what we're trying to do.

Our checklist includes:

• Organization of data sources, datasets, data models, and reports
• Minimum amount of scheduled refreshes
  • Avoiding multi-hop refreshes ( subsequent refreshes )
• Management of RLS in one location
• Report performance for end users
• Avoiding large, monolithic data models
• Having curated/certified data models that support both DnA projects, and citizen developers/analysts
• Ease of scalability
• Minimal tech debt

Please view the attached tenant architecture diagram to get a good sense of what we're trying to accomplish. I really appreciate any expert insights for an enterprise level solution. Thanks!!

[pmreis]

Hi @CoreyP 

Were you able to move forward with the project or still need any help?

As it's a complex project, it would need consulting support. If you are still facing troubles I'm happy to recommend expertise to help you plan a robust strategy.

[CoreyP]

We haven't landed on a final solution yet. We'll be looking into the Fabric Lakehouse / Warehouse to see if that can meet the needs. I appreciate you checking. I may also set up a meeting with our Microsoft consultant(s).