3 Ways to Rev Your Microsoft Project Online and Power BI Performance (Number 2 Is Our Favorite)

Are you having tired of waiting for long refresh times with Power BI? Perhaps, you are getting timeouts because you are retrieving too much data. There’s three easy ways to avoid these issues.

WHY IS MY POWER BI DATA REFRESH SO SLOW?

Many Power BI models refresh slowly as the models are not structured to take advantage of query folding.

Query folding is a process by which specific Power BI transformations are executed by the data source instead of Power BI. Allowing those actions to occur at the source means less data has to be sent to Power BI. Less data means faster data refresh times and smaller report models. Note, not all data sources support query folding, but oData for Project Online and SQL Server for Project Server do.

A sample of these foldable actions include

  • Column or row filtering
  • Group by and aggregation
  • Joins
  • Numeric calculations

For example, you need to find out the amount of work scheduled by week for the quarter. You are querying the time phased data from Project Online. If you aren’t careful, you may be retrieving hundreds of thousands of records. Query folding will make a huge difference in speed and the number of records retrieved. If you have the records filtered by Project Online before retrieving them, you may only receive a few thousand records instead.

ISSUE #1: NOT REFERENCING THE DATA SOURCE PROPERLY

This issue occurs primarily using oData sources, such as Project Online, Office 36 and other web based sources. Query folding breaks if you don’t properly reference the oData feed.

In order for query folding to work properly, the transformations that have folding support need to be the first things executed. If a non-folding transformation is added, no subsequent transformation will be folded. If you don’t reference the oData feed properly, the step to navigate to the oData feed isn’t foldable, therefore blocking all subsequent transformations from being folded.

ISSUE #2: DOING DYNAMIC DATE SELECTION IMPROPERLY

This issue causes more headaches with Project’s time phased data than any other action. In many cases, you are looking to create a range of dates for Power BI to query. If you use the Between filter in the Power BI Query Editor, there’s not option for Today’s date +/- a number of days. If you use many of the other Date/Time filters, like Is Latest, you still seem to pull back a lot of data.

Solving this particular issue requires getting familiar with the M query language so that you can understand how Power BI Desktop performs specific actions.

For example, let’s look at the Is Latest date selection. By default, your dataset is unsorted so Power BI creates a list of the values in the selected column and does a Max value. While this is correct, this could result in a lot of records being retrieved to perform a Max and get one record. The M code over a time phased table looks like this:

