IAdoMdCube.SqlText

Fore Syntax

SqlText: String;

Fore.NET Syntax

SqlText: String;

Description

The SqlText property determines text of the MDX query, that was used to form the cube.

Comments

To define the physical name of the source cube in the server, use the IAdoMdCube.NativeName property.

The text of the specified MDX query has some restrictions:

The NON EMPTY keyword must not be used in the axis expression, use the NonEmpty function instead. The function is not available when working with a database based on SAP NetWeaver BW.
The HAVING condition must not be used in the axis expression, use the Filter function instead.
Calculated elements are prohibited by default in sampling subqueries. The restriction can be changed by setting the SubQueries parameter to 1 in the IAdoMdCatalog.ProviderString property.

Fore Example

Executing the example requires that the repository contains an ADOMD catalog with the ADOMDTest identifier. An ADOMD cube with the CUBE identifier is created in the catalog. Add links to the AdoMd and Metabase system assemblies.

Sub UserProc;
Var
    MB: IMetabase;
    Cube: IAdoMdCube;
    SQL: String;
Begin
    MB := MetabaseClass.Active;
    Cube := MB.ItemByIdNamespace("CUBE", MB.GetObjectKeyById("ADOMDTest")).Edit As IAdoMdCube;
    //Get the MDX query based on which the cube is built
    SQL := Cube.SqlText;
    //Change the query
    SQL := String.Replace(SQL, "Year", "Month");
    //Refresh the cube
    Cube.SqlText := SQL;
    (Cube As IMetabaseObject).Save;
End Sub UserProc;

On executing the example ADOMD cube structure is refreshed by changing the MDX query, based on which the cube is built.

Fore.NET Example

The requirements and result of the Fore.NET example execution match with those in the Fore example.

Imports Prognoz.Platform.Interop.AdoMd;
Imports Prognoz.Platform.Interop.Metabase;

Public Shared Sub Main(Params: StartParams);
Var
    MB: IMetabase;
    Cube: IAdoMdCube;
    SQL: String;
Begin
    MB := Params.Metabase;
    Cube := MB.ItemByIdNamespace["CUBE", MB.GetObjectKeyById("ADOMDTest")].Edit() As IAdoMdCube;
    //Get the MDX query based on which the cube is built
    SQL := Cube.SqlText;
    //Change the query
    SQL := SQL.Replace("Year", "Month");
    //Refresh the cube
    Cube.SqlText := SQL;
    (Cube As IMetabaseObject).Save();
End Sub;