Configuration Tips - Data Source, Datamart

Warning on PA SQL queries

Think twice before using PA SQL Query API. Always ensure that you have designed your tables properly, to be well suited for solution of the problem before you start doing heavy PA SQLs on them. No special query will help, if your tables design does not fit the solution of the problem.

Advanced Data Source Queries

Querying saved/cached Datamart query

When querying the "saved/cached query", you are querying a different database (particularly the H2 engine which is used to store in-memory data), so you must use different "select projection expressions". Specifically, for example, if you need to do COUNT DISTINCT, then you need to write COUNT (DISTINCT column_name), but when querying Datamart, you use Datamart Expressions, and write COUNTDISTINCT(col_name).

Query a Datamart

Datamart query expressions are valid ONLY for direct Datamart queries, not for cached (Table Context) tables queries. For cached tables we use the H2 database which has a slightly different syntax.

Datamart Query Expressions

Date functions do not use data from cal Data Source

These functions (getWeek(), ...) always return values from Gregorian calendar and are always formatted in the default built-in way. In other words, it does NOT use data from cal Data Source. If you need the actual data from the cal table, you need to make a query to that Data Source.

Get Datamart Time Dimension from a Date

New Datamart Refresh behavior

When truncateDatamartOnNonIncRefresh is set to true, the solution here is to do the Truncate and Refresh in one and the same DB transaction, and let this be driven by the Data Load's incremental mode setting.

Advanced Configuration Options

Money fields values

Money fields are recalculated to Total values. Because of this behavior, the numbers you’re seeing in the same Money column in an underlying Data Source and in Datamart could be different.

Data Source Queries

Automatic currency conversion

Automatic currency conversion works only for Datamart, and NOT for Data Source tables. If you query a Data Source, where each line has a different currency symbol, no automatic conversion is used and if you use aggregation function, you will end up with mixed-up wrong results.

Data Source Queries