Zero to Hero: Microsoft Power BI and Data Connector

JOE FIELDS: All right, welcome everyone to Zero to Hero, Microsoft Power BI and Data Connector. Before we start, a safe harbor statement, mainly to remind you of the product changes and the availability of the tools we use now. This presentation contains the information, opinions, and data supplied by third parties, and Autodesk assumes no responsibility for the accuracy and completeness of such information.

First off, I'll introduce myself. My name is Joe Fields. I'm the manager of business intelligence and reporting within Autodesk Construction Solutions. I've been at Autodesk for five years, mainly focused on construction cloud tools.

Prior to Autodesk, I was a mechanical engineer for 13 years where I worked on designing HVAC systems for various buildings from critical facilities to higher education to health care facilities and central utility plants. I'm based out of Denver, Colorado, and now, I'm going to hand it over to William to introduce himself.

WILLIAM FIALLOS: Thanks, Joe. Hi, everyone, my name is William Fiallos, and I'm a business intelligence solutions engineer here at Autodesk based in Miami, Florida. My background is construction management, where I gain hands on experiences working at large GC firms for about 12 years in sectors like casino gaming, aviation, health care, and residential. I also have about four years professional experience in software development and business intelligence working for small and large software companies. Now, I'm going to hand it over to Dariusz.

DARIUSZ KISZKA: Thank you, William. Welcome, everyone. My name is Dariusz Kiszka. I'm a partner consulting manager based in Barcelona. I have over eight years of experience with ISE collection and construction cloud, products like Revit, Robot, AutoCAD, Revit cloud work sharing, BIM 360 platform, and ACC platform.

Previously, I've been working as a structural engineer in several countries across Europe, mainly Poland, UK, Portugal, and Spain. Thank you for your attendance today, and I wish that we will have a really great content that will be presented in a moment.

JOE FIELDS: All right, thanks, Dariusz. I'll go over, real quick, our agenda for this session. First off, we'll start with insight. We'll talk about the insight module, and where data connector lives within insight. We'll cover the differences between account and project level of insight, and we'll touch on the Power BI Partner Card. Then we'll dig into the data connector itself, and we'll talk about scheduling extractions, the difference between account level and project level extractions, and the Power BI template gallery.

Next, from there, we'll go into the Power BI connector and talk about how to extract your data into Power BI, adding additional data tables and relationships, visualizing your data, and publishing your dashboard to the Power BI web service. Lastly, we'll round things out with the Power BI templates by talking about how you can use these templates to jump start your dashboard development. We'll cover what's included in the templates from the baked in tables as well as the relationship mapping and the out of the box visuals and metrics.

Before we get into it, we're going to go over the learning objectives for this class. By the end of this class, we hope you know how to schedule and download your data connector extractions, where to find the data schema documentation, how to add data to your Power BI dashboard through the connector, how to use the Power BI templates to get a jump start on your dashboard development, how to publish to the Power BI web service and set up scheduled refresh, and lastly, how to embed your Power BI dashboard in an Insight Partner Card. With that, I'll hand it over to William.

WILLIAM FIALLOS: Great, thanks, Joe. So let's dive in and talk about exploring insight dashboards, Town Admin and Project Admin dashboards, executive overview, and Partner Card. The Insight module is found in the Product Picker dropdown menu. Users will need either account admin permission or project admin permission to be able to access Insight. Members with executive access level can also access Insight by clicking on the Executive Insight button in the My Home Page in Autodesk build.

Here, project admins and members can view a summary of critical and actionable information from a single location, assess trends and patterns in project data, identify areas of concern or growth, and use predictive risk data to improve project efficiency. At the account level, you will see the account name on the right side of the Insight module product picker.

The executive overview is a feature that gives data visibility at an account level. It does not require for a user to be added to every project, gives executive level access in account admin, and will already have member access.

