Value.Compare in Power BI, An Advanced Power BI Class Excerpt

Course Image

This post is an excerpt from our Advanced Power BI class.

Importance of Data State

Analyzing data states in the data collected is generally the primary focus of our Power BI analyses. We look at aspects related to standards, compare dates to today’s date and execute other such comparisons. The business user who consumes your data is very focused on specific data states, which are defined and driven by their internal business rules. These rules will tend to change over time as the business evolves. Hence, it is important to implement your state definitions in a way that is flexible and reduces the number of changes necessary to implement a changed business rule.

Introducing Power BI Value.Compare

You’ll learn a technique using Power BI M Value.Compare, which enables you to easily convert dynamic ranges into states while reducing your data model maintenance effort. The need for this is that states, such as those of overdue invoices or tasks, where a large number of variances is returned, can create challenges which result in pieces of business logic being implemented in several different locations, like visual filters, etc.

Value.Compare enables you to easily convert the large number of potential values into a discrete set of states. This technique encapsulates the business logic into one place, reducing long term maintenance effort and places to maintain the business logic when the business rules inevitably change over time.

You’ll see two examples of Value.Compare usage. One example will show you how to use the function with comparing appointment dates to today’s date and converting variances to a state. We’ll show you how to use embedded data type conversions to prepare the data so that you can use Value.Compare. The other example will show how to use Value.Compare to determine Service Level Agreement compliance, based on duration values. This will show you an easy way to implement this logic and how to externalize the comparison value using a parameter.[/fusion_text][/fullwidth]

Creating Beautiful Power BI Slicers

This post addresses one of several common challenges for new Power BI users face. We’ve compiled a list of challenges, based on Our Real World Power BI training series.

Making your Power BI slicers visually distinctive.

Many new users can create slicers in Power BI to enable the end user to dynamically explore their data. However, many don’t know about the styling options that can make your slicers visually distinct and finger friendly for touch devices.

The video below takes you through the steps to beautify your slicers.

3 problems tracking operations in Project – Part 2

In part 1, we outlined the problems that many customers encounter managing operational work in Project. We need to capture this information for higher fidelity resource capacity. However, previous methods required more work on the part of Resource Managers than desired.

In this article, you’ll see how to set up an operational project plan template that enables you to treat each week as a distinct unit. You’ll also see how to set up the project in Project Professional and how to configure an Operational Project Enterprise Project Type.

Part 3 will also show how to set up the project using PWA only. The requirement is to enable the Resource Manager to manage the project easily via Project Web App. You’ll also be taken through how to use this setup in a day to day fashion and will see timesheet and approval configuration. Lastly, you’ll see how this information appears via Power BI reporting.

If you have comments or questions, please leave them below in the comments field.

 

3 problems tracking operations in Project, and how to fix them.

Many organizations struggle to manage resource capacity. If they are following the OPRA Resource Capacity model, the need to track recurring operations work immediately becomes necessary. This article is based on real world experiences while managing large Project Implementations. Current tracking methods will be examined and some suggested approaches will be presented.

The old way of tracking Operations has issues.

In the past, the primary method used to track recurring operations work is to create a project that contains a yearlong task with all members of a support team. The theory is you can track all operations easily for the fiscal year, which many companies use as boundaries.

However, this approach makes three core assumptions, which causes numerous headaches for the operations manager.

  • Operations work is just like Project work
  • You will always use the same amount of operations work every week
  • No one will join or leave your operations team during the year

Myth #1: Operations work is like Project work

Project work is scheduled for a given week, team members do the work and status is reported. This is where the similarities between Project and Operations work ends.

If you do less work than scheduled in true Project, the incomplete work is typically moved forward into the following week. If you do more work than scheduled, the finish date should come in.

Operations work, however, is about reserving resource capacity for a type of activity. Thus, the difference in how we treat time variation is where the treatment of Project work and Operations work diverges.

If you go over or under time on a given week for an Operations task, it has no impact on the future of the task. You don’t move unutilized operations time forward as you don’t get that unutilized capacity back. You don’t move the end date in if you use more than planned. You simply record what was used, usually for a given week.

Therefore, each reporting period for Operations work should be treated as discrete tracking entities that have no forward schedule impact and preferably, can be closed.

Myth #2: Level of effort never varies

The reality is that the level of operations work varies week to week, sometimes greatly. There are times during the year where you know there’s more operations time. For example, a year end close process might be extremely taxing for the Finance support team. The ability to capture this seasonality would improve the ability to manage capacity for project work tremendously.

