---
title: "Defining Database Queries"
source_url: https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/defining-database-queries
language: en
last_updated: 2023-08-16
---
# 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/modeling-your-data/using-dimensions/create-edit-dimensions.md#linked) 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:**

Example query result

{% info-box %}
**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_
{% /info-box %}

## 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 2: License Plate to Vehicle Type Mapping**

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.