Hi all,
We have an aggregated fact table and a person level fact table that need to be kept very separate. Some dimensions are conformed and used by both.
Currently we have 3 reporting schemas in the SQL DB:
- Reporting_Aggregated
- Reporting_Conformed
- Reporting_Detailed
And we have 2 users in the database
- an Aggregated Power BI User with SELECT access on Aggregated and Conformed
- and a Detailed Level Power BI user with SELECT access on Detailed and Conformed (Could probably give them access to Aggregated too)
In Power BI Service we connect to each with the different SQL logins to bring in the data to create the dataflows
But this is where Im now needing a bit of advice.
In SQL We have Power BI users that can only see their corresponding schemas and data in those schemas.
In the Workspace do I either:
- Bring them both onto the one workspace and have some sort of security in there?
- Or 3 workspaces.
- one for Aggregated Fact and Dims
- One for Detailed Fact and Dims
- One for Conformed Dims
The currently logic is that we then create the two models in separate Power BI desktop files. Then load them into another Reporting Layer Workspace.
The aggregated model can be for Publish to web
The detailed one must be for specific users within the company only available via an app.
And we obviously want to make it secure because one model is for the public.
So for the Reporting Workspace. Normally if its all internal users we can use audiences to secure the reports down to the groups who need to see them. But some of this isnt going to be in an app because of publish to web.
So yep, the question is. Can I have dataflows in one and have secure access to the different models or do I need to have 3 workspaces?
Then for the Report Workspaces. Can we have everything in one, Or again should I have separate workspaces?
Im really wanting to avoid having too many workspaces. Semantic Model workspace and Reporting Workspace would be ideal.
Any advice would be really super helpful.