Right below, there are dashboards like risk, which displays an overview of metrics related to the current high risk issues in your project. Costs can be filtered by business units and/or project types. Design displays an overview of metrics related to design review and model coordination processes. Quality allows you to monitor issue status, activity, and root cause. And safety, allowing you to monitor safety issue status, safety observations, safety risks, and the subcontractors with the highest daily safety risk.

Project admins only have access at the project level. These dashboards are only for the project you're currently in, and the project name appears right below the account name right next to the Insight module product picker. At the project level there is My Dashboard, Design, Project Controls, Quality, and Safety.

Like in the account level, these dashboards are automatically generated using Autodesk Construction IQ, which takes project data, then applies analytical techniques and machine learning to transform that data into simple and actionable insights. Users can add more dashboards in the left pane by clicking on the plus symbol right next to Executive Overview or Dashboards respectively.

Data extractions are run in data connector that is found in the bottom left corner of either page. Power BI dashboards can be shared and added in Autodesk Build using a partner card. However, the dashboard first needs to be published to Microsoft Power BI web services, the web based version of Power BI.

This process will need to be repeated for each dashboard should you want to add it to your profile. That way, all published dashboards are managed inside workspaces in Power BI web services. Publishing a dashboard will generate a unique link that is used to embed the content.

Insight has a card library offering many partner cards, including Microsoft Power BI. To add the partner card to your profile in ACC, simply search for it in the card library, select it, and click Add Card. Once the dashboard is published, copy and paste the embedded link of the desired dashboard to the partner card.

The dashboard can be shared with team members who have a Microsoft Power BI license and added to their profile. The Microsoft workspace permissions can be found in this link. Now, I'm going to pass it to Dariusz.

DARIUSZ KISZKA: Thank you, William. Let's switch gears a little bit and step into data extraction available in Insight module. We will talk about how to extract the data via data connector, how to understand the extraction based on schema documentation, and what are different ways how we can access the extracted data. Let's start.

First of all, the data connector tool is embedded in Insight module. You can see this highlighted in yellow at the bottom of the screen. As William mentioned, we have two types of insights, on the project level and on the account level. Depending on which insight we use, we will extract the information as project specific or aggregated data from all of our projects located on ACC hub.

It is worth mentioning that the extraction is user specific. For instance, if I run the extraction with my credentials, Joe won't be able to see it when logging with his credentials to the platform. Therefore, if we want to build our dashboard based on data connector extraction, I would recommend to use a dummy user so that it won't cause any issues when someone is not available to perform any changes. Furthermore, this gives us the flexibility to analyze the extracted data set with the use of BI tools, joining our ACC data with all other data sources.

The blue button displayed in the middle tells us the option to run the extraction. Afterwards, we can see it displayed in the list. When we download the extraction, either we can see the latest data, or we can check what was available in the project at the specific point in time.

Here, we can see the list of extractions that were triggered previously along with the button to download them on the right side. Important to note, all extractions that we have run in the past will be available for 30 days. Afterwards, they will disappear from this list.

To automate the extraction process, we can also use the option to schedule extractions. This way, we may this event reoccurring on specific point in time. We have several settings here that will help us to customize this process further.

We have different options to run it, daily, weekly, or monthly, and we can choose, also, the timestamp, for example, if we want to have it extracted bi-weekly or every third week, et cetera. Next, for the weekly schedule, we have also an option to run it multiple days per week, like Mondays, Wednesdays or any specific time to finish the extraction at the end of the week. Afterwards, we can set the time. The time zone is automatically synchronized with our project time zone, specified when creating the project.

The last point is to choose-- is the start date and end date. In other words, for how long we would like to have this process automated? The maximum time available is 20 years, so needless to say we will run this extraction for most of the project life cycle.

Once we have the data prepared for us, we can download them locally in the ZIP file. When we unzip the package, we will see the list of different CSV tables that contains the information for particular tool or service. At first, it may look complex, but when we get familiar with the content, it should become clear and easy to use.

