CoP TIP: Prevent column changes from breaking your Power BI model

This is a post from our Managed Community of Practice service that supports clients in building a data culture. If you need help for your organization, contact us at hello@marqueeinsights.com to discuss.

Who Broke My Model?

Don’t you hate it? You have a Power BI model pulling data from many sources, like SharePoint and a few Excel spreadsheets. You come in in the morning and see a Teams message that your Power BI model isn’t updating! Oh no, what now?

You look in the Power BI service and see that there’s an error on the refresh. You sigh as once again; someone renamed a column, or they deleted a column from the data source.

Business is constantly changing so this shouldn’t be such an issue, but it is. Power BI refers to all columns by name, so the default behavior will lead to issues. Power BI is getting rolled out everywhere in the org, so this problem is growing quickly.

Models Should Adapt to Changing Data

In a perfect world, Power BI handles schema changes without blowing up. The column that is renamed or removed is simply returned as null values, giving you time to figure out where it is used and make changes accordingly.

Thankfully, that world is here today. Let’s review how to make that happen.

Here’s How to Make Power Query M Adapt Automatically

The key is in how you specify what columns to include in your Power Query. As discussed in your Marquee Insights Power BI class, this action should be done immediately after the Navigation step as this action generally folds back to the data source. The order in which this action is made is of even greater importance, using this technique.

It’s important to select your columns first, after navigation, for this to work.

Normally, when you select your columns, you specify the following Power Query M statement, usually through the UI action:

= Table.SelectColumns(Table_Name,{"Field1", "Field2",
"Field3"})

If Field3 is no longer returned by the data source, the query errors out. Why is that? Well the Table.SelectColumns command has three parameters. The third parameter is what action to take when a field is missing.

= Table.SelectColumns(Table_Name,{"Field1", "Field2",
"Field3"},ActionToTakeWhenFieldIsMissing)

There are four potential values for this third parameter.

  • MissingField.Error
  • MissingField.Ignore
  • MissingField.UseNull
  • MissingField.Type

MissingField.Error is the default so when a column is missing, an error is thrown, and the column is not returned as part of the dataset. The MissingField.Ignore option doesn’t throw the error but also doesn’t return the missing column. Unfortunately, these two options will break any reports that use that missing column.

Thankfully, there’s MissingField.UseNull. This option is the key to making your queries resilient to changes.  If a specified column is not found in the underlying data, it returns the column as a column of null values. The benefit is that your reports will continue to work, and it allows you to track down where the column is being used.

I’ve yet to find any documentation regarding MissingField.Type so that will be covered in a later blog post.

An Example

I’ll use the Projects oData feed from Project Online to illustrate the behavior. Project Online is an example of a product that allows Administrators to define and change custom metadata easily. If you are not reading this on the MarqueeInsights.com site, please go there for the original version. Consequently, model refresh failures are a common issue with Power BI and Project Online.

I’ve created a Project Custom Field called “A Test of Field Removal” in Project Online.

Set up of the Project level Custom Field in Project Online.

I’ve created a new query against the Project oData feed and selected five columns. I’ve specified the default MissingField.Error value. The values load normally as shown below.

Select columns using the default MissingField.Error value.

Now, let’s delete the column from Project Online and refresh Power BI. The result is this error.

Deleting the source column yields an error.

If we change the MissingField parameter to MissingField.UseNull, we get the following result instead. Note, the missing column is returned but now as a column of nulls.

Using MissingField.UseNull, the column is returned with nulls, allowing use to continue.

That’s all there is to it. Simply ensure you are using the correct parameter and you’ll see less data model issues due to renamed and/or removed columns.

Controlling Power BI Filter pane visibility with Bookmarks

I was working with some bookmarks in a Power BI model earlier when I noticed that in some cases the Filter pane was expanded and in other cases, it was collapsed. I was intrigued so I started investigating.

It seems when you save the bookmark, it now retains the open/close state of the Filter pane. I believe the Display option within the bookmark has to be set in order to control this. So, if you wish to show the Filter pane by default on a report, expand it in the PBI Desktop and update your bookmark. Collapse it and save bookmark if you want it collapsed by default.

If you have a question or comment, post it below.

Microsoft Ignite 2017 Session Picks!

It’s that time again and if you are headed to Microsoft Ignite 2017 and are overwhelmed with the session choices, here’s some recommended sessions to check out.

Power BI – Microsoft Ignite 2017

If Power BI is your interest area, here’s some great sessions to check out.

Dive into effective report authoring using Microsoft Power BI Desktop 

