Integrating CA PPM Data Warehouse security with Power BI RLS

Background

Tumble Road has partnered with CA Technologies over the last year, to make their reporting more Power BI friendly. At CA World 17, we helped introduce the new OData connector to the CA PPM Data Warehouse to attendees.

The CA Need

The CA PPM Data Warehouse (DWH) has very rich security support at the user level and at the role level. However, when the OData connector was originally in beta, a need to replicate this security functionality within Power BI was noted. If we were not able to have Power BI consume the CA PPM DWH security model, this would make Power BI a non-starter for many companies.

The Power BI Solution

Row Level Security within Power BI was the answer to carrying forward the DWH security model. Power BI enables filtering of data based on the current Azure Active Directory (AAD) User ID. Since all Power BI users have to have an AAD User ID, part of the solution was at hand.

The CA PPM DWH has a security table that lists every entity for which a user can access. This information is used by Power BI to security the data.

However, CA’s solution does not use AAD so a mapping exercise may be needed between the CA User Name and the Microsoft User ID. The video below shows a very simple case where we are able to append the Office 365 domain name to the CA User Name to get the requisite AAD User ID.

Your particular situation may require a more extensive mapping exercise. Elaborate mappings can be done in Excel and imported into the model. This imported mapping data can be used with the Security table to implement the requisite information.

The Presentation

In the video below, I show you how to bring the Security table from the Data Warehouse into Power BI and use the data as a basis for applying Row Level Security within Power BI. This should restrict the data to only that that the person is authorized.

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!

The one surprising thing about Visio Integration in Power BI

I was introduced to the new Visio custom visual for Power BI during the Microsoft Inspire convention. After a few minutes, I was impressed with the power and simplicity of it. It helped solve a problem that we’ve had when building out Power BI reports.

Telling a Complete Digital Story

In my Power BI classes, I talk about the importance of creating complete digital stories. They are complete in that you have three components, which allow the story to be understood in a standalone fashion. The three components are

  • Where are you
  • Where do you need to be
  • What is the path or connection between the two states

Think of Visio integration as the easiest way to show your data road map. The Visio diagram can add needed context to the overall picture. Adding proper context with a great diagram makes it much easier to interpret the results, make critical decisions, and take necessary actions.

Quick Power BI Example

Imagine you are a banker and you are trying to assess the current state of your loan process. Throughput is a very important to this process and you want to avoid things getting hung up as this impacts profits. Clients also get upset when they miss closing dates as they can lose real estate deals.

Today, Showing Data without Context

Today you have a Power BI report with various visuals that provide health metrics. You can easily see things like which step has the highest average age of items. You can even see with the bubble chart the overall distribution of steps by Average Age and Item Count.

However, the story isn’t very compelling and it doesn’t answer a key question, what else will be impacted if I don’t fix process step X? Do you clearly know where to focus your attention?

Tomorrow, Your Data In Context

Compare to this report where we’ve added a Visio diagram of the process. The diagram serves as a heat map. Areas that have high aging average values will be in Red. Those in danger are in Yellow and everything else is green. I can still answer the questions I had before. However, now I can see in a glance where I have too many “old” loans in process and what will be impacted downstream.

As I click on any visual on the report, the Visio diagram will zoom to the related step. If I click on the red process step in the Visio diagram, all other visuals on the page are filtered. These behaviors encourage further exploration of the data.

Surprisingly Easy to Implement

The one thing that surprised me about this visual is how easy it is to incorporate Visio diagrams you already have into your Power BI reports. The mechanics are such to make it very easy to map data to the shapes.

Scenario

I want to replace the Visio Diagram above with an existing one that I have. It shows the four major phases of the process. I want to use this diagram on an Executive version of the report, where I don’t need great operational detail.

Prepare Your Diagram

Step Action Diagram
Take your existing diagram and do this:
Design, Size, Fit to Drawing.
This helps reduce the white space around the drawing
The canvas will appear as shown.
Save your diagram using File, Save
If the diagram is not already in an Office 365 SharePoint folder, upload the diagram to a location that the consumers of the report would have access.
    
Click on the diagram to view it in the browser
Copy the URL as you’ll need this later in Power BI to insert the diagram.

Replace the Existing Visio Visual with a New Instance

Step Action Diagram
Open the model in Power BI Desktop
Select the Visio custom visual that shows the existing diagram
Go to the Visualization area and select another visual type. This resets the Visio custom visual
Click the Visio icon in the Visualization area to change it back
Paste in the URL of your diagram that you saved earlier.
Click Connect and login

Map Your Data to the Diagram

There are two tasks that are generally required when adding an existing diagram to a Power BI report.

  1. Replace the column value in the ID field.
  2. Map each shape to a data value in the ID column.

The procedure below will take you through the steps to do both actions.

Update the Column Values in the ID Field

Step Action Diagram
In Power BI Desktop, go to the Fields tab for the Visio visual. Drag the new ID column value over the existing column value.
Now Phase is in the ID Field.

Map Shapes to Data Values

Step

Action

Diagram

