Often in BI reporting, business needs require you to analyze facts from two different tables in the same visualization. At Trianz, we focus on helping our customers understand the options available within Looker and designing a solution that not only offers the most valuable business solution but is also clean and efficient technically. Looker has two main options to analyzing different data sets: merging results and persistent derived tables.
1. Do you understand how the data from the underlying database tables should be combined?
Explores in Looker are often developed by LookML developers that have a deep understanding of the underlying database tables and their relationships. Almost always, using a single explore with pre-defined relationships is less risky.
Yes -> As long as you have a firm understanding of the underlying data and how primary and secondary data sources differ, your merge will be successful.
No -> Using a single explore based on a PDT developed by a LookML developer with database knowledge is almost always more favorable.
2. Is performance an issue?
Depending on the size of your data and the relationships between the underlying tables, performance may be a large consideration. One major difference between PDTs and normal Looker views is that PDTs are materialized views and therefore have different performance considerations than typical Looker generated queries.
Yes -> PDTs are persistent, meaning they run on a set update schedule. This allows for flexible updates and manual performance tweaking. Furthermore, if everything is coming from a single query, if that query is written efficiently, performance in Looker will often be faster.
No -> Merging is Ok, assuming each underlying query perform adequately.
3. Is it reusable?
In BI reporting, reusability is important from both efficiency and user clarity standpoints. Looker focuses on reusability and “single source of truth” but also offer options for fast and easy ad-hoc reporting. Ask yourself if this same combination of explores will be needed in multiple places. How often do these types of reports come up? Is this an ad-hoc request?
Yes -> If this combination of tables is commonly used, then you should make a PDT and separate explore. By creating an explore and standardizing the filters and fields in the LookML, you ensure everyone is using the same logic.
No -> If this is a one-off report or the combination of facts are only used in one visualization then merging queries from two different explores is fast and easy.
4. Do you need to filter on the results set?
Filtering is often important for performance and usability. Both merging and PDTs have different options for filtering. PDTs have many more options for filtering because the user is writing the query and explores manually. If you are using merges, you have more limited options for filtering.
Yes -> If you need to filter on the final results set as a whole, then merging is not possible. A PDT will give you full flexibility to filter at the query level, explore level and/or Look level.
No -> Merging allows you to filter each individual query separately. You have to apply filters at the query level just as you would with a Look or tile.
5. Are there dimensions to merge on?
In Looker, merging two queries requires what are called “merge conditions”. In order to use fields for your merge conditions, the same or similar fields need to be available in each query.
Yes -> Merging requires common dimensions (such as order date) to merge the two queries together. When exploring each query, make sure you have the common dimension(s) in the select.
No -> Merging is not possible without common dimensions to specify in the merge rules section. If the two queries are too different or don’t have any common dimensions selected, you won’t be able to merge the two.
When you need to see two facts from different underlying tables, whose relationships haven’t been defined by Looker developers or cannot be defined due to technical limitations, there are multiple options available. Persistent Derived Tables and merging results are two efficient ways of combining tables. Outlined here are the major considerations taken when deciding which way to design your Looker model. Trianz specializes in helping clients make the decision that drives the most value for their business.
Senior Consultant - Analytics Practice
Rachel Stewart is a senior consultant in the Analytics Practice at Trianz who helps clients with all visualization needs. Rachel is certified in a number of different visualization tools, including Tableau and Looker. The Analytics Practice works with enterprises to achieve significant competitive advantage via modern visualization technologies, helping clients to maximize their analytics deployments.
Contact Us Today
Finding Hidden Patterns and Correlations Innovative technologies such as artificial intelligence (AI), machine learning (ML) and natural language processing (NLP) are transforming the way we approach data analytics. AI, ML and NLP are categorized under the umbrella term of “cognitive analytics,” which is an approach that leverages human-like computer intelligence to identify hidden patterns and correlations in data.Explore
The Rise in Big Data Analytics According to Internet World Stats, global internet usage increased by 1,339.6% between 2000-2021. With nearly thirteen times as many people using the internet, this has resulted in a massive increase in the amount of data being processed daily. Our increased sharing and consumption of digital media also compounds this increased usage to create an enormous pool of data for big data analytics firms to process.Explore
What Is an SQL Query Engine? SQL query engine architecture was designed to allow users to query a variety of data sources within a single query. While early SQL-based query engines such as Apache Hive allowed analysts to cut through the clutter of analytical data, they found running SQL analytics on multi-petabyte data warehouses to be a time-intensive process that was difficult to visualize and hard to scale.Explore
The Cloud is the Key to Transformation Success… Transitioning your applications to the cloud is undeniably a critical factor to a successful digital transformation endeavor. It’s more than just a lift-and-shift, however. Let’s explore several things that you need to consider before migrating your applications to the cloud, including: Readiness of your application portfolio Where to begin – the right business case and migration strategy Technology requirements and considerationsExplore
Application Modernization at Speed and Scale Enterprises are pursuing greater application scalability, cost efficiency, and standardization with containerization and virtualization platforms. So, what’s the difference? Containers are a type of virtualization technology that allows users to run multiple operating systems inside a single instance of an OS. They are lightweight and portable, making them ideal for running applications across different platforms.Explore