Clausion Cloud export data

Description

Clausion data to Platform is provided by export schema tables and views.

Delivery method

All source data is transferred to Clausion Azure file storage automatically using Rest API or manually using Azure Storage Explorer. This will be done using the provided SAS-token.

Blob storage has IP restrictions option available.

RestApi example:

PUT https://xxxxstorageaacount.blob.core.windows.net/xxxxdataexport/ACT_2022.csv?

sp=racwdl&st=2020-12-01T11:25:16Z&se=2023-12-02T08:37:00Z&sv=2019-12-12&sr=c&sig=PN9OnfiM1McTLHfcebNjb%2FKl

HTTP/1.1

x-ms-date: {{\$datetime rfc1123}}

x-ms-blob-type: BlockBlob

x-ms-Content-Length: 20

Content-Type: text/text/csv

Red part includes the SAS-token (not a real token, only an example).

Azure storage explorer

Software can be downloaded from:

https://azure.microsoft.com/en-us/products/storage/storage-explorer/

After the software has been installed and opened. Create a connection by clicking the plug-symbol. After this, choose Blob Container.

Choose Connection method Shared access signature:

Add token url (provided separately). This URL is valid for x years. Token is highly confidential (includes both the address and password).

File can be found from Storage accounts -> Containers -> Blob containers -> xxxxdataexport:

File can be updated manually by delete, upload and download -functions.

DATASHARING-base-views

The OBDF export schema views provide a standardized data access layer for extracting Clausion data to external platforms and reporting systems. These views enable structured access to financial periods, accounts, dimensions, fact data, and voucher series, with optimized queries to support various use cases. They are intended for reporting, analytics, and data integration scenarios where Clausion data needs to be accessed outside the core application.

Time

View: export.FM_FINANCIAL_PERIODS

This view provides a structured representation of financial periods and their associated start and end times.

View Columns:

AccountData Views

View: export.FM_ACCOUNTS_DEFAULT_LAN

This view provides a list of base accounts with their default language text. It joins the FMACCKEY, FMACCKEYLAN, and FMLAN tables to provide a filtered result where the language is marked as default (LAN_DEFAULT = 1).

View Columns:

Notes:

This view is designed to filter accounts that are active (ACCKEY_STATUS = 1) and have a default language set.

View: export.FM_ACCOUNTSHIERARCHY

This view retrieves account hierarchy and related metadata for export purposes. It includes account details, language-specific names, data type information, and hierarchy structure. This is the preferred view to use!

View Columns:

Accounthierarchy lookup from export.accdepend

This select statement provides list of valid accounthierarcies.

select ACCHIERUDID from export.ACCDEPEND where LEVEL=0

DimensionsData

View: export.FM_DimNNHierDataType

Purpose:

Recommended view to extract DimensionData!

View represents the hierarchical structure of FPM units with attributes like hierarchy level, node number, (LBower, RBower) low/high boundary values, language specific attributes, with DataType(Actual,Budget,Forecast etc) connection filtering.

Note: Not all units are in use in all datatypes!

View Columns:

FactData

View: export.FM_FactDataExtra

View Columns:

View: export.FM_FactDataMain

Base Fact data. Use when You need to compare different datatypes ACT,EST or ACT,BUD… Single datatypes use VW_DATAMAIN_xxx views.

View Columns:

View: export.FM_FACTLEAFDATA

Overview

Consolidated, leaf‑level fact data across multiple datatypes (e.g. ACT, EST, ESV, TGT, …) with period metadata. This view UNION ALLs the per‑datatype VW_DATAMAIN_<DT> views (each already normalized) and enriches rows with start / end timestamps and quarter period code from FM_FINANCIAL_PERIODS.

When to Use

• Need a single dataset spanning several datatypes.
• Reporting / analytics that pivot or aggregate across ACT vs BUD/EST without separate joins
• If you only require one datatype for high‑volume queries, prefer the underlying VW_DATAMAIN_<DT> view.

Output Columns (superset inherited from VW_DATAMAIN_<DT> + period timing)

Example 1:

SELECT DATATYPE_CODE,

YEAR_CODE,

PERIOD_POSITION,

SUM(AMOUNTUNITPERIOD) AS PeriodAmount

FROM export.FM_FACTLEAFDATA

WHERE DATATYPE_CODE IN ('ACT','EST')

AND YEAR_CODE = 2024

GROUP BY DATATYPE_CODE, YEAR_CODE, PERIOD_POSITION;

Example 2: BEG, change and END balance

SELECT

l.YEAR_CODE

,l.VOUCHER_CODE

,l.DIM00UNIT_CODE

,l.ACCOUNT_CODE

,l.DATATYPE_CODE

