Are you overspending on your capital investment operations and maintenance activities? From simple data consolidation to powerful self-service dashboards, learn how to use Microsoft Power BI to generate the insights you need to minimize costs and reduce operational risks. Using a utility company as an example, you’ll learn how to visualize key metrics—like total cost of ownership and mean time to repair—in a centralized dashboard so that you can make better decisions about your capital investments.
Learn how to:
Consolidate data from multiple sources including Microsoft Excel, SQL Server and Power Apps
Visualize key metrics about your capital investment operations and maintenance activities
Analyze the frequency of your most significant capital investment issues using Pareto charts
Transcript for webinar:
Jasmine: [00:00:06] Hi welcome everyone and thanks for joining us today.
Jasmine: [00:00:09] My name is Jasmine and I’m on the marketing team at AgileThought just a little bit about AgileThought.
Jasmine: [00:00:15] We’re a custom software development consulting company and we work mostly with Fortune 1000 companies to help them envision deliver and maintain software solutions that are critical to their business. Today I’m joined by our presenters who is a chinchilla who is our data analytics practice slate and Pat converse who is one of our senior client executives during this discussion today Pat and who they will discuss what Asset Management is the role it plays and extend the life of your capital investments and how you can use powerv.i. to improve the value of those investments. Before we begin I have a few housekeeping items. First we’re going to be randomly selecting three lucky winners to receive a one hour consultation with say. So depending on where you are with your analytics initiatives you can use this consultation for one of two things. One you can use it as a brief visioning and planning discussion so you can learn how to implement power VII. And who is able to work with you to understand what’s needed from a governance security licensing even scalability standpoint. Or you can use it as a chance to help you validate existing data all with power b I bet you can even talk to us a about different performance optimization opportunities. We’ll be emailing all of the winners after the webinar with next steps you can get those consoles scheduled with a in addition to that all of today’s attendees are going to receive 25 percent off of power VII night power be a knight is one of our solution offerings that will help you get started with Power VII and really accelerate your adoption. We’ll explain more about power be it. I ignite at the end of our discussion and I can send you details after the webinar as well. So with that being said I’m going to hand it off to Scott commerce.
Pat: [00:02:01] Thanks Jasmine. Hello. Im Pat Converse client executive at AgileThought. Covering fintech and the energy industry. One topic that I hear among all my customers is how do I manage my capital investments more efficiently to better forecast cut CapEx and OpEx spending around my fixed assets Asset Management is very important. And today I want to clarify what context we’re going to be talking about asset management. And for today the right context is industrial equipment actors compressors pumps and the like now putting a firm number on the true cost of ownership and time to live from a CapEx standpoint can be pretty difficult for these assets. Because forecasting costs for assets is difficult. Accurate budgeting can then be a nightmare affects maintenance costs on your ass. Inventory may not be forecast properly because of unexpected maintenance or asset men or asset downtime which can put a serious strain on your operations from a staffing and budgeting sampling. And this problem space when I talk to my customers almost universally I hear the same things the top level comment is we collect all this data but I can’t do anything with it. And I have no idea what we could do with it but as you think a little bit deeper and talk about business outcomes that they’re trying to move towards then things get more specific and you hear things like we have data disparity in silos so I can’t bring together ERP business data maintenance and planning operations data and anything close to real time. So how can I improve my business or our result. Our systems don’t talk to one another. Everything isn’t documented the way it should be. And every year an open risk item from x expended expenses around maintenance sometimes you hear forecasting down times and because of time because of the time of life of our assets is incorrect because we can never bring together all the data to do the proper engineering analysis. With me today I have a Zay who can talk more about how we utilize powerv.i. to make decision making easier dashboards and visualizations and addressing these problem we’ll also talk about how we can consolidate data from across those disparate data sources and data silos and how we can prioritize asset management activities based on that information. Housing you want to take it over.
Jose: [00:04:40] To further emphasize the point you were making. Let me demonstrate the solution that we put together some research social we’ve put together for some of our clients and for these demo using sharepoint and power b at so what we did for this demonstration we created a power b I report that contains data coming from Office 365.
Jose: [00:05:11] In this case Excel spreadsheets. And this will be for example an output or an export out of a perhaps a day story or award scheduler system into Excel spreadsheets and uploaded into a sharepoint site. We also have data coming in from single server which pass data relating to assets in maintenance records.
Jose: [00:05:35] So this could be the banking system of a data historian that allows us to connect to the banking database system and be able to aggregate and merge these data together into a consolidated view of our assets and our maintenance. And then like you see in the screenshot we publish these to a sharepoint site that allows us to manage the security deal who has access to these reports and be able to deliver the content. A intranet. For example if you have SharePoint Online or on premise deploy in your environment. So let me show you how this solution looks.
Jose: [00:06:22] And then we going to look how we put together these using power by power query and using some analytical formulas with Dex so what I’m going to do is I’m gonna open my browser and I’m gonna go to a demo sharepoint site that we put together for a fictitious company called Rocky Point. So as you see I need to enter my credentials and make sure that I have access to these sharepoint site once I enter my credentials I’m able to hit the SharePoint site. In this case the default document library which contains all the Excel extracts that we talk about. Take a look at all of these spreadsheets and we can see that we have some asset information along along with some maintenance records so with that data where we can do is now we can consolidate that into power RBI into a data model that allows us to do analytical imports and exports so what do we have in this initial report is a asset prices.
Jose: [00:07:39] So this is more of a descriptive type of analysis which allows us to see where our assets are located the distribution of those assets in different companies or divisions. What what specific plan are they located. And we can categorize them for example by categories so assets that belong to a boiler category a compressor category generator and we can multi select and only focus on those that we are interested on at the same time probably allows us to do what’s called content continental filtering and that is using other visualizations to filter our data. So you can see all of this data has relationships behind the scenes. We’re going to see that in the data modeling power. And this allows that interactive visualization and filtering in this report we to see the acquisition of of course a defined asset categories and also the acquisition of overtopping.
Jose: [00:08:47] And we can see at a glance CWIEK That’s an empirical data like number of assets in total acquisition cost. With all these assets all these numbers again they correlate with whatever we filter on our regionalization. So if I’m looking at regulators or generators good example I can see.
Jose: [00:09:09] How many have in each new friend division or our company. We can see how many have we acquired bought time.
Jose: [00:09:17] And we can see at an aggregate number. Our acquisition costs as well. So that is very important when doing some of your CapEx and analysis that I had mentioned and the next report we put together a month into 90s.
Jose: [00:09:34] And these are the records that we saw on Excel spreadsheet. But also come of sequel server that we merged together and we’re able to see what are the different water corners that have been created for the different asset categories and some of the work activities related to these four corners we can also use this license to use to filter or navigate our data and see what how many work orders have been there regarding calibration consumable replacements filters in actual installations. We can filter our data according to the status of the workers. We can see that.
Jose: [00:10:17] Efforts have been completed. Nine are in progress and Ten are waiting for parts.
Jose: [00:10:24] If we want to focus on what work orders are waiting on parts we can see that most of them are related to activities like calibration non schedule maintenance filter changes. So this is very important and it is actually a big insight from one of our clients. They discovered that lot of the time the downtime of their assets was related to not having the right parts or supplies on hand in order to do those schedules and actually a non scheduled maintenance. So that was a big insight from one of our clients.
Pat: [00:11:08] If one of the other other things I notice is down at the bottom the maintenance costs per quarter account.
Pat: [00:11:15] That really gives you a quick way to understand where you’re spending your dollars and on what equipment and where you’re probably spending a good bit of your time and where you should take a look at process engineering to improve the lifetime and reduce the wear and tear on those particular assets. I love how it can give you that prioritization.
Jose: [00:11:36] Absolutely and that was one of the big ask from one of the clients they wanted to have that quick insight report with all their consolidated assets and maintenance records. Another analysis that we often put together for some of our clients are parental charts and with far be I. It’s very easy to create these brutal charts and be able to analyze our data according to the 80 20 rule which says which states were. Where are we spending most of our 80 percent of the time. What are we working. What are we working on and what activities or maintenance are related to those that 80 percent of our efforts. In this example the bottom. I have that different asset categories in the total maintenance costs associated with each of these different asset categories. If you look at the 80 percent mark which should be right around this section we can see that most of our time all the work and the maintenance is related to assets that fall under the motor or compressor categories. So this might be the assets or asset categories that break the most or that require more maintenance and then we can correlate that as well with the 80 percent rule on what suppliers that are providing some of these parts or assets we need to do more me on. So for example if we look at the 80 percent March which will be right around here we can see all the suppliers on the left are the ones that are providing parts or assets that require more maintenance perhaps more downtime and we can filter these or be very detailed on what we’re looking at. And for example we’re looking at not scheduled maintenance right which is a big item in asset management. We can see what are those asset categories that we’re spending more. Most of the time in non-standard maintenance right. These are events or situations where those assets experience some downtime and that affects the bottom line because we’re not being productive and we can correlate that also with the suppliers which suppliers are providing us perhaps with assets or parts that are not up to par right that perhaps are not the right the quantity that we expect from them. And we can focus in some of the suppliers for example that are on the right side which means their suppliers in general suppliers we have parts and assets that don’t break us much.
Pat: [00:14:26] This is a great data especially around the not just the maintenance costs but also the vendors who were giving you know parts of that aren’t necessarily up to par. This gives you a lot of leverage and how you manage your vendors and how you can manage you know pricing discussions with them on those parts when you can show them the data that says hey these are breaking all the time. I’m spending a lot of money here. What can you do to help me out here is there a maintenance program that we can work to arrange. Is there a different part of your catalog that I need to choose. Or if the relationship just gets bad. You know what. What vendors should I look at for the for the best parts going forward. This is good data.
Jose: [00:15:11] Absolutely. I like you say this gives you a lot of leverage as a customer so you can negotiate back with your suppliers and make sure that they’re not only providing you with good pricing but also good quality parts and assets and that next report was one of the. A couple of metrics that some of our customers have asked is the ability to get the meantime or failure in the meantime to repair. And those two metrics give you the number of days or hours that you suspected for an asset to experience some type of downtime.
Jose: [00:15:57] So essentially how many days from today will this acid experience some type of downtime or will require some type of maintenance. In the meantime to repair it how long will that expected downtime last. So here we have a particular asset that has had three work orders ending in total have had 116 days of downtime. You can see that detail in this table and you can see that progression over time in terms of maintenance activities. We also going to show you KPI so perhaps certain assets may have a goal of uptime goal of you know 80 percent 90 percent. And we can quickly validate that against actuals for example if one of our suppliers what the SLA states that you should experience a ninety nine point nine percent uptime on these assets we can correlate that against our actuals and we can see that these assets has not matched the 9 percent uptime goal right. It’s been down 88 percent. He’s been up 88 percent of the time. So we need to evaluate and do some road cost analysis on that.
Pat: [00:17:20] And his age. So some assets just have you know time relations for those KPI. But others have more complex calculations on what kind of SLA you would expect. How can power beyond handle those kind of things.
Jose: [00:17:37] Absolutely. So we backed for example we can create more complex formulas that allow us to not only correlate the data based on our experience in terms of the different maintenance activities but also we can correlate that with more complex or more robust algorithms and models for example using machine learning you can tap into for example Azure matching services and being able to create models that give it a more robust indication or forecasting of failure over time.
Jose: [00:18:17] So so that is actually something that we worked on within I just thought we have a dedicated data science practice. And we have more with our customers to create models that ingest not only the historical data from the historians in terms of maintenance but also telemetry data and machine generated data.
Jose: [00:18:40] Things like temperature vibration and other metrics that create a more robust and holistic view of what’s happening enviromental around your assets in this case so and then as you can see we can create those metrics easily and understand how many how much downtime and what is the next expected downtime for those assets. So those are very important metrics that could be generated using seen your maintenance historical data or like I was mentioning more robots models that ingest other data environmental data for example like temperature vibration So with all of this information we can derive relatively easy. The true cost of ownership or total cost of ownership based on the formula that led you to the top taken in the acquisition cost maintenance plus operating costs and ended residual value on the books. And we can see visually that most if not all of our assets rested categories over the lifetime. We are actually investing in or in spending on maintenance just as much as if we are spending on the acquisition costs. So that is a very important metric because you and I know in terms of budgeting in forecasting how much money should you allocate for maintenance acting is in keeping your assets up and running in optimal shape and then the last report I wanted to show you is the acid lifetime.
Jose: [00:20:25] So is a very important report that we have put together for customers trying to understand. You know right now which assets are up and running that are within estimated lifetime expectancy. Others that did not last us as much as the manufacture and player stated and then assets that actually have are running beyond expected lifetime.
Jose: [00:21:00] So those are three important buckets and we can actually take on some of these buckets and look at our actual assets in the asset. For those that did not lasted as much as they were supposed to. We can see assets that actually have lasted beyond the estimated life thing and those assets are within the estimated lifetime.
Jose: [00:21:27] One important insight here for one of our customers was that they had a bunch of assets that were actually lasting much longer than the expected lifetime so that it is great because you know you don’t have to be spending on replacing those assets within the timeframe that originally forecasted or budgeted for but more importantly is the fact that they realized that a lot of their assets were way out of warranty and that they in some of these assets actually there were no longer being parts of being produced for them or replacement parts. So that is a very risky position to be because that asset breaks the amount of time you will take to replace asset Instapundit and get it up and running is much much longer than the ability to repair it. So they gain a lot of insight from this specific report and started a more aggressive project to replace those assets almost right away.
Jose: [00:22:39] So does it seem fair at all in terms of the demo that solution. I wanted to show you really quick how we pull this together being power B. So I have our big best open.
Jose: [00:22:51] And as you can see Power BI desktop is where we authored those reports that we published to the floor be a service and we should like through SharePoint and probably I guess I’ll be a free download.
Jose: [00:23:03] You can go to power be the come download it install it for free here with Barbie you can see that we have our fields or data set. So these are the two tables that we use to create this visualization. As you can see. And I was playing a little bit later. Part of me has the ability to connect to pretty much any data source that’s out there. So it comes preloaded with more than 100 connectors that are edible. When you download power be a desktop and you can connect to sources like IDE files SharePoint folders database system like IBM SQL Server side based or the AP and then some other Azure and online services even to some specialized online connectors for example to Salesforce Zendesk and many other special type of connectors so those connectors we can create workable called queries and these queries are essentially a step by step instructions on how to connect to a data and what to do with you. So in this case for example I have my assets data set in this dataset. I mean I’m extracting data from my Excel spreadsheets. I’m expanding older rows and merging with my company expanding that renaming column storing data creating unique ideas and adding in index to improve performance. Those are that asset query becomes a essentially a building block that I can use for other queries. So in this other query for example now I’m merging that query with my maintenance query. And it’s very very simple as being able to merge or join on columns like assets. This will be the unique identifier. Perhaps a company as well that gives us that unique join to our we do know our assets and maintenance records so with that said now we can use those datasets to populate some of these visualizations. We can also create some custom formulas like pad was meant in terms of more robust analysis in the language that power by uses for these queries. This form with this callbacks known as analysis expressions and is very similar to what you are used to in Excel formulas.
Jose: [00:26:01] The link rate very simple formulas or a little bit more complex formulas that allow us to get the value based on other values and more complex logic So in summary in terms of PA VII we have many many features I highlighted here some of the most important ones like I mentioned ability to connect to many many sources with the building connectors the ability to connect to both cloud and on prem data sources ability to compress large volumes of data in memory ability to ingest shape and transform data using power to query. So this is analogous to details with a detail tool which is building or being the user experience in terms of reporting development is essentially zero coding. You’ll just drag and drop data sets into the essentials you require essentially no no coding to do that. The RBA also comes preloaded with a bunch of reach between stations that you can actually customize. You can create your own customer service station or you can actually import other was an extension from the marketplace. Most of them are free and you can import them into your reports you can also share and collaborate with other users securely using the public service. You can create more complex and equal formats with index. Now you can incorporate also are in Python visualizations. But that’s important for data science and one of the biggest feature features is the ability to do data modelling and be able to interact with the data with natural language processing using Cortana Analytics.
Jose: [00:28:10] So let’s say I want to just talk a little bit about about possibly a length which is our solution offering typically a two week engagement. It will help you implement a power be a suite no matter what stage you you’re on. If you’re just exploring it we do a lot of envisioning and planning. If your already implemented it or wanted to do a proof of concept we can help you with that as well. And that solution allows you and your staff your developers not only to learn from it but also actually deliver some reports and dashboards using your own data which is important in order to understand the data modelling ingestion and utilization part so I want to thank you for joining us.
Jose: [00:29:03] And I want to hand it over to Jasmine for some questions.
Jasmine: [00:29:10] Thanks Jose. So some of you sent your questions ahead of time so we only have time for a few of them. The first one is can power API connect to Operation skater slash historian systems.
Jose: [00:29:26] Correct. So most of the cable systems or data storage systems allow you to connect to different endpoints so these endpoints could be SQL Server back database.
Jose: [00:29:41] Or it could be a API in power by the ability to connect to those either endpoints. So this could be you know we’ve actually connected RBI to some of the leading data historian systems in the market and be able to inject the data through either the API endpoint or SQL database. Back in.
Jasmine: [00:30:07] Thanks Jose the other question that we got is how would power bi handle mixed data sources residing on prom and in the cloud.
Jose: [00:30:20] So that’s a great question.
Jose: [00:30:21] So power bi has the ability to connect to on premise data and be able to consume that data in the cloud the way that is done is through what’s known as the on premise data gateway. So it’s a small installation that you can do you do on your own any other premise server doesn’t have to be your actual database server and that allows Barbie to connect from the cloud into any on premise data source. You’d also have the ability to connect to any cloud based solution a cloud based application so any application that no NSA software as a service or platform that service RBI has connected allow you to connect directly to those online sources as well as the unprivileged outsourcing because in the Gateway.
Jasmine: [00:31:21] OK.
Jasmine: [00:31:22] And we have one final question. Can I set up location based visualizations in Power BI for looking at pipeline related assets.
Jose: [00:31:33] Correct. So Power BI also has ability to plot your data in maps.
Jose: [00:31:41] You’ve seen big maps which is already natively proven with RBI. You can also bring your own shape files using our yes files. So this could be a geographic type location shaped like state city county whatever level you want to go into in terms of geography. And in addition to that you can also incorporate VCU diagrams. So for example some of the past work that we’ve done for some of our clients. We’ve been able to create a layout a visual diagram of their manufacturing plant their processing plant and we’ve been able to connect data points to all of their different assets or a machine that is running on that pipeline. So you can do that with Visa diagrams for more of an industrial type of scenario where you can actually see data flowing through your plant geographic data. You can also incorporate your it’s called Custom patients and those are no GSK type of civilization that you can create and the more you have more interactivity perhaps with your data. So yeah all that is possible with Power BI.
Jasmine: [00:33:00] Cool. Well that concludes our presentation today. Thank you all for attending. We’re going to be following up with you next week with details about your special. Twenty five percent off of Power BI Ignite offer and we’ll also be following up with the three lucky winners of the one hour consultation with jose and we’ll send you the webinar recording just in case you want to share it with your co-workers and friends. If you have any questions feel free to reach out to both Jose, Pat and myself. And with that. Have a great afternoon.
Watch the Full Session
Watch the Full Session
Speakers
Speakers