https://myignite.microsoft.com/sessions/53124

Miguel Llopis and Will Thompson

Session code: BRK2111

Microsoft Power BI Desktop is a tool that allows data analysts, data scientists, business analysts, and BI professionals to create interactive reports that can be published to Power BI. Join us during this session for a deep dive into the report authoring, data preparation, and data modeling in Power BI Desktop. Topics covered include third-party connectors, data exploration, and data visualization. This session includes lots of demos, including what’s new in Power BI Desktop and what’s coming.

Managing Space and Time with Visio and Power BI

https://myignite.microsoft.com/sessions/55898

David Parker, Scott Helmers

Session code: THR2177

You’re attending Ignite. You’ve registered for 15 sessions. The sessions are located in more than 300 meeting rooms. The meeting rooms are spread across nearly three million square feet in the Orange County Convention Center. What tools do you have that can help you to maximize your time and minimize unnecessary walking?

  • You have a list of sessions.
  • You have a floor plan.
  • You have a clock.
  • Best of all, you also have Visio Professional and Power BI!

Learn how you can use the data mining, operational intelligence, and data visualization capabilities of those products to navigate the cavernous convention center more effectively.

Mining Yammer data for gold using Microsoft Power BI

Melanie Hohertz, Dean Swann, Becky Benishek, Simon Denton, Loni French

https://myignite.microsoft.com/sessions/53789

Session Code:  BRK2148

It’s a noisy conversation around enterprise social right now. But when you cut through to the signal, Microsoft’s data says Yammer is growing faster than ever. If you want data-driven decisions and value in social collaboration, analytics have never been more critical. Join a group of Yammer experts as they explore the importance of taking the broad view of Yammer data. Attendees get an overview of Power BI and a review of the Office 365 Content Pack, focusing on Yammer. We take an in-depth look at the “art of the possible” with Yammer data in Power BI, with real-world examples. Come see the power of Yammer, expressed in data that mines the gold for hands-on community managers and executive stakeholders.

Learn how to apply advanced analytics for Microsoft Project & Portfolio Management (PPM)

https://myignite.microsoft.com/sessions/53818

Jackie Duong,  Rick Bojahra,  Michael Patrick

Session code: BRK3025

Empower decision making by unlocking business insights. Take your reporting capabilities to the next level through Power BI and other analytics tools, with easy-to-use live data monitoring to show your data in a simple and compelling way. Hear directly from the global leader in designing and manufacturing water parks, WhiteWater, who deployed Project Online alongside Microsoft Dynamics and Power BI to optimize their business.

SharePoint Search – Microsoft Ignite 2017

There’s a lot of renewed interest in search and these speakers are worth your time. I’d recommend the following sessions in this area.

Accelerate productivity with search and discovery in SharePoint and Office 365

https://myignite.microsoft.com/sessions/53316

Kathrine Hammervold, Naomi Moneypenny

Session Code: BRK2181

Effective search needs to know what information that is relevant to you, your colleagues, the work you do and your context right now. Find out how we have used insights across Microsoft Office to create such a personalized search experience. A new search UX has been developed focusing on simplicity and performance enabling the user to quickly interact with a more personal and semantic organization of data. Find out how search now also supports multi-national corporations and how hybrid search works with the Microsoft Graph. Also learn about the roadmap for enterprise search in SharePoint and Office 365 for experiences, extensibility and the convergence of FAST and Bing search innovations.

Build your personalized and social intranet with SharePoint, Yammer, Delve, OneDrive and Teams

https://myignite.microsoft.com/sessions/55059

Naomi Moneypenny, Brian Duke, Rick Garcia, Greg Nemeth

Session Code: BRK2185

Hear how other companies have recently built their intelligent intranets and learn how to use capabilities of SharePoint, OneDrive, Office Delve, Yammer, Microsoft Teams to create cohesive experiences for productivity and cohesive digital culture. Explore how to empower business users and site owners with the tools and guidance they need to create, target, personalize, and consume content as well as bring rich interactivity for different business scenarios. The intranet of the future awaits!

Not going to Ignite? Check out our Training classes!

Virtual Public classes and Private on site classes are available!

Check Out Our Classes!

How to: Group Dates by Week in Power BI

This post shows you how to use the date hierarchy and the grouping function to easily group your data by year, month, week in Power BI. This can be very handy when reporting against data that is timephased, such as sales transactions or Project Online schedule and capacity data.

If you need to start your week on a specific day of the week, you can create a new column based on your date, using this DAX statement:

