Microsoft Power BI and AutoCAD Plant 3D: Improve Decisions with Data Visibility

DAVID WOLFE: Welcome to Power BI and Plant 3D-- improve decisions with data visibility. My name is David wolf. I'm the director of Plant technology services at applied software. We are overwhelming-- We are overwhelmingly blessed with data. And in data, to such a degree, we are shifting how we interact with the world to optimize our choice.

Newsfeeds, infinite scrolling, cycling articles, or cards by swiping up, left, right, or down, are all techniques used to try to help us make sense of the data available to us. Globally, we have started to realize that data without a purpose is essentially trash. We don't even bother checking the second page of search results unless we are really desperate.

This class sits at an intersection of many themes working themselves out in business and design worlds. Data first organizations are starting to shape long term thinking, businesses are becoming more scientific in their approach by developing rigorous frameworks to experiment and test ideas. Some of these ideas have been referenced and captured in the class notes.

Regardless of background or philosophy, let's set some groundwork to pick our path forward. Think back to the last project status meeting you had. Some of us are fortunate enough to work on projects where at least part of the studies have been completed before we start modeling. For those like that, perhaps your project manager asked you to prepare a status update on what's preventing piping from getting muddled.

Let's talk through what your experience might have been like. First, you probably go to the process engineer and make sure that you have the latest equipment list available. Then you'd go check with document control and see what drawings or revisions have been submitted over the last week, and make sure you have the latest copy of those. And then you would go through each list, cross-reference them, and make sure that you have captured the latest status for each item.

Conservatively, you spend at least an hour prepping for the status update meeting. Let's imagine a different alternative. We'll make some assumptions like most of the time the delay in modeling is due more to not having the right information to model yet, rather than time spent modeling. We would be able to pull a report in a few seconds that shows what's missing. With a few extra steps added to the configuration of plant 3D, the project manager wouldn't even have to ask you, because the vendor information will be listed, as well as a follow update.

That's the type of opportunity available by gearing up to provide more of a self-serve data environment. To get there, your business needs to focus on three things. First, build a culture. At every level in your organization, people need to start looking for ways to rely less on gut and intuition, and more on testing and information. Next, you need to understand your business structure.

Data needs to target specific roles and personas across the organization. Providing too much detail at the wrong level will frustrate people and make them question the validity of the data. Target roles and functions based on the questions that they ask on a daily basis. Third, socialized data use.

Start a grassroots effort, as well as efforts at the higher level to empower people from all areas to get on board. They want to look for small wins and build on those. Resources for this class are available in the download. And in those resources, we provided an in-depth guide and the write up for the class materials. We also have a Power BI sample file that demonstrates all the techniques shown in the PowerPoint.

We have also included a plant 3D project that corresponds with the power BI file. Let's talk about how you connect power BI to SQLite or SQL server databases. To refresh, SQL server is usually used on projects with multiple users for plant 3D. Most of the time if there are more than three to five people in your project, you'll need to switch to SQL server.

The reason for that is as pipers and designers input information, the amount of time required to save and the number of people saving drawings changes how the database can react. And so for those scenarios, you will need to move off a SQL light and use SQL Server. Most of the time when people implement SQL server, they use Windows credentials.

So when you're logging into power BI you'll be able to use Windows credentials as well. If you're looking for the database or the server name that you should use, you can find that information in plant 3D in the product setup window. The SQL server connection inside of power BI is native so you don't need any extra tools in order to connect.

Let's talk about SQLite. SQLite is typically used for local or single user Plant 3D projects. Now there is a little caveat. When you're using BIM 360, it also uses local SQLite databases, as well as vault. But with Vault you would connect directly to the SQL server. When you're using Power BI with Plant 3D, you'll have to install the skylight ODBC driver. And the instructions and steps for this are all captured in the hand out for the class.

The data model for both scenarios is the same between SQLite and SQL server. So all of the techniques demonstrated here will apply to both situations. Let's talk through the Plant 3D data model. It's important to understand how Plant 3D organizes and structures the information within the project. So that you can build the accurate dashboard that you need.

