[Mondrian] SqlTupleReader.readTuples generates huge IN list causing java.sql.SQLException

[Peter Tran]

Hi,

We have a report with the following dimensions:

Organization = 'A02-Textile Chemicals'
Location = 'DE - Germany'
Customer Sold-To = All
Customer Ship-To = All
Product = All (group by PPC)
Sales Rep = All
Time = 2006

Where the Product dimension has two levels -> Bulk_Dynamic -> PPC.  So
the above report wants it group by Product at the lowest level.

# distinct members at Product.Bulk_Dynamic level = 216,868
# distinct members at Product.PPC level          = 998,280        

This generates the following MDX:

WITH 
        SET [#DataSet#]       as
'NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin
(NonEmptyCrossJoin(NonEmptyCrossJoin({[Organization].[All
Organizations].[A-TLP].[A02-Textile Chemicals]}, {[Location_View].[All
Location_Views].[Europe].[Western Europe].[DE - Germany]}),
{[SoldTo_Customer_View].[All SoldTo_Customer_Views]}),
{[ShipTo_Customer_View].[All ShipTo_Customer_Views]}),
{Descendants([Product].[All Products], 2.0)}), {[Time].[All
Times].[2006]}), {[ShipTo_SalesArea].[All ShipTo_SalesAreas]})' 
        SET [#GrandTotalSet#] as
'NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin(NonEmptyCrossJoin
(NonEmptyCrossJoin(NonEmptyCrossJoin({[Organization].[All
Organizations].[A-TLP].[A02-Textile Chemicals]}, {[Location_View].[All
Location_Views].[Europe].[Western Europe].[DE - Germany]}),
{[SoldTo_Customer_View].[All SoldTo_Customer_Views]}),
{[ShipTo_Customer_View].[All ShipTo_Customer_Views]}), {[Product].[All
Products]}), {[Time].[All Times].[2006]}), {[ShipTo_SalesArea].[All
ShipTo_SalesAreas]})' 
SELECT  {
          -- Bunch of measures
        } 
        ON COLUMNS, 
        NON EMPTY 
UNION
        ([#GrandTotalSet#], Hierarchize({[#DataSet#]})) 
        ON ROWS 
FROM    [Sales_Fact] 
WHERE   ([MB_Region].[ALL MB_Regions], 
        [ShipTo_Location].[ALL ShipTo_Locations], 
        [Grp_Key_Account].[ALL Grp_Key_Accounts], 
        [Lab_Office].[ALL Lab_Offices], 
        [Sub_Class2].[ALL Sub_Class2s], 
        [Sub_Class1].[ALL Sub_Class1s], 
        [Main_Class].[ALL Main_Classs], 
        [Material_Class].[ALL Material_Classs], 
        [Trade_Code].[ALL Trade_Codes], 
        [Sales_Group3].[ALL Sales_Group3s], 
        [MB_Detail_Segment].[ALL MB_Detail_Segments], 
        [Converter].[ALL Converters], 
        [ABC_Code].[ALL ABC_Codes], 
        [Specifier].[ALL Specifiers], 
        [Wins_NonWins].[ALL Wins_NonWinss], 
        [Currency].[ALL Currencys], 
        [SoldTo_SalesArea].[ALL SoldTo_SalesAreas], 
        [Incoterms].[ALL Incotermss], 
        [Sales_Area].[ALL Sales_Areas], 
        [Payment_Term].[ALL Payment_Terms])

Analyzing the SQL that Mondrian executes, it does the following:

1)  SqlMemberSource.getMemberChildren to get all Products at
BULK_DYNAMIC level.

    select "PA_PRODUCT"."BULK_DYNAMIC" as "c0" 
    from "PA_PRODUCT" "PA_PRODUCT" 
    group by "PA_PRODUCT"."BULK_DYNAMIC" 
    order by "PA_PRODUCT"."BULK_DYNAMIC" ASC

2)  SqlTupleReader.readTuples to get all Products at PPC using a massive
IN list
    From step #1 above.

    select "PA_PRODUCT"."BULK_DYNAMIC" as "c0", 
           "PA_PRODUCT"."PPC" as "c1" 
    from "PA_PRODUCT" "PA_PRODUCT" 
    where ("PA_PRODUCT"."BULK_DYNAMIC" in ( .. Results from #1 ..)

Step 2 will fail, because it creates a SQL statement that is 8,377,847
characters long!

The JDBC driver fails with "java.sql.SQLException: No more data to read
from socket"

Is there anything we can do to restructure the MDX to prevent this from
happening?  Is there any way we can get this report to execute?

Thanks,
-Peter


The information contained in this email may be confidential and/or legally privileged. It has been sent for the sole use of the intended recipient(s). If the reader of this message is not an intended recipient, you are hereby notified that any unauthorized review, use, disclosure, dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please contact the sender by reply email and destroy all copies of the original message. Thank you