I was wondering if someone could post an example of creating virtual
dimensions through DSO. The example in SQL Books doesn't seem to work (I
can't get my virtual dimensions to show up in Analysis Manager). When I run
the code, it doesn't error out, just never seems to create the dimension.
Thanks!
-Malcolm


Here's the example code:
Private Sub AddVirtualDimension()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim dsoDim As DSO.Dimension
Dim dsoLevel As DSO.Level

Dim strDBName As String
Dim strLQuote As String
Dim strRQuote As String

' Define constants used for the ColumnType property
' of the DSO.Level object.
' Note that these constants are identical to
' those used in ADO in the DataTypeEnum enumeration.
Const adDouble = 5

' Initialize variable for the database.
strDBName = "TestDB"

' Create a connection to the Analysis server.
dsoServer.Connect "LocalHost"

' Ensure that the server has an existing database.
If dsoServer.MDStores.Find(strDBName) = False Then
MsgBox "Database " & strDBName & _
" is not found."
Else
' Retrieve the database from the server.
Set dsoDB = dsoServer.MDStores(strDBName)

' Retrieve a data source from the database.
Set dsoDS = dsoDB.DataSources("FoodMart")

' Get the delimiter characters from the data source.
strLQuote = dsoDS.OpenQuoteChar
strRQuote = dsoDS.CloseQuoteChar

' Create the new dimension in the Dimensions
' collection of the database object.
Set dsoDim = dsoDB.Dimensions.AddNew("Store Size in SQFT")

' Set the description of the dimension.
dsoDim.Description = "The Store Size in SQFT virtual dimension"

' Set the data source of the dimension.
Set dsoDim.DataSource = dsoDS

' Set the dimension type, make it virtual,
' and identify its underlying source dimension.
dsoDim.DimensionType = dimRegular
dsoDim.IsVirtual = True
dsoDim.DependsOnDimension = "Stores"

' Next, create the levels.
' Start with the (All) level.
Set dsoLevel = dsoDim.Levels.AddNew("(All)")

' Set the level type.
dsoLevel.LevelType = levAll

' Set the MemberKeyColumn of the (All) level to a constant
' that also acts as the name of the level's only member.
dsoLevel.MemberKeyColumn = "(All Store Sizes)"

' Create the Store SQFT level. This holds the SQFT value.
Set dsoLevel = dsoDim.Levels.AddNew("Store Size")

' Name the source column for this level.
' The format for this is "table_name"."column_name".
' Database-specific delimiter characters are required.
dsoLevel.MemberKeyColumn = strLQuote & "store" & strRQuote & "." & _
strLQuote & "store_sqft" & strRQuote

' Set the following properties to be identical to their
' counterparts in the member property object that provides
' this level with its data.
dsoLevel.ColumnType = adDouble
dsoLevel.ColumnSize = 4

' Check to see that you set the level and
' dimension properties correctly, and that the rest
' of the dimension structure is correct. If so,
' update the repository and exit the function.
If dsoLevel.IsValid And dsoDim.IsValid Then
' Update the dimension.
dsoDim.Update

' Inform the user.
MsgBox "Virtual dimension has been added."
End If
End If

End Sub
And here's my Delphi Intrepretation (you pass in a field you want to make
virtual and it makes a member property of the current level, which is ITEMS,
and then tries to create a virtual dimension).procedure
AddMemberProperty(APropertyName: string);
var
FProperty: Variant;
FVirtualDimension: Variant;
FVirtualDimensionLevel: Variant;
FVirtualDimensionName: string;
FDataSource: Variant;

begin
APropertyName:=AnsiUpperCase(APropertyName);
FProperty:=FLevel.MemberProperties.AddNew(APropert yName);
FProperty.SourceColumn:='ITMGR.GSK_CUSTOM_ITEM.'+A PropertyName;
FVirtualDimensionName:=FCubeName+'^'+'virt_'+AProp ertyName;
FVirtualDimension:=FDatabase.Dimensions.AddNew(FVi rtualDimensionName);
FVirtualDimension.Description:='virtual '+APropertyName;
FVirtualDimension.DataSource:=FDatabase.DataSource s.Item(FDataSourceName);
FVirtualDimension.DimensionType:=dimRegular;
FVirtualDimension.IsVirtual:=True;
FVirtualDimension.DependsOnDimension:=FCubeName+'^ GSKItems';//.'+APropertyName;
FVirtualDimension.SubClassType:=0; // not sure what '0' represents
FVirtualDimension.Update;

FVirtualDimensionLevel:=FVirtualDimension.Levels.A ddNew('(All)');
FVirtualDimensionLevel.LevelType:=levAll;
FVirtualDimensionLevel.MemberKeyColumn:='All '+APropertyName;
FVirtualDimensionLevel.ColumnType:=3; // ??
FVirtualDimension.Update;

FVirtualDimensionLevel:=FVirtualDimension.Levels.A ddNew(APropertyName);
FVirtualDimensionLevel.MemberKeyColumn:='ITMGR.GSK _CUSTOM_ITEM.'+APropertyName;
FVirtualDimensionLevel.ColumnType:=200; // ??
FVirtualDimensionLevel.ColumnSize:=4;
FVirtualDimension.Update;

end;

Hi Malcolm,

You may have to refreash the Analysis Manager to see the object after being
created. Just make sure there is a DSO update command in the code after you
have added the Virtual Dimension.
I'm thinking there is a VBDSO sample that comes with AS in the Samples
folder, I believe this adds a Virtual Dimension.

David


"Malcolm Toon" <mtoon@ no-spam.retailsolutions.com> wrote

me;
me;