Week Beginning Date = ‘TableName'[DateFieldName] – MOD(‘TableName'[DateFieldName]-1,7) + [Add 0 for Sunday, 1 for Monday, etc. to start week accordingly]

Retrieve Fiscal Year Dates Dynamically

Finance guy

At some point, you will be asked for information by Fiscal Year. The Fiscal calendar was set up in Project Server so it should be accessible for reporting, right? A fair number of companies have fiscal calendars which don’t exactly line up with the standard calendar which can cause some challenges. You could hard code the beginning and ending dates of the fiscal year, but then that would create an annual report maintenance task. What’s a report writer to do?

If you’ve set up the Fiscal calendar within Project Server, it is easy to retrieve this data for the current Fiscal Year based on the current date. The Fiscal calendar information is stored in the MSP_TimeByDay table and can be queried to do all sorts of date based operations.

The example provided below can be used with Excel based reports as well as with SSRS reports.  The key is to do the lookup in two steps. The first part of the query retrieves the Fiscal Year beginning and ending date so that you can use this information in the WHERE clause of the following query to filter the dataset.

THE EXPLANATION

Let’s say I need to find out in real time, how many hours of work were scheduled by month for the Fiscal Year.  The real-time requirement pushes me to use SQL rather than OLAP for this data. Since monthly data on assigned work and cost requires aggregation of the time phased assignment data,  I need to query the Assignment By Day view (MSP_EpmAssignmentByDay_UserView) in the Reporting database. Production reports would require additional joins to Resource and Project views to complete the dataset but for illustration purposes, I’ll limit the example to only the Assignment By Day view for illustration simplicity.

THE QUERY

The following query works in Excel where I find this data is most commonly requested.

The DECLARE statement declares the two variables where we will store the date results for the beginning and end of the current Fiscal Year.

The next SELECT statement uses a subquery to get the beginning and ending date of the current Fiscal Year. The subquery uses the current date to find the current Fiscal Year for use in the WHERE clause of the primary query.

You may be wondering what this clause is doing as you look at the code below. CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))  This clause is needed to address the time mismatch between the TimeByDay data and the current time.

All of the dates in the TimeByDay field in the MSP_TimeByDay table are stored as dates with midnight time. If you retrieve the current date and time, the numbers won’t match if you attempt to use it as a filter as the time portion will be different. So, you have to reset the time part of the current time back to midnight for this filtering to work.

  • GETDATE gets the current date and time.
  • Casting the result as a FLOAT represents the date and time as a decimal number, where the portion to the left of the decimal is the date and the time is stored to the right of the decimal.
  • The FLOOR statement sets the decimal portion of the number to its lowest value, which would represent midnight.
  • The outer CAST makes it a Datetime field data type again so that we can use it to filter on a Datetime field. In the last query, you use the declared variables to find all records between the beginning and finishing date of the Fiscal Year. Note, this technique can also be used to get the current Fiscal Quarter, current Timesheet period, calendar month, etc. There is a fair number of data elements in the MSP_TimeByDay table that you can use for date related needs.

DECLARE @BEGINDT DATETIME, @FINISHDT DATETIME SELECT @BEGINDT = MIN(TimeByDay) , @FINISHDT = MAX(TimeByDay) FROM MSP_TimeByDay WHERE FiscalYear = (SELECT FiscalYear FROM MSP_TimeByDay WHERE TimeByDay = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) SELECT * FROM MSP_EpmAssignmentByDay_UserView WHERE MSP_EpmAssignmentByDay_ UserView.TimeByDay BETWEEN @BEGINDT AND @FINISHDT

WHERE DO I PUT THIS?

If you are using one of the default Excel report templates in the Project Server Business Intelligence Center, you would open the file in Excel client.

  • Go to the Data tab
  • Click Connections
  • Select the connection and click the Properties button
  • Go to the second tab and click into the SQL box
  • Select all (Ctrl+A) and delete the existing SQL
  • Copy the above query and paste into this box
  • Click OK to update and OK on any warning
  • Once updated, click Close to close the dialog

Once it returns the data, you would see the returned fields in the Excel field well next to the Pivot Table.


Looking for more Project Server information?

Visit the AboutMSProject.com Recommended Books page.


The Social Nature of Project Management

clip_image002

A successful configuration of Project Server is one that supports the conversations within the organization. Users have to go beyond use of the system and have concerns over the validity of the data entered. The Project instance captures the requisite information for the conversations and enables participant to synthesize additional outputs and insights. Therefore, an understanding of the work social environment is key to understanding the requirements for configuration and getting the necessary level of user engagement.

