Tag Archives: Power
Monitoring the Power Platform: Custom Connectors – Building an Application Insights Connector
Summary
Connectors are used throughout Power Platform Pillars such as Microsoft Power Automate and Microsoft Power Apps. They are also used in Azure services such as Azure Logic Apps. Connectors are a wrapper around first and third party APIs to provide a way for services to talk to each other. They represent the glue between services, allowing users to setup Connections to connect various accounts together. These connectors encompass a wide range of SaaS providers including Dynamics 365, Office 365, Dropbox, Salesforce and more.
This article will demonstrate how to build a custom connector for use with Power Automate and Power Apps Canvas Apps. This custom connector will attempt to build a connection to Azure Application Insights to assist Makers with sending messages during run time. We will discuss building and deploying an Azure Function and how to construct a Custom Connector. Finally we will discuss testing and supplying run time data from Power Automate.
Overview of Azure Function
Azure Functions provide a great way to build micro services including ones to help surface run time data from Power Automate Flow or Model Driven Application Plug-in tracing. Azure Functions can be written using .NET Core and included is native integration with Azure Application Insights. Alternatively, we can import the Azure Application Insights SDK to provide a streamlined approach to delivering messages. This article will focus on using the HTTP entry point and Azure Application Insights SDK to deliver messages to Application Insights.
Overview of Custom Connectors
Custom Connectors allow developers to supply custom actions and triggers that can be used by Microsoft Power Automate and Microsoft Power Apps. These connectors provide a reusable no or low code approach to integrating with an Application Programmable Interface otherwise known as an API. The complexity of interacting and implementing a connection and call to the API is hidden from makers, allowing focus on finding a solution to whatever business objective is at hand.
The custom connector can be thought of as a solution to a no cliffs approach to empowering makers. If a connector doesn’t exist for your particular need, for instance an in house API, a custom connector can be used to bridge the gap. No longer are we having to build and send HTTP requests or manage connection flows as the connector will fill the gap.
For additional information, including considerations for Solution Aware Custom Connectors allowing for migration between environments, refer to the article
Overview of the Open API Specification
built on the desire and need to standardize how we describe API endpoints. Built from Swagger, the Open API dictates how an API should be used. Everything from security to required fields are detailed allowing integrators to focus on developing and not chasing down API specs. One great feature is the ability to design a specification first without relying on code being written. This allows Makers to define what they are looking for with Custom Connectors.
This specification is used by Power Platform Custom Connectors to build out the various triggers and actions that will be made available. This will be covered in more detail in the Building a Custom Connector section.
For more information regarding Open API please .
Building the Azure Function
The section below will document the steps I took to create the Azure Application Insights Azure Function. There are several ways to build, frameworks to use, additional requirements to adhere to, etc that are not represented here. That said these steps should allow developers to create a proof of concept that can be used to learn and build from.
Creating the Visual Studio Project and Gathering Dependencies
To build the Azure Function, I started with Visual Studio 2019 and created a new Azure Function project.
AUTHOR NOTE: CLICK EACH IMAGE TO ENLARGE FOR DETAIL
I chose a name and left everything else as the default values. From there I chose the HTTP trigger and .NET Core Framework v2 version for my Azure Function.
Once loaded I add the latest NuGet package for Azure Application Insights.
The goal with this Azure Function project is to avoid any additional dependencies and simply deliver messages to Azure Application Insights and return an object that could help me track messages. An image of the code I used is below. For the full Azure Function code, please refer to within the that includes all samples from the Monitoring the Power Platform series.
In the sample, I’ve embedded the custom connector definition described below and image within the solution file.
Testing and Deploying to Azure
For testing I typically use . Its a free application that is an industry standard for testing APIs. Also as noted, its in the documentation for crafting a specification for a Power Platform Custom Connector.
Once you’ve tested and are ready to to deploy, right click on the Azure Function project and choose Publish. For my example I published using the Zip Deploy mechanism.
Building the Custom Connector
The Custom Connector can be built by hand or using an Open API definition. For my connector, I defined and tested my Azure Function and deployed using Visual Studio. Once running, I was able to use Azure API Management to assist with defining the specification for use with custom connectors. The documentation points to using Postman as a primary tool for the specification, however I wanted to mention other techniques to achieve the same goal. To follow a step by step guide using Azure API Management, refer to the article .
Icon, Description, Host and Base URL
The first section of the wizard will expect the endpoint from your Azure Function App. The specific operations will be defined later but for now, insert the Host and “/api” if part of your Azure Function URL. In my Azure Function this looked like “<functionapp>.azurewebsites.net“.
Next, the icon background color and image will need to be updated. I’ve noticed that the image will look skewed when used as an action but as a connection listed in a Power Automate Flow or Canvas App it looked ok.
Blue?…No thanks!
Security
The custom connector will need security defined which will be how we establish our connection, similar to other connectors. Many options exist, including OAuth 2.0 and Basic authentication, but for Azure Functions an API Key works well. Name the parameter “code” and set the location to “Query” as shown below.
The code will need to be standardized across functions or you may have to create multiple connections. A quick way to do this is to create a key for the host.
NOTE: Whatever this string is will be what is needed for the Custom Connector we will define below so I would suggest generating this yourself.
The Definition
The custom connector definition is where we begin to realize how our connector will be used within Power Automate flows. Each action will need to be defined within the “paths” section of the specification. Its important to point out here why the choice to use Azure Function helps here. By defining individual functions we can create actions that align directly with Azure Application Insights tables or to domain or workload specific actions. The example I’m using only shows that direct alignment but depending on the need an action can be invoked which from Azure Function can send multiple messages to various tables or even log stores (e.g. Azure Log Analytics).
Each action requires an operationId and response. Optional parameters include summary, description and parameters. Parameters define the fields in our custom connector action. Consider the following image, showing the Track Event action:
Now compare that to the definition of the specification:
Property Considerations
One item I ran into fairly early on was how to distinguish and work with json objects within other objects. Consider the scenario below:
{ "correlationid":"testcorrelation", "name": "test app insight", "properties": { "user":"John", "userTwo":"Jane" } }
My plan for this object was to take the properties shown above and add this to the customDimensions field within the customEvents table in Azure Application Insights. The issue I encountered was the data type of the field, thinking that I could use a serialized object as a string data type.
However the custom connector encoded this string resulting in a mismatch from what I was expecting in my Azure Function to what was actually delivered.
The expectation now was to reference the Open API or Swagger documentation to utilize the object or array data type to assist.
Testing the Custom Connector
The custom connector wizard includes a window for testing each operation. This is useful to see how the request properties are sent to the Azure Function from the custom connector and how the response looks. To begin, start by creating and testing a connection.
Next, choose an operation and fill out the properties defined in the Open API specification. When ready, click the Test operation button. A nice additional feature here is that is generates a cURL command that can be used locally.
The request and response will be shown from the operation. Here is where we can continually refine the custom connector specification to provide the correct data types to the Azure Function.
Using the Custom Connector
Once the Custom Connector has been created it can be used within Power Automate Flows or Power Apps Canvas Apps. I would assume this also applies to Azure Logic Apps. Below is an example using Track Event. In this example I’m including the correlationId that I passed from my originating request as well as the name property. I’m also using workflow, trigger and action objects detailed in the article .
To wrap, here is a gif showing an example run of the Application Insights Tester Power Automate Flow. The .
Next Steps
In this article we have discussed how to build and deploy an Azure Function to help deliver messages to Azure Application Insights. We then created a custom connector to allow Makers the ability to interact with our Azure Function like any other connector.
Continuing down this path we can use this approach for extending other logging APIs such as Azure Log Analytics. We can even extend the Common Data Service connector if needed. Be mindful however, of the nuances between the Open API specification and what Custom Connector requires.
In previous articles, . We have also discussed . Using the connector above, we can now send specific results from our scoped actions to Azure Application Insights allowing for proactive monitoring and action.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, .
Index
Monitoring the Power Platform: Power Automate – Run Time Part 3: Implementing a Monitoring Strategy
Summary
is a service allowing makers to create business processes, orchestrations and workflows to help achieve common and even complex business requirements. Within the Power Platform, Power Automate represents one of the most important pillars of the platform. It provides a no to low code solution to process automation. From sending , to complex flows, Power Automate can be used in virtually any workload.
The goal of this article is to describe how to implement a monitoring strategy for both Power Automate and the Logic App services. We will explore how to create custom identifiers to be used for tracking run time actions and flow history. We will discuss using the Azure Log Analytics Data Collector connector. The article will also show how to build a delivery mechanism to Azure Application Insights from Power Automate Flow. Finally, we wrap with considerations for using Azure Log Analytics and Azure Application Insights.
Adding Custom Identifiers
Power Automate Flows generate tracking identifiers for each individual flow and specific action within a flow run. For each workflow, triggers and action, these auto generated identifiers are included and available as part of the various objects . These typically are very helpful, specifically the workflow run name, when reviewing previous run information via the UI or PowerShell.
We’ve also seen, in , how connectors themselves can often provide identifiers in form of header properties that can be tracked. These returned properties from the connector response are vital when troubleshooting and supporting standard and especially custom connectors.
That said, there will come a time when you may need to generate or provide an identifier yourself. Luckily, Power Automate Flow provides the ability to do both, by passing in a tracking identifier or generating one which can be used across actions or even other flows as the calling flow. This allows makers to set the expectation that the flow will return a pre determined identifier which can then be used across other workloads.
Tracked Properties
Before we get into identifiers, please take a moment to review the Tracked Properties section in to familiarize yourself with attaching data to actions.
Utilizing a system provided Correlation Id
Power Automate includes its own correlation identifiers as referenced in the Get-FlowRun cmdlet as part of the ClientTrackingId. This identifier can be obtained in the flow itself by use of the outputs object. Consider using this or a custom tracking identifier when working with external integrations to assist with application and workload monitoring. By implementing this early, the beginnings of an application map can appear when working with Azure Application Insights as shown below.
AUTHOR NOTE: CLICK ON EACH IMAGE TO EXPAND FOR DETAIL
, we can generate a identifier based on various formats that can be used as the ClientTrackingId or passed to other connector actions such as Common Data Service creates or updates or even custom connectors as detailed below.
Adding a custom Tracking Id
The Custom Tracking Id, part of the trigger, allows for the static of dynamic input of an identifier that can be passed in or set from the trigger and added to the response. This identifier can also be used across child flows to represent the calling or parent flow.
As the image below shows, we can use the “Custom Tracking Id” field to set a static or dynamic value. This example shows setting the custom tracking id from a property in the body of a HTTP request called correlatonid.
An added bonus to using the trigger in this fashion is that we now can add conditions that work as a gate keeper to our flow. In this scenario we can strictly enforce that if a correlating identifier is not provided to the flow, the flow will not run and return a message expecting the identifier. This design is very important to ensure visibility and supportability, and as such needs to be included as a first step, as we include more and more integrations into our workloads.
In the example below we have a HTTP request and response Power Automate flow. In the request a Tracking Id “PowerAutomateArticleExampleId” has been provided.
In the response, captured in Fiddler or Postman, we can see the response header now includes our custom tracking Id represented by “x-ms-client-tracking-id”.
This technique can be propagated down to as well as shown below. As with the parent, the “x-ms-client-tracking-id” is now included within the Child Flow. This allows the Child Flow to use the same tracking Id for any actions or tracked property.
Finally, an added benefit is that this same custom tracking identifier will be available as part of the “Get-FlowRun” cmdlet covered in .
The Log Analytics Connector
One approach that can help deliver event data to Azure Monitor is to utilize the connector. Utilizing the Azure Monitor HTTP Data Collector API, we can ingest data in virtually any structure using a JSON formatted string. Since every object in our Power Automate Flow is formatted in JSON we can send any item we want, from scopes to actions to the entire workflow.
Creating Custom Tables
Log Analytics Data Collector connection action.
Here are the actions that created these tables starting with the PowerAutomateFlow custom table.
Here is the action for PowerAutomateActions:
As shown, one of the benefits of Log Analytics is the ability to add custom tables to the workspace. When these custom tables are created, they can be added to by passing in additional properties. For instance, in the image I have a table for Power Automate Actions that was created by passing an action object in. Starting with a simple initialize variable I may only have a couple of fields created in my new table. However by passing in a more robust output from an action, like the Common Data Service, I begin to grow my table exponentially.
Here is an example gif showing using the workflow and action object. The workflow object is well defined however actions can become cumbersome. In this example we are splitting these into two separate tables.
NOTE: Consider using custom tables for specific actions.
This approach provides extreme flexibility but does come with a cost. Data can quickly become fragmented, null saturation can become unmanageable and even fields that we would often use for identifying can become obscure. We quickly can render our logs useless working this way and should provide some limits to what can be provided. Without meaningful data, what value are the logs we’re capturing?
Thoughts on Controlling the Collector
I like the approach shown due to the flexibility of the endpoint and the native integration it has with Azure Logic Apps. Creating custom tables and allowing data ingress in any shape or form does have benefit. Also, by using the Log Analytics Connector, all Azure Logic App and Power Automate Flow run time data (tracked properties, action results, etc) can be centralized. This allows for ease of use reporting and monitoring. That said, we need to build in enforceable and stringent rules to what our tables will accept to ensure quality as we move ahead.
The Case for Application Insights
Azure Application Insights, as mentioned in previous articles, is an application performance monitoring platform. Power Automate, at the time of this writing, does not have a native integration or feature to push events to Application Insights. However, as with Log Analytics, there is an HTTP endpoint that can be used to send messages to Application Insights. How that HTTP endpoint is invoked is up to the enterprise. HTTP requests can be issued directly from Power Automate Flows. .
The next section will go into building a direct request to Azure Application Insights. .
Utilizing the Application Insights REST API directly
To achieve this we will send a POST message to the Application Insights endpoint with the definition of the specific event we want to capture. This endpoint can accept one or multiple messages so it can be used at the end of a Power Automate flow run or really anywhere. Combining the messages and sending at the end would follow a similar approach as with the Azure Log Analytics Data Collector action. As long as the action is ensured to run and we have our contracts correctly defined, we can rely on the API to deliver our messages. One of the benefits of the Application Insights endpoint is it does allow any well defined message payload and will report back the number of accepted and failed messages.
The trigger I’m using here is which allow for use of this flow as a child flow. Remember, to use this flow in this manner utilizing Child Flows, the flow must be solution aware.
Building the Message Body
Azure Application Insights, like Azure Log Analytics, can accept JSON serialized requests and store within tables within the log store. Unlike Log Analytics, specifically the Data Collector, is that Application Insights expects a well formed structured message. Each message contains these minimal properties: time (represents timestamp), iKey (Instrumentation Key), name (the name of the table/message) and data (the message payload).
The data property is specific to each message type and will include a baseType and baseData property within. A good reference for finding out more can be found on the For each message type, be it exceptions, custom events, traces, etc., there are common and explicit properties. Consider the below image.
The image above shows how to take the contract for a specific table and inject variables which will show up in Application Insights. In this example I’m only using the customEvents table but this can easily be extended for all tables within Application Insights.
Most tables in Application Insights include a customDimensions and customMeasurements property bag which I would recommend passing contextual data from the flow. In the example above, the properties property within baseData allow for a json object to be passed in. The image above shows the trigger function but this could also be the workflow object, or maybe a particular action that failed. The below image shows using the action object output with the Exception message in Azure Application Insights.
All tables include user and session data points which can be used to help facilitate out of the box capabilities in Application Insights such as the application map mentioned above.
Utilizing Azure Functions and Custom Connectors
Azure Functions provide a great way to build micro services including ones to help surface run time data from Power Automate Flow or Model Driven Application Plug-in tracing. Azure Functions can be written using .NET Core and included is native integration with Azure Application Insights. Alternatively, we can import the Azure Application Insights SDK to provide a streamlined approach to delivering messages. The article covers building both an Azure Function and Custom Connector to realize this approach.
Choosing between Log Analytics and Application Insights
The information above has provided mechanisms showing how to deliver events to Azure Monitor utilizing the Azure Log Analytics Data Collector or the Azure Application Insights REST API. Choosing one of the other requires considerations into the features and benefits and maybe more importantly the limitations of both. For an overview of each please review the article.
As we progress through the Monitoring the Power Platform series, we will dive deeper into these considerations and why to choose one over the other based on requirements. Ultimately the log store for Azure Application Insights is available in Azure Log Analytics but the features provided by Azure Application Insights allow for deep “insights” into scenarios we are interested in.
Next Steps
In this article we have discussed using custom identifiers to help track flow history and specifically group parent and child flows together. We have examined how we can use trigger conditions to enforce the use of custom identifiers. From there we reviewed the Azure Log Analytics Data Collector connector and how to setup and send messages to custom tables. After that, we went into building and sending Azure Application Insights messages directly in Power Automate flow.
In previous articles, . In the following articles covering Microsoft Power Automate Flow run time, we will discuss pushing events to Application Insights and reviewing previous flow runs for monitoring and governance.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, .
Index
Improve the Usability of Dynamics 365 Sales for Professional Services Firms with Power BI
If you’re already using the dashboards provided by Microsoft Dynamics 365 Sales, you may not see the need to use Microsoft Power BI as well. However, it can be an important tool to help professional services firms obtain better visibility on all the information they need, even data outside the CRM.
Dynamics 365 Sales dashboards only report on data found within your customer relationship management solution, but if you also want to consult information from outside the CRM, does it make sense to integrate all this data? Maybe not. That’s where embedded Power BI dashboards come in: Power BI lets you cross-reference information from virtually any database, letting you consult this information without having to bring it into the CRM first.
If you’re currently using your CRM for sales information and have another system to manage operations, you may also want to bring together the data stored in both systems For professional services firms, this could be what is coming up in the pipeline versus booked service hours, for example. The pipeline hours might come from the CRM and the booked hours from your , but your sales team needs visibility on both to know what your bandwidth will look like over the next few months. While bringing operational information into the CRM may not make sense for your organization, the business development team may still need access to this information to make informed business decisions.
With embedded Power BI dashboards, Dynamics 365 Sales remains your main point of entry, allowing your users to access and find all the information they need from a centralized platform. Having all necessary details gathered in one place keeps things simple and user-friendly, increasing the likelihood that users embrace the change and adopt your new solution.
Visit our website to learn more about or , or so we can learn more about your organization and help you meet your specific needs.
About JOVACO Solutions
JOVACO Solutions is a leading ERP and CRM solution provider operating in Quebec for over 35 years. As a specialist of Microsoft Dynamics business management solutions, we offer a wide range of products and services to meet all the needs of professional services firms and project-based organizations. We also offer specialized project management tools and timesheet add-ons fully integrated to Microsoft Dynamics solutions. Visit or for more information.
Power BI and Paginated Reports: Better Together
Wise Professionals See the Big Picture
Bringing it Home
Today we’ll be concluding our 3-part series on Analysis versus Reporting, Power BI versus SSRS and Paginated Reports, and looking closely at our habit of using the word “versus” so liberally:
-
Part One summarized the history of the industry, its long-stagnant roots in Reporting, and the recent “triumph” of Analysis.
-
Part Two examined the relative strengths of Power BI reports and Paginated (SSRS) Reports, and why you need both.
-
Today, we’ll explore how you can use both Power BI and Paginated Reports together, as part of a wholistic system.
What Does “Triumph” Mean?
I’d like to start by clarifying what I mean when I say Analysis has “won.” Even today, traditional reporting is STILL the majority of the “biomass” in the BI world. For example, right now around the world, there are probably three people using a Traditional report for every one person using an Analytical one.
So Analysis isn’t dominating usage today, but it IS dominating mindshare. No one is BUYING platforms which are 100% traditional reporting – not anymore. And in fact, lots of people ARE buying pure Analysis platforms like Tableau. Of course, what they SHOULD be doing is buying platforms capable of BOTH, which is kinda the focus of today’s article.
Analysis Will Keep Stealing Usage Share for Awhile
So we have a ways to go before Analysis equals or eclipses Reporting in usage, but the wind has most definitely shifted. Keep in mind that a LARGE percentage of “usage” of Reporting is not “looking at it with our eyeballs” usage, but rather EXPORT usage, which powers ANALYTICAL work in Excel. Below the radar of IT and invisible, and crude compared to the Analysis you can perform in Power BI, but Analysis nonetheless.
Until the world stops using Reporting as a crude form of database access (to power its Excel analysis), the fight is not over. And we have a LONG ways to go to get there. Even today, a client told me a story about a team member who last week printed out a 30-page report and was planning to go line by line through it, re-keying a subset of the data into Excel (manually!) to get a total of how many units of Product X they’d sold in the prior month. Said individual was thrilled to be shown the Power BI report where a single click provided the answer. So we still have some work to do, awareness-wise.
Where Will We Land?
At most, I expect the inverse of today: three Analysis reports for every Traditional report. If we slowly convert the “Traditional reports which pretend to be informative but are really just export sources” into Analytical (Power BI) reports, that should get us there – and really, that’s ALL that we SHOULD do.
And if you read Part Two, you know there are certain kinds of reports that Power BI is “bad” at. We still need SOMETHING for those, and I expect they will still constitute, say, 25% of all reporting usage once we reach equilibrium.
Switching from “Or” to “And”
Part Two covered when to use X versus Y, but even a firm grasp of that leaves something important on the table: what are the ways you can use BOTH in an integrated manner, thus getting a result which is more than the sum of its parts? Ah yes, the “Better Together” cliché! But there is substance here.
Microsoft should be leaning HARD into this aspect of their platform – that it has STRONG products in both worlds, because so few of their competitors have anything to offer in the Traditional space, even as said competitors are already behind in the Analysis space. The combination of the two – when well-integrated, brings at least three very interesting classes of benefits:
-
Cross-report drillthrough in either direction.
-
Both kinds of reports in a single portal.
-
Shared analytical model powering both flavors of reports.
Let’s take these one at a time.
Bi-Directional Drillthrough
This is the most fun, so I’ll cover it first. Consider the following wireframe of a Power BI report:
We’re Going Wireframe Today – No Time for Distracting Myself with Hyper-realistic Demo Building 
I hope it’s easy to imagine the interactive features of the above report. Click on a bar in one of the charts, for instance, and watch the rest of the report filter down to that Category or Customer, etc.
Imagine seeing Cust2 and thinking “hey, why is Customer 2 so low this month? Aren’t they normally buying a lot more from us? Let’s see what they actually bought – show me the invoices!” Well, here ya go…
Select Customer 2’s Bar, Click the Icon/Link
And then you get navigated to a Paginated Report to view the actual Invoices which were sent to Customer 2, because the filter selection you made in the Power BI report is “transmitted” as part of the URL:
Paginated Report Displays the Authentic Invoices
(The system “remembers” which customer you selected in Power BI – by passing it in the URL)
“Wait, Couldn’t I Build That Invoice Viewer in Power BI?”
Yeah, smart aleck, you COULD build precisely THAT one pictured above. But a couple things. One, a more realistic invoice might span multiple pages, with page breaks and intermediate subtotals, and Power BI isn’t good at that paginated stuff. Two, if you want to be absolutely sure you are looking at PRECISELY THE INVOICE which was sent, you would want to go to the source which generates and prints said invoices yes? And since said invoices often need to be pixel perfect, the original source again cannot be Power BI.
“Wait, How Do You Pass ‘Cust2’ in the URL?”
This isn’t a technical how-to article, and I’m trying to stay above the weeds, but here’s a quick series of screenshots which should help:
Format Shape –> Action –> Web URL –> fx –> Choose a Measure
Example of a Measure Which Constructs a URL to a Paginated Report
(Comes from a slightly more complex example than our wireframe demo)
And Now the Reverse Direction!
OK, so you’re looking at the invoice, and you spot the change. “They only bought ONE Flux Capacitor this month! Don’t they normally buy more??”
Aha! Well NOW you’re back to an Analysis kind of task! You COULD thumb backwards through invoices using the “Prev Invoice” link, but if the invoices are multi-page, that’s gonna be tedious, AND it’s hard to spot trends that way. Power BI is REALLY good at this kind of thing.
If only there were a way to seamlessly navigate to a Power BI report, pre-filtered to answering my question…
Ooh! It’s a Link!
Navigated back from Paginated to Power BI, With Product Category=”Flux Capacitor” and Customer=”Cust2”
(A different Power BI report than the one we started on, but of course depending on actual needs, it COULD have been the original)
“OK, Spill It – How’d You Do the URL THIS Time?”
Shockingly similar on the Paginated side (in Report Builder) to the Power BI side, actually…
In Report Builder, Select Text Box Properties
“Go to URL”
Build Yourself an Expression
(Again though I’m taking a pass on the weedy details today – trying to keep it as concise as we can around here)
Pretty Neat Huh?
Let’s see that whole flow one more time, in a more concise manner:
Glorious: You Bounce Back and Forth Across “the Line of Versus” with Agility
***BONUS THOUGHT: As cool as that is, I wonder if even TIGHTER integration makes sense in some cases. The custom visual framework in Power BI, in theory, could support an embedded Paginated Report control right? Would there be use cases for that? Or would the clarity of being in a completely separate report always be preferable? I’d bet that it would at least be interesting in SOME cases, but I need to think about it more. Leave a comment if you have an opinion, please!
Benefit Two: Single Portal
Not as sexy as cross-technology drillthrough, but a real benefit for everyone’s sanity as well as usage: both kinds of reports can be published to a single Workspace:
OMG, BOTH TYPES of Reports in the Same Workspace? THAT’S PETER VENKMAN’S MUSIC!
Back off, man. I’m a data scientist.
Don’t sleep on the little details like this. They make a big difference.
Benefit Three: Powered by the Same Analytical Model
SSAS Tabular (Power BI’s analytical engine) brings MANY advancements relative to its predecessor (SSAS MD), and one of them is the ability to return hyper-detailed rowsets without the performance penalty we suffered in the old days. If you want the data to populate an Invoice, you can fetch what you need with a DAX query and not sit there waiting forever for it to return. In fact, things like the Table visual already do this within Power BI itself.
So if you’ve invested in a robust Power BI analytical model to power your analysis, you might find yourself reaping unexpected Paginated Reporting benefits. If you don’t NEED to go build new, traditional SQL-powered plumbing to power a more traditional report, great! By all means, connect it to your Power BI model (aka Dataset), and off you go!
Your Data Model Can Power BOTH Kinds of Reports
And folks, that wraps up our series! What did you think? Did I leave anything out? Wait, what’s that you say? There IS something I missed? Lemme see…
“Hey Rob, How About a Good Microsoft Rant?”
How about more than one? I mean, I’m so glad you asked, cuz I’ve got two…
Integration Shouldn’t Cost So Much
First, why is it so expensive to get an integrated environment? Best I can tell, you can’t get a Power BI + Paginated integrated system for anything close to the price of getting them separately. 100 users of Power BI Pro would cost $ 1k a month, and an SSRS On-Prem server is a one-time payment of, say, $ 6500. So to run those two – separately – for two years, it would cost you about $ 30k all-in.
But to get them integrated, today you’d have to plunk down $ 5k a month for Premium, and that pushes your 2-year price to $ 120k – 4x having the same capabilities separately. Now, I’m pretty sure I can get benefits 1 and 3 (drillthrough and powering both from a shared data model) at the $ 30k two-year price, so what the extra $ 90k gets me is… benefit two? Side-by-side publishing of both report types in a single Workspace? Steep price jump for just that.
On the on-prem front, you can go Power BI Report Server, but that’s also a premium price point. Enterprise Edition plus Software Assurance is required, apparently, and that’s 100% an Enterprise SKU – well out of the reach of SMB’s, and just as importantly… out of Departmental reach within the Enterprise itself.
In fairness to Microsoft, the pricing for Enterprise customers DOES need to drive their thinking in large part. And that comes with benefits! Most of Microsoft’s data stack is actually quite underpriced for SMB’s and Departments, and we never complain about THAT, do we? At P3 for instance we can run all of our MS data stuff for a FRACTION of what we pay for Salesforce (which we deeply regret getting hooked into, but that’s a story for another day isn’t it)? There’s a lot more value in the MS platform than Salesforce, and yet the prices are reversed.
So this may be a sensible pricing strategy for the Enterprise, but this is one of those cases where it prohibits SMB’s (and again, the Departmental level of the Enterprise, which is crucial for “land and expand”) from playing the game properly.
It’s a massive strength for the MS platform versus the competition, and it seems a shame that Departmental/SMB is accidentally priced out of the most elegant version today.
OMG Naming!
Authentic Action Photo of Microsoft Naming Their Software
Oh, “Paginated Reports,” how I LOATHE thee as a name! This name caused me SO MUCH TROUBLE for a very long time. “How does this relate to SSRS?” is the question which kept plaguing me. Is it a new thing? If someone knows SSRS really well, will this be a foreign experience – a brand new technology – or super familiar? Is it not capable of pixel perfect document generation, and instead ONLY leans into the truly Paginated benefits of SSRS? Ugh.
Here’s what it REALLY is: it’s SSRS technology living within the Power BI environment. You still get Report Builder, and the whole thing still uses RDL. If you knew SSRS, Paginated Reports are for you. Which is good news – SSRS was, and is, a market leader in its space. Super robust and capable. Refined by TWO DECADES of development and experience.
Now, again, I kinda DO get why they’re doing this. Power BI is “the brand” now, and everything needs to line up under that banner. Years from now, this naming scheme will be a good one. “Power BI just has two kinds of reports, great!” But I think they underestimate the utter carnage this wreaks in the meantime, and how many times we’re going to have to explain to people that “it’s just SSRS in Power BI.”
Remember, I’m the guy who tried to rename PivotTables when I worked on Excel at Microsoft. So I’m definitely cut from the same cloth originally – I’m just trying to heal, and to atone for my past sins. But “yeah,” said Rob circa 2004, “PivotTable is such a wacky and scary name which has nothing to do with its primary function, which is to summarize/aggregate data.” I wanted to rename it SummaryTable. Just like I explained above, if you fast forward ten years, that’s a better name. Zero question. But is it worth the transition cost? In the end, wiser heads talked me out of it.
While we’re at it… can we rename Power Pivot and Power Query to be… Power BI in Excel? Because just like we’re explaining Paginated Reports = SSRS in Power BI, we have to explain the Excel relationship over and over and over…
Nah, too sensible.
Monitoring the Power Platform: Power Automate – Run Time Part 1: Triggers, Workflows and Actions
Summary
is a service allowing makers to create business processes, orchestrations and workflows to help achieve common and even complex business requirements. Within the Power Platform, Power Automate represents one of the most important pillars of the platform. It provides a no to low code solution to process automation. From sending , to complex flows, Power Automate can be used in virtually any workload.
The goal of this article is to describe how to implement a monitoring strategy for both Microsoft Power Automate and the Azure Logic App services. Every Microsoft Power Automate Flow or Azure Logic App contain functions and objects which can help designers capture and troubleshoot run time information. Within this article we will explore triggers and how they can be configured to reduce runs with conditions. From there we will go into the various workflow and run time objects that help deliver meaningful run time insights into our flows. Finally we will wrap with actions and their outputs, which can be used as determining factors for conditions or for monitoring integration responses.
Triggers
Triggers are used to fire off a Microsoft Power Automate flow. They can be triggers that are invoked, triggers that reoccur and triggers that poll for a specific event. For more information regarding different types of triggers such as poll and push triggers, please refer to the ““ article.
Trigger settings consist of custom tracking identifiers, secure input and outputs, concurrency settings, schema validation and trigger conditions.
NOTE: CLICK ON AN IMAGE TO ENLARGE TO SHOW DETAIL
Secure Inputs and Outputs
Secure inputs and outputs can be used to hide input and output data on triggers and actions. Keep in mind , when using secure inputs and outputs. I’ll cover this more in the following article for reviewing run time data.
Concurrency and Parallelism
The concurrency control is a feature enabling designers to handle queued runs in the Power Automate pipeline. As described in the feature description, this can’t be undone once enabled. The , while out of scope of this document, are well documented. For may come in handy when dealing with concurrency in loops.
Schema Validation
Schema Validation is a useful feature when dealing with triggers that handle custom inputs, such as HTTP requests. Within these requests can reside any payload with different types of serialization. To help enforce and ensure delivery of meaningful data to downstream actions, schema validation can be used.
Typically, when working with HTTP requests in Power Automate, a sample JSON payload can be provided to help describe what data needs to be provided and what format. Within this description, designers can provide a . does a great job showcasing how this feature can be used.
Schema Validation, coupled with trigger conditions, provide designers robust techniques to ensure that a Power Automate flow is triggered only when needed data is available.
Trigger Conditions
Trigger conditions are used to enforce conditional policies to ensure Power Automate flows will fire only once all conditions have been met. They can consist of trigger body or headers properties that can be used in conjunction with expressions and functions in flow.
The following image shows a trigger condition that will keep the flow from firing unless the correlationid and body fields are present in the triggerBody object.
Trigger Object
Triggers can contain inputs, outputs, timings, keywords and statuses. Depending on the type of trigger you may or may not receive specific properties. Keep this in mind when attempting to read from the trigger object as some, such as recurrence, will not contain inputs and outputs.
The below image is the trigger for a Recurrence:
Here’s an example of the trigger object for Recurrence:
The below image is the trigger information for the Common Data Service, which unlike the Recurrence contains the Retry Policy feature:
Notice the host contains the specific operation id, but more important is the dataset parameter which shows which environment the trigger is listening to. In this case this represents the “(current)” environment. Additionally the correlation and client request identifiers (x-ms-correlation-request-id and x-ms-client-request-id) are shown which can help track and troubleshoot flows.
Workflow
The Power Automate run time information can be found in the workflow method which contains information about the flow, the environment, and the specific run. This object can be stored as a variable in the Power Automate flow and even can be passed as a response from the flow itself. The below image shows how the output when setting the “workflow()” function as an object.
The Workflow Time Stamps and Tracking Identifiers
Within the root of the workflow object lies properties that can help administrators track the specific workflow run. The , a tracking Id, start time and end time are displayed here. Looking into the time stamps I don’t see them lining up with the data provided for solution aware flows using the Get-FlowRun cmdlet, but more to the trigger timing. That said, Tracked Properties can help for stand alone action and/or flow duration tracking.
The Workflow Run Object
To find out more about the specific run, you can cast the workflow().run object as a variable and view the properties. Run properties include the id, the name, and the type. This information, specifically the “name” property, can be correlated to the ClientTrackingId found in the Get-FlowRun cmdlet which we cover in the next article.
For Azure Logic Apps, please refer to .
The Workflow Tags Object
Additionally, the flow is further described in the tags object. The tags object contains the display name (flowDisplayName), the environment it belongs to (environmentName), if its part of the common data service (solution aware) (sharingType) and the logic app name (logicAppName).
The tags object is extremely important for Power Automate Flows as it stores the information needed to find a specific flow in an environment. Using this tags object, coupled with the run object properties, will allow for deeper insights and analysis.
Capturing Connection and Action Responses
One of the greatest capabilities of Microsoft Power Automate is the ability to connect systems in a no code approach allowing makers from all sorts of background to develop integrations. Most of these integration connections perform some sort of action to gather and manipulate data, invoke a process, and return some value. Each of these actions includes some output which can be read and stored for further examination natively within a Power Automate flow.
The Action and Actions Functions
To begin working with Actions responses within other actions, . The Actions functions (both and ) can be used but work a bit different depending on how they are to be used.
The current action output at runtime can be read from using the action function. The function is limited to specific action types such as unsubscribe or do-until actions. They can also be added to Tracked Properties which are discussed in a following article.
To read information from previously run actions within a Power Automate flow, use . When using the actions function, the entire action object is presented. Included are shorthand functions , and .
Action Outputs
The output for a particular connector action can be read by using the expression outputs(‘<name of the action>@outputs(‘Update_a_record’)“.
You can see the top action is called “Update a record” and is a Common Data Service connector action.
The image above shows a comparison of the Common Data Service Trigger against the Action. Notice how the action includes the status code as opposed to the status of succeeded that the trigger produced. This status code along with the header and body is essential to finding out the outcome of our integration.
Output Header
The output, if using a connector, will include the header properties from the response of the connector. Inside here we can find information pertaining to accepted and expected formats. Session and cookie information including correlating identifiers such as “REQ_ID” or “x-ms-service-request-id“, can also be found here to troubleshoot. For Common Data Service calls, the aforementioned identifiers and API throttle headers can be used to help shed light on potential issues.
Output Body
The output body contains the response data from the connector request. Depending on what connector and action used, we’ll see various values. Typically, when designing Power Automate Flows, values from the output body are what are used downstream in other conditions and actions. For the Common Data Service, this could be list of records, responses from custom actions, or possibly empty.
For outputs, just like inputs, keep in mind the considerations when using the secure inputs and outputs feature.
Next Steps
In this article we have covered the beginnings of controlling and monitoring Power Automate Flow runs. Configuring triggers using Schema Validation and Conditions to reduce noisy flows were examined. Using functions such as workflow and actions to expose run time information was also discussed. This information, exposed from these functions and objects, will prove useful as we continue down the following articles for monitoring run time information in Microsoft Power Automate.
In the following articles covering Microsoft Power Automate Flow run time, we will explore tracked properties, secure input and output and how they can be used to extract and pass data between actions. We will also look at techniques for error handling. Finally we will discuss pushing events to Application Insights and reviewing previous flow runs for monitoring and governance.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, .
Index
Monitoring the Power Platform: Power Automate – Run Time Part 2: Tracked Properties and Error Handling
Summary
is a service allowing makers to create business processes, orchestrations and workflows to help achieve common and even complex business requirements. Within the Power Platform, Power Automate represents one of the most important pillars of the platform. It provides a no to low code solution to process automation. From sending , to complex flows, Power Automate can be used in virtually any workload.
If you have not already reviewed Part 1 in the Power Automate Run Time series, please do so as this article is a continuation of previous installments. In this article we will discuss Tracked Properties and how they can be used across actions with a Microsoft Power Automate Flow. We will then look into handling and capturing error information within actions to help troubleshoot and report on failing actions or flows. Finally we will examine the Try, Catch, Finally pattern and result function for monitoring insights.
Action Tracked Properties
Tracked Properties are part of most actions within Power Automate. With tracked properties, we can set variables such as time stamps, capture trigger headers and outputs, action outputs, or even previous tracked properties from earlier actions. Expressions, such as “utcNow, outputs, actions” can be used to track what normally wouldn’t be stored in action inputs.
Action Headers Example
Consider the image below which shows capturing the status code and API throttle information for a List Records action in the Common Data Service (current environment) connector.
The example image below, where we are performing a ‘List records’ with the Common Data Service connector, now has a more friendly way of tracking the response code and API throttling response. This is a light weight example but should drive home the concept of how, using Tracked Properties, I can now reference the “TrackedPropertiesExample” variable which now includes outputs from a previous action.
NOTE: A key point here is that the action function will work for tracking current action data but not previous actions with the actions function. Attempting to do so will result in the “Tracked properties can only reference its own action’s inputs and outputs, trigger inputs and outputs and parameters.” error.
Timestamp Example
Using “utcNow()” as a tracked property for an action creates an interesting result. At first impression, we would be led to believe this may represent the start time of an action. However, when reviewing the output this function actually produces the end time exactly as if we were to reference it from the output of the action. Consider the below gif:
You can see that the five second delay is showing the tracked property “FiveSecondDelay” as the same timestamp as the endTime from the output!
Finally, as we look to Azure Logic Apps and what may come with Microsoft Power Automate, we can see how tracked properties work with monitoring solutions. Azure Logic Apps can to log logic app runs. One way to extend the information stored in the log store is to .
Handling and Capturing Errors in Actions
Typically Microsoft Power Automate flows will fail out if an issue occurs. Manually we can review these failures in the Power Automate Portal or with PowerShell. However inside the Power Automate flow we have the ability to track the statusCode within the actions function. Along with the Configure Run After setting we can conditionally capture any error messages and report or store for analysis.
Configure Run After
The Configure Run After setting allows the flow to continue if an error or timeout occurs. This can be used in conjunction with the below image showing a conditional based on status code of a previous HTTP request output body:
does a great job detailing the “run after” behavior. Each action is marked with a status of “Succeeded“, “Failed“, “Skipped” or “TimedOut“. The below image, from the Azure Logic App documentation, details how the “run after” behavior works in parallel branches.
Retry Policies
Retry architectural patterns are nothing new in our continually evolving, interconnected world of dispersed systems. In , he goes into great detail discussing various patterns. One such example to handle errors when connecting and processing integration requests and responses involving retry is the . (pgs 206 – 212).
In the case of Microsoft Power Automate and Azure Logic Apps, we have a feature called which can be beneficial when working with integrations. Based on the status code returned (408, 429, 5xx, ect), the retry action can be configured to trigger a retry based on different intervals (Default, Exponential, Fixed).
Mock Responses
which can be helpful to troubleshoot without invoking a live system or for designing error handling.
These simulated responses can be used, similar to mocks, when developing custom APIs and are quite useful when building custom connectors. The response can be successful, error prone, time out prone, etc based on the scenario. A specific status code and response can be added to help troubleshoot these specific scenarios.
The image below shows setting up a failed mock response and the many different error codes.
NOTE: Depending on what connector you’re using, you may not be able to use certain error codes.
The below image shows mocking a Common Data Service error response:
This image shows mocking a Common Data Service error response:
Finally, the below image shows the run history and capturing the error response from the Common Data Service:
Throttling with Azure Logic Apps
Throttling in Azure Logic Apps has many facets and can stem from all sorts of potential areas. Connectors can be throttled, destination APIs can be throttled and depending if you’re running in an integration service environment (ISE) or not can impact that. when deciding an implementation approach with Azure Logic Apps. Scenarios such as dealing with Azure Service Bus queues are thoroughly examined (e.g. using nested logic apps…this technique will work for Microsoft Power Automate as well!)
Webhook Tester Site and runtime debugging
The below reference and steps are taken from the . This technique of using the Webhook Tester site for debugging run time integrations is very useful. An added benefit is that the Webhook Tester site allows for both cloud and local based (e.g. Docker containers, Virtual Machines, etc).
To help with debugging, you can add diagnostic steps to a logic app workflow, along with reviewing the trigger and runs history. For example, you can add steps that use the service so that you can inspect HTTP requests and determine their exact size, shape, and format.
-
Go to the site and copy the generated unique URL.
-
In your logic app, add an HTTP POST action plus the body content that you want to test, for example, an expression or another step output.
-
Paste your URL from Webhook Tester into the HTTP POST action.
-
To review how a request is formed when generated from the Logic Apps engine, run the logic app, and revisit the Webhook Tester site for more details.
For more information,
NOTE: This tool is useful for testing outside of Power Automate including any resource that has HTTP Post capabilities, examples including Azure Functions and GitHub Web hooks!
Scopes and the Try, Catch, Finally Pattern
, actions can be in a logical group allowing for evaluation of multiple actions as a whole. This comes into play when working with exception and error handling. Scopes provide statuses similar to Power Automate flows or Logic Apps themselves such as “Succeeded“, “Failed“, “Cancelled“, etc. Pieter Veenstra has a really nice approach, “” for using scopes to implement a Try, Catch, Finally pattern which i’ll discuss below.
In the image below are three scope objects titled “Try“, “Catch” and “Finally“.
From a recent flow run, expanding the Try block, we can see two successful actions followed by our mocked failed response from the Common Data Service:
The Catch scope underneath the Try scope is using the run after property of the Try scope to catch results with a status of “Failed”. If any actions inside the Try scope, at least how this example is configured, fails, the Catch scope will begin processing. The remaining actions within the Try scope are not processed.
Below is an image of the Catch scope with Tracked Properties:
To work with the Try scope within the Catch scope, we will use the results function which provides context to what happened within the Try scope.
Result Function
The function, as shown in the reference link, returns an array object. At first glance this seems fairly straight forward until we dig deeper into the . The actions returned with the results function such as start and end time and tracking identifiers. You’ll also see that they include properties indicating if they were successful or not.
The image below shows a successful action named “successful_try_action” and a failed action named “Condition”. Each action includes two properties, code and message that can be used . The successful action is in green while the failed action is in red.
Finally, the below gif shows working with the Try, Catch, Finally pattern and scope and results objects to capture and report. This technique can be continued to then send data to Azure Application Insights or Azure Log Analytics as briefly shown below. I’ll cover this in an upcoming article!
Next Steps
In this article we have discussed tracking properties across actions to help supplement reporting. When examining error handling and preparing for designing resilient Microsoft Power Automate flows we examined various patterns. Utilizing retry policies, configuring the run after conditions and implementing scope can all help build an enterprise ready flow.
In previous articles, . In the following articles covering Microsoft Power Automate Flow run time, we will discuss pushing events to Application Insights and reviewing previous flow runs for monitoring and governance.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable.
Index
Paginated Reporting and Power BI: Why You Probably Need Both
Recap of Part One
If you missed Tuesday’s history lesson AKA Part One, don’t worry, here’s the summary:
- For a long time, Reporting was the dominant force in BI, with Interactive Analysis a distant second
- This wasn’t because Reporting was better – it was just because Reporting was easier
- Reporting only provided the business with a pinhole-sized field of view
- This led to a proliferation of reports AND an overwhelming dependence on Excel
- Tableau was a breakthrough of Interactivity for the business, expanding the field of view considerably – but still insufficient
- Power BI marries Interactivity with an underlying Analytical Model, providing the most comprehensive field of view
This is a Good Thing, But…
Yes this represents tremendous progress! The purpose of BI is too see what is going on, and then use that vision to drive improvement. Pinhole-sized fields of view (Reporting) don’t cut it, and neither do spotlight-sized fields of view (Tableau). You need to see as much as possible. “See EVERYTHING” is a difficult goal to attain, but it IS the only goal which makes sense.
But let’s revisit the other diagram from Tuesday, because there’s something significant to be gleaned:
Interactive Dashboard Tools Aren’t Good at Everything
Interactivity Tradeoff #1 – Not Pixel Perfect
Technologies always come with tradeoffs, and an interactive canvas is ultimately a place where you arrange a bunch of pre-built objects. In Power BI, the objects in question are mostly called “Visuals,” like charts and slicers, but we also have Textboxes, Images, Buttons, and Shapes.
Pre-built objects are GREAT building blocks for building interactive dashboards! I don’t want to design a chart from scratch. I just want to drag it onto the canvas and then tweak the formatting to suit my needs. And then I want it to offer the user things like filtering controls.
But pre-built means “less than 100% flexible.” The things a graphic designer can come up with frequently will NOT quite be doable in a drag-and-drop canvas environment – but ARE feasible in a pure HTML environment. Power BI has done an amazing job making their visuals flexible, and you can get some incredibly-polished results, but they cannot possible think of, nor implement, every possibility. “As flexible as HTML” will never be attainable, not even close.
Here’s something that’s not the BEST example of Pixel Perfect, because it’s simple enough (design-wise) that Power BI could get you probably 95% there, but it’s a handy example and not client-sensitive. It’s the email report I got from the very last time I went to the gym before COVID scared me off:
Yeah, you could get CLOSE to this in Power BI, but there would be a few things “off.”
(Not to mention the aspect ratio – this is several screens tall)
Plus, Interactivity ITSELF necessarily introduces some “clutter” in the form of on-hover controls and effects. We need these for interaction purposes, but in an airtight, graphic-designer-driven environment, that sort of clutter can spoil an otherwise perfect design.
Three On-Hover Elements on a Simple Chart:
1) Legend and data labels 2) Indicator line for the X-axis 3) Filter controls
In a rudimentary chart like the above, those 3 elements of clutter are No Big Deal, but in a highly-stylized situation, they might be quite unwelcome. (Imagine the filter controls hovering PRECISELY where the designer would like the company logo to be). And even when Power BI lets us turn those sorts of things off, it still highlights the tradeoff between “total control over design” and interactivity – you get one or the other, never both. (BTW, the real-world scenario above with redacted labels reflects a TRIPLING in effective advertising costs in reaction to COVID-19 disruption in a particular market. June’s figures so far seem to indicate a reversion TOWARD normal, but still significantly more expensive than pre-COVID).
Interactivity Tradeoff #2 – No Pagination
Dashboards are meant to be… dashboards. As in, everything you need to see – IN ONE PLACE. It would be kinda the opposite of the Dashboard Ethos to have them… scroll. Or to have them feature “go to next page” type of controls. If you need that stuff, it’s not a dashboard.
Plus, interactivity loses its punch when you have to scroll all over the place. Click a filter, then scroll to see the impact. Oh wait, that’s not the right selection. Scroll back up to the filter, repeat. Nope, not a good experience. So neither Power BI nor Tableau support this kind of thing.
Here are a few examples which illustrate the loosely-defined concept of Pagination:
Multiple Pages Long and Contains Variable Number of Sections. Can’t Be Done with Matrix or Table Visuals.
Pretty Much Impossible in Dashboarding Tools like Power BI.
Could ALMOST be Done With a Single Table Visual in Power BI – Which DOES Scroll.
But… There’s a Page Break and a Footer on Each Page. And a Next/Back Page Nav. Nope, Power BI Can’t Do It.
Next/Back Page Nav Again (400 of 563!). Clean Page Breaks for Printing. Flirting with Pixel Perfect for Added Difficulty.
Again: Pretty Much Impossible in Dashboarding Tools like Power BI.
It turns out, most businesses have significant need for things like the above. And if Power BI reports were your only tool, you’d end up like our friends from the top of this article – flooded or grounded, depending on which metaphor you prefer.
If You Try to Build the Reports Pictured Above in Power BI, You Won’t Get Far.
Also? Mo Details, Mo Pages
Another “meta characteristic” of paginated reports is that they TEND to display details rather than aggregations. EX: specific transactions rather than emergent trends. In paginated reports, you’re MORE likely (but not guaranteed!) to be looking at “raw” rows of data from the original database, whereas in a Power BI report, you’re more likely (but again, not guaranteed!) to NOT be seeing raw individual rows, but rather intelligent aggregations of MANY rows. But either way, more detail means you’re more likely to need multiple pages.
And since details seem to be more self-evident in terms of action they imply, it’s a good time to bring back my flowchart from part one…
An Action-Oriented Distinction
While they are perfectly-useful distinctions, I’m far from the first to write about “Pixel Perfect” and “Paginated” as factors tilting you toward the Traditional Reporting end of the spectrum. And at P3, one of our rallying cries is that BI is for Action, and information is merely a means to an end. So I thought I’d try to contribute something new, as a second lens, by contrasting the two in terms of “where will your report fit into the Action flow?”
Click for Larger Version
Thus far in this article, I’ve talked about things which fall on the right side of that flowchart, whereas Part One focused primarily on the left side. If you didn’t read part one, and aren’t interested in doing so, no worries – this flowchart is the last piece of part one you’d be missing. You’re welcome. Now let’s move on to some important actions of a different flavor – namely, Do’s and Don’ts for your organization.
DON’T Use Power BI as the New Traditional Reports!
(AKA Power BI is NOT a Visualization Tool)
Pet peeve time, or actually, “Oh My God Please Don’t EVER Do This” time: don’t fall into the trap of using Power BI as just the latest reporting tool. When you hear someone refer to Power BI as a “visualization” tool, you should be very suspicious, because either they have no idea what Power BI can do, OR they are actively trying to suppress its adoption. We have seen plenty of both.
Here’s a quick acid test. Open a PBIX and and flip it to Model View. Does it look like this?
If Your Power BI Model View Looks Like This, You’re Not Getting the Full Benefit of Power BI – NOT EVEN CLOSE
(And yet, this is very common, because old habit die hard)
This is 100% worth calling out, because it’s usually a dead giveaway, and all too common. Most of the time (with a few exceptions), a model view that looks like the above indicates a lack of understanding of Power BI. But it’s often “perpetrated” by IT professionals with long histories in BI, which is understandable given the history of the industry (OK, maybe there’s a reason to read Part One after all). Given the long-dominant Reporting mindset of “do some SQL, make flat views, then format as reports,” a model view like the above is precisely what we should expect if a longtime BI practitioner just assumes Power BI is some newfangled version of the tools they’ve used forever. Which it’s not.
True story time: P3 was involved in a very large enterprise project in which we were not the only consulting firm engaged. The other half of the project was staffed by a Big Four firm, and the Big Four firm had brought out the Big Guns. They had sent one of their “absolute best Microsoft BI experts” to lead their half of the project, and we believed them. A month went by, and our half of the project was going well, but the other half kept floundering. We offered to help, and were told in no uncertain terms to Stay In Our Lane. And we good-naturedly did. We assumed the other half of the project was just a lot more complicated than we realized, and went back to work. Another month passed, and the other half of the project STILL lagged well behind schedule. Late one night, after hours, the project sponsor quietly asked us to take a look. And we saw a model view that looked like the one above. Their Microsoft BI Expert might have indeed been the real deal in 2009, but knew absolutely nothing about Power BI or SSAS Tabular. We had their problem fixed in three days.
I can’t show you an example of bad without showing you an example of good, so here ya go…
It Doesn’t Have to Look Exactly Like This, Nor Even This Complicated – But You Get the Idea
DO Have Both Tools in Your Repertoire!
Same Quality Table, Now With 33% More Columns!
Pretty obvious: if your org has both kinds of needs, you need to know both tools. Not just so that you can DO both kinds of work, but just as critically: knowing both helps you CHOOSE the right one at the right time. For that reason, having both skills on staff, but living in their own siloes, will still be problematic. The ability to tell the difference, and utilize the right tool at the right time, DOES seem to be a bit lacking right now, which was the whole trigger of this multi-part series of articles.
Ready for Anything – Where You Want to Be
(But You Still Need to Know When to Use the Wheels or the Propeller)
Coming in Part Three!
On Wednesday June 17, in the third and final (I promise!) installment of this series, I will:
-
Go on a bit of a rant about product naming
-
Question whether Microsoft even “gets” all of this
-
Provide some examples of how the two toolsets can integrate – the Better Together cliche, yes, but there’s a lot of truth to it here
Hope to see you back next week 
Power BI and Paginated Reports: Have We Come Full Circle?
This is where we’re headed in Part Two. Today: taking stock of how we got here.
(Click image for larger version)
Summary of this 2-part piece:
- There’s always been a blurry line between Analysis and Reporting
- 20 years ago, BI was dominated by Reporting, with Analysis as an upstart
- Today, Analysis seems to have “won,” and this DOES reflect major progress!
- But I also think we’ve overcorrected quite a bit, and it’s time to take a fresh look
- Old habits die hard, and old use cases die even harder
- In an era of Action, we have the chance to hybridize in ways that were impossible before
The Thick Grey Line
There are few stories as tortured and twisted as my relationship with Analysis and Reporting. In 2002, as part of the Excel team at Microsoft, I was introduced to the concept of Business Intelligence. This is back when the term (BI) was fresh. It sounded cool, like Jack Ryan in a darkened room at the CIA. (The stodgy 2000s-era, IT-bottlenecked reality would not dawn on me until much later).
Excel was about to make a large investment in BI-related capabilities, and the powers that be had selected me to lead our part in it. I was excited, but now I needed a crash course in “what the hell is BI?” I was given multiple tutors, and they all were quick to introduce the concept of Analysis versus Reporting. The “versus” seemed to be pretty important. It wasn’t an “and” – no, the “versus” was chosen deliberately in these sermons. You see, these were Two Very Different Things.
I struggled mightily to grasp this difference. I was told that interactive things like PivotTables were Analysis tools – NOT Reporting tools! Reports were something completely different. “But,” I pointed out, “they’re called ‘Insert PivotTable Report’ on the Excel menu today!” (This was Excel 2003). “Yeah,” said the mentors, “…we might want to fix that.”
I tested the fences. “If interactivity is the key differentiator between Analysis and Reporting,” I asked, “if I just LOOK at the same PivotTable every day, and I NEVER manipulate it in ANY way, then THAT’S a report, yeah?” “No,” said the mentors with the tone of an exasperated Yoda addressing an impetuous Luke, “it’s still a PivotTable, and that’s an Analysis tool.” “OK then, how about an Excel report worksheet that’s assembled with formulas alone, and therefore non-interactive. Is THAT a report?” “Rob,” they said, “you’re just gonna need to get it through your head that Excel is an Analysis tool. We have work to do and we can’t get hung up on this.”
Based on my mentors’ evasive answers, I concluded that this was merely a Distinction Without a Difference. “Reports are things which inform people with data” became my motto – whether they were interactive or not. “And EVERYTHING should be interactive,” was the second half of my motto. It seemed to me that the distinction between Analysis and Reporting was an artifact of historically inadequate software. As a species to that point, we’d built some interactive tools, and some non-interactive ones, and each of them lacked some of the features of the other. It was time to Evolve. We were going to be Modern, and blur the line. “Good looking AND interactive – we shouldn’t have to choose.” And off I went.
(Aside: one of my top 50 memories of working at Microsoft follows. Early in this process, the leader of the SQL Server Reporting Services team told me in a meeting I was not allowed to refer to Excel documents as “reports,” and that only SSRS reports were reports. He instructed me that I could use the words “document,” “spreadsheet,” or “workbook,” but “report” was off limits. I found it amusing that someone thought they could trademark a basic noun, and dedicated myself to a campaign of good-natured rebellion. Since he had no political control over me whatsoever, for YEARS I then made a point to ALWAYS call Excel docs “reports” in his presence, turning each time to slyly grin at him after delivering the irritant).
How Did Excel 2007 Do? (At blurring the line)
It was a noble cause, and we made some progress. PivotTables became MUCH better-looking… but they were still PivotTables. Excel charts got prettier, but hardly set the world on fire. We added Cube Formulas to enable cell-by-cell layout of reports, but cell-by-cell is a far cry from the pixel-perfect capabilities of SSRS.
Ironically, I now give us worse marks on interactivity than aesthetics! Being told over and over that “Excel is interactive analysis” must have taken its toll, because we took interactivity for granted. Yes we added some cool filtering features to PivotTables, but that was basically it. Hell, we didn’t even think to add Slicers! (Those came in 2010).
Analysis in the Shadow of Reporting
Let’s get back to 2002: since Excel was on Team Analysis, I got partnered up with the SQL Server Analysis Services (SSAS) team. The best way to describe SSAS at the time is/was “SSRS’s smarter but younger brother.” SSAS was a super smart product built to tackle enormous and important challenges. Even the circa-2002 multi-dimensional version was one of the wonders of the software world (and today Tabular is even better). A brainy product built for brainy people tackling real-world problems of nearly every flavor.
SSRS is/was a much simpler beast, but it was FAR more successful in the market at the time. Even though it tackled a more modest set of problems, and quietly left MANY business problems unaddressed (more on this later), it outsold SSAS by a multiple. There’s really no mystery as to why: the world is FULL of people who know SQL, and if you give people a way to turn SQL queries into pixel-perfect reports (and distribute them), you’re gonna get a lot of takers.
Reporting’s dominance over Analysis lasted a long time. Even in 2010, when I spoke at a user group dedicated to professionals working in Microsoft BI, almost no one in the audience had any significant experience with SSAS. I was stunned, but it was a powerful lesson: most “BI” professionals were actually “Data Warehousing and Reporting” professionals.
Why this Was a Problem
SSRS was more popular than SSAS because reports are just easier to build than analytical models. SSRS reports basically answer a NARROW set of questions from a SINGLE data source, whereas SSAS models attempt to answer EVERY question, powered by MULTIPLE data sources. The latter is an exponentially more ambitious undertaking, AND it requires you to learn skills beyond SQL, so yeah… I get it. “Path of least resistance” might be the world’s most powerful force.
One little problem though: the world is an “every question from multiple data sources” kind of world. Everyone has/had that problem, and Reporting did nothing for it. Worse, Reporting gave us a false signal that we WERE doing a good job of BI. “What do you mean, you don’t have the data you need to answer your biz questions? What about those jillion reports we built you?”
I’ve written about this problem extensively – here and here for example, but the gist is this: ignoring those unanswered questions doesn’t make them go away. You either manage from your gut instincts, which is basically just guessing, or you answer them in Excel.
So Business Intelligence was still sorely lacking in… Intelligence. A Reporting-dominated world is a sad one – for the Business.
The Part Where I Say Something Nice About Tableau
Gather round children, because this doesn’t happen often: Tableau (and Qlik, and Spotfire, etc.) did us all a huge favor by breaking Reporting’s stranglehold on BI, and for bringing Interactivity to the forefront. We may take it for granted today, but the idea of a report canvas where damn-near EVERY element is interactable… just wasn’t anything near a mainstream idea before the rise of Tableau.
Tableau completely reset the reporting world’s expectations for interactivity – from “minimal” to “ubiquitous.” In the Reporting world, the developer had to go out of their way to enable every single point of interactivity – so we never got much (if any). But in Tableau, interactivity was the DEFAULT. If the Age of Reporting was the Dark Ages, Tableau was the Renaissance.
Interactivity is crucial – because without it, you cannot answer followup questions. A traditional SSRS report might tell you there’s a problem, for instance, but NOT help you decide on a course of action. Sometimes, all you needed to do was look at a different report, but very often, that report didn’t exist, because this particular problem had not been anticipated. So you request a new report. And wait. And wait. And then maybe use it once – or never, because the window for making a decision passes before the report arrived. (I think this is a big reason why every company has hundreds, if not thousands, of “active” reports which haven’t been used in years – they were one-offs to answer a specific one-time question. Interactivity cuts down on this problem dramatically).
Non-interactive Reporting was never going to cut it again – not once the Biz had seen Tableau. Something needed to shake us out of the Reporting-Dominated world, and we got it. For that, we should all be very thankful.
Fast Forward to Today
The illustration above gives you the gist: it’s all about field of vision. The “pinhole” view of traditional reporting was never sufficient to power the needs of the Business. Tableau’s more interactive approach was/is captivating by comparison, precisely because it enhanced end users’ (biz users’) field of vision – WITHOUT requiring additional IT intervention. But alas, Tableau also still relies on flat-table backends – it’s still “just” fundamentally another tool on top of SQL, and therefore only goes so far before you’re back waiting on IT.
If Tableau marked the Renaissance, Power BI is the Industrial Revolution. The multi-touch interactive approach established by Tableau married with the power of SSAS Tabular models under the hood (plus agile ETL in the form of Power Query/M) is the culmination of our multi-decade story. (I’m sure the world of data will continue to advance, but I expect the next breakthrough to be a Black Swan moment, rather than following the lineage of the story here).
Here’s another “slice” at the same diagram, this time a bit more specific to the capabilities of each genre:
I believe this is what progress looks like
Wait, didn’t Tableau just add a data model?
Why yes! Yes they did. And that actually reinforces my point rather than diluting it: you NEED an analytical data model engine under the hood, else you’re still bottlenecked. I’m glad to see Tableau acknowledging this, because it’s fundamentally good for the world. Heck, it even makes OUR messaging easier, since we can now say “yep, Tableau agrees, and they’re playing catchup.”
BTW, I’ve long said that our company (P3) is committed to using the best tools out there, and that if something better comes along, we’ll use it. So we’ll be keeping a close eye on Tableau but… I don’t expect them to be a credible competitor with their data model any time soon. Microsoft has a tremendous lead here – arguably twenty years or more – and we all know how that played out for Google vs. Bing. Plus, I’m skeptical that you can retrofit a data model into such a long-established “single table” product without serious compromises.
Analysis has Usurped Reporting, But…
Analysis (aka Interactivity – plus a true Analytical Model under the hood) has now risen to dominant position, relegating Reporting to the backburner. You can’t be a credible BI competitor these days with just a Reporting tool. You have to bring that sweet Interactive Dashboard love if you want to be considered.
But even though “my team” won, I think the industry is oversteering, and now neglecting Reporting to its own detriment. The SSRS bloodline is valuable and deserves to be part of your toolbox – in parallel with, and integrated with, Power BI. It’s time to talk some detail, and about the Way Forward, and we’ll do that in Part Two coming Thursday (June 11).
You Wouldn’t Use a Skateboard as a Surfboard (or Vice Versa).
We’ll talk about that in part two on Thursday.
The Power of Data to Do Good #TIBCO4Good
Reading Time: 2 minutes
Data is the transformative fuel that is changing the global economy. Partnering with TIBCO has allowed data-savvy businesses to lead in their respective industries and transform their strategies, cultures, and capabilities. But it’s not just businesses that are facing complex data challenges. Many of the world’s most pressing societal, educational, and scientific issues also depend on the ability to harness the power of data in order to gain insights, make smarter decisions, and implement the systems, applications, and processes that create change on a human scale.
TIBCO4Good applies the same technologies, strategies, and leadership deployed for business to the most pressing societal issues we face across the globe. TIBCO4Good is a way to come together as a community and harness data “for good.” Because nearly every complex challenge our planet and societies are facing is rooted in vast streams and reservoirs of data and confounded by the complexity of difficult-to-make decisions.
A great example of this initiative at work can be seen in the recent efforts by TIBCO’s data science team, led by Michael O’Connell, to create a Visual Analytics Hub to bring a data-centric approach to visualizing the deeper implications of the COVID-19 pandemic and build a fact-based understanding of reproduction, growth, and healthcare resources.
TIBCO + Conan Swag for Partners in Health
On a lighter note, as a part of #TIBCO4GOOD, TIBCO has kicked off a new project, inspired by the “Zoombombing” of our newest team member, Conan O’Brien. Conan crashed a recent TIBCO conference call and asked our CEO Dan Streetman, “Could I at least get a TIBCO sweatshirt out of this? We all know you have like fifty in the trunk of your car.”
Well, Conan—and everyone else eying Dan’s snazzy TIBCO jacket—now you too can purchase “TIBCO + Conan” branded apparel at www.tibcoswag.com. TIBCO will donate all proceeds from items purchased, up to a total of $ 50,000 to Partners in Health (PIH), a social justice organization supporting emergency response during the COVID-19 crisis, as highlighted by Conan.
Anyone around the world can shop the site and help support Partners in Health (PIH) as they mobilize high-impact programs to control the spread of COVID-19 and ensure that the most vulnerable receive access to dignified care. Learn more about how PIH is helping with COVID-19 emergency response here.
We’ll be back soon with more updates on new and upcoming TIBCO4Good Initiatives and how you can get involved. For more information on the program and the work that we’re doing, head to https://www.tibco.com/tibco4good.
Challenges And Opportunities For Power And Utility Companies
Like every year, LuxLicht, a big electric utility company in Germany, is holding its annual board meeting. It’s been a tough year for LuxLicht so far, and everyone feels the pressure of a more dynamic, complex, and data-driven environment. Every board member faces challenges in their specific area of responsibility and addresses them during the course of the meeting.
Alex, the CFO of the company, starts the meeting with an overview of hard facts regarding the financial situation. He points out that costs for aging infrastructure, grid expansion, and system integration for renewables are still rising heavily. The limited budget provided for CAPEX/OPEX is not enough to cover all crucial projects equally. Alex insists on searching for new solutions that can help reduce existing costs and therefore use the budget more efficiently.
COO Peter confirms the need for efficient cost optimization in the transformation process towards green energy and integrating decentralized renewables and their fluctuating electricity output into the current grid infrastructure. Further, he points out that one of his major responsibilities is to meet current and future energy demand. Peter emphasizes his doubts regarding the status quo of the business transformation and his ability to provide a robust, reliable energy infrastructure with decentralized assets with uncertain electricity output.
After describing the different aspects of the asset landscape, including the drive towards cost reduction, Bob, the CAM of LuxLicht, wants to discuss the last appointment he had with his maintenance staff where they evaluated overdue maintenance projects. Together, they reviewed a spreadsheet that contains preventive maintenance plans for a list of assets. This is the primary way LuxLicht has handled asset maintenance over the years. Nevertheless, Bob must acknowledge that this method leads to a high risk of under- or over-maintaining the assets. Having a methodology capable of individually reviewing assets and proposing tailor-made maintenance strategies would save a lot of money.
John, the CEO, is delayed in joining the meeting and apologizes to his colleagues. There was an important emergency case that had to be solved first. A local grid collapsed a couple of hours ago, and a team of employees was sent there to look at the issue. They found a bird’s nest on top of the transmission lines that caused the problem. One of the older workers climbed a ladder to remove the bird’s nest but fell and broke his leg. Luckily, the colleague is in good health and is now recovering from the accident in the hospital. John admits he has underestimated the current risk level of his employees and wants to achieve a better and safer work environment to limit further incidents.
Sussan, the CHRO, feels sorry for the injured employee and hopes that he will recover soon. Nonetheless, she reminds the board members that her department has been urging attention to the “war for talent” quite extensively. Changing demographics and the rapidly aging workforce coupled with a decline in available talent means incidents like this may happen more often in the foreseeable future. Sussan also recommends ensuring that knowledge transfer between older and younger employees works smoothly and the company takes steps to close the gap of skilled employees with an effective talent management initiative.
At the end of the annual board meeting, everyone agrees that these challenges must be addressed as soon as possible. Just one question remains: How can LuxLicht overcome all these challenges effectively?
Data standardization and harmonization
Companies are gathering an overwhelming amount of information in multiple asset classifications leading to the problem of a complex company structure. With the ongoing shift towards greater integration of decentralized renewables and other M&A integration projects, there is a constant need for one, consistent, common enterprise asset management (EAM) data model structure. Even if everyone is using the same asset management tools, there are always different definitions and nuances that make integration an insurmountable obstacle.
Therefore, the number one priority is to clean up the existing data model. This could be achieved with a customized machine learning solution that analyzes various patterns across different data models and consolidates everything into one model. Then, it becomes possible to decide which processes and current and future products are crucial to the portfolio. Finally, with trustworthy data across the network, companies can leverage the potential of their entire asset landscape by implementing a holistic EAM suite that effectively utilizes the accompanied cloud solutions. This will enable managers in the electric utilities segment with a much faster and more efficient decision-making process.
Collaboration and management of distributed assets
As the world becomes more dynamic, complex, and data-driven, the energy industry must react to changing influencing factors and framework conditions through different digitization processes. Essentially, all asset-intensive industries face disruptive challenges from changing business models and higher cost pressures.
With the constant rise of prosumers in the energy business, the competition in this segment is even stronger than ever. Nevertheless, new revenue streams can be created by implementing novel business models such as maintaining micro-grids and renewable energy sources for prosumers, especially those deployed by retail households and small industries. Revenue growth from new business models will help maximize returns while facing limited budget resources (CAPEX/OPEX).
A holistic asset network offers the opportunity to connect and collaborate with different stakeholders in the entire ecosystem. By collaborating with various manufacturers, operators, customers, public utilities, and service suppliers across the network, utilities have all the necessary asset information, like specific asset conditions and asset degeneration. Therefore, crucial information about different assets along the energy supply chain can be incorporated into future investment decisions and support management along the cost-optimization process.
Reliability-centered maintenance
The evolution of different maintenance routines over the past several years is also reshaping the energy sector, with the main objective to ensure asset uptime and increase profitability by avoiding unplanned and unnecessary maintenance in the energy supply chain. Decision making around maintenance strategies for an organization’s asset base can be instinctive, driven by intuition rather than hardcoded facts. Therefore, the risk of under- or over-maintenance plays a significant part in the process.
Well-established strategies, such as reactive and preventive maintenance, are already broadly used in energy markets. Hence, more advanced methods like predictive maintenance, where time-based condition monitoring provides many insights into asset behavior, can help companies achieve their objectives beyond cost optimization and further increase agility and optimal resource allocation. Predictive maintenance does maintenance only when it’s needed. It uses data science- and rules-based approaches to reduce unplanned failures and the overall number of maintenance actions.
An efficient asset and performance management strategy enables enterprises to adopt a risk-based approach to asset management, enables better decision making for maintenance planning, and reduces the probability of asset failure. Due to the segmentation of assets based on risk, criticality, impact, and environmental factors, it is possible to determine the best maintenance strategies at the lowest cost and risk to improve reliability. With tools like reliability-centered maintenance (RCM) and failure modes and effects analysis (FMEA), an appropriate asset strategy can help identify specific assets that need a change in maintenance strategy. With all this information at hand, it is feasible to calculate risk levels and determine alternative maintenance strategies. Strategies can include condition-based or predictive maintenance, making the most out of the existing asset landscape.
Organizational change management
Since most technical approaches are already feasible and mature, the implementation process of these projects often fails due to the lack of effective organizational change management (OCM). Many ERP implementations have discovered the hard way that people affect the success of an implementation. If the organization is not ready for the transformation, the project will miss timelines and come in over budget.
Organizations regularly follow a commitment curve on every ERP implementation. OCM educates the end users and leadership about the changes that are coming their way, using best practices to teach and encourage the organization about how to understand and accept the changes. Only after all individuals accept the changes can the organization say it has reached full adoption. OCM works at the individual levels through communication, involvement, training, support, reinforcement, and more to build consensus throughout the organization.
Get an overview of the SAP S/4HANA Value Discovery Workshop and its benefits by joining this Webinar.