First of all, you need to understand what the role of PnPID is. When we go to Plant 3D, we'll take a look at the Data Manager and identify the PnPID in the Data Manager column. Here we can see it demonstrated that it's referencing a pipe. And so on the engineering items table, we could look at the ID 8955.

We're going to trace this pipe throughout the project and see how it relates with different tables. First of all, let's look at the PnP based table. Here under a PnP base at the bottom, you can see we found the row the PnP ID 8955. That corresponds to this row. When an item is created inside of Plant 3D, every item will have a record in the PnP based table. And so that controls how increments across all of the items and tables and operations within the project.

Next, we'll need to understand how relationships work. In this case, we're going to look at the P3D line group relationship. Our part, 8955, which is that pipe. Is within a certain line and we need to be able to associate that line in our reports. So in this case, we looked at the part ID 8955, which found the corresponding Lang group on the P3 D line part relationship table. And that's 5752.

And this is a little dry. But we'll get there. We'll get to building the picture. So when we look up on the three line group table, that part ID of 5752, gives us our size, speck, and number information and all of the rest of the data that applies to that line group. Last of all, you'll need to understand how to connect drawings to objects.

So our pipe 8955 is connected to a drawing. And the drawing in this case is 1P01. We can find this to the data links table. The data links table is a little bit different, because most of the time when we're looking for things, we're going to use the row ID column. The row ID column and the PnP data links table. Is the PnP ID column for all the parts, like engineering items or valves or whatever.

So here we'll look up the drawing ID, which is 4284. In order to find the drawing number, name in the PnP drawings table. Now, let's create a model dashboard. We're going to focus on building a process that's repeatable. In a normal design process, you would build a structural model first, then the equipment, and then the piping. In that process, the vendor information for equipment ends up stopping progress on piping design as designers wait for vendor information.

The outline for the equipment model dashboard looks like the following. And in our handout we have this listed out. So first of all, we're going to answer a question. The question is what equipment has not been modeled yet. This question is helpful because it fits in the larger context of asking is the project on schedule. When raising the discussion of risk dependency on third parties for information, is always a factor that has to be mitigated. Which is why we're asking these types of questions.

Next, we're going to select a metric. In this case, the metric we're going to use is the tags that are missing from models that are on Pn IDs. There are some guidelines you should be using as you pick metrics. First of all, like we just mentioned, you want to find the key question ask. Next, you'll have to apply some creativity to find some easy ways to measure and you might want to invest in automating the metric capture.

You'll san-- make sure you sanity check just in case things feel out of whack. In every metric needs to have a validation process if it's not automated. Don't be afraid also to approximate. We don't need it over engineering metrics. Close enough is good enough. Think of it as selecting a tolerance for the right tolerance. We don't build to the 64th of an inch because it would require us to overinvest in our tools.

So you need to make sure to select some good metrics. Last of all, not last of all. Next, we're going to develop a model. So this model is going to represent the information in our power BI that's going to be put into a chart. And when we're talking about modeling for power BI, we're not talking about a 3D model which we're used to. We're talking about data modeling.

So in this case though, all the information we're using is found within plant 3D. So our model is going to Shift a little bit to add columns like is modeled or is on a pn ID. But by and large, everything is there. Next, we'll create-- after the data model is done, we're going to create charts and graphics to create and customize the dashboard.

So let's get into it a little bit. So with regards to our data model and how we're going to build this. And the handout, the handouts can refer to the project equipment table. It's going to show us the items that have been set up in order to report the data. So here you can see at the far right, we have a model file name, which is used to capture the drawing number. And we use the PnP data links table relationship in order to find that.

We also have the Pn ID and the model. We also have added a column for modeled and on Pn ID. And so our modeled column is going to control whether or not the items have been placed. And the Pn ID column is going to show whether or not they're in the schematic. So here's what the dashboard can look like once we've defined that data structure within power BI.

On the left, we have the count of the model we're showing a percentage complete. And the model the key shows that is true or false for the model values. In the center, we have the number of tags that are not on the Pn ID, but are in the model. And so here you can see that the risk is greater, because we need to go back and input information into our Pn ID that got modeled.