,SUM(CASE WHEN l.PERIOD_POSITION=0 THEN l.AMOUNTUNITPERIOD ELSE 0 END) AS OPENBALANCE_UNITAMOUNT

,SUM(CASE WHEN l.PERIOD_POSITION=0 THEN l.AMOUNTGROUPPERIOD ELSE 0 END) AS OPENBALANCE_GROUPAMOUNT

,SUM(CASE WHEN l.PERIOD_POSITION=12 THEN l.AMOUNTUNITCUMULATIVE ELSE 0 END) - SUM(CASE WHEN l.PERIOD_POSITION=0 THEN l.AMOUNTUNITPERIOD ELSE 0 END) AS CHANGE_UNITAMOUNT

,SUM(CASE WHEN l.PERIOD_POSITION=12 THEN l.AMOUNTGROUPCUMULATIVE ELSE 0 END) - SUM(CASE WHEN l.PERIOD_POSITION=0 THEN l.AMOUNTGROUPPERIOD ELSE 0 END) AS CHANGE_GROUPAMOUNT

,SUM(CASE WHEN l.PERIOD_POSITION=12 THEN l.AMOUNTUNITCUMULATIVE ELSE 0 END) AS ENDBALANCE_UNITAMOUNT

,SUM(CASE WHEN l.PERIOD_POSITION=12 THEN l.AMOUNTGROUPCUMULATIVE ELSE 0 END) AS ENDBALANCE_GROUPAMOUNT

FROM

export.FM_FACTLEAFDATA l

WHERE 1=1

AND l.DATATYPE_CODE='ACT'

AND l.YEAR_CODE='2020'

GROUP BY l.YEAR_CODE

,l.VOUCHER_CODE

,l.DIM00UNIT_CODE

,l.ACCOUNT_CODE

,l.DATATYPE_CODE

GO;

Example 3:

;with data as(

SELECT

l.YEAR_CODE,

l.VOUCHER_CODE,

l.DIM00UNIT_CODE,

l.ACCOUNT_CODE,

SUM(iif(l.DATATYPE_CODE='ACT',l.AMOUNTUNITPERIOD,0)) AS act,

SUM(iif(l.DATATYPE_CODE='est',l.AMOUNTGROUPPERIOD,0)) AS est

FROM [export].[FM_FACTLEAFDATA] l

WHERE 1=1

and l.DATATYPE_CODE IN ('ACT','EST')

GROUP BY

l.YEAR_CODE,

l.VOUCHER_CODE,

l.DIM00UNIT_CODE,

l.ACCOUNT_CODE

)

select

YEAR_CODE,

VOUCHER_CODE,

DIM00UNIT_CODE,

ACCOUNT_CODE,

act as ACTUNITAMOUNT,

est AS ESTUNITAMOUNT,

ACT-EST DIFF

from data

View: export.VW_DATAMAIN_<DATATYPE> (Pattern for per-datatype period fact views)

Purpose

Provides a normalized (row-based) projection of periodized financial data for a single data type (e.g. ACT, BUD, FCT, PRO, EST, ...).

Use when need data only from singe Datatype, multiple datatypes(ACT,EST,BUD…) use FM_FACTDATALEAF.

Key Features

• Unpivots DATAMAIN_UNITAMOUNT0–DATAMAIN_UNITAMOUNT12 and DATAMAIN_GROUPAMOUNT0–DATAMAIN_GROUPAMOUNT12 into AMOUNTUNITPERIOD and AMOUNTGROUPPERIOD rows.
• Produces cumulative running totals (AMOUNTUNITCUMULATIVE, AMOUNTGROUPCUMULATIVE) using window functions partitioned by year, voucher, account, dimension, input type and position.
• Adds period metadata (PERIOD_CODE, PERIOD_POSITION, PERIOD_TYPE).
• Joins the datatype‑specific dimension table (FMDATADIM<DT>), exposing dimension unit codes (DIM00..DIM09 and CODIM00).
• Common structure across all datatypes; safe to UNION ALL (used by FM_FACTLEAFDATA).

Output Columns

Performance Notes

• Window functions (SUM OVER ...) compute running totals efficiently.
• Controlled CROSS JOIN with a filtered period set (RestrictedFinper) avoids scanning irrelevant period types.
• Consistent column ordering enables set-based UNION operations without reshaping.

Document / Voucher Series

View: export.FM_DOCUMENTSERIES

Overview (Document Series)

Exports document (voucher) series information with language‑specific details, limits and classification codes for each series. Designed to provide a unified lookup of series configuration for reporting and validation.

Output Columns

Voucher Type Mapping

Export integration tools

• MS Azure Data Factory
• MS Azure Logic Apps
• SQL- query language