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
  • 8831
    Suggested Answer
    posted

    Hello Gary,

     

    I have tested pre-filtering against Adventure Works sample database and the data appears almost immediately, but of course it depends on amount of the data and connection to the server. If you give us more information we probably can look for some oprtimization there.

    The restriction you have added makes that you have loaded only the member you have specified but that breaks the hierarchy structure we depend on. What you need is to add also TREE_OP restriction with value of 40 (it’s 0x20 and 0x08) Here are the possible flag values of TREE_OP

     

    ·         MDTREEOP_ANCESTORS (0x20) returns all of the ancestors.

    ·         MDTREEOP_CHILDREN (0x01) returns only the immediate children.

    ·         MDTREEOP_SIBLINGS (0x02) returns members on the same level.

    ·         MDTREEOP_PARENT (0x04) returns only the immediate parent.

    ·         MDTREEOP_SELF (0x08) returns itself in the list of returned rows.

    ·         MDTREEOP_DESCENDANTS (0x10) returns all of the descendants.

     

    You have probably seen these values in the documentation of MDSCHEMA_MEMBERS Rowset

     

    Your event handler will be:

        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]");

                        discoverMethod.Restrictions.Add("TREE_OP", 40);
                }
            }
        }

    Also you have to keep this line

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

    so we can process the response properly in the way we process pre-filtered hierarchies. Please note that this hierarchy will be trimmed to this member and its descendants and we don't have mechanism to extend that hierarchy further, so if you remove it from rows and add it again later (even using drag-drop) you will have the same pre-filtered structure.

     

    Best regards.

    Plamen.

Reply Children