Power BI Project Example

Good morning, Power BI Developer

Let’s say you’re a BI developer within a corporate BI team and you’ve been assigned to a Power BI project. More specifically, you’re responsible for the Power BI dataset which supports the reports, dashboards, and self-service use cases across many users. Your morning routine may consist of any email notifications (jobs failed, data alerts),  checking Azure resources via the Azure mobile application, Slack, Teams, etc.

But let’s say you want to check the tasks you’ve been assigned and the progress of your Power BI project (AdWorks Enterprise Import) in Visual Studio Team Services (VSTS):

*Image captured during the afternoon.

VSTS is the SaaS offering of Team Foundation Server (TFS). The following 20 minute video from MS Build 2017 may be useful:  Agile Planning in VSTS

Click the ‘Work’ link from My favorites and quickly navigate to the project backlog of items as well as the current sprint (Sprint 12):

The Backlog

Meet the Team

• Anna Sanders is a Senior BI Manager. She’s the main point of contact for project sponsors, she manages the BI team and projects, and she works with the IT organization on implementing governance and security policies. As the Power BI Service admin, she’s already configured the tenant settings per the Done column.
• Jennifer Lawrence is a BI Report Developer. She understands visualization best practices and follows all corporate report standards such as policies with custom visuals and any corporate report theme to apply. Additionally, Jennifer knows the source dataset (relationships, measures) – she can comfortably explain the built-in logic and design reports accordingly.
• Stacy Loeb is a Business Analyst. She’s responsible for gathering and documenting requirements, testing solutions (within a QA App Workspace), and helping train business users.
• Brett is a dataset designer. He’s experienced in SSAS Tabular, DAX measures, M queries, SQL, and he works with data source owners such as DBAs and data warehouse architects. He’s tasked with making the dataset intuitive for users, performant, secure, sustainable and of course accurate and secure (e.g. Row-level security).
• Mark Langford is not on the BI team – he’s an Analytics Manager that reports directly to the VP of Sales, the project sponsor. Mark uses Power BI Pro features such as Analyze in Excel and Power BI live connection reports to rapidly deliver insights to the VP of Sales and the Sales team generally. He’s also the owner of the sales budget data source so he regularly collaborates with the BI team.

Additional details on the team roles were included in the decks at PBI World Tour and SQL Saturday Boston. Click here for details on the Customer History Segment column and here for the custom sorting of this column. Many dataset examples are also included in the Power BI Cookbook.

Sprint 12

A top deliverable for this sprint is a Sales and Margin dashboard for the Pacific Sales team and two supporting reports. Jennifer is working on this content but, per the red card, is blocked from completing the Margin report. In this case, a few DAX measures required for the report still need to be validated by Stacy.

Brett’s top remaining priority is to revise the products dimension table retrieval such that new products that haven’t yet been assigned categories or subcategories are allowed to load into the dataset.

As you likely (surely) know, Power BI projects and deployments can take on many different forms based on several factors (org size, complexity, resources, on-premises vs cloud, etc). This is only a small sample intended to share a few concepts such as team roles and task assignments. A takeaway from this scenario is that we’re delivering corporate, IT-managed BI as well as self-service BI (Mark Langford). 

Power BI Project Analysis

VSTS itself may provide sufficient visibility but, via the VSTS app for Power BI, Power BI and Excel can be used to further analyze project activity (ie commits, pull requests). The following images highlight some of the main components of this setup:

This slideshow requires JavaScript.

Power BI Project Code

For this project we currently have the M queries, the SQL views, and a few MSDAX queries in a single solution (.sln). Note that only the M queries and SQL views are synchronized with the VSTS project.

This slideshow requires JavaScript.

Currently VSTS doesn’t support colorization for M queries (.pq) or DAX queries (.msdax) as it does with .sql files. We do, however, have colorization of .msdax files in Visual Studio now per this blog from the SSAS team. (I recently revised many .dax and .mdx files (which contained DAX queries) to the .msdax extension to take advantage of new DAX support in SSMS and Visual Studio.)

Next Week

The revised product dimension query (Brett’s Sprint 12 task) will be featured next week. I may also talk about scaling the dataset with Power BI Premium (Anna’s task) and the relationship of this task to the latest Microsoft Power BI Premium Whitepaper.

Feel welcome to leave any comments (thoughts, questions) though I may not be available for several days.