Help & Support ↗ Lucanet Learning Space ↗
Getting Started
Global Functions
Administration
Extended Planning & Analysis (xP&A)
xP&A Video Tutorials
User Roles in Lucanet xP&A
Lucanet xP&A User Interface
All Steps at a Glance
Basic Concepts and Elements
Creating a Model
Integrating Data
Connecting to a Data Source
Connecting to a Spreadsheet
Connecting to an Accounting System
Connecting to a BI/Database System
Defining Database Queries
Connecting to BigQuery
Connecting to Postgres
Connecting to Databricks
Connecting to Microsoft SQL Server
Connecting to MySQL
Connecting to Redshift
Connecting to Snowflake
Connecting to Looker
Connecting to a CRM System
Connecting to an HRIS/Payroll System
Connecting to a Lucanet CFP Data Source
Connecting to a Source System Using a Webhook
Sharing Data Connections
Working With Integrated Data
Guideline for Exchanging Data Between xP&A and CFP
Modeling Your Data
Viewing and Visualizing Data
Working with Models
Using Comments and Descriptions
Comparison: xP&A vs. Excel Performance
Consolidation & Financial Planning
ESG Reporting
Disclosure Management
XBRL Tagger
Lease Accounting
Banking & Cash Management
Data Collection
Tax Compliance & Reporting
Glossary

Defining Database Queries

Overview

Database queries are used to define which data is pulled in from your BI/Database system into xP&A. They are entered during the set-up process of the integration (see individual set-up instructions for each system) and need to be structured in a certain way.

In this chapter we explain how a database query is defined and what needs to be considered in regards to linked dimensions that are queried.

Defining a Query

Rules:

  • A query starts with select.
  • A query must return a date column.
  • A query must return at least one value column for the variables (which can be numbers or dates).
  • A query can return optional columns for dimensions, dimension mappings, or cohorts.
  • Linked dimensions must be included in the following form `from dimension > to dimension` (using backticks)

For more details on linked dimensions, see Linking dimensions and the following chapter Limitations and Workarounds for Querying Linked Dimensions.

  • A query must contain from followed by the name of the table the data is to be extracted from.

Example query:

select date, users, country, region as "country > region" from users_table

Explanation:

  • The data is queried from the users_table.
  • The first column (date) has the dates of the transactions (timestamp or date type).
  • The second column (users) contains the values of the "users" variable (number type).
  • The third column (country) is a dimension column, the users are broken down by country (string).
  • The fourth column ( region as "country > region") is a dimension mapping. The region is not an independent dimension, but the countries are assigned to regions.

Example query result:

Shows the result of the aboce query example. A table with 4 columns: date, users, country, country > region
Example query result

Querying Constant Values

If you only want to import constant values (no time series), the date column can be null. For example:

select null as date, price, plan_name from price_table

Limitations and Workarounds for Querying Linked Dimensions

When working with queries, you can only create one level of linked dimensions (mappings) per query. This means you can establish multiple direct relationships from a single source dimension, but you cannot create chained relationships within the same query.

Allowed in one query:

  • A → B
  • A → C
  • A → D

Not allowed in one query:

  • A → B and B → C simultaneously

Workaround Solution

To handle multi-level relationships, you need to create two separate queries:

Query 1: Define the initial mapping relationship

Query 2: Define the secondary mapping relationship using a fictional value

Example: Lease Contracts and Vehicle Types

Consider a scenario where:

  • Lease contracts are tied to specific vehicles/license plate (A → B)
  • Vehicles are linked to vehicle types (B → C)
  • The relationship isn't always 1:1 due to vehicles appearing in multiple contracts

The following queries would be used:

Query 1: Lease Contract to License Plate Mapping

Query 1
Query 1

Query 2: License Plate to Vehicle Type Mapping

Query 2
Query 2

Explanation:

  • The second query uses a fictional value (in this case, 1) to satisfy query requirements which will not be used in any model.
  • Both queries use null as date as a placeholder.
  • The mapping syntax uses > to indicate the relationship direction.
  • This approach maintains data integrity while working within the system's constraints.

Last updated on Aug 16, 2023

Table of Contents
Defining Database QueriesOverviewDefining a QueryLimitations and Workarounds for Querying Linked DimensionsExample: Lease Contracts and Vehicle Types
Related Articles

Company

Legal Notice

© Copyright 2026 - Lucanet AG