[Books] [Courses & Consulting] [Code Samples]  [Links]  [Home] [Mail me] 

ADO MD Examples

Natalia Elmanova

Comments: ADO MD means ADO MultiDimensional. It is an ADO extension to work with OLAP cubes that are accessible via OLE DB

Here you can find  code samples for:

• Retrieving cube metadata in Delphi application using OpenSchema method of the TADOConnection component.

• Getting Cube names and properties using ADO MD objects

• Getting Dimension properties using ADO MD objects

• Getting info on Hierarchies, Levels and Members  using ADO MD objects

• Retrieving cube data  using ADO MD objects

 Other useful ADO examples (by Alex Fedorov)

Example 1. Retrieving cube metadata in Delphi application using OpenSchema method of the TADOConnection component.

procedure TForm1.Button1Click(Sender: TObject);
var
 SI:TSchemaInfo ; i:integer;
begin
//Select a type of metadata query
 case ComboBox1.ItemIndex of
  0: SI:=siCubes;
  1: SI:=siDimensions;
  2: SI:=siHierarchies;
  3: SI:=siLevels;
  4: SI:=siMeasures;
  5: SI:=siProperties;
  6: SI:=siMembers;
 end;

 //Retrieve results of the metadata query to ADODataSet1
 ADOConnection1.OpenSchema(SI,EmptyParam,
 EmptyParam,ADODataSet1);
 //Open a query result
 ADODataSet1.Open;
 //Change appearance of the DBGrid
 for i:=0 to DBGrid1.Columns.Count-1  do
 DBGrid1.Columns[i].Width:=80;
end;
 

procedure TForm1.FormCreate(Sender: TObject);
begin
 ComboBox1.ItemIndex:=0;
end;

Example 2. Getting Cube Names and Properties using ADO MD objects

unit cubelist1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ComObj, ADOMD_TLB, Db,ADODB, StdCtrls, ComCtrls;

type
TForm1 = class(TForm)
TreeView1: TTreeView;
Button1: TButton;
procedure Button1Click(Sender: TObject);
procedure CubeList(DataSource:WideString);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;
var CatalogGUID:TGUID;
DS: WideString;
Catalog1: ICatalog;


implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
DS := PromptDataSource(Application.Handle, '');
If DS <> '' Then
begin
CubeList(DS);
end;
end;

procedure TForm1.CubeList(DataSource: WideString);
var
CubeNode : TTreeNode;
CubeDefNode: TTreeNode;
RootNode : TTreeNode;
CubeDef1 :CubeDef;
i,j : Word;

begin
Catalog1:=CreateComObject(StringToGUID('ADOMD.Catalog')) as ICatalog;
TreeView1.Items.Clear;
RootNode := TreeView1.Items.Add(nil, 'Catalog');
CubeNode := TreeView1.Items.AddChild(RootNode, 'Cubes');
Catalog1._Set_ActiveConnection(OleVariant(DS));
If Catalog1.CubeDefs.Count > 0 Then
begin
For i := 0 to Catalog1.CubeDefs.Count-1 do
begin
CubeDef1:= Catalog1.CubeDefs[i] as CubeDef;
CubeDefNode:=TreeView1.Items.AddChild(CubeNode, CubeDef1.Name);
if CubeDef1.Properties.Count >0 then
for j:=0 to CubeDef1.Properties.Count - 1 do
begin
TreeView1.Items.AddChild(CubeDefNode, CubeDef1.Properties[j].Name+'='+
VarToStr(CubeDef1.Properties[j].Value));
end;
end;
end;
end;

end.

Example 3. Getting Dimension properties using ADO MD objects

unit cubedefslist1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ComObj, ADODB, ADOmd_TLB, ComCtrls;

type
TForm1 = class(TForm)
TreeView1: TTreeView;
Button1: TButton;
procedure Button1Click(Sender: TObject);
procedure CubeList(DataSource:WideString);
procedure SHowDimProp;
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;
DS: WideString;
Catalog1: ICatalog;
CubeDef1 : CubeDef;
Dimension1 : Dimension;
CubeNode : TTreeNode;
DimNode : TTreeNode;
RootNode : TTreeNode;
CubeDefNode : TTreeNode;


implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
DS := PromptDataSource(Application.Handle, '');
If DS <> '' Then
begin
CubeList(DS);
end;
end;

procedure TForm1.CubeList(DataSource: WideString);
var i:integer;
begin
Catalog1:=CreateComObject(StringToGUID('ADOMD.Catalog')) as ICatalog;
TreeView1.Items.Clear;
RootNode := TreeView1.Items.Add(nil, 'Catalog');
CubeNode := TreeView1.Items.AddChild(RootNode, 'Cubes');
Catalog1._Set_ActiveConnection(OleVariant(DS));
if Catalog1.CubeDefs.Count > 0 then
begin
for i := 0 to Catalog1.CubeDefs.Count-1 do
begin
CubeDef1:= Catalog1.CubeDefs[i] as CubeDef;
CubeDefNode:=TreeView1.Items.AddChild(CubeNode, CubeDef1.Name);
if CubeDef1.Dimensions.Count > 0 then ShowDimProp;
end;
end;
end;

procedure TForm1.SHowDimProp;
var j,k:integer;
begin
for j:=0 to CubeDef1.Dimensions.Count-1 do
begin
Dimension1:=CubeDef1.Dimensions[j] as Dimension;
DimNode:=TreeView1.Items.AddChild(CubeDefNode, Dimension1.Name);
if Dimension1.Properties.Count >0 then
begin
for k:=0 to Dimension1.Properties.Count - 1 do
begin
TreeView1.Items.AddChild(DimNode,
Dimension1.Properties[k].Name+'='+
VarToStr(Dimension1.Properties[k].Value));
end;
end;
end;
end;

end.

Example 4. Getting info on Hierarchies, Levels and Members  using ADO MD objects

unit hierarchy1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ComObj, ADODB, ADOmd_TLB, ComCtrls;

type
TForm1 = class(TForm)
TreeView1: TTreeView;
Button1: TButton;
procedure Button1Click(Sender: TObject);
procedure CubeList(DataSource:WideString);
procedure DimList;
procedure HierarchyList;
procedure LevelList;
procedure MemberList;
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;
DS: WideString;
Catalog1: ICatalog;
CubeDef1 :CubeDef;
Dimension1: Dimension;
Hierarchy1: Hierarchy;
Level1: Level;
Member1: Member;
CubeNode : TTreeNode;
RootNode : TTreeNode;
CubeDefNode : TTreeNode;
DimNode: TTreeNode;
HierNode, LevelNode: TTreeNode;
NodeName:String;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
DS := PromptDataSource(Application.Handle, '');
If DS <> '' Then
begin
CubeList(DS);
end;
end;

procedure TForm1.CubeList(DataSource: WideString);
var
i:Word;
begin
Catalog1:=CreateComObject(StringToGUID('ADOMD.Catalog')) as ICatalog;
TreeView1.Items.Clear;
RootNode := TreeView1.Items.Add(nil, 'Catalog');
CubeNode := TreeView1.Items.AddChild(RootNode, 'Cubes');
Catalog1._Set_ActiveConnection(OleVariant(DS));
if Catalog1.CubeDefs.Count > 0 then
begin
for i := 0 to Catalog1.CubeDefs.Count-1 do
begin
CubeDef1:= Catalog1.CubeDefs[i] as CubeDef;
CubeDefNode:=TreeView1.Items.AddChild(CubeNode, CubeDef1.Name);
if CubeDef1.Dimensions.Count > 0 then DimList;
end;
end;
end;

procedure TForm1.DimList;
var j:Word;
begin
for j:=0 to CubeDef1.Dimensions.Count-1 do
begin
Dimension1:=CubeDef1.Dimensions[j] as Dimension;
DimNode:=TreeView1.Items.AddChild(CubeDefNode, Dimension1.Name);
if Dimension1.Hierarchies.Count > 0 then
HierarchyList;
end;
end;