With the time passing, we will notice that the extraction is always provided in the same format, and it can be divided into different sections. For example, if we are focused on the admin sections, we can see that there are different tables related to that service, like project roles, companies that have been added to the project, users that are part of the specific projects, et cetera. Each table can help us to find the information quickly without accessing the irrelevant details.

Additionally to CSV tables, there is also schema documentation that will be included in our ZIP extraction. These schemas are provided in HTML format. As the information may be updated from time to time, it helps us to keep it aligned and send to the users.

The main purpose for this is to give the user more detailed information for each service. For example, as highlighted the issue tool, we can preview what are the CSV tables that we may expect, and we can find more details what is included in each table. We will talk about it in a moment.

But apart from that, the standard extraction from the tools available in the system, like issues, assets, forms, checklists, et cetera, we would like to also mention about the relationship table. This table is really specific, as here, we can track the data that are linked as a references in the project so that we can see the items that are linked in our ACC project, and also extracted into our CSV data.

As an example, if we link our issue to form, later, we can track this information joining those services, using BI tools. Ultimately, we want to display them connected on our dashboard.

Now, let's dive further into our schemas. As I've mentioned before, the issues tool may have several tables related to that section. Each table will be displayed with the blue heading that is displayed on the top of the screenshot.

If I'm interested in [? issue ?] CSV file, I can preview the information that has been extracted in that specific table. In other words, what are the columns that I may expect, the data input with its constraints, and the description for each column. Some columns may be self-explanatory, like issues title or issue description, but there might be also other data that are a bit more enigmatic or mysterious, like issue assignee type.

Here, we can see that there are possible values for [? any ?] type like user, company, or role. This means that our issue can be assigned either to user, to the specific company, or to the selected role. Important to note, every element has its own unique GUID number that is hidden in the user interface, in other words, in the web browser.

On the first row that is highlighted, we have this issue ID, which is given on the very beginning of the project. Whenever the new issue is created, this ID will be automatically assigned, something similar to Revit's element GUID number. All other parameters may be changed, like issue title, name, description, but this number will always stay the same. This number is a sequence of characters that is created on the background and remains stable across project life cycle.

This is just an example as an issue ID, but each different tables, like assets, forms, even users or companies, they have also assigned specific ID number that remains stable. Please don't mix it with the issue sequence ID that is displayed in the web browser.

Once we understood how to extract the data, and how the data set is created, we can see what are the different access types. In other words, how do we want to access this data from available in the platform? The first one is simple, and we have seen that before. We download the data set locally, and it will be provided in the zip format.

Besides that there's also another option to access the latest data extraction via Microsoft certified connector that is embedded in Power BI tool. Those access points are available both on the project level and also in our executive overview level, which is our account level. We will talk later about how to access that from Power BI interface, but I would like to mention that our Power BI templates that are available already in the product are also provided with both access types based on CSV extraction and connecting directly to the latest extraction via certified connector.

For those who are interested in fetching the data from API, there are two main endpoints that could be found in API documentation. First, to schedule the extraction multiple times per day is really useful for the users when they want to join this data with other API endpoints, and second, to fetch only specific data set or additional activities service that gives us more insight into activities for different tools, like issues and RFIs like assets, files and sheets, cost activities, or admin activities.

There are also other APIs available. We just highlighted the most important ones that have been requested by the other clients, but for example, we can also trigger another action once the extraction is completed via the webhook functionality. For more detailed information, please refer to the links provided in the session handout.

JOE FIELDS: All right, great. Thanks, Dariusz. Next, I will jump in and talk about the Power BI ACC connector. We'll cover how the power-- how the connector works, how to import data from the data connector extracts, how to transform your data, and lastly, how to publish your Power BI dashboard to the web and set up scheduled refresh.

Here's a high level overview of the Power BI connector. ACC connector is certified with Microsoft, so it's included with Microsoft Power BI Desktop out of the box. You can directly import data from your data connector extracts into your Power BI dashboard.