= Table.SelectRows(#”Filtered Rows”, let latest = List.Max(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

To get much better performance, make the following two changes. First, sort the dataset in a descending manner on the column which you are using to decide latest. In this example, that’s TimeByDay. Sorting is a foldable action so the data source will do the work.

Next, change List.Max to List.First. Since the latest date is the first record in the sorted dataset, a lot less data is required to get the answer. So, my statement is now = Table.SelectRows(#”Filtered Rows”, let latest = List.First(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

In testing, the original way required over a 1 Mb of data to be retrieved to answer the question. The new way only retrieves 127 Kb.

ISSUE #3: URLS ARE TOO LONG

This issue comes into play when working with Project Online and SharePoint based data sources. Project, in particular, has a lot of long field names. SharePoint has a limit of 2048 characters for a URL. If you are manually selecting a lot of field names, you can accidentally go past the 2048 character limit.

What this means is that Power BI receives a URL that can’t be acted upon. The default Power BI behavior is to then simply retrieve the feed without modifiers. If this happens on a source with many rows, this could significantly impact performance.

In this case, you would break up your dataset into two or more to fit within the limitation and then merge them back together in Power BI.

WANT TO KNOW MORE?

Join us for a free Power BI performance clinic on March 22! This session will do a deep dive into these issues as well as show you how to determine when folding is happening (and not.) Go here to register!

 

[imageframe lightbox=”no” lightbox_image=”” style_type=”none” hover_type=”none” bordercolor=”” bordersize=”0px” borderradius=”0″ stylecolor=”” align=”none” link=”http://academy.tumbleroad.com/courses/power-bi-performance-clinic” linktarget=”_blank” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””][/imageframe]

Project Tasks are Your Lowest Priority

Project tasks are the lowest priority work you have on any given day. Wait, what?

It’s true! Strategically, we know project work is the most important future investment for the company. When you break down what you do every day, you’ll see that you are fitting in project work around the other work you have to do. It’s frustrating. You know you could be doing more. It’s frustrating because someone thought you had the time to get this work done.

If you don’t believe the premise, imagine the following scenario. You are staying late at the office to get some project work completed. Your manager’s manager sees that you are in the office, comes over, and asks you to do a task for tomorrow morning. If your answer is “I’m sorry, but I can’t because I really need to get this project work completed.”, their response will determine the relative priority of project work in your environment. For some, rejecting the task would be a career-limiting move.

Perhaps then, we are asking the wrong question when it comes to resource capacity management. Instead of asking whether this resource has free capacity to do the work, shouldn’t we be asking if the resource has enough consolidated free time to work on project work? If they do not, what can we do to remedy this situation?

In my “Done in 40” webinar, we discussed recent research by time-tracking software companies that identified how the top 10% of productive employees work in an agile fashion. These employees typically work 52 minutes and take a 17 minute break away from the work.  This is coherent with ultradian body rhythms studies from the 90’s and 00’s that showed your focus naturally waxes and wanes on a 1.5-2 hour schedule. These work sprints can make you very productive and help reduce mistakes and rework.

I’ve personally tried the sprint approach and I can say, it works well for me. I use a timer app on my Pebble watch to monitor my sprints. Fifty minutes is roughly the time where the mind starts wandering to “Did Joe ever respond to my email?” or “Is there coffee?”. Three sprints enable the top three daily tasks to get done easily.

The catch is you need to have 69 uninterrupted minutes to complete a personal sprint. This leads us back to the question of does a resource have consolidated availability? Yes, they have 3 hours available that day but if it’s in 15 minute increments, that’s not usable.

When a client with project throughput issues engages my services, I find it’s usually not a project management issue. Many times, the lack of consolidated availability is preventing the project work from happening. If you are interrupted every 10 minutes, as are most office workers in the United States, it’s very hard to get work done. If you are having issues getting projects through the pipe, perhaps it’s time to look beyond your projects and to your operational work processes.

We spend the majority of our energy providing oversight and processes to projects, which are a minority of the work instead of doing the same for operational work. McKinsey released a white paper recently that showed most of the operational spend goes to keeping the company running. New projects are a small portion of the overall effort. Yet, we don’t monitor operational work holistically the way we do projects. Perhaps, its time we start.

Project management processes are very helpful and needed. We’ve worked out how to anticipate and reduce risk and how to deliver the reward. We need to apply these approaches to how we manage all work. It’s the operational work that provides the overall context within which we do our project work. If improperly managed, it also constricts our ability to get our project work done. Operational work management improvements could yield the biggest benefit by enabling the consolidation of availability, yielding more usable time for project work.

If you are interested in finding out more about the specific techniques and how to use Microsoft Project to support this need, sign up here and get the recording link to the full “Done In 40” webinar.

Is Your Data Looking for a Problem to Solve?

Data can be a wonderful thing. There are so many stories you can tell with the right data. These stories have the power to persuade and motivate positive changes in the organization. And so the marketing stories go on and on about the power of data.

The question is, do you have the right data? Can you tell compelling stories with your data? How do you know what is possible or needed? Let’s discuss some straightforward techniques to help you determine the answer.

Many times, I’ve walked into a client’s office and was greeted with large quantities of data. However, the client was still struggling to tell a compelling story from their data store. There was always doubt as to whether the reports they generated were useful or compelling.

I’ve also encountered clients who maintained additional data as they thought someone *might* need. Unfortunately, wishful thinking is not an effective business strategy and can waste scarce company resources.

Here are some signs that you might need to rethink your data strategy.

  • Do your reports require data definitions, report keys and long explanations for someone to understand what they are viewing?
  • Do you have a training session on “how to use the reports”?
  • Are you maintaining data for which you are unsure of the purpose or use of said data?

If you answered yes to any of these, it might be time for a little housecleaning.

The Effective SimplicityTM approach dictates that we maintain as little data as possible to meet the business need. Minimizing data is a way of reducing the overall cost by reducing the overhead to manage the system. The trick is to determine what data is needed which is sometimes easier said than done.

At Tumble Road, we use an approach that follows a pattern that is understandable and helps determine the context and the need for the right data.  The pattern is Conversation – Question – Supporting Data and Activities.

Conversation is about identifying the specific meeting / use case that the tool will ultimately support. A diagram of some standard Project Management conversations are illustrated below. The conversation will have a schedule so that you can determine how often the data needs to be updated. The conversation has standard participants, which is helpful if you need feedback on the data you will need to provide. It also helps later in the case where you need to inform the report consumers of an upcoming change. Lastly, a list of 1-3 key questions is defined for the use case.

Project Communications

Key Questions are the information needs that needs to be supported. The Key Question also determines the form in which the answer must be presented.

For example, if the conversation is the weekly Tuesday portfolio status meeting between IT Management and Finance, likely, you will need to answer questions similar to:

  • What have we spent?
  • What did we plan to spend so far?
  • What are we planning to spend in the near future?

Supporting Data and Activities are the exact data elements that allow the key question to be answered and the activities necessary to generate, collect and maintain said data. The data can help you determine other incidental data, specifically necessary for organizing and filtering the Supporting Data. The activities can help you spot process gaps that may be present that would prevent you from successfully addressing the question.

When examining the What have we spent question? above, Finance wants Project spend broken down by Project, Cost Type (Capital or Expense) and Sum the totals by Fiscal Year, Fiscal Period for each IT Director.

From this short exercise, I already know the following is needed:

    • A lookup table for Cost Type with values of Capital, Expense.
    • A task custom field with Assignment Roll Down of the values enabled.
    • A lookup table for IT Directors, to maintain consistency.
    • A Project custom field for IT Director so that this can be assigned to each project.
    • To add this Project custom field to the correct Project Detail Page so that the PM can maintain this data.
    • Resource rates in the system so that costs can be automatically calculated.
    • To enter the Fiscal Periods in Project Online.
    • A project template which exposes the Cost Type column at the task level.
    • The cross-tab report layout
Director/Project/Cost Type FY2014-M01 FY2014-M02 FY2014-M03 FY2014-M04 FY2014-M05
John Smith

$100

$100

$100

$100

$100

Project X

$100

$100

$100

$100

$100

Capital

$50

$50

$50

$50

$50

Expense

$50

$50

$50

$50

$50

 

As you can see, you can generate quite a bit of actionable detail using this approach. There are several follow-up questions that can now be asked since you have specific, concrete examples from which to work. For example:

  • Do you need filtering by director?
  • Do you show only incomplete projects?
  • Is the IT Director data something that should be maintained by PMs?

This approach also aids in designing supporting processes. You already know this is a weekly review, so cost information has to be updated on a weekly basis by the PM. As the meeting happens on Tuesday, Monday is the deadline to get updates in and published.

A final advantage is that it is easy to track the progress of visible work by your users. For these engagements, the top level summary task would represent the conversation, a key question represents a sub-tasks and key activities, such as gathering required data, defining maintenance process, etc. are the third level tasks. This makes it easy to determine the “health” of the conversation implementation.

Once you are maintaining the data essential to answering the questions, with clearly defined uses, you should see a reduction in overhead as well as evidence of easier training conversations. If you would like to learn more, please join our Community here.

Avoiding Chaos at Light Speed

Managing multiple Project Server instances over the years has taught me that Project Management tools amplify your project process and project communications effectiveness. If your process and communication effectiveness is good, a tool will make this situation better. If there’s a communications issue or process breakdown, a tool will create “chaos at light speed”, amplifying the underlying problem.

The first step of any Project Server or Project Online implementation is to review your current communication and process framework. Using a question-centric approach like our Effective Simplicity™ approach can help guide you away from potential communication gaps and issues.

Projects are Conversations

Projects have been around for as long as humans have worked together to achieve common goals. They weren’t called projects at that time as most interactions were face to face.  The Project Management Institute (PMI) defines a project as a temporary endeavor undertaken to create a unique product, service or result. Feeding the tribe, fighting off invaders or building shelter for the family were a form of projects.

The efforts grew in size as did the number of people involved, as time passed. The need to capture the conversation between all parties became more critical. Project plans were born as a technique to keep track of the overall conversation. Thus, project plans represent the latest state of the conversation between everyone involved with the project.

The diagram below is a simplified representation of the ongoing conversations related to just one project.

Project Communications

Effective Conversations Require Commonalities

Projects represent a formal conversation that is a temporary reorganization of your work social network. There are some requirements for this conversation to happen effectively.

  • Common language
  • Defined information outcomes
  • Information cadence
  • Defined audience

Notice, there is no mention of common approach. Having a single approach for all projects is a bit like having the same logic for all software. It just doesn’t work as business needs vary. Rather, the touch points between projects should be common, allowing project plans to be customized for the given business problem but still able to share information across the portfolio.

Common Language

Common language implies that the terms being used have the same meaning across the organization. If Marketing, Customer Support and IT are using different terms for the same concept or the same terms for different concepts, a conversation breakdown is imminent. For example, a Go Live date may mean the date that the software is placed into production by IT. However, Customer Support views the Go Live as the first date in which they can start generating tickets. As these are different events, unnecessary confusion with external stakeholders is bound to occur when communicating Go Live plans.

Detailed Information Outcomes

Defined information outcomes are the questions you need to answer with the data captured in the tool. The use of questions focuses your thought process on concrete examples that are easy to communicate, easy to define what is in scope and easy to judge value from the outcome.

For example, you’ve defined an Executive audience who have the following three questions.

  • What is the total project investment for this fiscal year for each of the CEO’s strategic initiatives?
  • Which quarter will key value propositions by CEO strategic initiative be realized?
  • What is overall variance trend of our project investment from original plan?

For each of these questions, you can discuss the desired outcome with the target audience, allowing the definition of clear and concise data to be collected to answer the question. It’s also easier to track progress on the question rather than attempting to ascertain progress from a list of functional configuration steps. Questions also help drive clear implementation requirements. Once these requirements are gathered and tracked according to each question, you can make more intelligent adjustments to overall scope by excluding questions rather than blindly cutting tool scope.

To drill down, the first question will require the scheduling of project costs and assignment of project contribution to strategic initiatives. The scheduling of project costs, which can be done using Project’s cost resources, can be a significant training effort for PMs new to cost scheduling. Using the drivers in Project’s Portfolio Management functions can capture the strategic initiative contribution. Portfolio Management functionality requires good schedule and cost data in order to be effective. This may represent a larger implementation effort than you are able to take on initially. Ultimately, you can do both but now you have a clearer picture of the impact.

Information Cadence

If you’ve ever been in a conversation with your significant other and had your mind drift, you quickly discovered how  a lack of timely information can lead to a serious issue. Information cadence within the organization is about ensuring that the right data is maintained and available on a regular interval. For some organizations, that means projects are updated weekly on Fridays. For others, a monthly cadence is more appropriate. Setting an information cadence expectation ensures that everyone is listening for the same information at the same time.

For example, one client has all updates made by Friday evening as standard reports are generated on Monday morning for the project review meetings that begin on Monday afternoon. The cadence ensures these meetings have the latest information.

Defined audience

Within the project, the importance and significance of a member’s role changes over the life of the project as information needs change to achieve work.

Tools such as RACI attempt to model the project’s social interactions. Communication plans also attempt to do this, but from a different perspective. However, RACI, communication plans and other tools of that sort represent a one-time look at how the project members and stakeholders interact. These models fall down as soon as the project starts and reality takes over.

The question-centric approach maps the audience to the question, enabling you to easily monitor and manage the needs over time. If the question mix changes for a given audience, it is easy to gauge the impact and required work.

Starting Well Prevents Later Issues

Starting your project management system design, using a question-centric approach, will help you avoid later issues by ensuring you are meeting the most important needs of your audience. Clear definition of audiences and questions facilitates clear communication of value propositions. The questions also enable clear prioritization and scope control. Tool configuration becomes a means of supporting a conversation rather than being a driver of conversations. Within this framework, the result will be a much leaner Project Server implementation.

Want to know more about the Effective Simplicity™ approach? Join our community to find out about upcoming events.