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

TIP: Set the Default View In Project Pro

If you have a preferred view that you use in Project Professional that is not the Gantt view, it gets a bit tedious to change the view every single time you open the application. The easiest thing to do is to change the default view setting so that your preferred view is displayed when the application is opened.

To update the default view used:

  1. In Project Professional, click File, Options, General
  2. Under the Project View section, click the Default View dropdown and change the value to the desired view (highlighted below)
  3. Click OK to apply the change

image