I must laud and thank you for your blog - it is an absolute treasure trove of material on Power BI, which is the best I've come across thus far and a kicker on top that it's free. I'm the Head of Finance at my organization (~50 employees) and I'm in the midst of determining the optimal path to build out our PowerBI infrastructure. I have intermediate (depending on how you define that) experience in SQL, have used Power Query for years, and have created a PowerBI semantic model (with the properly linked fact & dimension tables) which is connected directly to our SQL server, which I've used to create a few reports. The next steps for me are honing my DAX skills and architecting how to share/publish the dashboards where the right people in the organization get and only have access to the information they need.
In this article, my understanding is you're suggesting developers upload the semantic model onto Office 365 in a workspace and pull from that semantic model onto P-BI desktop/web? If the semantic model is pulling data directly from a SQL database (or offline files), I presume security measures will need to be adjusted to enable data refreshes on O365? I hadn't encountered the idea of a 'golden data model' before if that gives you a sense of my current knowledge gaps. I will need to do some learning on this and on Dataflows - would appreciate any supplemental writing you craft on these topics. I haven't done much trial and error thus far concerning report/dashboard deployment so recommendations of options are appreciated.
Hope this substack picks up some steam as you continue writing, it is an absolute gem.
Thanks for the really nice feedback :) And sorry, I've completely missed your comment earlier!
Here's how to build a setup you describe:
1. Create your semantic model using the source of your choice (I use Dataflows which pull from SQL)
2. Include also security roles in that model, using row level security, but you could also use object level security using Tabular editor if you need to hide complete tables from some users.
3. Publish this golden model to a Workspace in PBI service with highly restricted access (only admins). You can still share read/build rights to people without them having access to this workspace. This way they cannot download the golden model pbix with data.
3. Create live connected reports that pull data from that golden data model and publish these reports into a separate Workspace in PBI service. Only report creators need access to this Workspace, not report readers. The live connection respects security rules created in step 2. You can store these live connected pbix files in O365 for example as there is no security risk thanks to the live connection.
4. Create App with multiple Audiences to decide which users should see which reports. If you have all reports published to the same workspace you have the most flexibility to build dashboards that collect visualizations from multiple different reports.
I must laud and thank you for your blog - it is an absolute treasure trove of material on Power BI, which is the best I've come across thus far and a kicker on top that it's free. I'm the Head of Finance at my organization (~50 employees) and I'm in the midst of determining the optimal path to build out our PowerBI infrastructure. I have intermediate (depending on how you define that) experience in SQL, have used Power Query for years, and have created a PowerBI semantic model (with the properly linked fact & dimension tables) which is connected directly to our SQL server, which I've used to create a few reports. The next steps for me are honing my DAX skills and architecting how to share/publish the dashboards where the right people in the organization get and only have access to the information they need.
In this article, my understanding is you're suggesting developers upload the semantic model onto Office 365 in a workspace and pull from that semantic model onto P-BI desktop/web? If the semantic model is pulling data directly from a SQL database (or offline files), I presume security measures will need to be adjusted to enable data refreshes on O365? I hadn't encountered the idea of a 'golden data model' before if that gives you a sense of my current knowledge gaps. I will need to do some learning on this and on Dataflows - would appreciate any supplemental writing you craft on these topics. I haven't done much trial and error thus far concerning report/dashboard deployment so recommendations of options are appreciated.
Hope this substack picks up some steam as you continue writing, it is an absolute gem.
Hey Johnny,
Thanks for the really nice feedback :) And sorry, I've completely missed your comment earlier!
Here's how to build a setup you describe:
1. Create your semantic model using the source of your choice (I use Dataflows which pull from SQL)
2. Include also security roles in that model, using row level security, but you could also use object level security using Tabular editor if you need to hide complete tables from some users.
3. Publish this golden model to a Workspace in PBI service with highly restricted access (only admins). You can still share read/build rights to people without them having access to this workspace. This way they cannot download the golden model pbix with data.
3. Create live connected reports that pull data from that golden data model and publish these reports into a separate Workspace in PBI service. Only report creators need access to this Workspace, not report readers. The live connection respects security rules created in step 2. You can store these live connected pbix files in O365 for example as there is no security risk thanks to the live connection.
4. Create App with multiple Audiences to decide which users should see which reports. If you have all reports published to the same workspace you have the most flexibility to build dashboards that collect visualizations from multiple different reports.
Hope this answers your questions!