We define terms as below:
1) Data side: the backend side of the overall Power BI development; the IT department is responsible for extracting, transforming, and loading data into the data warehouse.
2) Report side: the front-end side of the overall Power BI development. The Business Unit is responsible for preparing, modeling from the data warehouse and developing dashboards.
3) Data warehouse developer: responsible for data side development. The objective is to create and populate Tables/Views/Synonyms in Reporting database.
4) Power BI developer: responsible for report side development. The objective is to create and populate Power BI datasets in workspaces, and to author complicated dashboards as needed.
5) Power BI Workspace: Placeholder for Power BI dataset and Power BI reports. Workspaces support security access controls. Each user(s) will need to grant a workspace(s) to access Power BI.
6) Power BI Dataset: a published data model with data and business logic. Business users can use shared datasets to develop visualizations as needed, while the developer can use them as data sources.
7) Power BI Report authors: develop reports/dashboards based on shared Power BI datasets.
2. Power BI development and deployment overview
The diagram below illustrates the overall Power BI development infrastructure, including Data gateway, Power BI workspaces, and deployment pipeline.
3. Configure data gateway with data-level database security
The objective is only to grant permission to access their tables for each Power BI project. The bottom line is that individual Power BI projects cannot access all tables from the database Reporting. At the same time, we want to control Power BI developers’ permission too.
1) There would be a matching Service Account for every Domain Group, which corresponds to a Power BI project.
2) Service Account gets access to DEV, TEST, and PROD.
3) We are giving the Domain User Group access to all environments incl. DEV, TEST, and PROD
4) Who is responsible for managing access in the Domain User Group? IT solution team, requested from the contact center, open ticket with ServiceNow, and workflow (manager, IT).
5) IT Analytics team is responsible for the Service Account password?
6) We should use these SA to access SharePoint too.
4. Establish Power BI dataset workspaces
Currently, we have designed a single workspace to host the Power BI dataset. Unfortunately, this design doesn't work in practice because of three reasons below:
1) The Power BI deployment pipeline can only deploy datasets from one workspace to another. Therefore, we can't allow the same dataset in the same workspace.
2) We shouldn't allow Power BI developers to modify Production datasets directly. Instead, we should use a deployment pipeline to do the promotion.
3) We must secure the Production Power BI dataset like a database.
Therefore, we have defined consistent workspaces for the Power BI dataset, including DEV, TEST and Production. Each dataset workspace should connect to the database, DEV, TEST, and production, respectively.
5. Enable Power BI developers to efficiently deliver Power BI solutions
The objective is to provide an excellent environment for Power BI developers to develop Power BI solutions effectively.
1) Use PBI Desktop to develop dataset and publish to DEV dataset workspace
2) Use PBI Service to refresh dataset in DEV dataset workspace
3) Use PBI Desktop to develop dataset and publish to DEV report workspace
Some of solutions are explored and shared below:
Q1: How is Power BI developer access all databases, incl. DEV, TEST and production?
A1: Power BI developers are assigned to a domain service group based on the database level security design. Therefore, they can access all three databases.
Q2: How to switch database connections for PBI Desktop?
A2: Typically, we are use parameters to switch database connections in a standard way. However, it doesn’t work with the Power BI deployment pipeline. Instead of changing all links for all power queries, you can change SQL server connection with the solution below; once changed, Power BI desktop changes Power queries accordingly.
Q3: How does the Power BI developer refresh the Power BI dataset to connect DEV, TEST, and production?
A3: At the data gateway level, we have added Power BI developers to the data source for DEV, TEST, and production, as Power BI is dataset owner, so they can set up a gateway for Power BI and refresh the dataset.
Q4: How does the Power BI developer refresh the Power BI dataset to connect DEV, TEST, and production?
A4: At the data gateway level, we have added Power BI developers to the data source for DEV, TEST, and production, as Power BI is dataset owner, so they can set up a gateway for Power BI and refresh the dataset.
Q5: How to handle big data volume with Power BI?
A5: Let the Power BI service refresh dataset instead of using PBI desktop. There are two solutions, use dynamic SQL and Filter data using Power Query. Please note the dynamic SQL doesn't support the Power BI deployment pipeline; therefore, please use the Power query filter and see the appendix for solutions in detail.
6. Promote Power BI datasets to Test and Production with deployment pipelines
As we have decided to use the Power BI deployment pipeline to deploy Power BI dataset, we have figured out two limitations with Power BI development below:
1) It doesn't support dynamic SQL. However, we can use the Power Query filter to resolve this issue, described in the last section
2) It doesn't support parameters with the data source; the solution is provided in the last section.
When promoting a dataset, the most helpful feature is to define database connection and parameters rule. The detailed steps are below
step 1: Deploy the dataset to TEST; you need to deploy to enable you to change the rule.
Step 2: Change the connection rule for the dataset. At this moment, the rule is not changed yet.
Step 3: Redeploy the dataset again
step 4: Now you can go gateway to enable connection to TEST database
No comments:
Post a Comment