Hyperion MDX Provider Release 7 API Function Reference and Example

API Reference

This document describes the Essbase API functions specifically built to deal with MDX manipulations.

This file contains the following sections:


Top

MDX Provider API General Information

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.


Top

MDX Provider API Reference

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.


Top

MDX Provider Declarations

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;

Top

EssMdxExecuteQuery()

Executes the specified query on the currently connected database.

Syntax

ESS_FUNC_M EssMdxExecuteQuery(ESS_MDX_QRYHDL_T   hQry);
hQryinputQuery handle

Top

EssMdxGetAxes()

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);
hQryinputQuery handle
pulNAxesoutputNumber of axes
pphAxesoutputArray of axis handles
phSliceroutputSlicer axis handle

Top

EssMdxGetAxisInfo()

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);
hAxisinputAxis handle
pulSizeoutputNumber of tuples in axis
pulNDimsoutputNumber of dimensions in axis
pphDimsoutputArray of dimension handles

Top

EssMdxGetClusters()

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);
hAxisinputAxis handle
pulNClustersoutputNumber of clusters
pphClustersoutputArray of cluster handles

Top

EssMdxGetClusterInfo()

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);
hClusterinputCluster handle
pulSizeoutputNumber of tuples in cluster
pulNDimsoutputNumber of dimensions in cluster (same as that in the axis that this cluster belongs to)
ppulDimSizesoutputArray of dimension sizes (number of members)

Top

EssMdxGetClusterMembers()

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);
hClusterinputCluster handle
ulIndexinputTuple position within cluster (first dimension changes slowest)
pphMbrsoutputArray of member handles for the tuple

Top

EssMdxGetAxisMembers()

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);
hAxisinputAxis handle
ulIndexinputTuple position within axis
pphMbrsoutputArray of member handles for tuple

Top

EssMdxGetCellAtOffset()

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);
hQryinputQuery handle
ulOffsetinputCell offset (first axis changes fastest)
phCellinputCell handle

Top

EssMdxGetCellAtIndices()

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);
hQryinputQuery handle
pulIndicesinputTuple indices, one for each axis
phCelloutputCell handle

Top

EssMdxGetValue()

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
                         );
hCellinputCell handle
pbIsMissingoutputWhether cell value is #Missing
pbNoAccessoutputWhether cell value is #NoAccess
pdValueoutputThe cell's value, if not #Missing

Top

EssMdxFreeQuery()

Frees memory used for the specified query.

Syntax

ESS_FUNC_M EssMdxFreeQuery(ESS_MDX_QRYHDL_T   hQry);
hQryinputQuery handle

Top

EssMdxNewQuery()

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
                         );
hCtxinputAPI context handle
pszQryinputQuery text
phQryoutputQuery handle

Top

EssMdxSetMbrIdType()

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
                             );
hQryinputQuery handle
mbrIdTypeinputMember identifier desired (name/alias):
- ESS_MDX_MEMBERIDTYPE_NAME
- ESS_MDX_MEMBERIDTYPE_ALIAS

Top

EssMdxGetDimInfo()

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
                           );
hDiminputDimension handle
ppszDimNameoutputDimension name
pulNPropsoutputNumber of properties returned
pphPropsoutputArray of property handles

Top

EssMdxGetPropertyInfo()

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
                                );
hPropinputProperty handle
ppszNameoutputProperty name
pPropTypeoutputProperty type:
- ESS_MDX_PROPTYPE_BOOL
- ESS_MDX_PROPTYPE_DOUBLE
- ESS_MDX_PROPTYPE_STRING
- ESS_MDX_PROPTYPE_DATETIME
- ESS_MDX_PROPTYPE_ULONG

Top

EssMdxGetMbrIdentifier()

Returns the identifier for the specified member.

Syntax

ESS_FUNC_M EssMdxGetMbrIdentifier(
   ESS_MDX_MBRHDL_T       hMbr,
   ESS_PSTR_T             ppszIdentifier
                                 );
hMbrinputMember handle
ppszIdentifieroutputMember identifier (name or alias)

Top

EssMdxGetMbrProperty()

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
                               );
hMbrinputMember handle
hPropinputProperty handle
pPropValueoutputProperty value

Top

EssMdxSetDataLess()

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
                            );
hQryinputQuery handle

Top

EssMdxSetNeedCellStatus()

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
                                  );
hQryinputQuery handle

Top

EssMdxGetClusterDimMembers()

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
                                     );
hClusterinputCluster handle
ulIndexinputDimension index within axis containing cluster
pphMbrsoutputArray of member handles for the specified dimension

Top

EssMdxGetCellStatus()

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
                              );
hQryinputQuery handle
hCellinputCell handle
pulStatusoutputCell status:
Bitmap with the following masks:
- ESS_MDX_CELLSTATUS_LINKEDOBJS
- ESS_MDX_CELLSTATUS_DYNCALC
- ESS_MDX_CELLSTATUS_CALCEDMBR
- ESS_MDX_CELLSTATUS_READONLY

Top

MDX Sample Client Program

#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;
} 

Top


Hyperion Logo
©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com