Also, if you are using planned hours on Operations work faster than originally planned, using the one long task will result in support calls. You may enter October with no remaining time left, resulting in the task disappearing from timesheets.

This again points to a need for discrete tracking entities that can be managed individually for a given time frame.

Myth #3: Teams never change

The year long task has a serious user management issue when it comes to tracking team composition. Adding and subtracting team members to the task requires Project Pro and a fair bit of Project knowledge to do properly.

When Heather joins the team in August and the operations task started in January, how easy is it to add Heather in a way that doesn’t mess up the current team tracking? The same is true if Sanjay leaves the team in April. How do you easily remove his remaining time?

This process is typically beyond the training of most Operations Managers. They shouldn’t need to be a tool expert to simply manage their team as this creates a situation that detracts value from the data.

The one long task also doesn’t lend itself to adjusting operations assignments so that you can easily reflect greater project demands in key weeks.

All of these usability questions lead us to a requirement that the solution should be usable by a user in Project Web App and doesn’t require a PMP to execute.

Requirements synopsis

Our desired Operations management solution should be:

  • Discretely managed, such that variances in time entered do not impact the overall timeline
  • Ability to individually adjust the time and team composition of tracking periods
  • Straightforward to manage, using only PWA

In our next post, a suggested solution that meets these three requirements will be presented. You’ll also see examples how it can be used in real-world settings. If you have a question or comment, feel free to post it below.

The Benefit of One Simple Change in Project Online

What we typically do today.

This post is about what can be gained from making one simple change in your Resource Custom Field configuration. Many companies have an FTE Yes/No type resource custom field to distinquish between employees and contractors. This has been the approach for many years, as I see this many times in Project 2007/2010/2013 implementations.

A better approach.

I suggest using a Resource Company custom field instead. You would create a lookup table, Resource Companies, where you include the name of your company and the company names of all of your contractors. You then attach this lookup table to the new Resource Company custom field. Lastly, update the value for all resources, where FTEs are set to your company name and contractors are set to their individual company name.

So why do this?

This simple change creates richer data for reporting. For example, your vendor management team is in the process of renegotiating a contract with a specific vendor. That vendor is supplying a number of contractors across many projects. If you use this approach, the ability to extract how much work this vendor is doing and which projects are they working on is as easy as setting a simple filter.

For employees, you may even consider making the lookup value hierarchical, so that you can enable your company name.your division name reporting.

Are you using this approach today? If so, please share your experiences in the comments.

Converting RTF to Text in SQL Part 3

captain-picard-facepalm-meme

Yes, Part 3. This technique was published some time ago and as it gets further “field testing”, new conditions have come to light which require a revision of this technique. Assuming this is the last post on this topic, I need to figure out how to duplicate this logic in PowerBI.

I consider this to be version 15 or so of this query since my first version was written in mid-September of 2010. The changes here are as follows.

First, the DTD definitions have been expanded to include all possible HTML codes. This should prevent a report blowing up when someone decides to use an unexpected symbol somewhere.

Second, I keep seeing random <br> tags in the fields, which ironically is breaking the query. I’ve added a replace statement for that as well.

Lastly, I’ve expanded the field sizes to Max where necessary to accommodate truncation issues.

Sample code is below. I strongly recommend copying into NotePad first so that you don’t accidentally get curly quotes. I’ve also left it as straight text to make it easier to copy.

declare @Headxml nvarchar(3000)
declare @Footxml nvarchar(50)

set @Headxml = N'<?xml version=”1.0″?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
http://www.w3.org/TR/html4/loose.dtd”
[<!ENTITY quot “&#34;”>
<!ENTITY amp “&#38;”>
<!ENTITY lt “&#60;”>
<!ENTITY gt “&#62;”>
<!ENTITY nbsp “&#160;”>
<!ENTITY iexcl “&#161;”>
<!ENTITY cent “&#162;”>
<!ENTITY pound “&#163;”>
<!ENTITY curren “&#164;”>
<!ENTITY yen “&#165;”>
<!ENTITY brvbar “&#166;”>
<!ENTITY sect “&#167;”>
<!ENTITY uml “&#168;”>
<!ENTITY copy “&#169;”>
<!ENTITY ordf “&#170;”>
<!ENTITY laquo “&#171;”>
<!ENTITY not “&#172;”>
<!ENTITY shy “&#173;”>
<!ENTITY reg “&#174;”>
]><html><body>’

set @Footxml = N'</body></html>’

select   *
,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName]

,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField2],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName2]

FROM  dbo.MSP_EpmProject_UserView