The ACC connector allows you to select between account level and project level extracts. You can also publish your dashboards to the Power BI web service for sharing with others, and lastly, you can schedule your data to refresh automatically using the Power BI connector, so your dashboards stay up to date automatically.

First, we'll start out by going through the workflow of getting your data from the ACC Power BI connector into a Power BI dashboard. Within the Power BI Desktop, you want to go up to Data, then go down to More. In the dialog box that pops up, you can browse the list of connectors. You can merge data from multiple connectors to create compelling dashboards based on a variety of data in your organization.

To find the ACC connector, you can either search in the top left corner, or you can click on Online Services on the left side and scroll until you see Autodesk Construction Cloud. Select the ACC connector and click Connect, and now, you will be prompted to select which server you want to be connected to. Just make sure that you're picking the right server for the region that your account is in, and then click OK.

Next, after you sign in with your Autodesk credentials, you will be brought to the navigation screen. You see here, on the left, where you can drill down into the account that you want to pull the extracts for, and then here, you can select between the account extracts or project extracts. Then lastly, you would select the various tables within the extracts that you want to pull into your dashboard by clicking the boxes on the left of each table.

Once you select the data tables that you want to use in your dashboard, you can either click the Load button, which will load them directly into your dashboard, or you can click the Transform Data button to go to the Power Query Editor to do further transformation. We recommend you go to Transform Data first, which allows you to define the data types of the various columns in the data tables as well as perform any additional transformations to shape the data, how you want it to help you visualize in your data.

If you're starting from scratch, the schema documentation can be helpful for defining the proper data types in the various tables once you're done working with your data in Power Query-- in the Power Query Editor, you can hit the Close and Apply, and your data will load into Power BI to begin building your dashboards.

Next, we need to create our data model by going to the model view on the left hand side. You can click and drag the tables from the left pane and drop them on the View Canvas to organize them. You can also create additional tabs along the bottom to help organize the table relationships. Tables can be added to multiple tabs, and the relationships will propagate through the entire data model.

Next, we need to create relationships between the various tables to connect them in the data model. You can left click and drag a column name in one table and drop it on a related table to create a relationship. This is where the schema documentation comes in, which gives you the mapping of relationships between the various tables.

Look for related columns in the schema as well as notes about foreign keys which will guide you in creating these relationships. If you double click a relationship line, you will get the Edit Relationship dialog. Here, you can modify the cardinality, which defines the table that has the unique ID and which table has repeating ID values. This is important for Power BI to understand how your data is structured.

You can also modify the cross filter direction, which controls how slicers and filters propagate through the data to the downstream tables. This can impact how the data is aggregated in your dashboards and what information is displayed.

Once we have the data model set up, we can then go to the report view on the left bar. This is your dashboard view. To begin building your dashboard, select the desired visual from the visualization pane. You can hover over each icon to see the names of each visual. You can drag and drop the visuals to the dashboard view or you can simply click and Power BI will try to find a place for your new visual on the dashboard view.

To populate your visuals with data, expand the data tables and click and drag the desired column of data to the input boxes of the visual. You can also check the box to the left of the data columns that you want and Power BI will try to guess where it fits best. However, this can sometimes lead to undesirable results, so we recommend clicking and dragging the values to the inputs you want.

Sometimes, it can be good to experiment with different arrangements to get the data organized how you want to visualize it. You can also change the visuals after loading the data to the inputs, and Power BI will try to convert everything to the new visual type.

Slicers are another helpful tool on the dashboards, which let you interactively filter the data. These are some examples of slicers to allow users to select specific projects, project types, and various other values. Multiple slicers can be used together to provide a more granular view of specific data within your data set.

Once you have your dashboard built out, you can then publish it to the Power BI web service. Publishing to the web allows you to share your dashboard with others, schedule the data to refresh automatically, and add your dashboard to the Insight Partner Card in the ACC. To publish your dashboard, make sure you save the pbix file first, then click Publish on the Home ribbon. You will then be presented with a list of workspaces to publish to select the desired workspace and click OK. Once the publishing is complete, you'll get a message like this one, which will contain a link to open your dashboard on the Power BI web service. Here are a few things to keep in mind when publishing to the Power BI web service. You'll need a paid Power BI Pro or premium license to share your dashboard on the web with others. Other users will need a Power BI Pro or premium license to be able to also view your dashboard.

