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.

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.

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]

How to query Project Online multiple value fields in Power BI

Power BI

This post addresses a need to arises when querying Project Online data via OData. It can also serve as a solution template for other data sources.

Synopsis

A number of projects in our portfolio have impacts in different countries. These countries are designated via a Country Project level multi-value (MV) custom field. When multiple values are selected from the dropdown list, it creates a comma-delimited list of values. For example, you could see Brazil, Canada, United States returned as a field value from OData.

Challenge

One of the challenges is that this list can be any number of values and will grow and contract over time. Therefore, the technique should automatically adjust to the underlying maximum number of specified values.

Approach

For each MV field, a new data set representing all values in the MV field will be added to the data model. This new MV field data set will be a child to the original Project master data set. The relationship between the Project set and the MV set will be 1:N.

An inline function will also be used so that it can be executed for each record in the data set. This function will perform the split of values into separate columns.

Once split, the values will be unpivoted, creating a record for each ProjectId, MV Field value pair.

Potential Issues

  • Correct data set relationships. Initially, Power BI created the MV data set with a 1:1 relationship instead of a 1:N. Once corrected, all errors went away.
  • You will need to watch refresh performance as you are basically querying the same data multiple times within the model.

Procedure

Register here for this free session to see the step by step video and to download the PBIX file containing the data model with sample code. http://academy.tumbleroad.com/courses/how-to-parse-multi-value-columns-in-power-bi

One surprising challenge that can derail your Business Intelligence implementation

One challenge facing Business intelligence implementations today is related to how data is shared within the organization. We’ve been working to make work more open and social over the last few years, but there’s one place we’ve neglected to address along the way. How do we share ad hoc business intelligence data within the organization?

 

In years past, our Business Intelligence content was developed as individual reports. This disconnected content approach enabled data producers to act as data gatekeepers. The owner of the report would decide who to share the data with and would email it to the person as they saw fit.

 

In subsequent years, we migrated to a portal based distribution strategy. Tools like SQL Server Reporting Services enabled us to post formal reports centrally, where they could be accessed. However, ad hoc reporting generally remained outside of the portal, to remain shared via email.

 

With the advent of new tools like Power BI, ad hoc reporting is easier than ever. Everything on the screen is clickable and it enables interactive data exploration. The intended interaction with these tools is similar to how we use Facebook, Twitter and Pinterest in our personal lives today. We post information and others can choose how and when to consume it.

 

Imagine the confusion that arises when a BI implementation team gets puzzling questions from groups. How do I email this data model to others? How do I print a dashboard and so forth? Simply updating the tool set to the latest generation, while ignoring certain underlying organizational behaviors can impact the long term success of implementing the latest generation BI tools.

 

An organizational assessment should be done as part of a Business Intelligence tool implementation to consider how people share data currently and as to whether those sharing habits are compatible with the new BI tools. If the organization is used to posting and forwarding links, then they should have no issues adopting the new tools. If the organization is still email centric in their information sharing, a concerted effort will be required to change habits away from sharing content via email and to posting and link sharing. These findings can be used to adjust scope around work needed for training and ongoing support.

 

What has been your experience? Please share below in the comments.

Using Project Online, Power BI Alerts and Microsoft Flow for Custom Alerts

This is so cool! I just received an alert about a data condition in Project Online from Microsoft Flow. The best thing is that this condition isn’t anything Microsoft provides out of the box. With the functionality now in Flow and Power BI, we can now construct our own using Power BI and Flow.

With recent updates, Microsoft Power BI added the ability to set alerts on the card visual. You can read more about the details here. One of the settings allows an email to be sent with a distinct subject. That got me thinking since I knew from playing with Microsoft Flow that you can connect it to Office 365 Outlook and drive flows from specific emails. Flow also provides a Push Notification function so that I can push a notification to the Flow app on my iPhone.

It seems like all of the pieces I need are there to create my own custom notification.

First, I have a Project Online instance where I’ve got projects and people assigned to those projects. I’m using Project to manage capacity so knowing when the total number of overallocations is growing helps me react accordingly.

Project Web App

Second, using our Marquee Project Dashboards product, I have a count of Overallocated resources already available. I’m using Power BI Pro, so my data model refreshes automatically multiple times a day.

Marquee Dashboard

Third, I’ve created my own dashboard and I pinned the count of Overallocated Resources to it. Once the card is on your dashboard, you can click the ellipsis menu … and access the alert function (bell icon).

When the Alert panel opens, I turned it on, changed the Alert title and set my threshold. You can also determine if you need once an hour or once a day for alerting. The key is to check the box that says to send an email. This provides a way for us to use Flow to act upon this alert.

Now, we go to http://flow.microsoft.com. Once you login (because you already have an account, right?), go to My Flows and select Create a New Flow. You are going to create a two-step flow where it connects to your email looking for a specific subject and sender and the second step is where it sends the notification.

  • Type Outlook into the Search window to see the Outlook events.
  • Select Office 365 Outlook – When a new email arrives
  • Ensure the connection is to the right account that will receive the alert email
  • Click Show Advanced Options
  • I filled in mine to look like this. Note the From and Subject Filter values.

  • Now click New Step
  • Click Add an Action
  • Type Push into the search window
  • Select Push Notification – Send a push notification
  • I filled out mine to look like the following. You could have the URL set to bring you back to Power BI or Project. I chose for simplicity to bring me to Outlook.

I would have loved to have had an SMS message generated. This would have required a Twilio account that I don’t have at the moment. So since I was playing, I took the free route and loaded the Flow app on my iPhone. The Push notification would then show up there on my phone.

Once set up,

  • I updated a Project Online project plan to create a new resource overallocation and published it.
  • Power BI automatically refreshed the dataset, the number of overallocated resources increased.
  • This value change triggered a Power BI alert and sent an alert email to my Outlook inbox.
  • Flow picked up the alert and fired off the Flow.
  • The push notification was sent to my phone and bam, there it is on my Apple Watch.

What do you think? What have you created with Flow and  Power BI? Tell me in the comments below! I hope you found this useful.