Appendix

SQL Grammar in AnalytiX-BI Server

Below is the current SQL grammar implemented in AnalytiX-BI Server, following Transact-SQL Syntax Conventions (Transact-SQL).

Be sure to read the Additional Notes below the SQL grammar examples below.

<query> ::= { <select_query> | <pivot_query> }

<select_query> ::= SELECT [ DISTINCT ] [ TOP n ] <select_list> [ FROM <table_list> ] [ WHERE <search_condition> ] [ HAVING <search_condition> ] [ ORDER BY <order_by_expression> ]

<select_list> ::= { <aliased_column> | <explicit_expression> } [,…n]

<aliased_column> ::= <column_source> [ AS <identifier> ]

<column_source> ::= { <aggregate_column> | <qualified_identifier> }

<aggregate_column> ::= <aggregate_name> ( <qualified_identifier> )

<qualified_identifier> ::= <identifier> . <identifier>

<explicit_expression> ::= ‘<free_literal>’AS <identifier>

<table_list> ::= <qualified_identifier> [ { INNER JOIN <qualified_identifier> ON <qualified_identifier> = <qualified_identifier> } [,…n] ]

<search_condition> ::= <binary_expression>

<binary_expression> ::= <expression> <binary_operator> <expression>

<binary_operator> ::= AND | OR | IN | LIKE | NOT LIKE | = | > | < | >= | <= | <> | !=

<constants_list> ::= { <string_literal> | <number> } [,…n]

<string_literal> ::= ‘<string>’

<order_by_expression> ::= { { <column_source> | <identifier> } [ ASC | DESC ] } [,…n]

<pivot_query> ::= PIVOT <aggregate_column> FOR <qualified_identifier> IN ( <pivot_column_list> ) [ GROUP BY <group_by_expression> ] [ ORDER BY <pivot_order_by_expression> ]

<pivot_column_list> ::= { <string_literal> [ AS <identifier> ] } [,…n]

<group_by_expression> ::= <qualified_identifier> [,…n]

<pivot_order_by_expression> ::= { <qualified_identifier> [ ASC | DESC ] } [,…n]

Additional Notes

The above grammar specifies the SQL syntax only. All points (which includes view definitions) must be specified with the following format (still using the pseudo-TSQL syntax as above):

bi: Models [ /<identifier> [,…n] ] : <identifier> ( <query> )

Identifiers that contain reserved symbols (spaces, commas, periods, etc.) need to be enclosed in square brackets. For example:

bi:Models:Northwind(SELECT [Order Details].OrderID)
bi:Models:Northwind(SELECT Orders.[OrderDate.Year])