TidyExecuteMdxScript
Execute an MDX script.
URL
/icCube/api/console/mdx/TidyExecuteMdxScript
Parameters
| Name | Type | Cardinality | Default value |
| schemaName | string | one | |
| script | string | one |
Return (Payload)
// -- An error or a set of MDX results ----------------------------
ScriptResult {
requestId: string;
error? : MdxError;
results? : MdxResult[];
}
// -- Error -------------------------------------------------------
MdxError {
errorCode : string;
errorMessage : string;
range? : MdxRange;
javaNativeErrors? : JavaNativeErrors;
}
MdxRange {
regions : MdxRangeRegion[];
}
MdxRangeRegion {
from : number /* 0-based */;
to : number /* 0-based: inclusive */;
lineNumber : number /* 1-based */;
}
JavaNativeErrors {
sourceCode : string;
errors : JavaNativeError[];
}
JavaNativeError {
lineNumber : number;
startPosition : number;
endPosition : number;
message : String;
}
// -- MDX Result --------------------------------------------------
MdxResult {
error? : MdxError;
dataSet? : MdxSelectTidyTable | MdxDrillthroughTidyTable | MdxInfoTidyTable;
}
TidyTable {
classID : string;
columns : COLUMN[];
rowCount : number;
tidyMaxRowCount : number;
tidyMaxRowCountReached : boolean;
}
TidyTableColumn {
classID : string;
name : string;
caption : string;
type : TidyTableColumnValueType:
typeParam? : TidyTableColumnValueType:
}
TidyTableColumnValueType = "UNKNOWN" | "NULL" | "CHARACTER" | "NUMERIC" | "LOGICAL" | "DATETIME" | "LIST" | "MIXED";
// Returned by regular MDX select statement.
MdxSelectTidyTable extends TidyTable {
classID : "MDX_TABLE";
query : Record /* a bunch of properties */;
axes : MdxSelectTidyTableAxis[];
infos : MdxSelectTidyTableMemberInfo[][] /* [axis-index][hierarchy-index] */;
}
MdxSelectTidyTableColumn extends TidyTableColumn {
axis : MdxSelectTidyTableColumnAxisInfo;
}
MdxSelectTidyTableColumnAxisInfo {
axis : number;
hierarchyIdx : number;
hierarchyInfo : MdxSelectTidyTableColumnHierarchyInfo;
// Available when all the members of the axis belongs to this level.
levelInfo? : MdxSelectTidyTableColumnLevelInfo;
}
MdxSelectTidyTableColumnHierarchyInfo {
caption : string;
name : string;
uniqueName : string;
type? : "date" | "time";
defaultMember? : MdxSelectTidyTableColumnMemberInfo;
}
MdxSelectTidyTableColumnLevelInfo {
caption : string;
name : string;
uniqueName : string;
type? : "date" | "time";
subType? : "YEAR" | "HALF_YEAR" | "QUARTER" | "MONTH" | "WEEK" | "DAY" | "DAY_MONTH" | "DAY_YEAR"
| "HOUR" | "HALF_HOUR" | "QUARTER_HOUR" | "MINUTE" | "SECOND" ;
}
MdxSelectTidyTableCellsColumn extends MdxSelectTidyTableColumn {
classID : "MDX_CELLS";
tupleIdx : number /* position in the axis 0 */;
fs? : string /* FORMAT_STRING of the corresponding member in the axis 0 */;
values : (any | null)[];
formattedValues? : (string | null)[];
errors? : (any | undefined)[];
}
MdxSelectTidyTableMembersColumn extends MdxSelectTidyTableColumn {
classID : "MDX_MEMBERS";
// Note that values are from the table.info[axis.axis][axis.hierarchyIdx].
}
MdxSelectTidyTableAxis {
asColumns : boolean /* the axis has been converted to tidy table columns (i.e., axis 0) */;
axis : number;
hierarchyCount : number;
length : number;
hasNonEmpty : boolean;
// Each MDX axis is converted into one or more members columns; each of these columns is using the same content.
// The tuple index for each row (a tuple is repeated because of other MDX axes creating a crossjoin).
//
// Aka. row-index-to-tuple-index.
content: number[];
}
// Actual content of the MDX axes.
MdxSelectTidyTableMemberInfo {
axis : number;
hierarchyIdx : number;
hasNonEmpty : boolean;
// The position of the member in the xyz (e.g., names) arrays for each index of the tuples in the axis.
//
// Aka. tuple-index-to-member-index.
//
// For example, retrieving the caption of the member for a given row index within a members column:
//
// tupleIdx = table.axes[column.axis.axis].content[rowIndex];
//
// members = table.infos[column.axis.axis][column.axis.hierarchyIdx];
// memberIdx = members.memberInfoIndices[tupleIdx];
//
// caption = column.captions[memberIdx];
memberInfoIndices : number[];
captions : string[];
names : string[];
uniqueNames : string[];
parentUniqueNames? : (string | null)[];
childrenCounts : number[];
levelCaptions : string[];
levelNames : string[];
levelUniqueNames : string[];
levelDepths : number[];
levelDepthsR : number[];
keys? : (any | null)[];
alls? : (boolean | null)[];
formatStrings? : (string | null)[];
colors? : (string | null)[];
ic3iso2? : (string | null)[];
ic3lat? : (number | null)[];
ic3long? : (number | null)[];
// DIMENSION PROPERTIES
dpNames? : string[];
dpTypes? : Record;
dpValues? : Record;
}
// Returned by MDX DRILLTHROUGH statement.
MdxDrillthroughTidyTable extends TidyTable {
classID : "MDX_DRILLTHROUGH_TABLE";
}
MdxDrillthroughTidyTableColumn extends TidyTableColumn {
classID : "MDX_DRILLTHROUGH_DATA";
values : any[];
}
// Returned by all other statement (e.g., CREATE SET...).
// Contains a single row table with three columns: type(string), details(string), on-error status(boolean).
MdxInfoTidyTable extends TidyTable {
classID : "MDX_INFO_TABLE";
}
MdxInfoTidyTableColumn extends TidyTableColumn {
classID : "MDX_INFO_DATA";
values : any[];
}
Description
This request execute an MDX script against a loaded schema.
Note that the execution of the script is ignoring the MDX result cache.
The script can contain one or more statements. A statement can be one of the following:
- an MDX select statement
- an MDX drillthrough statement
- a create or drop command statement
So on top of regular MDX statements (i.e., select from ...) a script can contain any statement (e.g., create/drop member ...) and ic3 commands (www).
On completion, the response contains either an error (e.g., more likely because the script could not be compiled/executed) or a list of results (one result for each statement of the script). Each result might be in turn either an error (e.g., an MDX select could not be performed because of an unknown member) or a dataset.
Note that multidimensional MDX results are converted to tidy tables. The MDX axis 0 is always converted into tidy table columns unless the query is a single axis query and is retrieving the cell_ordinal only. In that case, a tidy table with a single column (made of the axis 0 content) is being returned. For example, the following query is returning the list of countries in a single column:
SELECT
[Geography].[Geography].[Country] ON 0
FROM [Sales
CELL PROPERTIES CELL_ORDINAL
The format of the tidy table columns representing the members (vs cells) values is a bit complex because of potential cross-joins. The following query is showing a way to extract the required member information as a cell column. In this example, the names of the continent are retrieved as the first cell column:
WITH
MEMBER [Measures].[Name] AS [Geography].[Geography].currentMember.name
SELECT
{ [Measures].[Name], [Measures].[#Articles] } ON 0
[Geography].[Geography].[Continent] ON 1
FROM [Sales]
Please refer to Return (Payload) section above for a detailed description of the response. This description use a Typescript like notation.
As the cells columns might contain NaN, Infinity, -Infinity values, please use a JSON5 parser or the (Javascript) eval() method to extract the result.