Performance difference in SSAS query in MSS Studio vs Java MDX

85 Views Asked by At

For context, I am not an SSAS expert, or even an avid user, I'm primarily a Java developer. We have a data science team that uses SSAS to write, develop and test various models.

In order to integrate the output of these models with other non-Microsoft systems, I am building a Java-based service that can query certain fields from the cube using Olap4j/XMLA to run an MDX query. But the performance (or lack thereof) is confusing me.

If I open MSS Studio, "Browse" the cube, drag a number of measures into the measures pane, toggle "Show Empty Cells" (otherwise for some reason I get no results), and hit execute, I get the expected results almost instantly. If I click on the red square to turn off "design mode", it takes me to the MDX code that looks something like:

SELECT { } ON COLUMNS, { (
    [Main].[Measure01].[Measure01].ALLMEMBERS * 
    [Main].[Measure02].[Measure02].ALLMEMBERS * 
    [Main].[Measure03].[Measure03].ALLMEMBERS * 
    [Main].[Measure04].[Measure04].ALLMEMBERS 
) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( 
  SELECT ( { 
    [Main].[Measure01].&[1] 
} ) ON COLUMNS FROM [Model]) 
CELL PROPERTIES VALUE

If I take this MDX query and paste it into my Java application, and run it, it takes over 30 seconds to return the results, using the following code:

    Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
    Connection connection = DriverManager.getConnection(cubeUrl);
    OlapConnection olapConnection = connection.unwrap(OlapConnection.class);
    olapConnection.setCatalog(catalog);

    OlapStatement statement = olapConnection.createStatement();

    LOG.info("Running Cube query");

    CellSet cellSet = statement.executeOlapQuery("<<The MDX Query here>>");

And the more measures I add, the slower it gets. I've tried putting some logging and debug breakpoints in my code, but it really seems like it's SSAS itself that is being slow in returning my data.

Bearing in mind I know very little about SSAS, what can I try? Does Olap4j have some config options I haven't set? Does MSS Studio do some optimization behind the scenes that is impossible for me to replicate?

EDIT 1:

On a hunch I installed Wireshark to monitor my network traffic, and while my query is running I see hundreds of thousands if not millions of packets going between my laptop and the SSAS server. Network packets are hard to interpret, but a lot of them seem to be sending HTTP data with the measure values in. Things like:

<Member Hierarchy="[Main].[Measure01]">
    <UName>[Main].[Measure01].&amp;[0]</UName>
    <Caption>0.00</Caption>
    <LName>[Main].[Measure01].[Measure01]</LName>
    <LNum>1</LNum>
    <DisplayInfo>131072</DisplayInfo>
    <MEMBER_CAPTION>0.00</MEMBER_CAPTION>
    <MEMBER_UNIQUE_NAME>[Main].[Measure01].&amp;[0]</MEMBER_UNIQUE_NAME>
    <MEMBER_NAME>0</MEMBER_NAME>
    <MEMBER_VALUE xsi:type="xsd:double">0</MEMBER_VALUE>
</Member>
<Member Hierarchy="[Main].[Measure02]">
    <UName>[Main].[Measure02].&amp;[0]</UName>
    <Caption>0</Caption>
    <LName>[Main].[Measure02].[Measure02]</LName>
    <LNum>1</LNum>
    <DisplayInfo>131072</DisplayInfo>
    <MEMBER_CAPTION>0</MEMBER_CAPTION>
    <MEMBER_UNIQUE_NAME>[Main].[Measure02].&amp;[0]</MEMBER_UNIQUE_NAME>
    <MEMBER_NAME>0</MEMBER_NAME>
    <MEMBER_VALUE xsi:type="xsd:double">0</MEMBER_VALUE>
</Member>
<Member Hierarchy="[Main].[Measure03]">
    <UName>[Main].[Measure03].&amp;</UName>
    <Caption/>
    <LName>[Main].[Measure03].[Measure03]</LName>
    <LNum>1</LNum>
    <DisplayInfo>131072</DisplayInfo>
    <MEMBER_UNIQUE_NAME>[Main].[Measure03].&amp;</MEMBER_UNIQUE_NAME>
    <MEMBER_VALUE xsi:nil="true"/>
</Member>

So it seems like the slowness might actually all be network traffic! Is there a way to get olap4j/IIS/SSAS to compress the traffic, so that I can get similar performance in olap4j as I get with MSS, where the same volume of data is retrieved in under a second?

0

There are 0 best solutions below