ADOMD (on VB.NET)

[drkestrel]

Seems like ADOMD.NET is not yet available with the .NET Framework, even with SP2.

Anyway, I would like to build something very similar to SQL Server 2000's Analysis service manager's Cube Browser, but
- 3 different windows tab (one per dimension)
- Within each tab, a tree view listing the caption for each member (e.g. USA, California for 'areas' dimension) and the single measure used across all dimension, which is called dimension

- A label in the form indicating the currently selected total number. A user could select a branch in each of the 3 trees for area, time and product dimensions.

The number should indicate the intersection between the 3 dimensions.
e.g. If the total measure for all areas+time+product is 999,999, of which

- 9,999 is from California,USA
  -  of which 9,500 is in 2002 (time)
     - of which 5000 is for product paper 
     - of which 4500 is for product metal
  - of which 499 is in 2003 (time)
- 990,000 is from Newcastle, UK
   - of which 900,000 is in 2002 (time)
     -of which 500,000 is for product paper
     -of which 400,000 is for product metal
   - of which 90,000 is in 2003 (time)
     -of which 50,000 is for product paper
     -of which 40,000 is for product metal

Then when the user first select California (with nothing else selected from the 2 other trees), then the total number should be 9,999

Now, if the user also select 2002 for the time tree, the number should change to 9500. If the user then select paper in the product tree, the number changes to 5000, but if the user then change to metal in the product tree, the number should change yet again to 4500.

I could just about using the following VB6 code (Visual Studio 2002 is not much help trying to use quick watch on a COM object like ADOMD) iterate through all cells, indicating the cell caption (e.g. USA, California) and the associated number, but no dimensions:

Dim cnn As New ADODB.Connection
Dim cst As New ADOMD.cellset
cnn.Open ("Data Source=MySource;initial catalog=myCat;Provider=MSOLAP")
Dim strSource3 As String
strSource3 = "SELECT [area_dimension].members on COLUMNS "
strSource3 = strSource3 & "FROM testdatacube "
strSource3 = strSource3 & "WHERE (Measures.[Measurement])"

cst.Open strSource3, cnn

For i = 0 To cst.Axes(0).Positions.Count - 1
    Debug.Print cst.Axes.Item(0).Positions.Item(i).Members.Item(0).Caption &
"-" & cst(i)
Next

Well, I am just trying to play with one dimension above, but you could see the immediate problem. Naivgatin the object model via Cellset->Axes->Cell->Positions->Members, I lose the 'hirerarchy', but my big questions are

1) how could I navigate (using cubedefs->Dimensions->Hirerarchy->Levels->Members) and obtain the 'mesaurement' I wanted?
2) How could I obtain the total number, which is the 'intersect' for all selected items in the 3 trees?

 

[drkestrel]

Found a (partial solution)

It looks like I could use the LevelDepth property of Member object to figure out the 'hirerarchy' of the current cell being listed.

Any other cunning way of doing the same thing and indeed, in regards to my 'filtering' on different dimension/indication of total number of items selected across all dimension, could that be done without running a different MDX query (with additional where clauses)?

Also, in terms of performance, any big difference in navigating via the CubeDefs or Cellset route? If Cube defs is better, could someone please tell me how I could get the 'measurement' using this route?