procedure TForm1.HierarchyList;
var k: Word;
begin
for k:=0 to Dimension1.Hierarchies.Count-1 do
begin
Hierarchy1:=Dimension1.Hierarchies[k] as Hierarchy;
NodeName:=Hierarchy1.Name;
if Hierarchy1.Name='' then NodeName:='Hierarchy';
HierNode:=TreeView1.Items.AddChild(DimNode, NodeName);
if Hierarchy1.Levels.Count >0 then
LevelList;
end;
end;

procedure TForm1.LevelList;
var l: Word;
begin
for l:=0 to Hierarchy1.Levels.Count-1 do
begin
Level1:=Hierarchy1.Levels[l] as Level;
LevelNode:=TreeView1.Items.AddChild(HierNode, Level1.Name);
if Level1.Members.Count >0 then MemberList;
end;
end;

procedure TForm1.MemberList;
begin
for m:=0 to Level1.Members.Count - 1 do
begin
Member1:=Level1.Members[m] as Member;
TreeView1.Items.AddChild(LevelNode, Member1.Name);
end;
end;

end.

Example 5. Retrieving cube data  using ADO MD objects

unit cellset2grid1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ComObj,Series, ComCtrls, Grids, ExtCtrls, TeeProcs, TeEngine, Chart,
adodb, ToolWin;

type
TForm1 = class(TForm)
Panel1: TPanel;
StringGrid1: TStringGrid;
Splitter1: TSplitter;
Splitter2: TSplitter;
Memo1: TMemo; //to input an MDX query
Chart1: TChart;
ToolBar1: TToolBar;
Button1: TButton;

procedure Button1Click(Sender: TObject);
procedure CellGrid(DataSource:WideString);
Procedure CellChart;
procedure ComboBox1Change(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;
DS: WideString;
Cellset1:Variant;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
DS := PromptDataSource(Application.Handle, '');
If DS <> '' Then
begin
CellGrid(DS);
CellChart;
end;
end;

procedure TForm1.CellChart;
var i,j:integer;
ASeries:THorizBarSeries;
y: real; 
x:string;
begin
Chart1.SeriesList.Clear;
for i:=1 to StringGrid1.ColCount-1 do
begin
Aseries:=THorizBarSeries.Create(Chart1);
Chart1.AddSeries(ASeries);
Aseries.Marks.Visible:=False;
Aseries.Title:=StringGrid1.Cells[i,0];
for j:=1 to StringGrid1.RowCount-1 do
begin
x:=StringGrid1.Cells[0,j];
y:=StrToFloat(StringGrid1.Cells[i,j]);
Chart1.Series[i-1].AddX(y,x);
end;
end;
end;

procedure TForm1.CellGrid(DataSource: WideString);
var i,j: word;
begin
CellSet1:=CreateOleObject('ADOMD.Cellset');
CellSet1.Open(Memo1.Text,DataSource);
StringGrid1.ColCount:=CellSet1.Axes[0].Positions.Count+1;
StringGrid1.RowCount:= CellSet1.Axes[1].Positions.Count+1;
for j:=1 to CellSet1.Axes[1].Positions.Count do
StringGrid1.Cells[0,j]:= CellSet1.Axes[1].Positions[j-1].Members[0].Caption;
for i:=1 to CellSet1.Axes[0].Positions.Count do
begin
StringGrid1.Cells[i,0]:= CellSet1.Axes[0].Positions[i-1].Members[0].Caption;
for j:=1 to CellSet1.Axes[1].Positions.Count do
begin
if Cellset1.Item[i-1,j-1].FormattedValue<>'' then
StringGrid1.Cells[i,j]:=Cellset1.Item[i-1,j-1].Value
else StringGrid1.Cells[i,j]:='0';
end;
end;
CellSet1.Close;
CellSet1:=Unassigned;
end;

end.

Copyright N.Elmanova, 2000

Last updated 23.03.00 

You are the  visitor of this page since 14 Jun 1999  

This page hosted by  . Get your own Free Home Page