IAdoMdCatalogInstance.RefreshBySQL

Syntax

RefreshBySQL(SqlText: String);

Parameters

SqlText. MDX query text.

Description

The RefreshBySQL method refreshes contents of the ADOMD catalog by running the specified MDX query.

Comments

If the query contains information about not yet existing ADOMD cube, it will be created.

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.

To refresh the contents of the ADOMD catalog according to its contents in the server, use the IAdoMdCatalogInstance.Refresh method.

Example

Executing the example requires that the repository contains an ADOMD catalog with the ADOMDTest identifier. This catalog contains ADOMD dimensions required to create a cube. Add links to the AdoMd and Metabase system assemblies.

Sub UserProc;
Var
    MB: IMetabase;
    Catalog: IAdoMdCatalogInstance;
    MDX: String;
Begin
    MB := MetabaseClass.Active;
    Catalog := MB.ItemById("ADOMDTest").Open(Null) As IAdoMdCatalogInstance;
    //MDX query
    MDX := "select [Commodities].[Commodity].members on rows, [Time].[Year].members on columns from [PricesForecast] where [Measures].[Value]";
    //Create a new cube based on MDX query
    Catalog.RefreshBySQL(MDX);
End Sub UserProc;

After executing the example a new ADOMD cube is created based on the specified MDX query in the ADOMD catalog.