Hi,
We have a Date dimension with an attribute that represents each individual date. We have about 10 years worth of data in our data warehouse which effectively means there are about 3650 individual dates in the Date dimension. As you can imagine, loading all these dates from the SSAS to the PivotGrid takes a significant amount of time.
We have written our own ADOMD provider for PivotGrid that connects to our domain service which uses ADOMD to fetch information from the SSAS database. This allowed us to change the behavior of the method responsible for returning the dimension attribute members so that it only returns the dates we actually want to analyze. As a result, the MDX query XmlaCommand class generates looks roughly as follows:
SELECT NON EMPTY (Hierarchize(VisualTotals(Hierarchize(AddCalculatedMembers({DrilldownMember({[Date].[Date].[All Dates]},{[Date].[Date].[All Dates]},RECURSIVE)}))),POST)) DIMENSION PROPERTIES CHILDREN_CARDINALITY,PARENT_UNIQUE_NAME ON COLUMNS FROM [Cube]
This MDX query will, of course, return all possible dates in the Date dimension and not just those that we want to analyze. Next we tried changing the method that returns the dimension attribute members so that it returns the dates we want and a few extra ones that will be marked as not being selected. This generates the following MDX query:
SELECT NON EMPTY (Hierarchize(VisualTotals(Hierarchize(Except(AddCalculatedMembers({DrilldownMember({[Date].[Date].[All Dates]},{[Date].[Date].[All Dates]},RECURSIVE)}),{[Date].[Date].&[20010108],[Date].[Date].&[20010109],[Date].[Date].&[20010110],[Date].[Date].&[20010111],[Date].[Date].&[20010112]}))),POST)) DIMENSION PROPERTIES CHILDREN_CARDINALITY,PARENT_UNIQUE_NAME ON COLUMNS FROM [Cube]
This is not much better as it will return all dates except for those few extra ones that were loaded but marked as not being selected.
Looks like the generated MDX query will always return all members with the members not being selected (IsSelected=False) removed by using the EXCEPT clause. Is there any way to change this behavior so that the generated MDX query would return only the members that are selected (IsSelected=true)?
With the latest service release we provide XmlaDataSource.InitXmlaMethod event. When you are listening for this event you are able to:
1. Track all Discover queries passed against XMLA service – it means that in the event handler you have access to properties and restrictions included into the SOAP body when Discover is posted.
2. Track all Execute queries – you have access to the statement which we generate and execute against XMLA service.
Have a look at this sample:
private void XmlaDataSource_InitXmlaMethod(object sender, InitXmlaMethodEventArgs e)
{
XmlaSoapMethodExecute executeMethod = e.SoapMethod as XmlaSoapMethodExecute;
if (executeMethod != null)
string statement = executeMethod.Statement;
}
The statement can be modified and the modified version is the one that we will pass to XMLA service. Hopefully we can process properly the result returned in response of the modified statement.
Regards
Unfortunately this doesn't really help us. As I said we have written our own ADOMD provider for the PivotGrid. Essentially this means that we have inherited XmlaCommand, XmlaDataProvider and XmlaDataSource and changed them so that they retrieve data from our own domain service instead of communicating directly with Analysis Services. Our domain service communicates with Analysis Services via ADOMD.NET.
In our AdomdCommand class (that inherits XmlaCommand) we do the following to generate the MDX query and send it to our domain service:
public override void ExecuteCommandAsync(IQuery query, XmlaConnection connection){ string commandText = GenerateCommand(query); if (this.CancelPending) { return; } AdomdService.GetGridData(commandText);}
Here we could modify the MDX query before it is passed to the domain service and eventually to SSAS. I think this would be kind of similar to the approach you suggested. However, modifying the MDX query via string manipulations sounds a bit dodgy. Of course it would be entirely possible for us to override the MDX query generator in XmlaCommand altogether but this is something we would have wanted to avoid.
Maybe for future versions you could consider having some sort of an option to specify whether to generate an MDX query that brings back only the members the user has specifically selected. Most MDX query generators I've seen work this way instead of explicitly stating which members not to bring back.
Hi teemuh-
We are struggling with the same integration you describe. Did you pursue a solution here and if so would you mind sharing? I am currently considering overwritting the GenerateQuery method to support our Filter()/Sort() needs as the string parsing seems messy.
Hi guys,
have you found any solution for using your own customized mdx query?
Many thanks,
Fabian Zandomeni
Hello Fabian,
Thank you for your post. I have further investigated the functionality that you are trying to achieve and also contacted our development team and it seems that currently the MDX query can be changed by creating custom XmalDataSource and overriding the XmlaCommand’s GenerateCommand method, since the Statement property of the XmlaSoapMethodExecute is currently read only. You can refer to the following blog post:
http://es.infragistics.com/community/blogs/philip_atanassov/archive/2012/06/20/building-a-custom-data-provider-for-xampivotgrid-in-silverlight-using-wcf-and-adomd-net.aspx
where you can find an example on how custom XmlaDataSource can be created. The blog post is for Silverlight and it can be used for WPF also.