Jedox 3rd Party Access/MDX: Supported Statements

image_pdfimage_print

SELECT statement

The SELECT statement is the most important statement used by the MDXInterpreter.

<select_statement> :: [WITH <formula_specification>] 
     SELECT [<axis_specification> 
          [, <axis_specification>...]] 
     FROM [<cube_specification>] 
     [WHERE [<slicer_specification>]] [<cell_props>]

The parts of the SELECT statement are defined as follows:

a) “formula_specification” expression
<formula_specification> :: <single_formula_specification>
     [<single_formula_specification>...]
 <single_formula_specification> :: <member_specification>
     | <set_specification>
<member_specification> :: MEMBER <member_name> AS <value_expression>
     [, <solve_order_specification>]
     [, <member_property_definition>...]
          MEMBER member_name AS set_value_expression
 <set_specification> :: SET <set_name> AS <set>

Set functions can be used in member specifications, and member functions can be used in set specification if the function call is between brackets.

b) “axis_specifications” expression
<axis_specifications> :: <axis_specification>
     [ ,<axis_specification>...]
 <axis_specification> :: [NON EMPTY] <set> [<dim_props>] ON <axis_name>

The SELECT statement supports only set functions that return a set, or set and member functions that return a set on axis specifications.

<value_expression> :: <numeric_value_expression>
     | <string_value_expression>
<numeric_value_expression> :: <term>
     | <numeric_value_expression> {<plus> | <minus>} <term>
<term> :: <factor> | <term> {<asterisk> | <solidus>} <factor>
<factor> :: [<sign>] <numeric_primary>
<numeric_primary> :: <value_expression_primary>
     | <numeric_value_function>
<value_expression_primary> :: <unsigned_numeric_literal>
     | (<value_expression>)
     | <character_string_literal>
          | <tuple>
          | <tuple>.VALUE
          | < conditional_expression >
<string_value_expression> :: <value_expression_primary>
     | <string_value_expression>
       <concatenation_operator>
       <value_expression_primary>
     | <conditional_expression>
<cube_specification> :: [<cube_name> [,<cube_name>...]]
<slicer_specification> :: <set> | <tuple>
<cell_props> :: [CELL] PROPERTIES <cell_property> [, <cell_property>...]

Example:

WITH MEMBER [Measures].[one] AS '[Measures].[Units]*10'
SELECT AddCalculatedMembers({[Measures].[Units]}) ON COLUMNS
FROM [Orders]
WITH MEMBER [measures].cchildren as 'addcalculatedmembers([products].currentmember.children).count'
SET filteredmembers as '{[products].[all products].[monitors],[products].[all products].[portable pc''s],[products].[all products].[stationary pc''s]}'
SELECT {[measures].cchildren} on rows,
hierarchize(generate(filteredmembers, ascendants([products].currentmember))) dimension properties parent_unique_name, member_type on columns
FROM [Sales]

UPDATE statement

<update_statement>:: UPDATE [CUBE] Cube_Name SET <update clause>
     [, <update clause> ...n] [allocation_method ]
<update_clause> :: <tuple> = <New_Value>
New_Value =term
<allocation_method> :: NO_ALLOCATION
     | USE_EQUAL_ALLOCATION

Example:

UPDATE CUBE [Sales] SET ([Measures].[units], [Products].[All Products].[Stationary PC's], [Regions].[Europe].[West].[France] ) = 100

 

ALTER DIMENSION statement
<alter_cube_statement>:: ALTER CUBE Cube_Name <alter_statemet> 
     [, <alter_statement> ] ...n]
<alter_statement > :: < create_dimension_statement > 
     | < move_dimension_statement> 
     | < remove_dimension_statement > 
     | < update_dimension_statement >
<create_dimension_statement> :: = CREATE DIMENSION MEMBER <identifier> 
     KEY = <member>
<move_dimension_statement> :: MOVE DIMENSION MEMBER <member> 
     [ WITH DESCENDANTS] UNDER <member>
<remove_dimension_statement> :: DROP DIMENSION MEMBER <member> 
     [ WITH DESCENDANTS]
<update_dimension_statement> :: UPDATE DIMENSION MEMBER <member> 
     [ AS <value_expression> ]

 

Example:

To see how these statements work we will do the following operations:

Create member [Products].[All Products].[test]
Create member [Products].[All Products].[test1]
Create member [Products].[All Products].[test].[child]
Create member [Products].[All Products].[test].[child1]
Create member [Products].[All Products].[test].[child].[child_1]
Delete member [Products].[All Products].test.child1
Move member: [Products].[All Products].test.child with descendants under [Products].[All Products].test1
Move member: [Products].[All Products].test1.child.child_1 under [Products].[All Products].test
Delete [Products].[All Products].test with descendants
ALTER CUBE sales create DIMENSION MEMBER test key = [Products].[All Products]
ALTER CUBE sales create DIMENSION MEMBER test1 key = [Products].[All Products]
ALTER CUBE sales create DIMENSION MEMBER child key = [Products].[All Products].test
ALTER CUBE sales create DIMENSION MEMBER child1 key = [Products].[All Products].test
ALTER CUBE sales create DIMENSION MEMBER child_1 key = [Products].[All Products].test.child
ALTER CUBE sales drop DIMENSION MEMBER [Products].[All Products].test.child1
ALTER CUBE sales move DIMENSION MEMBER [Products].[All Products].test.child with descendants under [Products].[All Products].test1
ALTER CUBE sales move DIMENSION MEMBER [Products].[All Products].test1.child.child_1 under [Products].[All Products].test
ALTER CUBE sales drop DIMENSION MEMBER [Products].[All Products].test with descendants
image_pdfimage_print
Was this post helpful?
NoYes (No Ratings Yet)
Loading...