Projects: One of the Original Social Networks

Projects have been around for thousands of years as humans started working together to achieve common goals. We didn’t call them projects back then as most interaction was face to face and immediate. By PMI’s definition, these interactions were projects in that they were temporary endeavors undertaken to create a unique product, service or result. Feeding the tribe or building shelter for the family can be considered projects.

As projects got longer in duration and more people were involved, the need to capture the conversation between all parties became critical. The social networks involved in doing the work grew larger and longer. 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.

Projects are Dynamic Social Networks

Projects today represent a temporary reorganization of your work social network. Within the project, the roles and importance of members change 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 from a different perspective. However, RACI, communication plans and other tools of that sort represent a one-time, almost theoretical look, at how the project members and stakeholders interact. These models fall down as soon as the project starts and reality takes over.

Many social networks have different levels of access based on either whether you friend someone (Facebook) or through which contact you know them (LinkedIn’s Levels). Unlike Facebook and LinkedIn, your work social network can be defined and refined by the interaction level with others. The interaction level with a specific person or team represents mutual dependency as the interaction yields information needed to achieve work. A time aspect adds priority to the interaction as tasks that are due earlier require information sooner. Emails, meetings, IMs, tasks and break room discussions all represent types of interactions. As a result, today’s joint deadlines will prioritize a co-worker’s activities to a higher visibility than a co-worker with which you have a deadline 3 months from now.

Modeling the Work Social Network

Your work social network can be modeled as a hierarchy of social zones based on interaction and common experience. Each zone contains social group which morphs over time, depending on the level of immediate interaction. My basic model contains four zones. These are:

  • Inner
  • Shared
  • Experience
  • Public

Members of the Inner zone are those individuals with which I have immediate interactions. These would include:

  • People with which I have joint dependencies
  • My Project Managers
  • My Direct Manager

By immediate, I mean there is a deadline within the three week window of activity that most focus. We look back to last week for status and forward to the next two weeks for planning. The immediate nature of the interaction means my attention to this group’s information and needs will be prioritized above other interactions.

The Shared zone members are people with which I have a current shared experience but do not have joint dependencies. This group’s information will be of interest but not priority. This group includes:

  • Other project team members with which I’m not directly collaborating
  • Other members of my work team with which I do not have project work
  • Past direct collaboration partners on current projects
  • Other interests or information to which I have subscribed

The Experience zone comprises the edge of your social work network. While the Inner and Shared zones tend to focus on interactions within your current work environment, the Experience zone extends beyond. The Experience zone is comprised of those individuals with which you’ve had past interactions. This can be:

  • Former Work team members
  • Former Project team members
  • Past transitory interactions within and external to the work environment

Many may consider this to be the “LinkedIn” zone. Members of this zone can be a source of information as well as an external communication channel of activities and opportunities.

Last, the Public zone is attached to your public persona. This can include:

  • All others in your company with which you have had no interaction but share a common identity
  • Contacts based on public facing interactions

Example Scenario

I have a joint deadline with my DBA this week. The DBA has a family emergency and has to fly to the other coast immediately. Currently, they are within my inner zone and I would be very interested in this absence so that I can respond accordingly.

If my DBA was someone who is on my project but I don’t have shared tasks or deadlines, they would be in my Shared zone. I might be interested in this event but there may be no immediate action needed.

If we had worked together in the past but not currently working together, the DBA would be in my Experience zone. I’d probably regard it as informative.

If the DBA is in my fantasy football league and I email them frequently on the upcoming draft, I may have other social ties to this person, which could raise their level to Shared or Inner.

As you can see from this example, the challenge of implementing a social-centric project management system in the organization is incomplete information. Likely, you will not support interactions in the Experience or Public zones. Failure to support the Inner Zone interactions will slow or prevent adoption of the tool by users.

Inner Zone Support is Key

If projects are the latest state of the conversation, then Project Managers manage conversations rather than schedules. Schedules should be developed to support the conversations necessary to get the work complete. I’ve met many project managers who feel that their job is only about the project schedule. As they don’t see themselves as conversation managers, they tend to develop project plans that don’t support their conversations. Plans will either gather dust or worse, lead the team to improper conclusions.

Creating infrastructure to support the Inner Zone conversation is key to successful adoption of Project Server. Too many times, the system is configured from the perspective of the PMO. The PMO is happy but no one else will see anything of value. No value leads to a lack of engagement and the death spiral of apathy and bad data ensues.