Some [? tiers ?] of Office 365 licenses also include Power BI Pro so check with your company's IT team to see what your options are for Power BI licensing. You can publish to your My Workspace to see how your dashboard looks. However, it can be difficult to share dashboards in this workspace. It is best to create a workspace and invite your colleagues to the workspace to view and share dashboards.

Workspaces can be organized by teams, business units, or other functions within your organization. You can add additional data sources from other connectors to your dashboard. However, to schedule your data to refresh, we'll need to use online data sources only. Using local files like CSVs and XLS files on your hard drive will prevent scheduled refresh from working.

Now to set up your data refresh on a schedule, you first go to your workspace, find the data set corresponding to your dashboard, and click the ellipses and select Settings. When first setting up your scheduled refresh, you need to add your credentials for the connector by expanding the data source credentials and clicking Edit credentials. A window will popup asking you to sign into your Autodesk account. Once you sign in, you can scroll down to Schedule refresh and expand it. Set your refresh frequency, time zone, and add one or more times during the day you want to refresh your data. You can also add additional contacts here to notify others if the refresh fails. Make sure to hit the Apply to save your changes.

One thing to note is the ACC Power BI connector will always pull the latest data connector extract. You also need to set up the data connector extraction schedules that Dariusz went through in addition to the Power BI scheduled refresh. We recommend having a buffer in the extraction schedule and the Power BI refresh schedule of two to three hours to ensure the data connector extract is complete and ready before Power BI tries to refresh your data. With that, I'll hand it over to William.

WILLIAM FIALLOS: Thanks Joe. Let's dive back in and talk about jumpstart your dashboards, ETR relationships baked in, out-of-the-box visuals. Here are the two types of Power BI templates that are offered and supported in Autodesk Build, and those are the connector template and the CSV template.

Like Dariusz mentioned previously, when you run a data extraction, the extraction date is added to the log, and will remain there for 30 days. The data extraction takes about 10 to 15 minutes, depending on how big the data is, and will show the download icon once it has completed along with an email sent directly to your inbox.

One of the many benefits of using the connector template is that once the extraction is completed, you don't need to do anything else. The connector template uses APIs to fetch the extracts directly, allowing you to work more efficiently. Another benefit is being able to schedule data extractions any day, not needing to manually run the extractions and schedule automatic refreshes in Microsoft Power BI web services, also mentioned by Joe.

CSV templates, on the other hand, also have their own benefits. By manually downloading the CSV file extracts to your local machine, you can speed up the table loading since the files are in your local machine. However, after every extraction, you will need to manually replace all the old files in your local machine with the new ones and repeat the process each time.

Let's open these templates so we can have a better understanding. Let's start with the connector template. When the connector template opens, the first prompt will ask the user to fill in the account name, project name, and region.

The account name field is required and must be filled exactly like it is in your ACC account, which is case sensitive. It is not an email. The project name is optional. However, if it is left blank, it will fetch the account level extractions by default. Therefore, if you're looking for a project level extraction, the project name must also be entered exactly as it is in your ACC account, which can also be found right below the account name.

The region can be US or EU, but US is selected by default. When opening the CSV templates, the prompt will ask the user to only fill one field, and that is the source folder, which is the address of where the files are saved in your local machine.

Being in the construction industry, I have experienced and learned how urgent things are and always need the information yesterday. In addition to that project team members tend to use multiple hats on the job, which in my opinion, is what makes our industry very special because we are pros at getting the job done no matter what.

