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.

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

Thanks,
Plamen.