What is our Process?



Processes of Power BIThis paper describes several of the fundamental processes and workflows within Power BI environments. By understanding these processes, including their common owners, tasks and considerations, you can adopt and enhance processes aligned to the requirements and goals of your organization’s Power BI environment.A process can be defined as a task or set of tasks carried out by individuals according to a set of policies, procedures, or logic. Therefore, by definition a process does not exist when either the individuals responsible for executing the task(s) are not known or the logic, procedures or policies to be executed are not known. The success or failure of Power BI at a project or overall environment level is often driven by the processes implemented (or not) rather than the levels of technical skill, available resources, or the capabilities of Power BI. These other factors are of course important and are written about extensively but its processes which tie together people (e.g. developers, analysts) with technology. What is our Process?Processes in Power BI environments generally reflect the policies of an organization, its values, the level of maturity of the environment, and the preferences and goals of those responsible for deploying and managing Power BI. Therefore, leaders of Power BI environments, including solution architects, administrators, and business intelligence team managers and directors may benefit from considering the following questions in relation to their Power BI environment: Do we have a process for this workflow? If not, what are the risks and costs associated with our current state? How effective is our process?Does it have a sufficient level of logic and rigor to drive informed decisions and quality solutions?Alternatively, is the process too simple and generalized in its interpretation?For example, does a business analyst simply request a workspace or a pro license in order to obtain these items? Similarly, does a BI architect apply a set of specific criteria in evaluating a dataset for certification or is only variable such as data accuracy considered? Is the process too rigid and restrictive?There are highly diverse use cases for Power BI which could inadvertently be denied or shut down if a process enforces a narrow or singular approach. Additionally, an excessive or complex set of required tasks can impact project timelines and perceived value by various stakeholders. Which processes should be targeted for improvement or re-evaluation?Typically, a BI team responsible for managing Power BI and contributing to or delivering Power BI solutions must allocate resources between ongoing projects and support responsibilities with higher level environment-wide decisions. Therefore, processes can be prioritized and targeted for evaluation and improvement over time. The Costs of Ad HocWhen you don't have a process, tasks get carried out in a haphazard and unpredictable manner. There’s no clear expectation of a timeline for delivery and there’s no confidence that the outcome will be of high quality or sustainability. A few of the symptoms of ad hoc or ‘on the fly’ approaches to Power BI processes include project delays, often reflecting long email chains to determine who can and should execute the task and why as well as suboptimal designs such as datasets which don’t perform or only perform via an excessive use of resources which can drive up the cost of Power BI. It’s very easy to accumulate a high level of technical debt when you don’t have or follow processes. Once a user or team has a certain asset such as pro licenses or premium capacity or has built certain artifacts such as Power BI datasets it can be very difficult or costly to re-factor this content or reverse prior decisions. Without active monitoring (an essential process), you could quickly find, for example, that premium capacity resources are now fully utilized with untrained users creating near-duplicate datasets for each new report.A real but less tangible cost associated with a lack of process is the perceived unfairness and accompanying rivalry between individuals and teams. For example, is there a defensible business justification that a certain user or team or project is permitted to use premium capacity resources but another user or team or project is not? Well-defined and documented processes governing Power BI resources and permissions can help avoid any perception that certain teams are monopolizing Power BI resources to their own benefit at the expense of other teams or the organization. Power BI ProcessesThe list of processes identified in this paper is not comprehensive but rather a representative sample of processes that readers can consider and extend to their own environments. Additionally, it’s assumed that the reader is familiar with common Power BI terms and concepts and thus the introduction of this content unnecessary. For example, if you’re unsure what an on-premises data gateway is or what permissions are associated with a Power BI Pro license then you should first learn this material before considering a process to manage these assets. It’s not the purpose of this paper to recommend any specific best practice to follow for each process but rather to highlight factors and examples to consider in formulating a process suitable for a given environment. The following processes are covered in this paper:Pro License AssignmentPremium Capacity Provisioning HYPERLINK \l "_Power_BI_Desktop" App Workspace CreationPower BI Desktop Deployment HYPERLINK \l "_Solution_Design_1" Solution Design HYPERLINK \l "_Active_Directory_Group_1" Active Directory Group Administration HYPERLINK \l "_Report_Development" Report Development HYPERLINK \l "_Application_Lifecycle_Management" Application Lifecycle Management HYPERLINK \l "_Monitoring_Power_BI_1" Monitoring Power BIDataset Development HYPERLINK \l "_Publishing_Content_1" Publishing Content HYPERLINK \l "_Certifying_Datasets_1" Certifying Datasets HYPERLINK \l "_Managing_the_On-Premises" Managing the On-Premises Data Gateway HYPERLINK \l "_Administering_Power_BI_1" Administering Power BI PermissionsLearning Power BITraining on Power BI Pro License AssignmentIf premium capacity has been provisioned, only users that will be publishing and editing content in workspaces require a pro license. Therefore, for environments utilizing Power BI Premium capacity to support the read-only needs of end users or consumers, a process is needed to determine who should be assigned a pro license, on what basis, as well as how this license should be assigned and who is responsible for this assignment. At an absolute minimum, it should be determined that the user has a business need for a feature exclusive to Power BI Pro licenses. It’s very common for users and administrators with limited experience in Power BI to conflate the need for access to published Power BI content or to the use of Power BI Desktop with a need for a Power BI Pro license. Other considerations as part of the pro license assignment process may include the following:Are there other user(s) on the given team that have been assigned a pro license?If so, it may not be essential that this user have a pro license as the pro-licensed users on the team can handle publishing the content to workspaces and apps. Has the user received or passed any training on Power BI?For example, has the user reviewed and agreed to policies in using Power BI within the organization? Similarly, has the user proved a basic understanding of Power BI such that there’s confidence the user will not inadvertently use Power BI incorrectly or inefficiently such as with duplicating datasets. Does the user’s manager or project leader approve/support the pro license request?Must someone on the IT/BI team responsible for Power BI also approve the request or is it completely driven by business request and approval?The IT/BI team may utilize a standard form and quickly review the merits of a given request. Are there conditions in which the user’s pro license can be revoked?A failure to follow certain essential, agreed upon policies such as data security may justify at least temporarily suspending a pro license. Like other processes in Power BI environment, the key is to embed reasonable and targeted checks and logic without severely impacting timelines or limiting viable use cases. The timeline to deliver an assigned pro license is important as typically the requesting user has a clear and present use case. Therefore, ensure that approvers of pro license assignments act on incoming requests and that there are technical processes such as PowerShell scripts to quickly assign the approved licenses. Premium Capacity ProvisioningPower BI Premium is much more than enterprise datasets and read support for free licensed users. Premium capacity resources can be allocated between datasets (Analysis Services in Power BI) and other workloads including paginated reports (essentially Reporting Services in Power BI), AI, and dataflows for self-service data integration. Additionally, premium capacities are commonly architected to isolate distinct projects or sets of use cases such as splitting self-service BI solutions owned by business teams from enterprise BI (IT-owned) solutions. A significant level of thought and discussion needs to go into the process of provisioning and allocating premium capacity. Given the cost of premium capacity and the scenarios which can only be feasible with premium features, these decisions essentially reflect an organization’s top priorities for Power BI. Here are some questions and considerations to help advise this process:Is it the intent for Power BI to support self-service BI projects only or enterprise BI (IT-owned) projects only or both? Given the very distinct nature of the people and projects involved between self-service and enterprise BI it may be useful to provision separate premium capacities with distinct workload settings and company policies governing each. Is it the intent for Power BI to replace existing enterprise reporting platforms?Many organizations with past investments in enterprise reporting platforms including SQL Server Reporting Services (SSRS), Cognos, MicroStrategy, and others are naturally inclined to sunset these systems to better align with a more modern cloud service offering like Power BI. The paginated report workload which supports the pixel-perfect or ‘canned’ reports of SQL Server Reporting Services (SSRS) applicable to these enterprise report migration projects represents a static allocation of premium resources. Therefore, the planning process needs to account for the resources required for both paginated reports and Power BI datasets.Do you plan to migrate each report 1:1 to a paginated report in Power BI or do you expect self-service BI ad hoc analysis and Power BI interactive reports to replace many of your legacy reports? Is it the intent for Power BI to support self-service ETL scenarios?Power BI has always had Power Query (M) and many organizations are surprised at the degree to which Power Query (M) expressions are embedded throughout their Power BI datasets and even many Analysis Services (Tabular) models to deliver missing or value-enhancing logic not available in source systems. When properly deployed, dataflows in premium capacity can be extremely valuable for self-service BI scenarios. However, many organizations may not have confidence that these artifacts can be developed and managed properly and thus choose to limit or avoid the use of dataflows. One use case for dataflows is to support the migration from other self-service ETL solutions such as Alteryx and to consolidate various self-service ETL use cases (ad hoc SQL, MS Access, Excel) into one self-service ETL tool in Azure. Like other workloads, if dataflows are desired then, even though the resource allocation is dynamic, planning is needed to provision sufficient resources for other dataflows with other workloads. What workload settings will be applied to the premium capacity?For example, will you impose lower limits on the number of rows queries can return or the amount of memory queries can use in order to avoid impacts from inefficient reports or datasets? The avoidance of ‘data extracts’ (aka ‘data dumps’) via Power BI reports may be an important policy to ensure Power BI reports are used for data analysis and visualization, not as ETL tools.Who will be granted permissions to assign workspaces to premium capacity (if anyone beyond admins)? Who (if anyone) will be assigned to the Capacity Admin role?Who will be responsible for monitoring the premium capacity via the Premium metrics app?App Workspace CreationPower BI content is contained in app workspaces which are hosted in premium or shared capacity and which have a one-to-one relationship with Power BI apps, the primary method of distributing content to end users. Therefore, the creation and scoping of app workspaces can significantly impact the overall environment in terms of resource utilization as well as the user experience. and should be governed by some level of policy and process.Similar to pro license assignments, the app workspace creation process should be severely limited to a small number of users or groups, perhaps 1-2 users representing different teams, thus creating a clear line of communication between the IT/BI team managing Power BI and the teams utilizing Power BI. Additionally, the app workspace creation process should drive informed decisions about the scope and access to the workspace. When a scenario arises involving an apparent need for a new workspace, the following considerations should be accounted for:Does the content to be included in a workspace represent a duplication or near-duplication of the content in another workspace? If so, can the existing workspace and its app be extended or modified to meet the requirements?What will be the scope of the new workspace? Perhaps multiple workspaces should be created to preserve a more focused user experience.Power BI workspace roles are utilized to grant only the permissions requiredIt’s not uncommon, for example, to find over 10+ users mapped to the Power BI administrator role. In almost all scenarios, these elevated permissions should be reserved for 1-2 users per workspace. End users or ‘consumers’ who only require the ability to view and interact with the content are not granted membership to the workspace of the content. End users should generally consume the content via a published app. The Power BI viewer role ‘can’ be utilized (in premium capacity environments) to provide users with free licenses read access to the workspace but this should usually be limited to a few test users.Power BI apps should remain the primary option for publishing/distributing to groups of users given their various advantages and features such as content navigation and isolation from workspace content. Power BI Desktop DeploymentPower BI Desktop is the primary tool used to create core Power BI artifacts (datasets, reports) and is also regularly used for ad hoc self-service analysis via live connections to Power BI datasets or Analysis Services models. As new versions of Power BI Desktop are released monthly, a process is needed to ensure a common, recent version of Power BI Desktop is deployed across the organization.It’s common to find a mixed approach in Power BI environments with some users obtaining local admin rights and manually installing Power BI Desktop on an ad hoc basis whereas other users (without admin rights to their device) submit a request to obtain an updated version. In many cases the version available to fulfill update requests is itself several months old and there’s no process for determining the version to be deployed.The following items can be considered with this process:Minimize or avoid granting local admin rights and manual, user installs This approach introduces version control issues as the owner of the device controls the updates and may not align with any standard. Local admin rights could also introduce security vulnerabilitiesCompare using the Microsoft Store in Windows 10 for automated updates versus a controlled, packaged deployment approach with Microsoft Endpoint Configuration Manager or another tool. Microsoft Store is of course only an option for Windows 10 but assuming devices have been updated to Windows 10 this approach avoids the management overhead of a regular update process and ensures users have the very latest version including any fixes between monthly updates.If the greater level of control is desired or required, and the organization has access to software deployment tools and experience with similar software deployments/updates, define a process and cadence to be implemented:For example, a certain group of Power BI Desktop test users will be responsible for reviewing and testing the latest version of Power BI Desktop with a set of existing reports once a month or once every two months. If no issues are found, this version will be deployed automatically to Power BI Desktop users throughout the organization. For example, the August 2020 version of PBI Desktop (released on 8/12/2020) would be deployed to users sometime in September of 2020 so the organization would always be 1 to 2 months behind. Solution DesignPower BI Desktop makes it very easy to produce BI content of value quickly and with little to no coding or planning. However, it’s still almost always appropriate early in projects to create solution design documents to describe the solution architecture and facilitate further planning and iterations. Review and approval of the solution design by other developers and architects may be made a requirement in order to deploy to premium capacity. A failure to design solutions and review these designs can lead to hidden technical debts and solutions that are more difficult to support and maintain. As one example, a simple diagram could highlight the connections between the datasets and the data sources, the reports to the dataset(s), and finally (if applicable) the dashboards to the reports. At a minimum, this diagram and a solution design review meeting would help reinforce the essential concept that Power BI reports are separate from Power BI datasets and that a single dataset for multiple or all reports is desirable. The diagram and documents should provide clear answers to structural issues such as the granularity supported by the datasets, the scope of the workspaces, and why multiple datasets are used. Here are a few items you may consider as part of a solution design process:A solution architecture diagramThis should identify the data source(s), dataset(s), reports, and dashboards Of course, iterations and revisions at the visualization layer can be expected so a greater level of focus should be on the data source and datasetA solution design documentWhat is the goal or purpose of the solution?Who are the primary stakeholders (Users, Developers)?How is security being applied?Are there RLS roles in the dataset or is the source system applying security via single sign-on? Another diagram may layout the workspace(s), apps, and premium capacity Will the solution be completely contained in a single Power BI app and its source workspace or are multiple workspaces and apps needed?Which premium capacity will host the solution?Does the capacity have resources available? Are test workspaces and deployment pipelines required or is the scope/complexity such that this isn’t necessaryDetermine the role of paginated reports in the solution versus interactive Power BI reportsIf the intent is to provide a familiar user experience and functionality as traditional enterprise reporting platforms such as multi-page printing and email attachments, then paginated reports are naturally the option.If the intent is to promote a more visual, interactive experience, even if this introduces some change to the end users and the loss of certain features, then development hours can likely be saved by focusing on interactive reports. Of course, solutions can include a mix of paginated and interactive reports leveraging their unique features as appropriate.Active Directory Group AdministrationSecurity groups are required for granting certain groups of users the permission to use certain Power BI features such as creating workspaces and using datasets across workspaces. Security groups are also the recommended approach for providing end users with access to Power BI content such as Power BI apps. Therefore, a well-designed and managed Active Directory security group creation and management process is essential for Power BI environments.Typically, an Identity and Access Management (IAM) team will already have a process and internal knowledge and skills in group creation and management. However, in many cases the business teams or even the BI/IT teams deploying Power BI solutions may not know or trust in this process. This can result in large, manually maintained lists of users being added to workspaces, apps, and RLS security roles or Azure Active Directory groups being created and managed outside of any standard IAM process. Other common issues include the duplication or near-duplication of existing security groups and the lack of support in existing processes for creating mail-enabled security groups, which are required for certain features in Power BI.If you (or your Identity team) don't build and manage processes around AD groups, you often see manually maintained lists of users mapped to Power BI apps and/or dataset row-level security roles. Additionally, you need to be clear on the differences between types of groups (distribution lists, O365 groups, mail-enabled security, security) and what these groups can be used for in Power BI. Without going into detail, only mail-enabled security groups can be used across all permissions and functionality. The following items can help to establish and improve the group creation process:Identify existing Power BI solutions which are not utilizing security groupsDo you have long lists of users in various workspace roles?Is there a long list of users with access to a Power BI app?Is there a long list of users mapped to an RLS role in a Power BI dataset?Obtain input from the owners of these solutions why they’re not using security groups?Are they not aware of a process to create a group?Do they not trust the group creation and management processes?For example, they’d rather manually update their lists of users because requests to add or remove users from groups can take several days. Understand the current state of the group creation and management processIs there an intuitive request form in a corporate portal? Is there clear ownership and SLAs on completing the requests?Are different group types (O365, mail-enabled security) supported by the existing process? Make a list of the groups needed now or in the near future:For example, you may want a group for the Workspace Creators permissions, groups to replace long lists of users for different Power BI apps, and a group for developers and testers that can be mapped to workspace roles. Close down the gaps between the areas in Power BI which should use groups over time and build greater trust/confidence in an IAM-owned process for groups.Report DevelopmentDeveloping reports is one of the most fundamental processes in Power BI and thus it’s important to embed certain practices to guide this development toward quality, consistent reporting content. In the absence of a report development process, Power BI’s rich set of visualization options can quickly lead to an environment filled with reports containing vastly different design approaches that confuses end users. As one example, one Power BI app could utilize several 3rd party custom visuals and many slicer visuals aligned at the top of each report page while another app could only use standard visuals and only use the filter pane for end-user filtering. Here are several considerations with respect to report development:Ensure that report developers have a crystal-clear understanding of the difference between datasets and reports. One of the most painful scenarios to avoid is for a new dataset to be created for each new report. However, despite years of documentation and training on this issue it remains common for users to conflate datasets and reports (as they’re both built in Power BI Desktop) resulting in a high volume of mini-datasets representing data silos that can’t easily be migrated away from. Ensure report developers understand which datasets should be used, such as certified datasets, and that they have required access to build reports against these datasets.Typically the report developers should not require access to the workspace of the source dataset since they can be granted build permission to the dataset.Recommend or require data visualization training such that business analysts and BI developers creating reports are familiar with data visualization practices regarding the proper choice of visual, alignment, symmetry, the use of color, and more. Thoughtfully consider paginated reports versus Power BI reports (interactive reports).Assuming premium capacity has been provisioned and allocated for paginated reports, there are several scenarios such as email attachments of report data and multi-page printing at a detailed granularity in which paginated reports are more aligned to the requirements. Build a set of report templates for both interactive and paginated reports to streamline report development. The report templates generally contain standard colors, logos, report themes, and may contain a connection to a certified dataset. Different templates can be available for different datasets or for different teams and scenarios. Utilize a Power BI Center of Excellence to design on various report standards that must be followed such as titles, headers and footers (in the case of paginated reports), corporate logos, color schemes, fonts and font sizes, etc. Application Lifecycle ManagementDo you have any standard process in managing Power BI solutions across multiple environments (Dev, QA, Prod) or managing the source code repositories and artifacts of these solutions? Do you even have a test environment you use for performance testing and general UAT such as a Power BI Embedded capacity? Particularly for widely used, enterprise datasets and reports, it’s important to follow at least some basic application lifecycle management (ALM) processes with tools such as Deployment Pipelines, ALM Toolkit, and Azure DevOps. The following are some basic considerations in applying ALM to Power BI projects:Nothing beyond proof-of-concept (POC) artifacts should be presented to end users without first being tested and validated against a set of criteria. This implies that you have a testing process that could be as simple as reviewing a report in an app workspace to A) confirm the metrics align with a system of record and B) test the performance and user experience. You may assign 1-2 business users the Viewer role in an app workspace and, upon their approval and other basic test results, publish the content to groups of users via a Power BI app. More rigorous and sophisticated testing processes can be applied to incorporate many other factors such as the design standard, colors, the use of custom visuals, the theme, and accessibility considerations. The testing process should severely minimize the probability that end users will have a negative experience such as a visual not rendering or resulting in an incorrect value or rendering with unacceptable performance. Have clear owners of the content based on their roles, skills, and experience level. A report author should not be able (without consequences) to deploy a change to a dataset without clear approval from the project team and primary dataset developer. Likewise, a dataset developer may not have the time or experience level to develop high quality data visualization experiences and thus this activity should be delegated to others on the team. Decide on a version control system that allows for easily maintaining version history and restoring prior versions and ensure itsLike the testing requirement, if version control isn’t being used, the solution probably shouldn’t be allowed to move forward to a production deployment. At a bare minimum a SharePoint team site or OneDrive for Business folder could be used through more robust options are available. Utilize Power BI’s primary ALM tools including ALM Toolkit and Deployment pipelines. With ALM Toolkit, there’s no reason that the entire dataset should be deployed from Power BI Desktop – incremental deployments following a source-target differential analysis should be the norm. Monitoring Power BIThere are out-of-the box tools for monitoring Power BI such as the Power BI Premium Metrics app and it's now easy to retrieve and store the Power BI activity log data. The question is, "Do you have a process for consistently leveraging these resources to improve the health of your Power BI environment?" For example, a Power BI administrator could have a weekly process for analyzing premium capacity usage and advising on any issues or trends such as a large in-memory dataset being refreshed frequently yet not receiving any (or many) queries. (Datasets which consume larger amounts of memory need to be justified by higher query volumes or queries/views supporting highly valued use cases (e.g. Accessed regularly by VP of Finance and in weekly meetings). Similarly, a process for collecting and analyzing the activity log data should make it clear which users are most active, which content is most utilized, and call out new/recent content being deployed, updated, deleted, etc. A lightweight BI solution that integrates activity log data with Active Directory data in a Power BI dataset can be a powerful source for ongoing Power BI monitoring. Power BI administrators can sometimes be pulled into specific projects or troubleshooting scenarios but without consistent monitoring and administration of the overall environment the risk of widespread issues and inefficiencies increases dramatically. Here are just a few questions that an admin could be responsible for analyzing every 1-2 weeks?What's our current state in terms of pro licenses and premium capacity?This includes pro licenses provisioned versus assigned and premium capacity RAM provisioned versus being utilized. How much RAM is remaining/available until dataset evictions will occur?What are the most viewed reports and dashboard?Has this content been consistent or has certain content grown or declined in popularity?What content has been created or deleted recently?Identify the owner of the new dataset, the workspace and premium capacity of the new dataset, and what the new content means for overall resource utilization. How stable/reliable have dataset refreshes been in our premium capacity?The Power BI Premium metrics app provides this dataFor the most widely utilized datasets, what is the performance (average duration?)You can establish a baseline of expected performance for comparison. Dataset DevelopmentPower BI primarily runs on datasets, which internally are Analysis Services semantic models. The level of design, testing, and sophistication of Power BI datasets greatly drive the value and sustainability of Power BI projects. Not all BI projects can utilize an enterprise BI dataset and thus there may be a self-service Power BI environment, perhaps isolated to a specific premium capacity node, in which standard dataset development processes are not followed by the business analysts responsible for this content. However, for datasets developed by the IT/BI team in order to support production BI projects and/or the most common ad hoc analysis scenarios, it's important that proven design and development processes are adhered to. Foundational decisions such as the granularity of the fact tables, the storage mode of each table (import, DirectQuery, Dual), the definition of aggregation tables, an incremental refresh policies and partitioning approaches, and how security will be handled all need to be thought through early as part of a design and development process. Unfortunately, what often happens is that a developer, perhaps driven by a project timeline or a need to show value quickly, will build a dataset which technically 'works' for the current known requirements of only a few reports. The lack of design and planning for the dataset may be painfully clear later as the dataset fails to scale to support other scenarios, or uses an excessive amount of resources for its workload, or fails to refresh consistently, etc. Here are a few considerations in dataset development:Build a matrix identifying the fact and dimension tables.Fact tables as rows, dimension tables as columnsUse this to help communicate the dimensionality of the model (e.g. can filter sales by product but can’t filter budget by product)Define the deepest grain possible for the fact table to support maximum dimensionality. With aggregation tables, it’s now very straight-forward for models use support DirectQuery connections to large source systems for granular details and in-memory aggregation tables to answer most or many important queries. Identify the queries (and their grain) that should be optimized for performance relative to the amount of RAM this may require for the aggregation table. For example, supporting in-memory analysis at the individual product (UPC) level may require 10GB of RAM for the aggregation table – are you comfortable provisioning this much premium capacity to support this grain? Alternatively, you could support in-memory analysis at the product category level which requires only 2GB of RAM.Analyze a data warehouse source system for the required attributes and leverage this system for the required source data. Avoid embedding ETL logic and processes in the dataset in the form of Power Query (M) expressions or DAX calculated columns. If this is going to be an enterprise BI dataset, then surely the required data can be sourced from the data warehouse.Create a view object in the data warehouse for each table of the model.Identify the most common and important queries and test/benchmark performance of these queries with tools like Performance Analyzer and DAX Studio? Leverage calculation groups to minimize the number of measures in the dataset. If feasible, utilize incremental refresh policies on large in-memory tables to benefit the speed and resource usage of dataset refreshes. Use a dataset documenting tool that makes it easy to browse the metadata of the model to quickly understand and all the objects. Though far from perfect, I’ve found the Tabular Model Schema Reference v2.0 to be useful in my projects. Follow application lifecycle management (ALM) processes such as using tools like the ALM Toolkit to incrementally deploy changes to model. Consider using weekly sprints to incrementally improve and develop datasets. Additional related considerations for datasets are noted in the Certifying Datasets process. Publishing Content The methods used in making Power BI solutions available to end users has significant implications which are often overlooked or underappreciated. A business analyst or BI developer who’s worked hard in Power BI Desktop or the Power BI Report Builder application naturally wants this new content to be available to users and likewise end consumers are anxious to view and interact with this content. Whether this access is provided via Sharing an individual report or dashboard, adding a user or group to a workspace, or publishing/updating an app may seem inconsequential initially but it greatly impacts both the manageability of the solution and the user experience. Power BI development teams and report authors generally should therefore follow a process which guides them to choose the best distribution method for their scenario. In most scenarios, the best long term option is to publish/update a Power BI app and grant security group(s) permission to this app. Power BI apps provides scalability to support a mix of different content (paginated reports, dashboards), a clear separation between content being developed or tested (the workspace), and a number of navigation and usability features for consumers. A policy and supporting documented process for publishing Power BI content should look to accomplish the following:Consumers obtain access to the content via their membership in Azure Active Directory security groupsThere should not be long lists of individual names maintained anywhere in Power BI including the Power BI app permissions tab.In Power BI premium environments, end users (with free licenses) should rarely be granted any role in a workspace.For example, 1-2 consumers such as a test user or a key stakeholder may use the Viewer role to evaluate the content not yet published or included in a Power BI app.A suboptimal but common approach is simply mapping all consumers to the Viewer role of the workspace thus negating the benefits of apps in relation to workspaces. Sharing individual reports or dashboards should be limited to minimal and ad hoc scenarios in which no expansion of the solution (more reports, dashboards) is expected. Less experienced Power BI report authors may find the ‘sharing’ option intuitive and simple relative to a Power BI app but rarely does a single dashboard or report sufficiently address requirements. In a worst-case scenario, many individual reports each have their own long list of individual users with access to the shared report. A report author or owner of the content will be responsible for manually updating the access list for each item and end users will not have any easy option for navigating between the different reports shared. Once a policy and process are in place promoting the use of Power BI apps as a standard, identify a subset of popular solutions which utilize other methods for publishing content and plan to re-align these solutions to the recommended or required content distribution process. Certifying DatasetsCertified datasets are only valuable if the processes applied in determining whether a dataset should be certified are sound and consistently executed. Ideally a certified dataset is regularly evaluated against a series of objective criteria applicable to other enterprise datasets in the environment such as security, data validation and accuracy, query performance, processing/refresh design and reliability, usability, manageability, etc. A dataset that satisfies these criteria can indeed serve as a ‘single source of truth’ supporting many Power BI reports and self-service BI scenarios. In many cases a dataset is certified with too weak, loose, or limited criteria and testing. Perhaps this is due to a desire to drive user adoption of this dataset or because the dataset analysis process wasn’t objective. When various weaknesses in the ‘certified’ dataset are exposed in production environments such as with incorrect DAX calculations or slow queries this results in lost confidence in both the specific dataset and the entire meaning of certified datasets. Here are some considerations you may apply to your dataset certification process:Create a dataset certification document that identifies A) the criteria that will be applied and B) how frequently certified datasets will be re-evaluated to determine if they should remain certified.A certified dataset that was without any flaws in August could easily be degraded later in the year on various criteria due to modifications to the dataset reflecting attempts to support other projects.Likewise, new owners or developers with less experience in Analysis Services and DAX may have obtained access to the dataset and unwittingly eroded the quality and value of the dataset. At a minimum, the certification process should include criteria for security and performance.In terms of performance, establish a baseline threshold (e.g. 3 seconds) and compare the performance of the most important and/or common queries against this dataset. If important or common queries are found to perform poorly, perhaps this indicates that the dataset should be re-factored to use an aggregation table to better address these queries or some other modeling change should be considered. In terms of security, it should be confirmed that the security model aligns with company data security policy. It should also be very clear how the dataset implements security and how this security is managed going forward. Does the dataset implement row-level security? If so, are there Azure Active Directory security groups mapped to these roles? Are these Azure AD groups well-maintained? Does the dataset utilize single sign-on over DirectQuery connections to leverage the security implemented in the source system?If so, does this security align with company policy?Separate dataset developers from dataset certifiers.In many cases, the most senior or primary dataset developer on the team is exactly who you’d want to administer the certification process. However, for the datasets this developer has built, if anyone else is experienced with datasets or Analysis Services it’s probably best if this more objective party can evaluate the dataset.Test the dataset independently of anything shared by the dataset developersFor example, confirm that the model’s base metrics accurately reflect the data warehouse and accepted business definitions and use tools such as DAX Studio or Performance Analyzer to benchmark performance for common queries.Do not certify the following types of datasets:Datasets built on many distinct data sources (i.e. 3+) such as files, folders, web URLs, etc.Datasets containing embedded local tables (no system of record) should generally not be certified.Datasets containing inefficient and difficult to maintain data integration processes via many complex Power Query (M) expressions. A certified dataset should generally just reference simple view objects in a source database – it shouldn’t be creating many new tables and columnsDatasets containing fact tables which represent a duplication (or near duplication) of another dataset in the environmentMultiple datasets available to analyze the same data is what certified datasets should help to avoidDatasets in which there’s not a clear longer-term owner responsible for any necessary adjustments.Datasets in which the developer(s) can’t succinctly explain the reasoning for fundamental design decisions such as the storage modes of tables or the use of aggregations.Datasets with no proven success including refresh history, query performance, actively used reports, etc.Managing the On-Premises Data GatewayThe on-premises data gateway is required for many data sources ranging from local files and folders to existing on-premises databases to many cloud-based sources such as databases running on virtual machines (VMs). Therefore, even Power BI environments which predominantly utilize cloud-based PaaS (Platform-as-a-Service) or SaaS (Software-as-a-Service) data sources still utilize gateways to some extent as revealed by the Power Platform Admin Center and the Data Gateway PowerShell module. Many Power BI environments include a mix of both gateway clusters administered by IT as well as gateways running on individual user machines in either standard or personal mode. Therefore, particularly for environments in which the current primary data source(s) require the use of a gateway (e.g. on-premises SQL Server, Oracle, Teradata, etc.), the process applied to managing on-premises data gateway clusters becomes essential to project workflows. The following progression of tasks can help establish a process to manage gateways:Collect information on the current state of gateway usage in the organization:Which data sources are being accessed via gateways?Which datasets are dependent on these data sources?What gateways have been installed? What servers host these gateways and which users have configured these gateways? Which gateways and which gateway-dependent data sources are being used most heavily and/or in production environment?If an IT-managed gateway cluster or clusters(s) have been provisioned:Who is responsible for installing updates?Who is responsible for configuring data sources and granting users access to the gateway?Which users or groups have access to which data sources in these gateway clusters?This information can be obtained via a combination of the Power BI Management PowerShell module, the DataGateway PowerShell module, the Power BI Activity Log or Office 365 Audit log, data gateway log files, and the Power Platform Admin center. Determine the greatest risks or conflicts of policies or preferences based on a review of the gateway usage information:Is a popular and important Power BI dataset dependent on a gateway installed on a user’s laptop?Similarly, is there widespread use (dependencies) on local data sources and gateways installed on user machines?Does the IT-managed gateway cluster have sufficient resources for its workload?Are updates being installed regularly and is there monitoring in place?Does this gateway support the required data sources, or can these sources be added?Discuss options and implications for changes in gateway policy and process? Should gateway installation be disabled altogether, or should only certain users/groups be allowed to install gateways? Can certain existing gateway usage scenarios be modified to eliminate the need for a gateway by changing the data source (e.g. switch from a local file to a SharePoint site or Azure SQL database)?If a non-gateway data source isn’t an option, can existing personal gateway usage scenarios be migrated to IT-owned gateway clusters?Perhaps a gateway cluster can be created to support self-service solutions which may indefinitely require access to certain on-premises files/folders while a separate gateway cluster can be used for an on-premises data warehouse systemShould solutions which depend on gateways running on user machines (or perhaps gateways in general) be flagged/marked and isolated from other solutions without this dependency?For example, a company which has migrated an on-premises data warehouse to Azure Synapse SQL, which does not require a gateway (unless necessary for custom security scenarios), may deem that reports built against the on-premises system via gateways should be isolated and/or marked in some fashion as not the official or sanctioned data. Include gateway monitoring and management as part of a recurring Power BI administration process such as a weekly review.At a minimum it’s important to always know that the IT-managed gateway clusters are updated and available for the intended data sources to support. A better practice is to develop a monitoring solution built on top of the gateway logs to easily identify/alert to any significant change in performance or resource utilization. Create and manage security groups of users that can be mapped to gateway data sources.Limit membership in these groups to specific dataset or dataflow creators across teams.Administering Power BI PermissionsThere are many permissions associated with capabilities and features that Power BI administrators can grant or deny to groups of users or the entire organization. For example, most organizations would agree that the ability to create workspaces (modern workspaces) should be limited to a specific group or groups of users rather than enabled for the entire organization or any user with a pro license. Similar decisions are needed for the creation and use of dataflows, sharing content with users outside the organization, publish-to-web scenarios, policies around exporting data, printing reports and dashboards, and several other Power BI features.As with other core processes in Power BI environments, the first step is simply acknowledging that granting and denying permissions is an important and recurring process that requires some level of thought and consistently implemented policy. A lack of process can easily result in a mismatch between the permissions needed for business purposes versus the permissions granted as well as delays and confusion. A next step is identifying the individuals or teams responsible for implementing the permissions (e.g. Power BI or Power Platform administrators) and those responsible for deciding the logic or policy to be followed such as senior managers from the IT/BI organization or a dedicated data governance or Center of Excellence (CoE) group. The following list of ideas may help address this process:Create a permissions document that identifies each Power BI setting available in the Power BI Admin portal (Tenant settings page) and the current state of each permission:The document should clarify which permissions are enabled or disabled for the entire organization and which permissions are specifically enabled or disabled for certain Azure Active Directory GroupsThis document may be extended to include permissions specific to Power BI Premium capacity (Capacity settings page) such as the right to assign workspaces to a premium capacityUpon review of the document, choose a subset of the permissions that represent a greatest risk or conflict with existing organizational policies or the views/preferences of those who determine these policies. For example, you may find that a group of 100+ users is mapped to the ability to create workspaces or to publish their content to the web – perhaps far too many than necessary or appropriate.Identify and discuss alternatives to the current state such as creating a new security group for a particular setting or disabling a feature for all users. Identity the implications of the change such as any disruption to solutions dependent on the permission.Determine a new approval process and the essential decision logic to be applied for the given permission For example, using datasets across workspaces may only require a Power BI lead or admin to review and approve the use case whereas sharing content with users outside the organization may involve a governance team review and approval. If possible, integrate the request and approval process workflow, along with supporting policies into an online portal or application intended for similar IT requestsThis should provide a track record of how requests were handled, their duration and owner, and avoids inefficiencies with emails between various parties.Incrementally implement revisions to Power BI permissions with clear and advance notice of changes to any impacted user or team. For example, every other week or every month a BI leadership and governance team could consider policies for one or two Power BI permissions. Power BI admins familiar with the technology as well as other stakeholders knowledgeable of use cases and company policies could discuss and decide on what to implement. Update the permissions document and consider publishing it or making it available to Power BI team leaders or more broadly via a company portal.Learning Power BIPower BI and its collection of tools and services are constantly evolving with new features which either enable entirely new scenarios or offer an improvement from past approaches. Likewise, the roles in Power BI projects and environment are often shuffling with certain distinct skills, which may not have been necessary in past projects now essential for a project’s successful delivery. As one example, a project’s requirements may call for utilizing the AI and dataflows Power BI premium workloads yet the BI team responsible for this project may be inexperienced with either of these technologies. Therefore, although learning is a process generally carried out by individuals outside of normal business hours, it’s nonetheless an essential process for Power BI professionals to constantly learn about changes to the Power BI platform, additional Power BI technologies they may be less familiar with, and consider how these changes and technologies can impact their own projects and environments.At a minimum, individuals should regularly review the Power BI Blog for announcements relevant to their role. Those who primarily develop reports should naturally focus on the report authoring features in new releases of Power BI Desktop. Likewise, Power BI administrators and those close to governance and security can review posts and roadmap items relevant to access controls, network isolation, licensing, monitoring, and more. Another minimal or very reasonable consideration would be to review Power BI documentation articles relevant to the given role. Here are some considerations that may improve your learning process:Create a folder of Power BI bookmarks/favorites in your browser(s) with references to resources relevant to your roleFor example, if you’re a dataset developer, you may want easy access to documentation on DAX functions. If you’re a Power BI Admin, the Power BI REST API and Power BI Management PowerShell module documentation may both be important learning resourcesBuild your own test environment and sample contentThe essential idea here is to lower the amount of effort or time required to test and learn. Applying a new feature or enhancement such as new DAX function to your own dataset is more likely to aid your learning than just reading about the new DAX function. One lightweight approach for BI developers is to install a development instance of SQL Server database engine to a local machine and restore a backup of the AdventureWorksDW data warehouse sample database. This database could be used as the source for Power BI datasets, paginated reports, dataflows, and more. Another option is to a virtual machine (VM) Create examples of the core functions, commands, and syntax relevant to your roleFor example, with DAX you need to learn how to apply, remove, and manipulate filter context and invoke context transition so you can build several examples using CALCULATE(), ALL(), FILTER(), ALLSELECTED(), etc. If paginated report development is important to your projects or environment, creating sample reports which represent common expression patterns and design techniques can be useful. Power BI architects may regularly review the status (preview or GA) of top features such as large models (over 10GB), XMLA read/write, and more from Power Platform Release Waves. This learning can inform recommendations about how and when certain capabilities align with the plans for an environment such as migrating from Analysis Services models to Power BI premium datasets. Training on Power BIInterest in using Power BI by various teams throughout an organization often grows organically as a result of access to Power BI Desktop or knowledge of other teams’ usage of the platform. Typically, these new users or teams have a general idea of how they’d like to use Power BI and may have already created something. However, in most cases they don’t yet understand how the components of Power BI work together, what licenses or permissions are required, what vulnerabilities or inefficiencies their solutions may contain, or generally how Power BI is governed at the organization.It’s possible to avoid facilitating any training sessions by simply pointing users and teams to various free learning resources. However, this increases the risk that those new Power BI users will struggle to use the platform effectively in the short term and not achieve a level of success necessary to sustain their interest and grow adoption. Another real risk to consider is that an untrained team will be more likely to build content which technically does deliver the intended insights and thus is utilized by many users but this represents poor design and development practices which could drive up support/maintenance issues for IT and a waste of provisioned resources. Therefore, it’s generally considered a good practice to provide some form of standard Power BI training or orientation to users and teams getting started with the platform. You may plan training sessions targeting three separate roles:ConsumersReport AuthorsDataset DesignersEach training session of 60 minutes can leverage the same sample solution (e.g. AdventureWorks sample database) such as a Power BI dataset with a simple star schema, three Power BI reports built against this dataset, and one dashboard published as an app.One of the three reports may be a paginated report if you expect these to be common in your environment. The sample solution should be clean and complete using standard, out-of-the-box features and recommended designs.The idea is to present a practical solution that relatively new users could build with minimal training/learning. You may use 1-2 dataflow as sources for a dataset if you expect dataflows to be used in the environment but generally the dataset can just leverage a relational database with minimal to no Power Query (M) customization.If on-premises data gateway clusters are used in the environment, then incorporating a data gateway into the sample solution makes sense. An initial meeting can primarily target the Consumers and higher-level stakeholdersTrainers can demonstrate the end/finished product – viewing and interacting with reports and the dashboard in the Power BI app as well as ad hoc analyses via live connection reports against the dataset. Trainers can also share just a few slides or diagrams explaining the architecture of the solution and introduce the core terms (report, dataset, dashboard)One point to call out in these meetings is that this consumption experience does not require a pro license for the end user due to provisioned premium capacity. After an initial demo, there can be an open discussion about the use cases the new users/team are considering and if/how the sample solution is relevant or if other features or tools are more pertinent.If time is available, trainers should try to cover primary content consumption features such as cross-highlighting, filter panes, focus mode, navigating hierarchies, data export, alerts, and possibly email subscriptions. The training session with report authors, who may be the same users as the dataset designers, should reiterate the distinction of reports from datasets and the importance of leveraging a single dataset for multiple reports.Report authors should also learn about corporate report design standards and available report templates and report themes. There should be examples from the sample solution demonstrating core report design features such as filter scopes and slicers, formatting visuals and pages, tooltips, conditional formatting, drill through report pages, bookmarks, mobile-optimized reports, etc. Publishing and managing content in Power BI workspaces and apps as well as options to expose content to users should be covered as well The training session with dataset designers is necessarily more technical as this involves the dataset to data source(s) connection (Power Query (M)), a basic introduction to DAX, and an introduction to data model design. One option is to walk through the essentials of building the sample solution dataset step-by-step. Another consideration is to share steps (a process) to design datasets such as 1) choosing the fact table 2) defining a grain of this table 3) identifying dimension tables and 4) defining measuresDataset designers ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download