I'm attempting to use AdomdConnection.GetSchemaDataSet to see what information is in the AdomdSchemaGuid.PartitionStat dataset. I am setting the restrictions parameter to null.
The error I get is "Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException : XML for Analysis parser: The DATABASE_NAME restriction is required but is missing from the request."
All well and good, except that I can't see in the documentation what form the restrictions should be, the parameter is of type object[]. How are the restrictions defined? Is there any documentation that says what restrictions apply to which dataset? Is there any documentation that says what information is returned in the dataset for each AdomdSchemaGuid?
John.
If you were to connect to Analysis Server using SQL Profiler you would see that any call you make in AMO to obtain some schema rowset is translated to a Discover request. For instance
You can read more about schema rowsets in books online: http://msdn2.microsoft.com/en-us/library/ms126233(SQL.90).aspx
See what schema rowset is requested by AMO and then you can look it up in the BOL.
As for specifying restrictions:
restcoll = New AdomdRestrictionCollection;
restcoll.Add("CATALOG_NAME", "MyDatabase");
dsCubes = AdomdConnection.GetSchemaDataSet("MDSCHEMA_CUBES", restcoll)
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Hi Edward, thanks for this.
This is generally useful knowledge for me, but...
AdomdSchemaGuid.PartitionStat returns a dataset DISCOVER_PARTITION_STAT for which there is no detail of result columns in the documentation.
Further, the dataset is returned by a call to GetSchemaDataSet(Guid, object[]), where it is not documented what the objects of object[] should be. I assume the parameter is not an AdomdRestrictionCollection, otherwise why is the parameter not of that type in the first place, like the GetSchemaDataset(string, AdomdRestrictionCollection) call?
The documentation does not indicate what restrictions are required for each dataset, and the exception does not explicitly say what the name of the restriction is.
John.
|||
There is a little survey on bottom on page in books online. I would encourage you to fill it. This should give an idea about which topics in documentation should get improved.
As for the using discovers. I was getting at giving you some generic mechanism you can use to figure out how to use any discover request.
AS you have seen in Profiler AMO is sending a Discover request to Analysis Server behind the scenes.
You can compose such request directly in SQL Managemet studio open an XMLA query editor. For example of discovering all cubes in your database you can send following request:
<Envelope xmlns = "http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Discover xmlns = "urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList>
<Catalog>MyDatabase</Catalog>
</PropertyList>
</Properties>
</Discover>
</Body>
</Envelope>
The error messages you are getting back from the server when submitting such request should give you an idea what is missing.
In your case looks like the RestrictionList node is missing DATABASE_NAME restriction.
In AMO all AdomdRestrictionCollection object does: it is appending more elements to the RestrictionList node in the XMLA request.
So the mechanism is pretty generic, you should be able to figure out how to send almost any request and see what is getting returned back to you.
Hope that helps
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
You can find most schema rowsets descriptions for example here: http://msdn2.microsoft.com/en-us/library/ms126233(SQL.90).aspx (proabbly books online have them as well)
Usually you would be able to find the descriptions of returned columns and restrictions columns in the docs. However, i think some schemas are missing, and DISCOVER_PARTITION_STAT seems to be one of those.
In such a case i can suggest executing the Discover_Schema_Rowsets in the SSMS, which should return you the list of supported schemas and their restrictions.
Request can look something liek this:
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_SCHEMA_ROWSETS</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList/>
</Properties>
</Discover>
</Body>
</Envelope>
for the DISCOVER_PARTITION_STAT, we get the following restrictions:
<Restrictions>
<Name>DATABASE_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>CUBE_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>MEASURE_GROUP_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
<Restrictions>
<Name>PARTITION_NAME</Name>
<Type>xsd:string</Type>
</Restrictions>
Adomd.Net has a number of overloads for GetSchemaDataSet function.
All GetSchemaDataSet overloads basically require you to provide:
- which schema rowset to retrieve (either by schema guid, or by schema name)
- restrictions to be applied in the schema rowset request (either in the "oledb style" – as object[], or using the AdomdRestrictionCollection)
And return the resulting schema rowset as System.Data.DataSet.
In the "ole db" style case, restrictions are mapped by position, and in the other approach - by name.
Here are couple samples to retrieve the DISCOVER_PARTITION_STAT rowset.
// ole db – like approach
// schema specified by guid;
// restrictions matched by position;// assuming ‘con’ is an opened connection
object[] rest = new object[]
{
"Adventure Works DW", // DATABASE_NAME
"Adventure Works", // CUBE_NAME
"Internet Sales", // MEASURE_GROUP_NAME
"Internet_Sales_2002" // PARTITION_NAME
};DataTable partitionStat =
con.GetSchemaDataSet(
AdomdSchemaGuid.PartitionStat,
rest).Tables[0];
foreach (DataColumn column in partitionStat.Columns)
{
Debug.WriteLine(column.ColumnName);
}// another way – schema specified by name;
// restriction matching done by name;
// using restrictions collection// assuming ‘con’ is an opened connection
AdomdRestrictionCollection restrictions =
new AdomdRestrictionCollection();
restrictions.Add("DATABASE_NAME", "Adventure Works DW");
restrictions.Add("CUBE_NAME", "Adventure Works");
restrictions.Add("MEASURE_GROUP_NAME", "Internet Sales");
restrictions.Add("PARTITION_NAME", "Internet_Sales_2002");partitionStat =
con.GetSchemaDataSet(
"DISCOVER_PARTITION_STAT",
restrictions).Tables[0];foreach (DataColumn column in partitionStat.Columns)
{
Debug.WriteLine(column.ColumnName);
}
hope this helps some,
|||Thanks, these posts help a lot. As an aside, is there a reason there's no overloaded version of GetSchemaDataSet that takes a Guid and AdomdRestrictionCollection as parameters?
|||hello John,
There is no particular strict reason. The 2 sets of overloads available (with guid and name) are basically kind of 2 different style. Personally i prefer second one as it seems to be more self descriptive (especially in the restrictions part). But perhaps there are people who like the first one (and it is consistent with System.Data's GetOleDbSchemaTable). I don't think the overloads should be intermixed, but don't think there is any road block to create such overload. On the other hand i don't think there is a strong need to have one. So i guess it is sort of an arbitrary call.
thanks,