To support the Inner Zone conversations, every configuration element has to be viewed from the perspective of the self and the Inner zone. A great place to start is to “make the system about me”. By this, little things like a My Projects view in Project Center, a Team Site home page that has my Active Issues and Risks and reports that show My Tasks make the system more about me. This raises engagement. Once this “me” need is satisfied, the ability to address the Inner Zone can be an extension of the self-views.

The goal is to turn Project Server into a source of information that drives people to want more, similar to honey in a beehive. By comparison, many customers of Project Server host a weekly cattle drive where we try to herd our user base to enter their time, update their projects and run their reports. Cattle drives aren’t satisfying and aren’t sustainable long term. We can and should look at the existing successful social platforms for ideas to improve the collection and use of data in Project Server. Honey may be easier to achieve than you think.

Converting RTF to Text in SQL Revisited

If you’ve ever attempted to include content from a Project multi-line text custom field or Issues multi-line fields in an Excel report, you will get a result that looks like this:

<div><p>Vendor is having issues with deliveries to the East Coast.</p></div>

At the 2012 Project Conference, I presented a SQL based solution that converts the RTF content to XML and then returns the text. This technique was also published here in the Project Server Forum.

In practice, the technique worked but had failures in some cases. I found early bugs in SharePoint where the lead or trailing <div> tag was missing. However, this did not explain all failures.

I got an email from Doug Welsby of Microsoft Canada, that the failures were due to text values like &nbsp; which are not standard XML. Basically, an inline (Document Type Definition) DTD is needed to convert a non-standard XML value like &nbsp; to XML compliant &#160;  The full list of possible values to convert can be found here: HTML Codes

Technique Assumptions

This technique works on any multi-line Project custom fields or multi-line list columns in SharePoint. Therefore, the Issue Discussion field from SharePoint can also be cleaned with this technique.

The following example illustrates the SQL technique, though you may have to extend the DTD definitions to fit your own data. Use the table to above to add additional ENTITY declarations. The technique also assumes that the multi-line field contains valid HTML. I’ve found no issues with this technique on systems patched to June 2012 CU or later on Project Server 2010.

This technique will work with Project 2007, 2010 and 2013 databases as there are no real differences in how this type of data is stored across releases. This technique does work inside an Office Data Connection.

NOTE: One of the challenges I faced is that I could find no good examples of how to do an inline DTD in SQL. While the technique below works, I’m open to a more elegant way to construct the DTD. Please post any ideas or issues in the comments.

The Technique

The technique does the following:

  • Converts the RTF value to an XML document
  • Extracts the text value from the XML
  • Trims the blank space from the resulting value
  • Replaces any null values with a blank
    First, two variables are declared to hold the XML Header and Footer information. The Header contains the inline DTD declaration for &nbsp; and could be augmented with more declarations. The Footer contains the tags needed to complete the document.

The SQL CONVERT function changes the concatenated string of Header, your multi-line Project custom field and Footer into an XML document. The 3 option in the CONVERT command enables the inline DTD as well as it leaves all internal spaces as is. More details on the CONVERT command can be found here.

The .value method performs and XQuery and returns the string value to SQL. More details on this method can be found here. The method returns the value to an nvarchar(4000) field but you can use nvarchar(max) if you think you may have a truncation issue.

LTRIM removes the leading spaces of the resulting string. You could also do a RTRIM to remove following spaces but I didn’t find this to be an issue. The ISNULL is used to return a blank when a null value is returned. If you don’t do this, you get (blank) in Excel rather than an empty cell. You can fix this in Excel but I found it more efficient to simply fix it in the data source for every report that uses it.

Code Sample

Replace the YourMulti-lineCustomField text below with the name of your custom field. Since the original field is also in the query result set, I tend to name it YourMulti-lineCustomFieldClean so that I can distinguish the two versions.

The ISNULL clause should be repeated for each custom field you are cleaning.

    declare @Headxml nvarchar(350)
    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  nbsp "&#xA0;"><!ENTITY quot "&#x22;">]><html><body>'
    
    set @Footxml = N'</body></html>'
    
    select   *      
            ,ISNULL(LTRIM((CONVERT(xml,(@Headxml+[YourMulti-lineCustomField]+@Footxml),3)
            .value(N'(/)','nvarchar(4000)'))),'') AS [YourMulti-lineCustomFieldNewName]
    
            ,ISNULL(LTRIM((CONVERT(xml,(@Headxml+[YourMulti-lineCustomField2]+@Footxml),3)
            .value(N'(/)','nvarchar(4000)'))),'') AS [YourMulti-lineCustomFieldNewName2]
    FROM  dbo.MSP_EpmProject_UserView