OraOLEDB OLAP supports connections to Oracle databases. In most cases, the User Id, Password, and the Data Source are required to establish a connection. The data source is not required when connecting to a local database. However, when connecting to a remote database, the data source must be supplied and set to the appropriate Oracle Net Service Name, which should be included as an alias in the tnsnames.ora file.

Dim cat As New ADOMD.Catalog
...
cat.ActiveConnection = "Provider=OraOLEDB.OLAP;" & _ 
  "User Id=<userid>;Password=<password>;Data Source=oracle;"

The following is a list of Oracle OLAP Provider for OLE DB provider-specific connection string attributes:

• OSAuthent - Operating System Authentication

  This feature enables operating system users to connect to an Oracle database.

  To enable operating system users to connect to the database, either:

  • Set the OSAuthent connection string attribute to "1"

    or

  • Set the User Id connection string attribute to "/".

  To disable operating system users from connecting to the database:

  • Set the OSAuthent connection string attribute to "0".

• PwdChgDlg - Password Change Dialog

  This feature enables the provider to display a password change dialog box for non-console applications during logon if the password has expired.

  To enable the dialog box to be displayed in the event of a password expiration:

  • Set the PwdChgDlg connection string attribute to "1".

  To disable the dialog box from displaying in the event of a password expiration:

  • Set the PwdChgDlg connection string attribute to "0"

• PreserveMaxPrecision - Preserve Maximum Precision

  This feature allows the application to specify whether the maximum precision of NUMBER and FLOAT column values are preserved. If this feature is enabled, the provider preserves the maximum precision of the column values by fetching them as an Oracle native type. If this feature is disabled, the provider fetches the column values as C native types, which can cause some precision loss based on the limitations of the C native type. However, this approach provides better performance than fetching numeric data as an Oracle native type.

  To preserve maximum precision of NUMBER and FLOAT column values:

  • Set the PreserverMaxPrecision connection string attribute to "1".

  To disable preservation of maximum precision for NUMBER and FLOAT column values:

  • Set the PreserverMaxPrecision connection string attribute to "0"

Provider-specific properties are part of the DBPROPSET_ORAOLEDBOLAP_COMMAND property set, which is part of the Rowset property set group.

Table 2-2 lists the provider-specific OLE DB command properties. All these properties take effect only if they are set before the execution of the MDX statement:

OLE DB consumers can enable or disable caching of the result set data cell by setting the MDPROP_ORAOLEDBOLAP_CELLDATACACHE property to either VARIANT_TRUE or VARIANT_FALSE, respectively.

If cell data cache is enabled, the OraOLEDB OLAP provider tries to fetch, at least, the number of cells specified by the MDPROP_ORAOLEDBOLAP_CACHEBLOCKSIZE for every server round-trip.

OLE DB consumers can explicitly set the Cache Block Size by setting the MDPROP_ORAOLEDBOLAP_CACHEBLOCKSIZE property with an array of VT_I4 values.

If Cell Data Cache is enabled, the Cache Block Size determines the following:

• The shape of the cache block.

• The minimum amount of cell data the provider attempts to fetch for each server round-trip.

If Cache Block Size is set to its default value of NULL, the provider determines a reasonable Cache Block Size for the result set.

To override the default behavior, the OLE DB consumer must provide an array of four-byte integers. OraOLEDB OLAP uses the supplied array values to determine the shape of the Cache Block Size. The value at the 0th index of the array determines the number of cells that are to be fetched from the X-axis (that is, Axis(0)). The value at the 1st index of the array determines the number of cells that are to be fetched from the Y-axis (that is, Axis(1)). In general, the value at the nth index of the array determines the number of cells that are to be fetched from Axis(n).

If the length of the array is greater than the actual number of axes that exist on the result set, then the extra values are ignored. However, if the length of the array is less than the actual number of axes that exist on the result set, the provider populates the missing values with reasonable values.

OLE DB consumers can choose to either preserve maximum precision or maximize performance when fetching numeric cell data by setting the MDPROP_ORAOLEDBOLAP_PRESERVEMAXPRECISION property to either VARIANT_TRUE or VARIANT_FALSE, respectively.

This property can also be set by using the PreserveMaxPrecision connection string attribute which overrides the registry value.

