Lot of events produced in SSAS (optimization)

Hi all,

When I execute the first MDX sentence with "

executeOlapQuery(mdxSentence)

" method of OlapStatement over a SSAS source, it takes a lot of time until
data is retrieved.

I watched in SQLServer Profiler, that a lot of events are generated by the mdx
query (for example SELECT ..MEMBERS ON ROWS, { ., .} ON COLUMNS FROM ).

I downloaded the source code and debug an execution. I 've got some questions
about the "lookupmembers" operations.

I detected debugging that the most important point for the inefficiency is in
the line 229 of class "XmlaOlap4jCellSet".

// Fetch all members on all axes. Hopefully it can all be done in one
// round trip, or they are in cache already.
metadataReader.lookupMembersByUniqueName(uniqueNames, memberMap);

This method implemented in XmlaOlap4jCube makes a XMLA request to SSAS for
**ALL ** the uniqueNames which are in the first method parameter, one by one.
However, for mondrian do it in one call, passing the list of uniquenames.

I'm not sure if this call is necessary, at least for my case, executing a MDX
with the

executeOlapQuery

and retrieving results accesing only to axes 0 and 1 with Positions obtained
from the CellSet.

I made some proofs adding comments to the method

lookupMembersByUniqueName

of XmlaOlap4jCube in order to improve the performance not caching these
members.

        public void lookupMembersByUniqueName(
            List<String> memberUniqueNames,
            Map<String, XmlaOlap4jMember> memberMap) throws OlapException {
//            if (olap4jSchema.olap4jCatalog.olap4jDatabaseMetaData
//                    .olap4jConnection.getDatabase()
//                    .indexOf("Provider=Mondrian") != -1)
//            {
//                mondrianMembersLookup(memberUniqueNames, memberMap);
//            } else {
//                genericMembersLookup(memberUniqueNames, memberMap);
//            }
        }

Al proofs done were succesfully, all data was retrieved as expected and the
performance was increased a lot.

For example, the results obtained with the previous MDX were:

Total rows: 18485

OLAP4J (olap4j-1.0.1.500)

Events in SQLServer Profiler: 57082
Execution time: 178 s

Modified version of OLAP4J (olap4j-1.0.1.500)

Events in SQLServer Profiler: 1049
Execution time: 15 s

As you can see, differences are strong and data retrieved seems ok.

So, for what is necessary the commented code? Is this an incorrect
optimization?

No comments about this because is a crazy change or because anyone knows about
it?

this is a very important issue to me. i did have a look at your changes but
dont fully understand them yet

when i have some time in the next few days i will look at it

making olap4j faster on SSAS is a priority to me!

I think your patch is not acceptable. It breaks quite a few tests because you
have crippled the mechanism used to fetch the properties of members.

The method XmlaOlap4jCellSet.populate() passes a map to
XmlaOlap4jCube.lookupMembersByUniqueName, which it then uses to create the
OLAP objects. Your modification prevents the code from populating this map.

Later on, when the XmlaOlap4jCellSet.populate method makes use of that map
(and expects it to contain the members) it can't find them. At that point, we
create what we call a "surprise member" with the limited information we have
on it. (Usually just the unique name and caption)

This causes problems, because if someone takes a cell of that CellSet and asks
for the properties of that member, they won't be available.

The method lookupMembersByUniqueName should only be called for the members of
a given cellset. If you think that this method performs lookups for members
which it shouldn't care about, please file a bug report.

BTW. The performance hit is caused by the number of HTTP requests to populate
this list of members. In Mondrian, we have added support for multiple member
lookups in a single request, thus eliminating the problem. SSAS doesn't
support that. Maybe you should also notify them of the issue.

We were discussing this morning on IRC. Here are my thoughts.

I think you are improving performance simply by removing the fetch. The cache
is not populated. If you ask for a member property of a member on one of those
axes you'll get a NPE or something. In short, it's not a valid fix. It's a
case of the law "An incorrect program can be made arbitrarily fast."

scruz, can you please log a bug so we can track this?

This is definitely a performance bug. The question is how to fix it correctly,
and in a way that works with as wide a range as XMLA providers as possible.

There may be a valid fix by generating an MDX query to fetch members that
appear on axes but are not in cache. After a query has returned, find all
members that are not in the cache. Then generate an MDX statement 'select {m0,
m1, ..., mn} member properties p0, p1, ... pn on 0 from cube' and execute,
populating the cache.

Also, an orthogonal fix. Observe that we need the cache only for "advanced"
properties of members. Basic properties come back in the MDX result. So, write
an implementation of member that blocks and populates the cache only if
someone asks for one of these properties. With this fix in place, we can skip
populating the cache. Most of the time, we would never populate it, as the
members that came back from the query had all the information we need.

Luc, you say

we have added support for multiple member lookups in a single request, thus
eliminating the problem
SSAS doesn't support that.

They do provide a means for that. It is an MDX query. Hence my suggestion. See
also Mosha's blog post Querying dimensions in
MDX.

Hi all, thanks for your answers.

I understood the situation with the cell properties, obviously are not
generated because they aren't retrieved from server.

But what do yout think about this MDX as workaround?

WITH MEMBER . AS 'IIF (..CURRENTMEMBER.LEVEL.ORDINAL =
1,..CURRENTMEMBER.PROPERTIES("Departments"), NULL)'
SELECT ..MEMBERS ON ROWS, { ., .} ON COLUMNS FROM

I've tested this mdx, and other similars, and it works OK. With this query, i
don't access to properties with OLAP4J methods, desired properties are
retrieved as measures.

I made this kind of queries in order to retrieve the minimun kind of data per
query.

Is a valid workaround?

I think that you only found problems accessing to methods like
"member.getProperties()" and similar.

About the bug, ok I can open open. What is the objective of it? Improve
performance with SSAS?

The workaround looks like it will work. However you will need to change the
XMLA driver code to prevent the costly cache-population. So the bug will need
to be fixed too.

Subject of this bug is same as this thread -- lots of events produced against
SSAS. Please post the bug URL to this thread when you have it.

Ok, thank you vere much :-)

You are doing a very good job with olap4j, congratulations ;-)

i had to disappear during the discussion but i think you covered the main
points of the problem. i understand both sides and think that a solution like
that would fix the issue.

we just have to make the olap4j driver more "intelligent" and hence faster.
especially when it comes to metadata requests it should do that in batches
(like your request or what julian described) instead of a lot of single
requests

Any update on this? I have found that the first time I call executeOlapQuery()
on a connection it makes over 500 XMLA requests to the server, even for a very
simple query. Subsequent calls to executeOlapQuery() only make a single XMLA
request.

See my reply just now in the bug.

I am running into the same issue with SSAS , it creates close to 11K events for a simple MDX query. Is there any fix available yet for this expensive lookupMembersByUniqueName ?

Thanks
Ashish