Forums Retired Products and Controls
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Gary
760
Adding MDSCHEMA_MEMBERS restriction causes malformed Execute statement
posted

We have a need to limit the rows axis to specific members:

xmlaDataSource.Rows = DataSourceBase.GenerateInitialItems([Date].[Calendar]{[Date].[Calendar].[Date].&[20080702]}");

Unfortunately, this causes a change in behavior within XmlaDataSource.UpdateHierarchiesCollection whereby the entire dimension hierarchy is retrieved instead of just a single level. Looking at the code it seems like the design approach in this case is to fetch the hierarchy and then apply this "pre-filter" after the dimension results are returned.

The associated performance impact is severe -- in the dimension we are working with there is still no response after 10 minutes, whereas without the member value it is a few seconds.

It would be wonderful if Infragistics can address this issue! It's a severe limitation for us and I expect others.

If that is not feasible in the near-term, I still need to resolve it for my own purpose. My approach is to add an event handler for InitXmlaMethod and modify the MDSCHEMA_MEMBERS RequestType to add a MEMBER_UNIQUE_NAME restriction for the member value:

 

    void xmlaDataSource_InitXmlaMethod(object sender, InitXmlaMethodEventArgs e)
    {
        var discoverMethod = e.SoapMethod as XmlaSoapMethodDiscover;
        if (discoverMethod != null)
        {
            if (discoverMethod.RequestType == "MDSCHEMA_MEMBERS")
            {
                    discoverMethod.Restrictions.Add("MEMBER_UNIQUE_NAME", "[Date].[Calendar].[Date].&[20080702]");
            }
        }
    }

 

Looking at the request and response in Fiddler, I see that this works well.

However, this change causes the following Execute request to generate a malformed statement.

Below is what the statement looks like without adding the restriction. Bolded is what is omitted from the statement when the restriction is added.

 

SELECT

NON EMPTY

{{[Date].[Calendar].[All Periods]},

{[Date].[Calendar].[All Periods].Children},

{[Date].[Calendar].[Calendar Year].&[2008].Children},

{[Date].[Calendar].[Calendar Semester].&[2008]&[2].Children},

{[Date].[Calendar].[Calendar Quarter].&[2008]&[3].Children},

{[Date].[Calendar].[Month].&[2008]&[7].Children}}

DIMENSION PROPERTIES

CHILDREN_CARDINALITY

,PARENT_UNIQUE_NAME

ON COLUMNS

FROM

(

SELECT

[Date].[Calendar].[Date].&[20080701] ON COLUMNS

FROM [Adventure Works]

)

WHERE ([Measures].[Internet Sales Amount])

 

I appreciate your assistance with working through this difficulty.

Thanks,

Gary

Parents Reply
  • 760
    posted in reply to [Infragistics] Plamen Pilev

    Hello Plamen,

    I am referring to the XMLA Execute statement:

    The goal is to be able to use the XmlaDataSource to query data without the side effect of loading complete dimensions.

    When the Rows or Columns property is initialized with filters (individual member values), these filters are not included within the Execute statement, but are instead applied as "pre-filters" on the results that are returned.

    xmlaDataSource.Rows = DataSourceBase.GenerateInitialItems([Date].[Calendar]{[Date].[Calendar].[Date].&[20080702]}");

    To support this approach, the entire hierarchy or the entire level of applicable dimensions are loaded, which causes a performance issue on dimension levels with a large number of members.

    When we apply filters we are reducing the number of applicable members from a large number to a small number. We would like the XmlaDataSource to not have the overhead of querying the large number of members when only a small number are needed.

    Hopefully this makes sense, but please let me know if you need more information.

    Regards,

    Gary

Children