Graphed LogoGraphed
Login

How to Create a Maintenance Dashboard in Looker

Cody Schneider•

Creating a maintenance dashboard can feel like a massive task, but it’s the key to transforming your maintenance operations from reactive to proactive. Instead of digging through spreadsheets to find out why a machine failed, you can spot trends ahead of time. This guide will walk you through building a maintenance dashboard in Looker, step by step, to get the real-time insights you need.

First, Plan Your Maintenance Dashboard

Before you even open Looker, a little planning goes a long way. A great dashboard answers specific questions, so start by defining what you need to know and for whom you're building it.

Identify Your Key Performance Indicators (KPIs)

What metrics truly define the health of your maintenance operations? Your KPIs will become the core visualizations on your dashboard. While your specific needs will vary, here are some common and powerful maintenance metrics to consider:

  • Mean Time To Repair (MTTR): How long, on average, does it take to repair a piece of equipment after it fails? A high MTTR might indicate a need for better spare parts inventory, more training, or clearer repair procedures.

  • Mean Time Between Failures (MTBF): What’s the average time a piece of equipment operates before it breaks down? A low MTBF can signal aging equipment or a faulty preventive maintenance schedule.

  • Overall Equipment Effectiveness (OEE): A composite score that measures availability, performance, and quality. It provides a holistic view of how efficiently your equipment is running.

  • Work Order Status: A simple count of open, in-progress, overdue, and completed work orders. This is a must-have for tracking workload and identifying bottlenecks.

  • Preventive Maintenance Compliance (PMC): What percentage of planned preventive maintenance tasks are actually completed on time? Low compliance is often a leading indicator of an upcoming increase in reactive repairs.

  • Maintenance Costs: Tracking total costs (labor, parts, contractors) against a budget helps control spending and justify future investments.

Know Your Audience

Is this dashboard for front-line technicians, maintenance managers, or company executives? The answer changes everything.

  • Technicians might need a dashboard focused on their assigned open work orders, upcoming preventive maintenance tasks, and an inventory checker.

  • Managers will want to see team performance, MTTR/MTBF trends, budget vs. actual costs, and overdue work orders.

  • Executives care about high-level metrics like OEE, total maintenance costs' impact on profitability, and downtime's effect on production goals.

Start with one audience in mind. You can always create different versions or more detailed dashboards later.

Getting Your Data Ready for Looker

Looker works by reading data that’s already structured in a SQL database (like BigQuery, Redshift, Snowflake, or PostgreSQL). This means your maintenance data - which might live in a Computerized Maintenance Management System (CMMS), an ERP, or even spreadsheets - needs to be accessible in such a database.

Getting it there is a critical step. You might use an ETL (Extract, Transform, Load) tool to pipe data from your CMMS into your data warehouse. If you keep records in spreadsheets, you can upload them to a warehouse like Google BigQuery. The key is to have your core tables, such as work orders, assets, and technicians, consolidated in one place that Looker can connect to.

How to Build Your Maintenance Dashboard in Looker

Once your data is connected, you can start building. The magic of Looker happens in its modeling layer, LookML, which lets you define your business logic once and reuse it everywhere.

Step 1: Create a LookML Model

A LookML model acts as a semantic layer, translating your raw database tables into user-friendly business definitions like "Revenue," "Work Order Count," or "Customer Name."

  1. Create a new LookML project and set up a connection to your database.

  2. Create view files for each of your key data tables (e.g., work_orders.view, assets.view). A view file defines the dimensions (your data columns, like 'Status' or 'Technician Name') and measures (your aggregations, like 'Count' or 'Average Cost') you can use for analysis.

Here’s a simplified example of what your work_orders.view file might look like. Don't worry if the code seems intimidating, Looker has generators to help you get started from your database schema.