Click the < on the Field Mapping bar in the Visio Custom Visual
You will see the ID: field highlighted in yellow
Click the dropdown next to the ID field name. You’ll see the list of data values from the ID column shown.
To map a shape to a data value, select the shape, then select the data value to map to it.
Repeat for each shape and data value.
When you are done, collapse the ID field
Review the Values Settings below.
If you want to show the actual value, change the Display As to Text
OR
If you want to show the value in the form of a heat map, change the Display As to Colors. Set the colors and range accordingly.
Save and Publish Your Power BI model.

Live Example

When you see your report online, you can either click any box in the Visio diagram to filter all other visuals or you can click another visual to filter the Visio diagram.

An example of this report can be found below.

Conclusions

As you’ve seen, the mapping feature makes it quite easy to incorporate any existing Visio diagram into a Power BI dashboard. You can now add things like Org charts, process maps or other visual data for filtering in your reports.

More Information

If you want to know more, check out these links.

Want to Learn More? Register for one of our virtual training classes today!

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.

The Augmented Project Manager

This article was originally published on our CIO Magazine blog, the Effective Enterprise.  After seeing recent industry presentations on bots, machine learning and artificial intelligence (AI), I see the application of these technologies changing the practice of project management. The question is, is this future desirable or will we have a choice?

The project manager role and the growing role of Artificial Intelligence

Much of the daily work of a project manager has not dramatically changed over the last 30 years. We may use different management methodologies, but we spend a great deal of time manually collecting and disseminating information between the various roles on a project. This effort directly results from the need to fill the information gaps caused by systems that can’t capture what is truly happening within the organization. In a recent PMI sponsored roundtable discussion, missing or incorrect data was highlighted as a significant issue. Today’s systems are totally dependent on human entry of information, where it can be nuanced or simply not entered.

The combination of artificial intelligence in the form of bots and cloud computing could radically change this situation. PM effectiveness would be dramatically enhanced and likely the need for some PM roles diminished. In the future, as data capture becomes richer and more automated, we may see new adviser services that arise from improved data quality and completeness. I foresee significant improvements in three key areas.

Planning

One of the black arts of project management is predicting the future, where we represent this future state as a new project plan. We draw upon our own domain and company experience to determine the steps, resources and time needed to accomplish the goal. Our success rate at predicting the future is not good. Our predictions are fraught with error due to the limits of our experience and that of the organization. If you’ve ever managed a project for something completely new to an organization, you are familiar with this situation.

Imagine if your scheduling bot generates a proposed project plan, based on the aggregated and anonymized experiences of similar sized companies doing the same type of project. Today, we use tools like Monte Carlo to simulate this information. The bot could incorporate real world data, potentially yielding better results.

Benchmarking of business data has been around for some time. These new cloud capabilities could see bench-marking expanded to include real-time project management data.

Resource allocation

Another common challenge of project managers is that of resource constraints. Imagine a world where your resource pool is the world and it’s as easy to use as Amazon.
We are seeing the continued growth of the freelance nation trend in corporations. Currently, corporations use agencies to locate and recruit talent. Agencies may simply be a stopgap as bots become a more efficient clearinghouse of freelancer information. Staff augmentation agencies could become obsolete.

For example, your resourcing bot determines that you need a social media expert on your project on April 5th for two days of work. It searches data sources like LinkedIn and your public cloud calendar to find a list of suitable and available candidates. Three are West Coast of the U.S., one is in Paris and one is in Sydney. It then automatically reaches out to these candidates with offers. If multiple people accept, it automatically manages the negotiation. Once complete, the planning bot is informed, a virtual desktop with requisite software is provisioned, user login credentials are generated and the specific task information is sent to them. When the job is complete and rated as satisfactory, the bot coordinates with your accounts payable system to pay the freelancer. The planning bot automatically updates the plan and pushes the data to the BI dashboards.

Tracking

Project feedback loops on work are awful. The largest challenge is incomplete data, which results from increasingly fragmented work days, limits of the worker’s memory and tools that rely on human input. It is also incomplete as it serves little benefit to the person entering the data.

Workers are overwhelmed with tasks arriving via multiple communication channels and no consolidated view.

Imagine a world where the time sheet is antiquated. Today, we have systems such as Microsoft Delve that know what content you’ve touched. We have IP-based communication systems that know what collaborations you’ve conducted. We have machine learning capabilities that can determine what you’ve discussed and the content of the documents you’ve edited. This week, we have facial recognition capabilities and other features that can track and interpret your movements. Given all of this, why is a time sheet necessary?

Professional athletes use this type of data in the competition setting to improve their performance, using the data feedback to spot areas of development. Combining this activity information could prove a boon to productivity.
I can see this working as a “Fitbit” type feedback loop that helps the worker be better at their job and allows them to get home on time. Doing so provides direct benefit to the employee and reduces the Big Brother feel of this data.

The personal bot acts as a personal assistant, reminding the worker of tasks mined from meeting notes and marking tasks as complete in real time. All the while, it is also keeping track of the time spent that enables to the worker to get a better picture of how they spent their time.

Brave new world

There are many challenges with the view I’ve presented above. Many of these challenges are the same faced when we automated and integrated procurement processes. It is also hard to deny that there is compelling opportunities to improve the worker lives as well. Bots, machine learning and artificial intelligence are reachable capabilities that should be incorporated in the PM toolbox as you plan your organization’s future work management needs.

I look forward to reading your viewpoints and experiences in the comments below.

 

 

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]

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.