How To: Connect Project Pro to Project Online

Here’s a quick article on how to connect Project Pro to Project Online. The question seems to come up on a regular basis so I’m documenting it here.

In Project 2013 and Project Online, Microsoft added the ability for Project Pro to autoconfigure the connection to Project Web App. However, to make this happen, you need to start in PWA.

  • Navigate to your PWA site
  • Go to Project Center by clicking Projects tile or Projects in the Left Navigation
  • Click the Projects tab to display the ribbon
  • Click the New button in the ribbon
  • Click the In Project Professional option in the dropdown menu
  • Project Pro will open a blank project, after a brief pause
  • Close Project Pro without saving anything

Project Pro is now configured to open and edit projects from Project Web App.

Going forward, you should open your project from PWA as opposed to Project Pro. This is the future interaction direction. The Edit button provides an option to edit the project in Project Pro.

How to show Implementation Milestone Dates in Project Center

This technique illustrates how to use formulas to extract dates from tagged milestone tasks within your project plans and show the dates in the Project Center.

This technique has been tested with Project Online and Project Server 2013 On Premises. However, I don’t see a reason why it wouldn’t work in Project Server 2010. The primary difference is that 2010 will require a round-trip of the data in Project Professional to calculate the formulas.

Scenario

I’ve had several clients ask “Can we show the next Implementation date in Project Center?”. The challenge is that the Implementation milestones are task level data. The Project Center only shows project level data, therefore you must extract and transform the data to show it appropriately.

Since you may have multiple Implementation milestones, this technique will show the next upcoming Implementation date. If there is no upcoming date, it will show the most recent past Implementation date. This way, the field should always show data.

Using Metadata Instead of Task Names

My first job in industry was converting old RPGII programs on the IBM System 38. I quickly learned the evils of using content as data as that was a very common technique in those days. This technique led to users accidentally breaking processes by inadvertently adding a space or changing a term. If you’ve told your users not to update a task name or the name of some other element, you too are using content as data.

Search engines later taught us to use metadata tags to separate functional data from content. Thus, this technique requires the creation of a task metadata field for tagging tasks with a specific reporting purposes. The illustrated design only allows one tag per task.

The benefit of this technique is that it is agnostic to the names of tasks and milestones, as long as they are tagged appropriately. PMs can change the task names and plan structure to meet their needs as long as they tag the tasks and milestones appropriately.

Metadata Lookup Table

The Reporting Purposes lookup table supports two levels of values. This enables multiple classes of tags, such as milestones and phases. This exercise focuses on the Milestone.Implementation value.

image

Metadata Custom Field

Create the Reporting Purpose task custom field and attach it to the Reporting Purposes lookup table. Specify that Only allow codes with no subordinate values is selected. This prevents the user from selecting Milestones without selecting a more specific purpose.

image

Extracting the Data

The technique takes advantage of the rollup capability within Project, that enables a task level value to be rolled up to Task 0 (Project Summary Task). Task 0 values can be retrieved and used in Project formulas, thus translating the task data to project data.

Task Custom Date Field Setup

Two task level formula fields are needed to determine which Implementation date is available as stated in the requirement above. Show me the next Implementation date or if not available, show me the most recent past Implementation date. These requirements constitute two separate conditions leading to the need for two separate task formula custom fields.

Next Implementation Task Date

The first task custom date field is Next Implementation Date. This field determines if there are one or more Implementation dates in the future. The future is defined as any date equal to or greater than today on any task marked with a Reporting Purpose value of Milestone.Implementation.

The rollup is used to retrieve the soonest Implementation date if there are multiples. When defining this field, specify the rollup behavior as Minimum so that the Implementation date closest to today will be shown. 

image

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] >= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] >= Now(),

[Finish], ProjDateValue("NA")))

This formula may appear a bit odd when examined closely. It appears that there are duplicate conditions specified. This is done intentionally as a workaround to a difference between PWA and Project Professional in formula evaluation during the schedule edit.

The Reporting Purpose custom field is hierarchical so the tagged value is Milestone.Implementation. In PWA, the formula only sees the “Implementation” part of the value when you edit the schedule. The first check ensures that PWA evaluates the formula correctly. In Project Professional, the formula sees the whole value. The second check of the full value is added to ensure Project Professional evaluates the formula correctly. Both checks prevent data from being inadvertently deleted when editing in across both platforms. If neither value is found, the field is set to a special date value of NA, using ProjDateValue(“NA”).

Previous Implementation Task Date