view: work_orders {&nbsp,&nbsp,&nbsp,&nbsp,sql_table_name: your_database.work_orders_table ,,

&nbsp,&nbsp,&nbsp,&nbsp,dimension: work_order_id {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,primary_key: yes&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Work Order ID"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,type: number&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.id ,,&nbsp,&nbsp,&nbsp,&nbsp,}

&nbsp,&nbsp,&nbsp,&nbsp,dimension: status {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Status"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,type: string&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.status ,,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,# Make the status field more readable for users&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,case: {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,when: {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.status = 'open' ,,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Open"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,}&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,when: {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.status = 'in_progress' ,,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "In Progress"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,}&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,when: {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.status = 'closed' ,,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Completed"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,}&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,}&nbsp,&nbsp,&nbsp,&nbsp,}

&nbsp,&nbsp,&nbsp,&nbsp,dimension_group: completed {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Completion Date"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,type: time&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,timeframes: [raw, date, week, month, year]&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.completed_at ,,&nbsp,&nbsp,&nbsp,&nbsp,}

&nbsp,&nbsp,&nbsp,&nbsp,measure: count {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Work Order Count"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,type: count&nbsp,&nbsp,&nbsp,&nbsp,}

&nbsp,&nbsp,&nbsp,&nbsp,measure: total_cost {&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,label: "Total Cost"&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,type: sum&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,sql: ${TABLE}.cost ,,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,&nbsp,value_format_name: usd_0&nbsp,&nbsp,&nbsp,&nbsp,}}

Do this for each of your core data sets, then use a model file and "joins" to define the relationships between them (e.g., a Work Order belongs to an Asset and is assigned to a Technician).

Step 2: Create a Visualization (a "Look")

With your model defined, the fun begins. Let's create our first chart in the "Explore" interface.

  1. Navigate to an Explore (you'll see the ones you defined in your model file).

  2. Let’s build a simple Bar Chart showing "Work Order Count by Status."

  3. From the left-hand panel (your defined dimensions and measures), click on the Status dimension and the Work Order Count measure.

  4. Looker automatically queries the data and displays a table. Now, click the Visualization tab and select the bar chart icon.

  5. Voila! You have your first chart. Click "Save" and choose "Save to Dashboard."

Step 3: Assemble Your Tiles on a New Dashboard

A dashboard is simply a collection of these visualizations (called "Looks" or "Tiles").

  1. When you save your first visualization, you'll be prompted to add it to a dashboard. Choose to create a new one and give it a name like "Maintenance Operations Overview."

  2. Now, repeat Step 2 for your other KPIs. For each one:

    • MTTR & MTBF: A single value visualization works best. Create a measure for 'Average Time to Repair' in your LookML and display it on a scorecard tile.

    • Costs Over Time: A line chart showing 'Total Cost' by 'Completion Month' is perfect for spotting trends.

    • Work Orders by Technician: A bar chart is great for comparing workloads.

    • Preventive vs. Reactive Maintenance: A pie or donut chart can effectively show this ratio.

  3. As you save each Look, add it to your new "Maintenance Operations Overview" dashboard. Go to your dashboard page and drag and drop the tiles to arrange them logically. A good layout often places high-level KPIs at the top and more detailed charts below.

Step 4: Make it Interactive with Filters

A static dashboard is good, but an interactive one is fantastic. Filters let your users slice and dice the data themselves.

  1. On your dashboard, click "Add Filter".

  2. Let’s add a date filter, which is almost always useful. Create a filter that pulls from the 'Completion Date' dimension in your work_orders view.

  3. In the filter settings, go to the "Tiles to Update" tab and select every tile you want this filter to affect. Now, users can easily see performance for last week, last quarter, or any custom date range.

  4. Add other helpful filters like "Asset Type," "Technician," or "Work Order Priority." This empowers managers to drill down into specific areas without needing new reports.

Best Practices for Effective Dashboards

  • Keep it simple. A crowded dashboard is an unusable dashboard. Focus only on the most critical KPIs for your intended audience. If you need more detail, link to a separate, detailed dashboard.

  • Keep it clean. Arrange your tiles in grids and leave some white space to make your dashboard more readable. Start with high-level summaries at the top and then detailed views at the bottom. Start broad and then go specific.

  • Don’t be afraid to add context outside the charts. Use a Title tile or notes to provide context on where a KPI comes from, who maintains it, and when it gets updated.

  • Make it scannable with colors. Use the visualization editor to customize colors. You can align them with your branding and highlight important thresholds (e.g., make your "Overdue Work Orders" count stand out as bold red color).

Final Thoughts

Crafting a maintenance dashboard in Looker is a process that bridges the gap between your raw data and valuable, actionable insights. It empowers your team with the information they need to make smarter decisions, predict problems, improve efficiency, and ultimately drive the business forward.

For those without a data engineering team or time to learn LookML and set up complex models, we built Graphed. It allows you to ask questions in plain English and instantly generate real-time dashboards connected to your tools like Google Analytics, Shopify, and even spreadsheets like Google Sheets. Instead of hours spent configuring view files, you can ask "How is our dashboard for open work orders by priority?" and get reports in a matter of seconds.