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 query 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

Money and Number fields extended values

Money and Number fields with “Per Unit” measure will be multiplied by the quantity field with “Per Unit Basis” function when querying the datamart. This applies only to datamarts.

Data Source Queries

Automatic Currency conversion

Money fields' values will be automatically converted from the Data Source Currency to the Datamart target Currency. It is expected that for each data source having fields of Money type that one field of Currency type is also present and added to the Datamart to enable the automatic conversion.

Set up Currency Conversion in Datamart

Blank Money values in DM due to missing or bad currency conversion

• Ensure a column of type Currency exists for each Datasource with Money Fields

• All from and to combinations, including same currency code, exist in DMDS.ccy

• Make sure Price/Cost/Margin/Discount fields are of Money type

Wrong totals due to missing or bad quantity multiplication

• Make sure a column of type Quantity exists, its Function is set to “Per Unit Basis”

• Numeric and Money fields must be explicitly configured as Per Unit or Extended

SI/CI Dashboards and Agent calculations are run in different currencies and units of measure than the ones expected by the customer

• Make sure Base Currency and UOM are set based on the customer location

• Sometimes, you might need to parameterize the target Currency and UOM in the datamart query itself

Year, Month and Week dimensions are left blank

• Ensure the Calendar datasource has the appropriate mappings to Year, Month and Week dimensions for all dates