Let's talk through the line design status. So another common question that we have is what's the status on the piping lines. Did we get everything modeled that's on the Pn ID. Where are we on ISOs. These are very common questions that they take a lot of time to figure out exactly where we are. So with a little bit of automation and integration into power BI we can answer those questions easily.

On the data model portion we did have to do some complex operations inside of power BI. So we did things like connecting the line number with the ISO style, and that enables us to get our ISO report. We also created a project lines table, which is going to summarize all of this for us. So we're going to use the line group ID, the Pn ID land group ID column, and the ISO count in order to reflect our design status.

And here we took a very simple approach. Just to set a basis for operations moving forward. We're choosing a design status of estimated. If a pipeline is only in the Pn ID or in the 3D model. And then if the line exists in both spots but it hasn't been ISO'd yet, the design status will be set to InDesign. Complete lines are lines that exist in the Pn ID, the model, and on the isometric.

So here is what our final output report would look like. And a couple of clicks just refreshing the data, we can get this report any time we need it. And so it gives us a much better ability to judge where we're at without having to go back and dig into information. Let's jump over to plant 3D and see some of these concepts in action.

All right. So let's review the SQLite database. Over here we can see our table of items and these corresponds to Data Manager. Which we'll look at in plant 3dD. Here's our PnP ID. And so we can go find anything we need on the PnP based table. Notice that on the left is where we have a view of all the items within the database.

So any item without a PnP at the end of it is a table. And you'll see the icons change. When you see the PnP that means that it's a query. And so our engineering items table is going to have a limited set of information. But if we look at the query for engineering items is going to put together the class name, the good, the timestamp, all of these first five properties come from the PnP based table.

So when we're selecting tables to bring into power BI, we're going to select these PnP table, the PnP views by default. Let's jump over and take a look at line group relationships. So this one isn't any different than the table. It just shows our line group and our part. And then here's the PnP data links. So we can review this and see these items. And so on.

So let's jump over to Plant 3D now. Over here in our Data Manager, you can see the PnP ID column. So this is the data record pointer that I'll let you know what part you're on. So by selecting this we can go through and find different items and correspond them back to a row in the database. Let's jump over to power BI and do a quick look at what that looks like.

So here's the report. And when we're using it we can come over here to our dashboard, and we can interrogate different parts of it. So I can right click. I can do show data points table. And it's going to give me the list of all the items that were included and the status. So there's a lot of complicated things we can do. We can format. We can do a lot of other powerful tools to let this work.

In the background, under relationships, we've gone through and defined the relationships between P3 land group. And the drawing links for piping, for between project equipment, based on tags, and engineering items, and other equipment. And so you'll be able to view all these relationships to see how they work together to function.

We also have under the Data tab, it can take a look at the project equipment table, which is the table created to generate our comparison between the model equipment and the items on the Pn ID. And you'll notice up at the top we have formula some similar to Excel. That build these columns for us. So you want to reference those as you look at the documentation.

Also, the project lines has columns defined for us. We can add or remove items as needed. And so we have formulas to show the steps for that process as well. Under-- Let me expand this a little bit. And under PnP drawings ISO is where the work has been done to correlate the ISO with the line number. And so at the far right, you'll see a line number column and then also the ISO style.

So that's the key contest behind creating dashboards with power BI, and using the Plant 3D data model to provide that information. All right. So let's do a quick review of the handout that you'll be provided with class. So we have the lessons learned. We have the business context and the resources list.

So you'll be able to get all the things you need to run through the sample provided. We also have the handout and the guide for accessing and using SQLite and SQL server. So these are step by step. We identify the tables that you'll need in order to create your data dashboard, and also some steps for switching the databases. Like to another project.

We also have detail around the data model, and how you can navigate it, and how they're put together, as well as the steps for creating the data model dashboard. Here are some guide around how to use and add items to your chart, and configuring that, as well as how to control your theme. And so out of the box your report's going to look something like this, but you can customize it using a palette designer, a color palette designer. Then also using a report theme generator to get the output that you want.

Last of all, we have the overall view and the steps for what we're used to create that. And some references for your notes in the future. Thank you for joining us for power BI and plant 3D. We're excited to see what you guys come up with when you build your dashboards for reporting against playing 3D data models.