Well, one thing for sure is that using the pre-built templates is highly beneficial because it is meant for users to hit the ground running with their data for having preloaded queries behind the scenes and not waste time. The specific service template will have the necessary tables loaded and connected from the very start. This means, if you're looking for issues, your issues template will already have the issues tables as well as the admin tables preloaded for you, including the table relationships, which is what Joe and Dariusz covered earlier.

Raw data comes in as a default type. It is a best practice to always change all raw data type to its proper type. This means, for example, if 1 is meant to be numeric, make it numeric. If it's letters and numbers, make it alphanumeric. If they are Yes or No checklists, make it a Boolean, and so forth.

As everyone's data is unique, there is a basic data transformation included that is a standard for all ACC accounts. However, these could be adjusted in all cases and dive deeper in data transformation when needed. Here's when and where you can merge tables, add custom DAX formulas and measures, rename data entries, and much more.

Let's dive a little deeper in the data model. It is essential to first set rules and boundaries between your data for any data leaks or wrong information, and here is where you set those rules. Continuing with Joe's example, in the model view is where you can add more tables if needed and select columns that are related. You can edit the table relationship's cardinality to many to one, one to one, one to many, and many to many, as well as edit the cross filter direction presets, which defines how the data flows to single direction or both direction.

Another best practice is to use the star schema, a fundamental query language guidance for data workflow. However, this may not always be the case. Microsoft, as owner of Power BI, will release periodic updates that improve its functionality or even add new visuals or features in the Power Query Editor.

The Power BI template gallery offers a variety of service dashboards that are managed and maintained by our team. These updates also include feedback from our customers where we find a common ground and create these metrics that can apply to most if not all customers. We are constantly reviewing such requests. However, if there is a request that is unique, we can make it client specific through our consulting services.

Each template offers two tabs. One is the account level, and the other is the project level. Although they are the same file, this provides a different perspective to customers depending on what level metrics they would like to see.

For example, some may not need the account level metrics. Therefore, they can simply delete the tab and use only the project level metrics, or vice versa. If desired, these metrics can be copy-pasted to the other tab in case the metric applies to that data set. This makes the Power BI templates 100% customizable for all cases.

Like I mentioned, the Power BI templates offered in the template gallery are 100% customizable, where users can make them their own. Users can add more tables from different services like issues, RFIs, submittals and more, combining them in the same template. When selecting the tables on the left, users will see all the available fields or custom attributes they can verify if there are any custom query transformation needed, including DAX measures.

And of course, users can tweak the visuals like its parameters or even color formatting to their liking. Now, I'm going to pass it back to Joe.

JOE FIELDS: All right, great. Thank you, William. I'm going to go through, recap what we've covered in this class, so some of the outcomes that we have. So we went through, to recap the Insight module, we talked about ACC and the partner cards. We talked about the shareable dashboards as well as the account and project level differences between Insight.

We also talked about the data connector, the various services included in the various tools or modules or features within ACC, where to find the schema documentation, as well as scheduling your extractions. And lastly, we talked about Power BI, using the Power BI connector to customize your dashboards as well as the Power BI templates and pulling in additional data sources from other places.

Now, to round things out, we leave you with a few references that can be helpful on your journey working with these different data sets. The first link at the top is a link to our product documentation that talks about the Power BI connector as well as the templates. Down from that, we've got the links to download Power BI Desktop on Windows, and then also a link to Power BI licensing, which can be helpful as you're digging in and using the tools.

Next, from that, we have DAX Guide, which is a really handy as you start to get more advanced in using the tools here. This can be really helpful to learn the formulas that are used by the DAX language, which is the programming language within Power BI. And lastly, we have a few recommended YouTube channels that are great resources if you're just getting into using Power BI. In particular, a few of them, Guy in a Cube is actually Microsoft employees that put together videos, helpful guides for customers to get more familiar with Power BI and various new features and concepts. SQL BI is actually the team behind DAX Guide, so they have some really good videos there as well.

Curbal Data Labs and Power BI Guy are also some good resources for how-tos that you can go to, and that's it for our session. Thank you everyone for joining, and we hope you have a good day.