This field helps determine if the most recent past Implementation date. This field determines if there are one or more Implementation dates in the past. The past is defined as any date less than or equal to today on any task marked with a Reporting Purpose value of Milestone.Implementation. The same double check of the Reporting Purpose is required to keep the editing behavior consistent.

image

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] <= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] <= Now(),

[Finish], ProjDateValue("NA")))

Rollup to the Project Level

A Project level custom date field is created to perform the logic of determining the correct Implementation date to display in the Project Center. The formula checks the Next Implementation Task Date for a value of NA. If found, it assumes there is no future date and uses the most recent past date. If NA is not found, it uses the soonest future date.

image

Formula

IIf([Next Implementation Task Date] = ProjDateValue("NA"),

[Previous Implementation Task Date], [Next Implementation Task Date])

Add to the Task Summary View in PWA

I now add the Reporting Purpose, Previous Implementation Task Date and Next Implementation Task Date fields to the Project Task Summary view. This enables the PM to mark the appropriate tasks in PWA if desired. It also helps you see the effects of the formulas.

When I edit the project in PWA, I will see something like the following. Here, I’ve created a project with four Implementation milestones, two in the past and two in the future.

image

Today is April 16, 2014, so the next future Implementation date is 4/25, which is what should be shown in the Project Center, as seen at 1 below.

image

I hope you have found this technique to be useful. Please post any questions in the comments.

Don’t Get Burned By Your Security Templates

image

Problem

If you’ve ever tried to use the built-in security templates in Project Web App, you may have accidentally messed up your security model without realizing it. This problem applies to Project Server 2003-2013 versions.

Security templates are designed as a way to quickly apply or reapply permissions for predefined roles, when creating new groups and categories. However, the out of box implementation can lead to issues if you don’t realize the impact of applying them.

Background

Groups and Categories have what is known as a many to many relationship.  A group can be associated to multiple categories and a category can be associated to multiple groups. The default security model relationships are shown below where blue boxes represent the Groups and orange boxes represent the Categories.

image

We’ll use Resource Manager as an example of the security template issue. Resource Manager has four relationships out of box by design:

  • My Organization so that they can see all resources and build team on any project
  • My Projects so that they can view any project of which they are part of the project team or own
  • My Resources so they can only see their resources below them in the RBS so that the Resource Manager can add them to a Resource Plan
  • My Direct Report which is reserved for you to customize functionality for the resources directly below the Resource Manager in the RBS The heavy lifting in the security model is at the intersection points between Group and Category. The intersection is where you set the what allowed Project and Resource actions (Group) can be taken on the data returned by the Category. If you’ve seen a “troubled” security model, it’s usually because this nuance was lost on whoever was maintaining the model.

Scenario

NOTE: PLEASE DON’T DO THIS PROCEDURE WITHOUT READING THE ENTIRE ARTICLE FIRST

Felix is a Project Server administrator who accidentally changed some category permissions in production on the Resource Manager – My Projects intersection. “No problem”, thinks Felix, “I’ll just reapply the Resource Manager security template and all will be good.”

    Felix then does the following actions.

He goes to PWA Settings under the Gear.

image

He clicks on Manage Groups under the Security section.

image

He clicks on the Resource Managers group to edit.

image

He scrolls down to Categories to access the Category permissions for the group for My Projects.

He selects My Projects in the Category list to show the permissions. At the bottom of the category permissions section, he selects the Resource Manager template and clicks Apply.

image

All good right? Not exactly.

The Issue

Remember, Resource Manager Group has four category relationships.

image

However, if you go into Manage Security Templates, there’s only one entry for Resource Manager.

image

So, which relationship does this security template represent? Was it the right one for Felix to apply? You don’t know without further research.

Suggested Fix For This Situation

If you choose to use Security Templates, I highly recommend doing the following prep work. This recommendation is based on the real world experience of managing two Fortune 250 company implementations and having cleaned up numerous security models for other companies. An hour or two of prep now will prevent tears later on.

Create a new template for group permissions and one for each intersection for the category permissions using this procedure. http://technet.microsoft.com/en-us/library/cc197679.aspx If you’ve heavily customized your security model, you will need to create a diagram similar to the one I have above first.

The resulting template list for Resource Manager will be as follows.

  • Resource Manager – Group Permissions Only
  • Resource Manager – My Organization
  • Resource Manager – My Projects
  • Resource Manager – My Resources
  • Resource Manager – My Direct Reports
    Now, when Felix applies a security template, he knows exactly which one he is applying to the security relationship.

Resources

You can find the default Project Server 2013 group and category permissions at these links for constructing your templates.