Analyzing Databases

Getting Started

Global Functions

Lucanet Lume

Administration

Data Hub

Consolidation & Financial Planning

User Interface of Lucanet Consolidation & Financial Planning

General Functions

Basic Configuration

Dashboards

Reporting & Analyzing

Import and Journals

Journals

Importing Data into Reporting Entities

Importing data into Lucanet.Financial Warehouse

Creating and Executing Scripts

Configuring Period Locks

Analyzing Databases

SQL Queries for Lucanet.Financial Warehouse

Planning

Consolidation

Dimensions

Excel-Integration

Workspace Configuration

Lease Accounting

Extended Planning & Analysis (xP&A)

ESG Reporting

Disclosure Management

XBRL Tagger

Tax Compliance & Reporting

Banking & Cash Management

Data Collection

Glossary

With the Analyze database function, you can execute your own SQL queries against a connected database and display the result directly in Lucanet Consolidation & Financial Planning, without using a separate database tool.

Typical use cases for the Analyze database function are:

Verifying the correctness of imported data by querying specific values and comparing the values of related tables

Analyzing the database of a source ERP system when investigating a data import problem

Access to the databases during the analysis is read-only.

The following databases can be analyzed with the Analyze database function:

Database	Description
Lucanet.Financial Warehouse	The temporary and the persistent Lucanet.Financial Warehouse
Database of a source system	Any database that you can connect in Lucanet Consolidation & Financial Planning, for example the database of an ERP system. The database can be located in the cloud or in your local network.

In Lucanet Consolidation & Financial Planning, the Analyze database button is displayed in the detail view of the following elements:

Element of type

Connection

(see

Elements in Directory Structures

)

Data source for importing data into Lucanet.Financial Warehouse, once for the temporary and once for the persistent Lucanet.Financial Warehouse (see

Defining a Data Source

)

Data source of type

Lucanet.Financial Warehouse (#2068)

for importing data into reporting entities (see

Lucanet.Financial Warehouse (#2068)

)

In the detail view of an element of type Connection, the button is displayed as follows, for example:

No separate permission is required for the Analyze database function. Access to the workspace that contains the respective element is sufficient.

To open a query window in Lucanet Consolidation & Financial Planning:

Click Analyze database.

If credentials are required for the database, the Connect dialog is displayed as follows, for example:

Shows the 'Connect' dialog of the 'Analyze database' function in Lucanet Consolidation & Financial Planning with the 'User name', 'Password', and 'Application' fields, and the 'Cancel' and 'Connect' buttons. — 'Connect' dialog

Enter the credentials, select the application, and click Connect (for more information, see Options in the 'Connect' Dialog).

The Analyze database dialog is displayed as follows, for example:

Shows the 'Analyze database' dialog in Lucanet Consolidation & Financial Planning with the 'Catalog' and 'Schema' fields and the 'Query' button. — 'Analyze database' dialog

If necessary, in the Catalog field, specify the catalog of the database to which the queries are to be restricted.

The catalog is taken from the connection, if a catalog is configured there.

If necessary, in the Schema field, specify the schema of the database to which the queries are to be restricted.

A database can contain several schemas, for example one schema per Lucanet.Financial Warehouse. The schema is taken from the connection, if a schema is configured there.

Click Query.

The query window is displayed as follows, for example:

Shows the empty query window of the 'Analyze database' function in Lucanet Consolidation & Financial Planning with the 'URL' line, the 'Records limit' field with the value 500, the empty 'SQL query' field, and the disabled 'Run query' button. The 'Results' area displays a note that a query must be run here. — Query window before the first query

Under URL, the database against which your queries are executed is displayed. The Records limit field contains the maximum number of records that are displayed in the Results area: 500.

The Connect dialog is displayed if credentials are required for the database. The following options are available in the dialog:

Option	Description
User name	The name of the database user with which the queries are executed. The user name is taken from the connection and can be changed.
Password	The password of the database user
Application	The execution target of the queries. Select one of the following options: - Execute on the server (default): for databases that are reachable from Lucanet.Cloud - Name of a Lucanet.Script Execution Application (SEA): for databases in your local network. One entry is displayed per SEA application that is created in your environment (see Installing and Updating Lucanet.Script Execution Application).

To run a query in Lucanet Consolidation & Financial Planning:

Specify the desired statement in the SQL query field.

Lucanet does not restrict the SQL statements. Which tables and data you can query does not depend on your permissions in Lucanet, but on the permissions of the database user that is configured in the connection.

For sample queries, see SQL Queries for Lucanet.Financial Warehouse.

Click Run query.

The result is displayed in the Results area below the field as follows, for example:

Shows the query window of the 'Analyze database' function in Lucanet Consolidation & Financial Planning with a SQL statement in the 'SQL query' field and the result below it. In the upper right, the 'Records limit' field with the value 500 is displayed. Above the result table, the number of records read is displayed. The row below the column headers displays the data type of each column. — Query window with the result of a statement

Above the result, the total number of records read is displayed. Below the column headers, the data type of each column is displayed.

The Records limit applies to the display only and cannot be changed. The query itself always runs to completion on the database, which is why the total number of records read can be higher than the number of records displayed.

If the statement cannot be executed, an error message is displayed in the Results area. The message comes from the database and can be a technical message.

Queries on very large tables can return several million records. Restrict the result set in the statement itself using the syntax of the target database, for example LIMIT in PostgreSQL or TOP in MS SQL Server.

In Lucanet Consolidation & Financial Planning, you can open multiple query windows at the same time and position them next to each other, so that you can compare the data of related tables side by side. Each window keeps its own connection, statement, and result. Opening a new window does not affect the windows that are already open.