Very Computer

Hello,

Is there a more efficient way of displaying data from
a cellset other than retrieving it on a cell by cell
basis?  I am building a cube-browsing client app
where the user might choose to view a large part of
the cube.  In an MDX query that returns 3,000 cells, it
takes a couple of seconds to open the cellset, and
then a minute or more to iterate through each of the
3,000 cells.  For a larger cellset such as 10,000,
this could take 15 -20 minutes.

My VB code looks like this:

    Dim cst As New ADOMD.Cellset
    .
    .
    .

    conn.ConnectionString = "Datasource=TestServer; Provider=msolap; Initial
Catalog=Test;"
    conn.Open
    Set cst.ActiveConnection = conn

    cst.Source = MDXQuery
    cst.Open

    nRowDim = cst.Axes(1).DimensionCount '# Dimensions in the row axis
    cRow = cst.Axes(1).Positions.Count   'total # cells in all row
dimensions

    nColDim = cst.Axes(0).DimensionCount '# Dimensions in the column axis
    cCol = cst.Axes(0).Positions.Count   'total # cells in all column
dimensions

    'Iterate through the cell set array values.
    'The results are not actually being displayed, just retrieved
    nNonNA = 0
    For iRow = 0 To cRow - 1
        For iCol = 0 To cCol - 1
             result = cst(iCol, iRow).Value
        Next
    Next

The MDXQuery used in the code is a string of the form

select
crossjoin({[Measures].[Sales]},
          crossjoin({[5 Times]},{[25 Depts]})) on
columns,
crossjoin({[3 Locations]},{[8 Products]}) on rows
From SALES_CUBE

Thanks,
Grace

Here the issue seems to be that you have a large crossjoin of (I guess)
highly irregular sets (meaning they are not hierarchically structured and
the members are at somewhat "random" positions in their hierarchies.

Example:

selecting the top 100 cities from a hierarchy based on Continent, Country,
State, City might lead to the cities being in some "random positions" (like
one city from USA, WA another from Sachsen, Germany etc). They are not
"siblings" or somwhat resembling to a "cluster" inside the hierarchical
view.

(This is just a guess because I don't know how you really build the sets
named [25 Departments] etc)

This would lead to the fact that data requests would be scattered to the
server in many pieces (such that we avoid retrieving unnecessary data).

I suggest as improvment you either "precache" the data of all of the
original sets, prior to do the filtering (in my example above all of the
cities). (see the CACHE constructuion of MDX). The cache statement is
requested in one rountrip per CACHE construction and then, after the
filtering, you would have the data locally and this would not require any
more roundtrips. The disadvantage is that it'll consume a *lot* of memory
(if the analysis services didn't ask for all of the cell in one shot it was
a good reason not to do so: scalability)

I'll take a look and see how could I modify your query to make it faster.

Also you could consider using raw OLEDB in C++ instead of ADOMD in VB as
this will also might help performance/scalability.

C

I would suggest you not to retrieve too many cells at once.
For the most cases users don't need them all. MDX has
a lot of functions for top and bottom performance analysis
such as TOPCOUNT, TOPPERCENT, TOPSUM etc.

If you use these kind of functions,  you can initially retrieve relativley
small set
of data and then provide drill-down for the data user is mostly interested
in.

Otherwise OLAP can be very slow, especially if you use calculated members,
that are resolved in the run time.

Stanislav Bashtavenko