Hyperion MDX Provider Release 7 API Function Reference and Example |
This document describes the Essbase API functions specifically built to deal with MDX manipulations.
This file contains the following sections:
MDX queries that follow the grammar given in the MDX functional specification can be submitted to the server through the MDX-API. The query results can then be retrieved by the client using this API.
The grammar of Essbase MDX statements is covered in the MaxL Data Manipulation Language (MaxL DML) section of the Essbase Technical Reference.
A few basic MDX concepts and terminology are reviewed here. An MDX query consists of several axis specifications, and an optional slicer specification. Each axis specifies a set-valued expression. A set is an ordered collection of tuples, with tuples being a sequence of members from one or more dimensions. Tuples in a set are homogeneous in dimensionality (each tuple has members from the same dimensions in the same order).
An example of a set expression based on the Sample Basic database is:
Union( CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}), Union( CrossJoin([Total Expenses].Children, {[Actual]}), {([Opening Inventory], [Variance]), ([Additions], [Variance %])} ) )
This expression uses several MDX functions: Union, CrossJoin, Children. The value of this expression is the set:
{ ([Sales], [Actual]), ([Sales], [Budget]), ([Profit], [Actual]), ([Profit], [Budget]), ([Marketing], [Actual]), ([Payroll], [Actual]), ([Misc], [Actual]), ([Opening Inventory], [Variance]), ([Additions], [Variance %]) }
Note that in the result of the CrossJoin, the tuples are ordered so that the first dimension changes slowest. The tuples in this set have the dimensionality: ([Measures],[Scenario]). The dimensionality of tuples across axis sets must not overlap.
In addition to the set expression, each axis specifies the name of the axis (COLUMNS, ROWS, PAGES, etc.) or the axis number (AXIS(0), AXIS(1), etc.). The cube consisting of all possible combinations of tuples, one from each axis, constitutes the result of the query. Dimensions that are not present in any axis and in the slicer default to having their root member included in defining the result cube. The slicer, if present, specifies a set, with a single tuple, which identifies the members of interest along the respective dimensions. This makes the final result a slice of the cube created from the axes. The result of an MDX query contains the metadata about each axis and the slicer, as well as the data values in the cells in the result cube.
Here is a complete MDX query:
SELECT Union( CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}), Union( CrossJoin([Total Expenses].Children, {[Actual]}), {([Opening Inventory], [Variance]), ([Additions], [Variance %])} ) ) ON COLUMNS, CrossJoin( [200].Children, {[East], [West]} ) ON ROWS FROM Sample.Basic WHERE {[Jan]}
The result of this query has 9 tuples on the column axis and 8 tuples on the row axis, which means there are 72 cells in all. Each cell has an ordinal, or offset, which depends on the position of its tuples along each axis. Offsets and positions start at 0. The cells are ordered so that the first axis position changes the fastest.
For example, the cell identified by tuple 3 in the column axis and tuple 4 in the row axis is at offset 3 + 9*4 = 39.
The concept of clusters is needed for reasons of efficiency. A set can be considered to be an ordered collection of tuples, or it can be considered to be an ordered collection of clusters. A cluster is a collection of tuples that involve all possible combinations of certain members from each of the set's dimensions. The tuples need to ordered in the same manner as in the output of the CrossJoin function (the first dimension changes the slowest). Use of the CrossJoin function causes clusters to be created, but the server may determine clusters from the results of other functions as well.
The C API for MDX query processing is designed to fit in with the existing Essbase APIs. Client programs are given handles to various structures internal to the API, and use methods to access their components. The number of functions is kept small by judicious combining of output results normally needed together. Except where noted, memory allocated by the API for its internal structures is freed when the client invokes the query free function. ESS_MDX is the prefix used for the handle types, and EssMdx is the prefix used for the functions introduced by the MDX-API.
The type definitions are as follows:
typedef void *ESS_MDX_QRYHDL_T; /* MDX query handle */ typedef unsigned long ESS_MDX_MEMBERIDTYPE_T; /* MDX mbr id type */ typedef void *ESS_MDX_AXISHDL_T; /* MDX axis handle */ typedef void *ESS_MDX_DIMHDL_T; /* MDX dim handle */ typedef unsigned long ESS_MDX_PROPTYPE_T; /* MDX property type */ typedef void *ESS_MDX_PROPHDL_T; /* MDX property handle */ typedef void *ESS_MDX_CLUSTERHDL_T; /* MDX cluster handle */ typedef void *ESS_MDX_MBRHDL_T; /* MDX mbr handle */ typedef void *ESS_MDX_CELLHDL_T; /* MDX cell handle */ typedef unsigned long ESS_MDX_CELLSTATUS_T; /* MDX cell status */
The constant definitions are as follows:
/* MDX member identifier types (ESS_MDX_MEMBERIDTYPE_T) */ #define ESS_MDX_MEMBERIDTYPE_NAME 8 #define ESS_MDX_MEMBERIDTYPE_ALIAS 16 /* MDX property value types (ESS_MDX_PROPTYPE_T) */ #define ESS_MDX_PROPTYPE_BOOL ESS_DT_BOOL #define ESS_MDX_PROPTYPE_DOUBLE ESS_DT_DOUBLE #define ESS_MDX_PROPTYPE_DATETIME ESS_DT_DATETIME #define ESS_MDX_PROPTYPE_STRING ESS_DT_STRING #define ESS_MDX_PROPTYPE_ULONG ESS_DT_ULONG #define ESS_MDX_PROPTYPE_NONE 0 /* MDX cell status bitmasks (ESS_MDX_CELLSTATUS_T) */ #define ESS_MDX_CELLSTATUS_LINKEDOBJS 0x00000001 #define ESS_MDX_CELLSTATUS_DYNCALC 0x00000002 #define ESS_MDX_CELLSTATUS_CALCEDMBR 0x00000004 #define ESS_MDX_CELLSTATUS_READONLY 0x00000008
ESS_MDX_PROPVALUE_T
typedef struct ess_mdx_propvalue_t { ESS_MDX_PROPTYPE_T ulPropType; /* ESS_MDX_PROPTYPE_XXXX */ union { ESS_BOOL_T bData; /* Boolean value */ ESS_ULONG_T ulData; /* Ulong value */ ESS_STR_T strData; /* String value */ ESS_DATETIME_T dtData; /* Datetime value */ ESS_DOUBLE_T dblData; /* Double value */ } value; } ESS_MDX_PROPVALUE_T;
Executes the specified query on the currently connected database.
Syntax
ESS_FUNC_M EssMdxExecuteQuery(ESS_MDX_QRYHDL_T hQry);
hQry | input | Query handle |
Returns information about the axes in the query.
After EssMdxExecuteQuery(), use EssMdxGetAxes(), EssMdxGetAxisInfo(), and EssMdxGetDimInfo() to obtain information on the nature of the axes in the submitted query.
Syntax
ESS_FUNC_M EssMdxGetAxes(ESS_MDX_QRYHDL_T hQry, ESS_PULONG_T pulNAxes, ESS_MDX_PPAXISHDL_T pphAxes, ESS_MDX_PAXISHDL_T phSlicer);
hQry | input | Query handle |
pulNAxes | output | Number of axes |
pphAxes | output | Array of axis handles |
phSlicer | output | Slicer axis handle |
Returns information about the specified axis.
After EssMdxExecuteQuery(), use EssMdxGetAxes(), EssMdxGetAxisInfo(), and EssMdxGetDimInfo() to obtain information on the nature of the axes in the submitted query.
Syntax
ESS_FUNC_M EssMdxGetAxisInfo(ESS_MDX_AXISHDL_T hAxis, ESS_PULONG_T pulSize, ESS_PULONG_T pulNDims, ESS_MDX_PPDIMHDL_T pphDims);
hAxis | input | Axis handle |
pulSize | output | Number of tuples in axis |
pulNDims | output | Number of dimensions in axis |
pphDims | output | Array of dimension handles |
Returns the clusters within the specified axis.
Obtain information about the contents of an axis set using :
Syntax
ESS_FUNC_M EssMdxGetClusters(ESS_MDX_AXISHDL_T hAxis, ESS_PULONG_T pulNClusters, ESS_MDX_PPCLUSTERHDL_T pphClusters);
hAxis | input | Axis handle |
pulNClusters | output | Number of clusters |
pphClusters | output | Array of cluster handles |
Returns information about the specified cluster.
Obtain information about the contents of an axis set using :
Syntax
ESS_FUNC_M EssMdxGetClusterInfo(ESS_MDX_CLUSTERHDL_T hCluster, ESS_PULONG_T pulSize, ESS_PULONG_T pulNDims, ESS_PPULONG_T ppulDimSizes);
hCluster | input | Cluster handle |
pulSize | output | Number of tuples in cluster |
pulNDims | output | Number of dimensions in cluster (same as that in the axis that this cluster belongs to) |
ppulDimSizes | output | Array of dimension sizes (number of members) |
Returns the tuple at the specified position within the given cluster.
Note: The client should use EssFree() when done with pphMbrs.
Obtain information about the contents of an axis set using :
Syntax
ESS_FUNC_M EssMdxGetClusterMembers(ESS_MDX_CLUSTERHDL_T hCluster, ESS_ULONG_T ulIndex, ESS_MDX_PPMBRHDL_T pphMbrs);
hCluster | input | Cluster handle |
ulIndex | input | Tuple position within cluster (first dimension changes slowest) |
pphMbrs | output | Array of member handles for the tuple |
Returns the tuple at the specified position in the given axis. Use this function to directly retrieve a particular tuple from an axis.
Note: The client should use EssFree() when done with pphMbrs.
Obtain information about the contents of an axis set using :
Syntax
ESS_FUNC_M EssMdxGetAxisMembers(ESS_MDX_AXISHDL_T hAxis, ESS_ULONG_T ulIndex, ESS_MDX_PPMBRHDL_T pphMbrs);
hAxis | input | Axis handle |
ulIndex | input | Tuple position within axis |
pphMbrs | output | Array of member handles for tuple |
Returns the cell at the specified offset.
Obtain the cell values in the cube formed from the axes in the query using :
Syntax
ESS_FUNC_M EssMdxGetCellAtOffset(ESS_MDX_QRYHDL_T hQry, ESS_ULONG_T ulOffset, ESS_MDX_PCELLHDL_T phCell);
hQry | input | Query handle |
ulOffset | input | Cell offset (first axis changes fastest) |
phCell | input | Cell handle |
Returns the cell at the intersection of the specified tuple indices.
Obtain the cell values in the cube formed from the axes in the query using :
Syntax
ESS_FUNC_M EssMdxGetCellAtIndices(ESS_MDX_QRYHDL_T hQry, ESS_PULONG_T pulIndices, ESS_MDX_PCELLHDL_T phCell);
hQry | input | Query handle |
pulIndices | input | Tuple indices, one for each axis |
phCell | output | Cell handle |
Returns the specified cell's value.
Obtain the cell values in the cube formed from the axes in the query using :
Syntax
ESS_FUNC_M EssMdxGetValue( ESS_MDX_CELLHDL_T hCell, ESS_PBOOL_T pbIsMissing, ESS_PBOOL_T pbNoAccess, ESS_PDOUBLE_T pdValue );
hCell | input | Cell handle |
pbIsMissing | output | Whether cell value is #Missing |
pbNoAccess | output | Whether cell value is #NoAccess |
pdValue | output | The cell's value, if not #Missing |
Frees memory used for the specified query.
Syntax
ESS_FUNC_M EssMdxFreeQuery(ESS_MDX_QRYHDL_T hQry);
hQry | input | Query handle |
Takes the MDX query specified by pszQry and returns a query handle.
Syntax
ESS_FUNC_M EssMdxNewQuery( ESS_HCTX_T hCtx, ESS_STR_T pszQry, ESS_MDX_PQRYHDL_T phQry );
hCtx | input | API context handle |
pszQry | input | Query text |
phQry | output | Query handle |
Sets the type of member identifier desired in the result. Defaults to ESS_MDX_MEMBERIDTYPE_NAME.
Syntax
ESS_FUNC_M EssMdxSetMbrIdType( ESS_MDX_QRYHDL_T hQry, ESS_MDX_MEMBERIDTYPE_T mbrIdType );
hQry | input | Query handle |
mbrIdType | input | Member identifier desired (name/alias): - ESS_MDX_MEMBERIDTYPE_NAME - ESS_MDX_MEMBERIDTYPE_ALIAS |
Returns information about the specified dimension, including the properties available for members in this dimension.
Syntax
ESS_FUNC_M EssMdxGetDimInfo( ESS_MDX_DIMHDL_T hDim, ESS_PSTR_T ppszName, ESS_PULONG_T pulNProps, ESS_MDX_PPPROPHDL_T pphProps );
hDim | input | Dimension handle |
ppszDimName | output | Dimension name |
pulNProps | output | Number of properties returned |
pphProps | output | Array of property handles |
Returns information about the specified property.
Syntax
ESS_FUNC_M EssMdxGetPropertyInfo( ESS_MDX_PROPHDL_T hProp, ESS_PSTR_T ppszName, ESS_MDX_PPROPTYPE_T pPropType );
hProp | input | Property handle |
ppszName | output | Property name |
pPropType | output | Property type: - ESS_MDX_PROPTYPE_BOOL - ESS_MDX_PROPTYPE_DOUBLE - ESS_MDX_PROPTYPE_STRING - ESS_MDX_PROPTYPE_DATETIME - ESS_MDX_PROPTYPE_ULONG |
Returns the identifier for the specified member.
Syntax
ESS_FUNC_M EssMdxGetMbrIdentifier( ESS_MDX_MBRHDL_T hMbr, ESS_PSTR_T ppszIdentifier );
hMbr | input | Member handle |
ppszIdentifier | output | Member identifier (name or alias) |
Returns the value of the specified property for the specified member. The property value will have a type of ESS_MDX_PROPTYPE_NONE if the property is not applicable to the member.
Syntax
ESS_FUNC_M EssMdxGetMbrProperty( ESS_MDX_MBRHDL_T hMbr, ESS_MDX_PROPHDL_T hProp, ESS_MDX_PPROPVALUE_T pPropValue );
hMbr | input | Member handle |
hProp | input | Property handle |
pPropValue | output | Property value |
Turns on a query execution mode in which cell data are not retrieved. EssMdxGetCellAtOffset() and EssMdxGetCellAtIndices() should not be called for the query. The default is to retrieve cell data.
Syntax
ESS_FUNC_M EssMdxSetDataLess( ESS_MDX_QRYHDL_T hQry );
hQry | input | Query handle |
Turns on retrieval of cell status information. By default the cell status information is not retrieved.
Syntax
ESS_FUNC_M EssMdxSetNeedCellStatus( ESS_MDX_QRYHDL_T hQry );
hQry | input | Query handle |
Returns the member handles for the specified dimension within the given cluster.
Syntax
ESS_FUNC_M EssMdxGetClusterDimMembers( ESS_MDX_CLUSTERHDL_T hCluster, ESS_ULONG_T ulIndex, ESS_MDX_PPMBRHDL_T pphMbrs );
hCluster | input | Cluster handle |
ulIndex | input | Dimension index within axis containing cluster |
pphMbrs | output | Array of member handles for the specified dimension |
Returns the status of the cell specified by hCell. The status can be tested against the bitmasks in pulStatus to determine whether the cell is of the corresponding type. This function should be called only after an earlier call to EssMdxSetNeedCellStatus().
Syntax
ESS_FUNC_M EssMdxGetCellStatus( ESS_MDX_QRYHDL_T hQry, ESS_MDX_CELLHDL_T hCell, ESS_MDX_PCELLSTATUS_T pulStatus );
hQry | input | Query handle |
hCell | input | Cell handle |
pulStatus | output | Cell status: Bitmap with the following masks: - ESS_MDX_CELLSTATUS_LINKEDOBJS - ESS_MDX_CELLSTATUS_DYNCALC - ESS_MDX_CELLSTATUS_CALCEDMBR - ESS_MDX_CELLSTATUS_READONLY |
#if defined _WIN32 || defined _WINDOWS #include <windows.h> #endif #include <string.h> #include <stdio.h> #include <ctype.h> #include <stdlib.h> #include <assert.h> #include <time.h> #if defined _WIN32 || defined _WINDOWS #pragma pack(push,localid,1) #endif #include <essapi.h> #if defined _WIN32 || defined _WINDOWS #pragma pack(pop,localid) #endif ESS_HINST_T hInst; ESS_HCTX_T hCtx; #define MAXQRYLEN 65536 ESS_CHAR_T qry[MAXQRYLEN]; ESS_STR_T AppName = "Sample"; ESS_STR_T DbName = "Basic"; static ESS_CHAR_T *axisnames[] = { "COLUMNS", "ROWS", "PAGES", "CHAPTERS", "SECTIONS" }; void ESS_Init() { ESS_STS_T sts; ESS_INIT_T InitStruct = {ESS_API_VERSION, NULL, 0L, 255, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0L }; if ((sts = EssInit(&InitStruct, &hInst)) != ESS_STS_NOERR) { printf("EssInit failure: %ld\n", sts); exit ((int) sts); } printf("EssInit sts: %ld\n", sts); } void ESS_Login () { ESS_STS_T sts = ESS_STS_NOERR; ESS_USHORT_T Items; ESS_PAPPDB_T pAppsDbs = NULL; ESS_CHAR_T SvrName[ESS_SVRNAMELEN]; ESS_CHAR_T UserName[ESS_USERNAMELEN]; ESS_CHAR_T Password[ESS_PASSWORDLEN]; /* Initialize parameters */ strcpy(SvrName,"localhost"); strcpy(UserName,"essexer"); strcpy(Password,"password"); sts = EssLogin(hInst, SvrName, UserName, Password, &Items, &pAppsDbs, &hCtx); if ( (sts != 0) && (sts != 1051093L) && (sts != 1051090L) ) { printf("EssLogin failure: %ld\n", sts); exit ((int) sts); } printf("EssLogin sts: %ld\n", sts); } void ESS_MdxAxis(ESS_MDX_QRYHDL_T hQry, ESS_MDX_AXISHDL_T hAxis, ESS_STR_T pszAxisName ) { ESS_STS_T sts; ESS_ULONG_T ulNAxisDims, ulAxisSize; ESS_ULONG_T ulNClusters, ulClusterSize, ulNClusterDims; ESS_ULONG_T ulAxisDimCnt, ulIndex, ulPropCnt; ESS_ULONG_T ulClusterCnt, ulClusterDimCnt; ESS_PULONG_T ulaDimSizes; ESS_MDX_PCLUSTERHDL_T haClusters; ESS_MDX_CLUSTERHDL_T hCluster; ESS_MDX_PDIMHDL_T haDims; ESS_STR_T pszDimName, pszMbrIdentifier, pszPropName; ESS_MDX_PMBRHDL_T haMbrs; ESS_PULONG_T ulaNProps = NULL; ESS_MDX_PPPROPHDL_T haaProps = NULL; ESS_MDX_PPROPHDL_T haProps; ESS_MDX_PROPHDL_T hProp; ESS_MDX_PROPTYPE_T propType; ESS_MDX_PROPVALUE_T propval; if ((sts = EssMdxGetAxisInfo(hAxis, &ulAxisSize, &ulNAxisDims, &haDims)) != ESS_STS_NOERR) { printf("EssMdxGetAxisInfo failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetAxisInfo sts: %ld\n", sts); printf("%s Size %ld Num dims %ld\n", pszAxisName, ulAxisSize, ulNAxisDims); if (ulAxisSize == 0) { return; } if ((sts = EssAlloc(hInst, ulNAxisDims * sizeof(ESS_ULONG_T), (ESS_PPVOID_T) &ulaNProps)) != ESS_STS_NOERR) { printf("EssAlloc failure: %ld\n", sts); exit ((int) sts); } printf("EssAlloc sts: %ld\n", sts); if ((sts = EssAlloc(hInst, ulNAxisDims * sizeof(ESS_MDX_PPROPHDL_T), (ESS_PPVOID_T) &haaProps)) != ESS_STS_NOERR) { printf("EssAlloc failure: %ld\n", sts); exit ((int) sts); } printf("EssAlloc sts: %ld\n", sts); for (ulAxisDimCnt = 0; ulAxisDimCnt < ulNAxisDims; ulAxisDimCnt++) { if ((sts = EssMdxGetDimInfo(haDims[ulAxisDimCnt], &pszDimName, &ulaNProps[ulAxisDimCnt], &haaProps[ulAxisDimCnt])) != ESS_STS_NOERR) { printf("EssMdxGetDimInfo failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetDimInfo sts: %ld\n", sts); printf("Dim %ld name %s #props %ld\n", ulAxisDimCnt, pszDimName, ulaNProps[ulAxisDimCnt]); haProps = haaProps[ulAxisDimCnt]; for (ulPropCnt = 0; ulPropCnt < ulaNProps[ulAxisDimCnt]; ulPropCnt++) { hProp = haProps[ulPropCnt]; if ((sts = EssMdxGetPropertyInfo(hProp, &pszPropName, &propType)) != ESS_STS_NOERR) { printf("EssMdxGetPropertyInfo failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetPropertyInfo sts: %ld\n", sts); printf("Property %ld type %ld name %s\n", ulPropCnt, propType, pszPropName); } } if ((sts = EssMdxGetClusters(hAxis, &ulNClusters, &haClusters)) != ESS_STS_NOERR) { printf("EssMdxGetClusters failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetClusters sts: %ld\n", sts); printf("Num clusters %ld\n", ulNClusters); for (ulClusterCnt = 0; ulClusterCnt < ulNClusters; ulClusterCnt++) { hCluster = haClusters[ulClusterCnt]; if ((sts = EssMdxGetClusterInfo(hCluster, &ulClusterSize, &ulNClusterDims, &ulaDimSizes)) != ESS_STS_NOERR) { printf("EssMdxGetClusterInfo failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetClusterInfo sts: %ld\n", sts); printf("Cluster %ld Size %ld\n", ulClusterCnt, ulClusterSize); for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims; ulClusterDimCnt++) { printf("Cluster Dim %ld Size %ld\n", ulClusterDimCnt, ulaDimSizes[ulClusterDimCnt]); } for (ulIndex = 0; ulIndex < ulClusterSize; ulIndex++) { if ((sts = EssMdxGetClusterMembers(hCluster, ulIndex, &haMbrs)) != ESS_STS_NOERR) { printf("EssMdxGetClusterMembers failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetClusterMembers sts: %ld\n", sts); for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims; ulClusterDimCnt++) { if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulClusterDimCnt], &pszMbrIdentifier)) != ESS_STS_NOERR) { printf("EssMdxGetMbrIdentifier failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetMbrIdentifier sts: %ld\n", sts); printf("Mbr %ld identifier %s\n", ulClusterDimCnt, pszMbrIdentifier); haProps = haaProps[ulClusterDimCnt]; for (ulPropCnt = 0; ulPropCnt < ulaNProps[ulClusterDimCnt]; ulPropCnt++) { if ((sts = EssMdxGetMbrProperty(haMbrs[ulClusterDimCnt], haProps[ulPropCnt], &propval)) != ESS_STS_NOERR) { printf("EssMdxGetMbrProperty failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetMbrProperty sts: %ld\n", sts); printf("Property %ld Type ", ulPropCnt); switch (propval.ulPropType) { case ESS_MDX_PROPTYPE_ULONG: { printf("Ulong Value: %ld\n", propval.value.ulData); break; } case ESS_MDX_PROPTYPE_STRING: { printf("String Value: %s\n", propval.value.strData); break; } case ESS_MDX_PROPTYPE_BOOL: { printf("Bool Value: %s\n", propval.value.bData ? "TRUE" : "FALSE"); break; } case ESS_MDX_PROPTYPE_DOUBLE: { printf("Double Value: %lf\n", propval.value.dblData); break; } case ESS_MDX_PROPTYPE_DATETIME: { ESS_CHAR_T tmpbuf[80]; struct tm* pTime; pTime = gmtime((time_t*)&(propval.value.dtData)); sprintf(tmpbuf, "%02i-%02i-%04i", pTime->tm_mon+1, pTime->tm_mday,pTime->tm_year+1900); printf("DateTime Value: %s\n", tmpbuf); break; } case ESS_MDX_PROPTYPE_NONE: { printf("NULL Value\n"); break; } } } } if ((sts = EssFree(hInst, (ESS_PVOID_T) haMbrs)) != ESS_STS_NOERR) { printf("EssFree failure: %ld\n", sts); exit ((int) sts); } printf("EssFree sts: %ld\n", sts); } for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims; ulClusterDimCnt++) { if ((sts = EssMdxGetClusterDimMembers(hCluster, ulClusterDimCnt, &haMbrs)) != ESS_STS_NOERR) { printf("EssMdxGetClusterDimMembers failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetClusterDimMembers sts: %ld\n", sts); for (ulIndex = 0; ulIndex < ulaDimSizes[ulClusterDimCnt]; ulIndex++) { if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulIndex], &pszMbrIdentifier)) != ESS_STS_NOERR) { printf("EssMdxGetMbrIdentifier failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetMbrIdentifier sts: %ld\n", sts); printf("Dim %ld Mbr %ld identifier %s\n", ulClusterDimCnt, ulIndex, pszMbrIdentifier); } } } for (ulIndex = 0; ulIndex < ulAxisSize; ulIndex++) { if ((sts = EssMdxGetAxisMembers(hAxis, ulIndex, &haMbrs)) != ESS_STS_NOERR) { printf("EssMdxGetAxisMembers failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetAxisMembers sts: %ld\n", sts); for (ulAxisDimCnt = 0; ulAxisDimCnt < ulNAxisDims; ulAxisDimCnt++) { if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulAxisDimCnt], &pszMbrIdentifier)) != ESS_STS_NOERR) { printf("EssMdxGetMbrIdentifier failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetMbrIdentifier sts: %ld\n", sts); printf("Mbr %ld identifier %s\n", ulAxisDimCnt, pszMbrIdentifier); haProps = haaProps[ulAxisDimCnt]; for (ulPropCnt = 0; ulPropCnt < ulaNProps[ulAxisDimCnt]; ulPropCnt++) { hProp = haProps[ulPropCnt]; if ((sts = EssMdxGetPropertyInfo(hProp, &pszPropName, &propType)) != ESS_STS_NOERR) { printf("EssMdxGetPropertyInfo failure: %ld\n", sts); exit ((int) sts); } if ((sts = EssMdxGetMbrProperty(haMbrs[ulAxisDimCnt], hProp, &propval)) != ESS_STS_NOERR) { printf("EssMdxGetMbrProperty failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetMbrProperty sts: %ld\n", sts); printf("Property %ld Type ", ulPropCnt); switch (propval.ulPropType) { case ESS_MDX_PROPTYPE_ULONG: { printf("Ulong Value: %ld\n", propval.value.ulData); break; } case ESS_MDX_PROPTYPE_STRING: { printf("String Value: %s\n", propval.value.strData); break; } case ESS_MDX_PROPTYPE_BOOL: { printf("Bool Value: %s\n", propval.value.bData ? "TRUE" : "FALSE"); break; } case ESS_MDX_PROPTYPE_DOUBLE: { printf("Double Value: %lf\n", propval.value.dblData); break; } case ESS_MDX_PROPTYPE_DATETIME: { ESS_CHAR_T tmpbuf[80]; struct tm* pTime; pTime = gmtime((time_t*)&(propval.value.dtData)); sprintf(tmpbuf, "%02i-%02i-%04i", pTime->tm_mon+1, pTime->tm_mday,pTime->tm_year+1900); printf("DateTime Value: %s\n", tmpbuf); break; } case ESS_MDX_PROPTYPE_NONE: { printf("NULL Value\n"); break; } } } } if ((sts = EssFree(hInst, (ESS_PVOID_T) haMbrs)) != ESS_STS_NOERR) { printf("EssFree failure: %ld\n", sts); exit ((int) sts); } printf("EssFree sts: %ld\n", sts); } if ((sts = EssFree(hInst, (ESS_PVOID_T) ulaNProps)) != ESS_STS_NOERR) { printf("EssFree failure: %ld\n", sts); exit ((int) sts); } printf("EssFree sts: %ld\n", sts); if ((sts = EssFree(hInst, (ESS_PVOID_T) haaProps)) != ESS_STS_NOERR) { printf("EssFree failure: %ld\n", sts); exit ((int) sts); } printf("EssFree sts: %ld\n", sts); } void ESS_MdxQry() { ESS_STS_T sts; ESS_MDX_QRYHDL_T hQry; ESS_ULONG_T ulNAxes, ulNAxisDims, ulAxisSize, ulResultSize; ESS_ULONG_T ulNClusters, ulClusterSize, ulNClusterDims; ESS_ULONG_T ulAxisCnt, ulAxisDimCnt, ulIndex, ulPropCnt; ESS_ULONG_T ulCellOffset, ulClusterCnt, ulClusterDimCnt; ESS_MDX_CELLSTATUS_T ulCellStatus; ESS_PULONG_T ulaDimSizes; ESS_MDX_PCLUSTERHDL_T haClusters; ESS_MDX_CLUSTERHDL_T hCluster; ESS_MDX_PAXISHDL_T haAxes; ESS_MDX_PDIMHDL_T haDims; ESS_STR_T pszDimName, pszMbrIdentifier, pszPropName; ESS_MDX_AXISHDL_T hAxis, hSlicerAxis; ESS_MDX_PMBRHDL_T haMbrs; ESS_MDX_CELLHDL_T hCell; ESS_DOUBLE_T dValue; ESS_BOOL_T bIsMissing, bNoAccess; ESS_PULONG_T ulaNProps; ESS_MDX_PPPROPHDL_T haaProps; ESS_MDX_PPROPHDL_T haProps; ESS_MDX_PROPHDL_T hProp; ESS_MDX_PROPTYPE_T propType; ESS_MDX_PROPVALUE_T propval; if ((sts = EssMdxNewQuery(hCtx, qry, &hQry)) != ESS_STS_NOERR) { printf("EssMdxNewQuery failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxNewQuery sts: %ld\n", sts); if ((sts = EssMdxSetMbrIdType(hQry, ESS_MDX_MEMBERIDTYPE_ALIAS)) != ESS_STS_NOERR) { printf("EssMdxSetMbrIdType failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxSetMbrIdType sts: %ld\n", sts); if ((sts = EssMdxSetNeedCellStatus(hQry)) != ESS_STS_NOERR) { printf("EssMdxSetNeedCellStatus failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxSetNeedCellStatus sts: %ld\n", sts); if ((sts = EssMdxExecuteQuery(hQry)) != ESS_STS_NOERR) { printf("EssMdxExecuteQuery failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxExecuteQuery sts: %ld\n", sts); if ((sts = EssMdxGetAxes(hQry, &ulNAxes, &haAxes, &hSlicerAxis)) != ESS_STS_NOERR) { printf("EssMdxGetAxes failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetAxes sts: %ld\n", sts); printf("Number of axes: %ld\n", ulNAxes); ulResultSize = 1; for (ulAxisCnt = 0; ulAxisCnt < ulNAxes; ulAxisCnt++) { hAxis = haAxes[ulAxisCnt]; if ((sts = EssMdxGetAxisInfo(hAxis, &ulAxisSize, &ulNAxisDims, &haDims)) != ESS_STS_NOERR) { printf("EssMdxGetAxisInfo failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetAxisInfo sts: %ld\n", sts); printf("Axis %ld Size %ld Num dims %ld\n", ulAxisCnt, ulAxisSize, ulNAxisDims); ulResultSize *= ulAxisSize; } if (hSlicerAxis) { ESS_MdxAxis(hQry, hSlicerAxis, "SLICER"); } else { printf("Slicer Axis is empty\n"); } for (ulAxisCnt = 0; ulAxisCnt < ulNAxes; ulAxisCnt++) { hAxis = haAxes[ulAxisCnt]; ESS_MdxAxis(hQry, hAxis, axisnames[ulAxisCnt]); } for (ulCellOffset = 0; ulCellOffset < ulResultSize; ulCellOffset++) { if ((sts = EssMdxGetCellAtOffset(hQry, ulCellOffset, &hCell)) != ESS_STS_NOERR) { printf("EssMdxGetCellAtOffset failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetCellAtOffset sts: %ld\n", sts); if ((sts = EssMdxGetValue(hCell, &bIsMissing, &bNoAccess, &dValue)) != ESS_STS_NOERR) { printf("EssMdxGetValue failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetValue sts: %ld\n", sts); if (bIsMissing) { printf("CellOffset %ld Value #Missing\n", ulCellOffset); } else if (bNoAccess) { printf("CellOffset %ld Value #NoAccess\n", ulCellOffset); } else { printf("CellOffset %ld Value %lf\n", ulCellOffset, dValue); } if (!bNoAccess) { if ((sts = EssMdxGetCellStatus(hQry, hCell, &ulCellStatus)) != ESS_STS_NOERR) { printf("EssMdxGetCellStatus failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxGetCellStatus sts: %ld\n", sts); if (ulCellStatus & ESS_MDX_CELLSTATUS_LINKEDOBJS) { printf("Cell status: LINKEDOBJS\n"); } if (ulCellStatus & ESS_MDX_CELLSTATUS_DYNCALC) { printf("Cell status: DYNCALC\n"); } if (ulCellStatus & ESS_MDX_CELLSTATUS_CALCEDMBR) { printf("Cell status: CALCEDMBR\n"); } if (ulCellStatus & ESS_MDX_CELLSTATUS_READONLY) { printf("Cell status: READONLY\n"); } } } if ((sts = EssMdxFreeQuery(hQry)) != ESS_STS_NOERR) { printf("EssMdxFreeQuery failure: %ld\n", sts); exit ((int) sts); } printf("EssMdxFreeQuery sts: %ld\n", sts); } void ESS_Term() { ESS_STS_T sts = ESS_STS_NOERR; if ((sts = EssTerm(hInst)) != ESS_STS_NOERR) { /* error terminating API */ exit((ESS_USHORT_T) sts); } printf("EssTerm sts: %ld\n", sts); } void ESS_Logout() { ESS_STS_T sts = ESS_STS_NOERR; sts = EssLogout(hCtx); printf("\n\nEssLogout sts: %ld\n",sts); } void ESS_SetActive() { ESS_STS_T sts = ESS_STS_NOERR; ESS_ACCESS_T Access; sts = EssSetActive(hCtx, AppName, DbName, &Access); printf("EssSetActive sts: %ld\n",sts); } int main(int argc, char *argv[]) { FILE *f; char *s, *sout; int n, l, e; assert(argc > 1); f = fopen(argv[1], "r"); assert(f != NULL); s = qry; n = MAXQRYLEN; while (n > 0 && !feof(f) && fgets(s, n, f) != NULL) { l = strlen(s); s += l; n -= l; } if ((e = ferror(f)) != 0) { printf("fgets error %d\n", e); exit((int) e); } fclose(f); printf("The query is\n%s\n", qry); if (argc > 2) { AppName = argv[2]; } if (argc > 3) { DbName = argv[3]; } ESS_Init(); ESS_Login(); ESS_SetActive(); ESS_MdxQry(); ESS_Logout(); ESS_Term(); return 0; }
![]() |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |