As dashboards and reports become more and more complex, slow run times can present major roadblocks. Here’s a collection of some of the top tips on how to improve dashboard performance and cut slow run times when using Tableau, Power BI, and Looker.
Universal Tips
Before getting into how to improve dashboard performance within the three specific tools, here are a few universal principles that will lead to improved performance in almost any case.
Limit logic used in the tool itself: If you’re generating multiple calculated tables/views, performing complex joins, or adding numerous calculations in the BI tool itself, it’s a good idea for performance and governance to execute all those steps in the database or a separate business layer. The more data manipulation done by your BI tool, the more queries and functions your tool has to execute itself before generating visualizations.
Note: This is not an issue for Looker, as Looker offloads all of its computing onto the database via SQL.
Have the physical data available in the needed format: When the physical data in the source matches the granularity and level of aggregation in the dashboard, the BI tool doesn’t need to execute a function to aggregate it. Developing this in the data mart/warehouse can be a lot of work but can save a lot of time and pain during dashboard development.
Keep your interface clean and dashboards focused: Consolidate or delete unused report pages, data sources, and fields. Limiting the number of visualizations on each dashboard also helps cut dashboard refresh time.
Simplify complex strings: In general, processing systems execute functions with strings much more slowly than ints or booleans. Where possible, convert fields like IDs to ints and avoid complex string calculations.
Tableau
Take advantage of built-in performance tracking: Always the sleek, powerful, and intuitive leading BI tool, Tableau has a native function that analyzes performance problem areas. The performance recorder tells you which worksheets, queries, and dashboards are slow and even shows you the query text.
Execute using extracts rather than live connections: Tableau performs much faster when executing queries on extracts versus live connections. Use extracts whenever possible, and keep them trimmed down to limit query execution time. If you want to stream data or have a constantly refreshing dataset, then extracts won’t be an option.
Again, limit logic: Tableau isn’t built to handle too much relational modeling or data manipulation – too many complex joins or calculations really slow down its processing. Try to offload as many of these steps as possible onto the database or a semantic layer.
Limit marks and filters: Each mark included on a visualization means more parsing that Tableau needs to perform, and too many filters bog down the system. Try instead to split complex worksheets/visualizations into multiple smaller views and connect them with filter actions to explore those relationships more quickly.
Further Sources: Tableau’s website has a succinct and very informative blog post that details most of these suggestions and other specific recommendations. You can find it here.
Power BI
Understand the implications of DirectQuery: Similar in concept to Tableau’s extract vs. live connection options, import and DirectQuery options for connecting to data sources have different impacts on performance. It’s important to remember that if you’re using DirectQuery, the time required to refresh visuals is dependent on how long the source system takes to execute Power BI’s query. So if your database server is flushed with users or operating slowly for some other reason, you will have slow execution times in Power BI and the query may time out. (See other important considerations when using DirectQuery here.)
Utilize drillthrough: Drillthrough pages are very useful for data exploration and decluttering reports, but they also have the added benefit of making sure your visuals and dashboards aren’t overly complex. They cut down query execution time and improve runtime while still allowing for in-depth exploration.
Be careful with row-level security: Implementing row-level security has powerful and common security use cases, but unfortunately, its implementation has the tendency to bog down system performance. When RLS is in place, Power BI has to query the backend and generate caching separately for each user role. Try to create only as many roles as absolutely necessary, and be sure to test each role to know the performance implications.
Further Sources: Microsoft’s Power BI documentation has a page dedicated to improving performance that further details these options and more. Check it out here.
Looker
Utilize dashboard links: Looker has a wonderful functionality that allows for easy URL linking in their drill menus. If you’re experiencing long refresh times, a nifty remedy is to split up your dashboard into different dashboards and provide links between them in drill menus.
Improve validation speed: LookML validation checks the entire project – all model, view, and LookML dashboard files. Increased complexity and crossover between logic in your files lead to longer validation time. If large files and complex relationships make lag in validation time problematic, it can be a good idea to break up your projects into smaller pieces where possible. The key here is handling complex SQL optimally by utilizing whatever methods will maximize SQL performance on the database side.
Pay attention to caching: Caching is another important consideration with Looker performance. Developers should be very intentional with how they set up caching and the conditions for dumping and refreshing a cache, as this will greatly affect dashboard runtime. See Looker’s documentation for more information on caching.
Optimize performance with Persistent Derived Tables (PDTs) and Derived Tables (DTs): Caching considerations come into play when deciding between using PDTs and DTs. A general rule of thumb is that if you’re using constantly refreshing data, it’s better to use DTs. If you’re querying the database once and then developing heavily off of that query, PDTs can greatly increase your performance. However, if your PDTs themselves are giving you performance issues, check out this Looker forum post for a few remedies.
Further Sources: Looker’s forums are rich with development tips. These two forum pages are particularly helpful to learn more about how to improve dashboard performance using Looker:
Want to learn more about how to improve dashboard performance? Our data and analytics experts are here to help. Learn about our data visualization starter pack.
In this blog post, read about this consultant’s experience with Looker, in their own words.
As a data management and analytics consultant, I have developed dashboards in a majority of the popular BI tools such as Tableau and Power BI, as well as their backend data structures. The opportunity to develop dashboards in Looker arose when a new client, Byrider, needed 2nd Watch to help them model their data and develop Looker dashboards for their sales team (more details here).
Based on my limited experience with Looker, I knew that it makes creating quality visuals simple and that coding in LookML is unavoidable. I worried that LookML would be extremely nuanced and I would lose time troubleshooting simple tasks. I could not have been more wrong on that front. Along with this realization, below are my top takeaways from my first Looker project.
Takeaway 1: LookML is easy to learn and ensures consistent metrics across reports.
Given the vast amount of documentation provided by Looker and the straightforward format of LookML code, I quickly caught on. This learning curve may be slightly different for report developers who have minimal experience with SQL. LookML adds transparency into what happens with data presented in visuals by directly showing how the code translates into the SQL queries that run against the source data. This makes it much easier to trust the results of dashboards and QA as you develop.
More importantly, LookML allows users to ensure their metric definitions are consistent across dashboards and reports. Establishing this single source of truth is key for the success of any reporting efforts. Within the semantic layer of the reporting tool, users can create SQL queries or harness LookML functions to develop custom measures and include descriptions to define them. Transforming the source data into predefined measures in the back end of the reporting tool ensures that report developers access the same metrics for every dashboard business users will see. This is a clear contrast from tools like Power BI and Tableau where the custom measures are created in each workbook and can vary. Furthermore, by using roles, administrators can limit who has access to change this code.
Takeaway 2: Creating dashboards and visuals is super intuitive for about 95% of use cases.
After setting up your data connections and LookML, developing a visual (“Look”) in Looker only requires a simple point and click process. Once you select the filters, measures, and dimensions to include in a visual, you can click through the visualization options to determine the best possible way to present the data. From there, you can easily adjust colors and stylistic options in settings using drop-down menus. Compared to other BI tools, these visuals are fairly standard across the board. That being said, Looker greatly stands out when it comes to table visualizations. It allows for conditional formatting similar to that in Excel and a wide range of visual options in comparison to other BI tools. This makes Looker a great selection for companies that often require tables to meet reporting requirements.
Although detailed documentation and the simple interface meet most reporting needs, there are limitations when it comes to easy customization in visuals. This includes the inability to set drill-ins by a visual rather than a field. In Looker, any demographic used across reports has to drill into the same fields (unlike those set per visual in a Tableau Tool Tip, for example). Additionally, you cannot format visuals based on customized metrics (e.g., color bands, conditional formatting for Field A based on the value of Field B, etc.). The caveat here is that you can unlock many customized visuals by writing custom code, a skill not always handy for report developers.
Takeaway 3: Looker is extremely collaborative, something not often seen in BI tools.
With most BI tools, developers are forced to work independently because two people cannot easily contribute to a single workbook at the same time. Looker’s web-based format seems to have been built with collaborative development in mind, making this tool stand out when it comes to teamwork. Business users can also easily contribute because the web-based tool makes sharing dashboards and embedding them within websites easy. While this may seem minor to some, it significantly enhances productivity and yields a better result.
The following features ensure that your team can iterate on each other’s work, edit the same dashboards, and develop LookML without accidentally overwriting work or creating multiple versions of the same report:
Ability to duplicate Looks developed by others and iterate on them, and Looks can then be added to dashboards
Shared folders where Looks and Dashboards used by multiple people can be stored and reused (if needed)
Ability to “Explore” a Look created by someone else to investigate underlying data
Ability to edit a dashboard at the same time others can make changes
Sharing dashboards using a link and the ease of embedding dashboards, which allows for seamless collaboration with business users as well
With a properly modeled data source, Looker impressed in terms of its performance and ability to provide highly drillable dashboards. This enabled us to dramatically reduce the number of reports needed to address the wide range of detail that business users within a department required. While the visuals were not as flashy as other BI tools, Looker’s highly customizable table visualizations, row-level security, and drill-in options were a perfect fit for Byrider’s use cases.
2nd Watch specializes in advising companies on how to gain the most business value possible from their analytics tools. We assist organizations with everything from selecting which tool best suits your needs to developing dashboards for various departments or structuring data to enable quick reporting results. Contact us if you need help determining if Looker is the tool you need or if want guidance on how to get started.
Looker is one of several leading business intelligence (BI tools) that can help your organization harness the power of your data and glean impactful insights that allow you to make the best decisions for your business.
Keep reading for a high-level overview of Looker’s key features, pros and cons of Looker versus competitors, and a list of tools and technologies that easily integrate with Looker to augment your reporting.
Overview of Looker
Looker is a powerful BI tool that can help a business develop insightful visualizations. Among other benefits, users can create interactive and dynamic dashboards, schedule and automate the distribution of reports, set custom parameters to receive alerts, and utilize embedded analytics.
Why Use Looker
If you’re looking for a single source of truth, customized visuals, collaborative dashboards, and top-of-the-line customer support, Looker might be the best BI platform for you. Being fully browser-based cuts down on confusion as your team gets going, and customized pricing means you get exactly what you need.
Pros of Looker
Looker offers performant and scalable analytics on a near-real-time basis.
Because you need to define logic before creating visuals, it enforces a single-source-of-truth semantic layer.
Looker is completely browser-based, eliminating the need for desktop software.
It facilitates dashboard collaboration, allowing parallel development and publishing with out-of-the-box git integration.
Cons of Looker
Looker can be more expensive than competitors like Microsoft Power BI; so while adding Looker to an existing BI ecosystem can be beneficial, you will need to take costs into consideration.
Compared to Tableau, visuals aren’t as elegant and the platform isn’t as intuitive.
Coding in LookML is unavoidable, which may present a roadblock for report developers who have minimal experience with SQL.
Select Complementary Tools and Technologies for Looker
Any SQL database
Amazon Redshift
AWS
Azure
Fivetran
Google Cloud
Snowflake
Was this high-level overview of Looker helpful? If you’d like to learn more about Looker reporting or discuss how other leading BI tools, like Tableau and Power BI, may best fit your organization, contact us to learn more.
The content of this blog is an excerpt of our Business Intelligence Tool Comparison Guide. Click here to download a copy of the guide.
So, you’ve been tasked with building an analytics dashboard. It’s tempting to jump into development straight away, but hold on a minute! There are numerous pitfalls that are easy to fall into and can ruin your plans for an attractive, useful dashboard. Here are five important principles for dashboard development to keep in mind every time you open up Power BI, Tableau, Looker, or any other BI tool.
1. Keep it focused and defined.
Before you start answering questions, you need to know exactly what you’re trying to find out. The starting point of most any dashboarding project should be a whiteboarding session with the end users; the dashboard becomes a collection of visuals that hold the ability to answer their questions.
For every single visual you create, make sure you’re answering a specific question. Each graph needs to be intentional and purposeful, and it’s very important to have your KPIs clearly defined well before you start building. If you don’t include your stakeholders from the very beginning, you’ll almost certainly have a lot more reworking to do after initial production is complete.
Courtesy of discourse.looker.com
2. A good data foundation is key.
Generating meaningful visualizations is nearly impossible without a good data foundation. Unclean data means holes and problems will need to be patched and fixed further down the pipeline. Many BI tools have functions that can format/prepare your data and generate some level of relational modeling for building your visualizations. However, too much modeling and logic in the tool itself will lead to large performance issues, and most BI tools aren’t specifically built with data wrangling in mind. A well-modeled semantic layer in a separate tool that handles all the necessary business logic is often essential for performance and governance.
Don’t undervalue the semantic layer!
The semantic layer is the step in preparation where the business logic is performed, joins are defined, and data is formatted from its raw form so it’s understandable and logical for users going forward. For Power BI users, for example, you would likely generate tabular models within SSAS. With a strong semantic layer in place before you even get to the BI tool, there will be little to no data management to be done in the tool itself. This means there is less processing the BI tool needs to handle and a much cleaner governance system.
In many BI tools, you can load in a raw dataset and have a functional dashboard in 10 minutes. However, building a semantic layer forces you to slow down and put some time in upfront for definition, development, and reflection about what the data is and what insights you’re trying to get for your business. This ensures you’re actually answering the right questions.
This is one of the many strengths of Looker, which is built specifically to handle the semantic layer as well as create visualizations. It forces you to define the logic in the tool itself before you start creating visuals.
It’s often tempting to skip the data prep steps in favor of putting out a finished product quickly, but remember: Your dashboard is only as good as the data underneath it.
3. PLEASE de-clutter.
There are numerous, obvious problems with the dashboard below, but there is one lesson to learn that many developers forget: Embrace white space! White space wants to be your friend. Like in web development, trying to pack too many visuals into the same dashboard is a recipe for disaster. Edward Tufte calls it the “data to ink ratio” in his book The Visual Display of Quantitative Information, one of the first and most impactful resources on data visualization.
Basically, just remove anything that isn’t essential or move important but non-pertinent information to a different page of the dashboard/report.
4. Think before using that overly complicated visual.
About to use a tree-map to demonstrate relationships among three variables at once? What about a 3-D, three-axis representation of sales? Most of the time: don’t. Visualizing data isn’t about making something flashy – it’s about creating something simple that someone can gain insight from at a glance. For almost any complex visualization, there is a simpler solution available, like splitting up the graph into multiple, more focused graphs.
5. Keep your interface clean, understandable, and consistent.
In addition to keeping your data clean and your logic well-defined, it’s important to make sure everything is understandable from start to finish and is easy to interpret by the end users. This starts with simply defining dimensions and measures logically and uniformly, as well as hiding excess and unused columns in the end product. A selection panel with 10 well-named column options is much easier than one with 30, especially if end-users will be doing alterations and exploration themselves.
You may notice a theme with most of these principles for dashboard development: Slow down and plan. It’s tempting to jump right into creating visuals, but never underestimate the value of planning and defining your steps first. Doing that will help ensure your dashboard is clean, consistent, and most important, valuable.
If you need help planning, implementing, or finding insights in your dashboards, the 2nd Watch team can help. Our certified consultants have the knowledge, training, and experience to help you drive the most value from your dashboard tool. Contact us today to learn about our data visualization starter pack.
Healthcare executives often must quickly make informed decisions that affect the trajectory of their business. How can they best access and analyze the key performance indicators (KPIs) needed to make those decisions? By referring to well-designed healthcare dashboards.
Using tools such as Looker, Power BI, and Tableau, healthcare organizations can integrate customizable, interactive dashboards into their reporting to improve decision-making across a range of areas – from healthcare facility operations and surgeon performance to pharmaceutical sales and more. We’ve compiled several healthcare dashboard examples to illustrate a variety of use cases and a sampling of dashboard design best practices to help your organization make the most of your data.
A Healthcare Dashboard Using Data Science
A healthcare dashboard like this one demonstrates how a company could use data science to determine sales projections – in this case, pharmaceutical sales – and guide their field reps’ sales activities. To make the information easy to digest, this dashboard uses the rule of thirds (a design principle that draws a viewer’s eye to points of interest – you’ll notice variations on the rule of thirds throughout these healthcare dashboard examples):
New prescription (NRx) and total prescription (TRx) values at the top make goals and expectations easily accessible.
A visual in the middle illustrates the TRx projection over time, also broken down into specific drugs for competitive analysis.
Customer segmentation comparisons at the bottom provide broader context and more actionable information about doctors prescribing the company’s drugs. This section indicates the company’s most valuable customers and the areas where their competitors are seeing higher growth, allowing them to target accordingly.
Combining projections and customer segmentation information from a dashboard like this one, a company can adjust their sales strategy as necessary or determine they don’t need to change their sales plan to meet their goals.
A Customized Healthcare Dashboard in Tableau
This dashboard provides quick and easy insight into the financials and patient visits specific to this organization’s California healthcare facilities. It shows the most valuable metrics at the top of the dashboard and enables executives to drill into those that warrant further investigation below.
Using a custom map feature in Tableau, company executives can quickly see which zip codes generate the most revenue and, even more specifically, which individual facilities contribute the most patient encounters. Users are able to uncover areas of improvement across the business such as facilities with lower levels of visits than expected or a revenue-to-encounter ratio not meeting profitability expectations. Conversely, this dashboard also highlights successful facilities that less-successful facilities can model themselves after.
A Regional Healthcare Finance Dashboard
This dashboard showing clinical practice financial data and patient visit information would be referenced by two audiences: the clinical practice’s financial team and the practice owner. In this case, the owner is a private equity firm. Including overall financial performance and that of individual practices in one healthcare finance dashboard allows the private equity firm to understand practice financial data in context.
The heat map provides an easy-to-digest visual to communicate location-specific patient encounter information. Each clinical practice can reference the map to see how the number of patient encounters correlates to their individual financial information, and the private equity firm can understand how location impacts operating costs and revenue across multiple nearby facilities. They’re able to see which clinics and which zip codes bring in the greatest net revenue, allowing them to strategically approach potential new healthcare practice acquisitions.
An Interactive Surgeon Performance Dashboard
Hospital administrators and department heads would use a dashboard like this to evaluate surgeons’ performance. This could be the average time spent in the operating room for a specific surgery, patients’ average time in the hospital recovering after surgery, or how patients fare in emergency surgeries vs. planned surgeries. Alternatively, surgeons could evaluate themselves and learn about areas for improvement.
Using multiple spreadsheets to dig into the factors relevant to a surgical department evaluation can be time-consuming and confusing. Instead, this healthcare dashboard example provides a high-level view of a neurology department and also details regarding each surgeon. The dashboard is interactive, so users can filter in many ways to evaluate one surgeon or multiple surgeons against each other.
A user can easily identify potential issues, such as a surgeon spending a significant amount of time in the operating room for very few surgeries. Department heads can then drill down further into the average age of patients, the surgeries performed, and how many surgeries were planned vs. emergency to see if those factors explain the amount of time spent, all without having to reference multiple spreadsheets or dashboards.
A High-Level Sales KPI Dashboard
Similar to the dashboard using data science shared at the beginning of this post, this highly visual dashboard gives the user a quick overview of KPIs. The user would likely be high-level sales employees at a pharmaceutical company who don’t want to be bogged down with details upfront. However, they can click through this “nine-box stoplight” to get more information as needed.
A user could click on the box showing the change in NBRx (prescriptions for patients who are new to the brand) to see related trends or details about specific sales reps that contributed to this “green light,” a positive indicator. They could then investigate the negative change in TRx by clicking on that box. Pulling in only the details the user needs, they are better able to explain these numbers without the distraction of unrelated details. Perhaps the brand saw great growth in the number of distinct HCPs (healthcare providers) prescribing NBRx but not prescribing high enough volume of RRx (returning patients) to positively affect TRx. Or maybe a high-performing sales rep was on maternity leave and therefore not selling this month, causing a temporary drop in TRx.
A Dialysis Clinic Dashboard in Looker
Dialysis facility owners, administrators, and directors can use a dashboard like this one, created using Looker, to track dialysis machine utilization and allocate machine staffing needs based on location. This dashboard tells a full story with data, moving from a broad picture of total patient encounters, through dialysis-specific encounters, and drilling into details at the facility level.
Like the previous healthcare dashboard examples, this dashboard is driven by the rule of thirds. It first provides KPIs, then highly visual representations of location-specific information (both individual facilities and another zip code heat map), and finally a broader data point broken down based on time. Within one dashboard, a user can understand their data from multiple viewpoints to draw varied, in-depth insights.
These healthcare dashboard examples demonstrate how a well-designed dashboard can uniquely meet the needs of a range of healthcare-related organizations. Healthcare executives can make the most of their data and empower their business users with customizable, interactive dashboards. To learn how 2nd Watch could help your healthcare organization develop dashboards that best suit your needs, set up a complimentary healthcare analytics whiteboard session.
In the first part of this series, A Step by Step Guide to Getting the Most from Your JD Edwards Data, we walked through the process of collecting JDE data and integrating it with other data sources. In this post, we will show you how to add business logic unique to a company and host analyzable JDE data.
Adding Business Logic Unique to a Company
When working with JD Edwards, you’ll likely spend the majority of your development time defining business logic and source-to-target mapping required to create an analyzable business layer. In other words, you’ll transform the confusing and cryptic JDE metadata into something usable. So, rather than working with columns like F03012.[AIAN8] or F0101.[ABALPH], the SQL code will transform the columns into business-friendly descriptions of the data. For example, here is a small subset of the customer pull from the unified JDE schema:
Furthermore, you can add information from other sources. For example, if a business wanted to include new customer information only stored in Salesforce, you can build the information into the new [Customer] table that exists as a subject area rather than a store of data from a specific source. Moreover, the new business layer can act as a “single source of the truth” or “operational data store” for each subject area of the organization’s structured data.
Looking for Pre-built Modules?
2nd Watch has built out data marts for several subject areas. All tables are easily joined on natural keys, provide easy-to-interpret column names, and are “load-ready” to any visualization tool (e.g., Tableau, Power BI, Looker) or data application (e.g., machine learning, data warehouse, reporting services). Modules already developed include the following:
Account Master
Accounts Receivable
Backlog
Balance Sheet
Booking History
Budget
Business Unit
Cost Center
Currency Rates
Customer Date
Employee
General Ledger
Inventory
Organization
Product
Purchase Orders
Sales History
Tax
Territory
Vendor
Hosting Analyzable JDE Data
After creating the data hub, many companies prefer to warehouse their data in order to improve performance by time boxing tables, pre-aggregating important measures, and indexing based on frequently used queries. The data warehouse also provides dedicated resources to the reporting tool and splits the burden of the ETL and visualization workloads (both memory-intensive operations).
By design, because the business layer is load-ready, it’s relatively trivial to extract the dimensions and facts from the data hub and build a star-schema data warehouse. Using the case from above, the framework would simply capture the changed data from the previous run, generate any required keys, and update the corresponding dimension or fact table:
Simple Star Schema
Evolving Approaches to JDE Analytics
This approach to analyzing JD Edwards data allows businesses to vary the BI tools they use to answer their questions (not just tools specialized for JDE) and change their approach as technology advances. 2nd Watch has implemented the JDE Analytics Framework both on premise and in a public cloud (Azure and AWS), as well as connected with a variety of analysis tools, including Cognos, Power BI, Tableau, and ML Studio. We have even created API access to the different subject areas in the data hub for custom applications. In other words, this analytics platform enables your internal developers to build new business applications, reports, and visualizations with your company’s data without having to know RPG, the JDE backend, or even SQL!