All the commands created from that connection inherit the value specified in the connection string (or the registry, if it is set there). The MDPROP_ORAOLEDBOLAP_PRESERVEMAXPRECISION property exposed on the command object allows OLE DB consumers to override this inherited value on a particular command object.

...
 
  HRESULT             hr              = S_OK;
  ICommandText       *pICmdText       = NULL;
  ICommandProperties *pICmdProperties = NULL;
  IMDDataset         *pIMDDataset     = NULL:
  long               *px              = NULL;
  SAFEARRAY          *psa             = NULL;
  SAFEARRAYBOUND      rgsabound[1];
  DBPROPSET           dbPropSets[1];
  DBPROP              dbProp[3];
 
  ...
 
  hr = pIDBCreateCmd->CreateCommand(NULL, IID_ICommandText,  
          (IUnknown **)&pICmdText);
 
  hr = pICmdText->SetCommandText(DBGUID_DEFAULT, pCmdText);
 
  hr = pICmdText->QueryInterface(IID_ICommandProperties,
           (void**)&pICmdProperties);
 
  // Create the SAFEARRAY
  rgsabound[0].lLbound = 0;
  rgsabound[0].cElements = 3;
  psa = SafeArrayCreate(VT_I4, 1, rgsabound);
 
  // Get a pointer to the elements of the array.
  hr = SafeArrayAccessData(psa, (void HUGEP* FAR*)&px);
 
  // Create an array for a cache block size of {20, 40, 1}
  //   that will fetch 800 cells for each server round-trip
  px[0] = 20; // 20 coordinates from Axis(0)
  px[1] = 40; // 40 coordinates from Axis(1)
  px[2] = 1;  // 1 coordinate from Axis(2)
 
  dbPropSets[0].rgProperties    = &dbProp[0];
  dbPropSets[0].guidPropertySet = DBPROPSET_ORAOLEDBOLAP_COMMAND;
  dbPropSets[0].cProperties     = 3;
 
  dbProp[0].dwPropertyID        = MDPROP_ORAOLEDBOLAP_CELLDATACACHE;
  dbProp[0].dwOptions           = DBPROPOPTIONS_OPTIONAL;
  dbProp[0].colid               = DB_NULLID;
  V_VT(&(dbProp[0].vValue))     = VT_BOOL;
  dbProp[0].vValue.boolVal      = VARIANT_TRUE;
 
  dbProp[1].dwPropertyID        = MDPROP_ORAOLEDBOLAP_CACHEBLOCKSIZE;
  dbProp[1].dwOptions           = DBPROPOPTIONS_OPTIONAL;
  dbProp[1].colid               = DB_NULLID;
  V_VT(&(dbProp[1].vValue))     = VT_ARRAY | VT_I4;
  dbProp[1].vValue.parray       = psa;
 
  dbProp[2].dwPropertyId    = MDPROP_ORAOLEDBOLAP_PRESERVEMAXPRECISION;
  dbProp[2].dwOptions       = DBPROPOPTIONS_OPTIONAL;
  dbProp[2].colid           = DB_NULLID;
  V_VT(&(dbProp[2].vValue)) = VT_BOOL;
  dbProp[2].vValue.boolVal  = VARIANT_FALSE;
  hr = pICmdProperties->SetProperties(1, dbPropSets);
 
  SafeArrayUnaccessData(psa);
  SafeArrayDestroy(psa);
 
  // Execute the MDX statement
  hr = pICmdText->Execute(NULL, IID_IMDDataset, NULL, NULL, 
           (IUnknown **)&pIMDDataset);
 
  ...

OraOLEDB OLAP supports a collection of MDX keywords which are provided in the appendix.

OraOLEDB OLAP supports the following cell properties:

• VALUE

• FORMATTED_VALUE

  OraOLEDB OLAP uses the FORMAT_STRING, VALUE, and user default-locale identifier to generate the FORMATTED_VALUE. The FORMATTED_VALUE is not affected by any Oracle NLS parameters.

• CELL_ORDINAL

• FORMAT_STRING

The Oracle databases store the Unicode data in the UTF8 encoding scheme, which is an ASCII compatible multibyte encoding of Unicode. Supported Microsoft operating system use the UCS2 encoding, which is a 2-byte fixed width encoding scheme. OraOLEDB OLAP transparently converts the data between the two encoding schemes allowing the consumers to deal with only UCS2.

In order to prevent any data loss, the database character set should be UTF8. Other than this, there is no other setup required for the Unicode support.