3.1.14 Semantic Query
The following is an example that shows how to define a semantic query that can be used for querying a semantic model. Such a query would typically be used in a Query.CommandText element of a DataSet report definition language (RDL) file when creating a report against a semantic model. The example shows a Hierarchies definition, which includes Grouping and Filter elements; a MeasureGroups definition, which includes Measures; and definitions for CalculatedAttributes and a Parameter.
The following example of a SemanticQuery element is extracted from an RDL file where it is used to make a semantic query against a semantic model. The SemanticQuery element has several attributes, one of which specifies the definition of the SMDL Schema namespace to be http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling. The other attributes specify various namespace prefixes.
The first child element in the SemanticQuery is a Hierarchies collection that contains a single Hierarchy member. This Hierarchy element contains a BaseEntity, a Groupings collection, and a Filter element. The BaseEntity references the "Sale" Entity via the EntityID that has a QName value of g7d048573-fb87-42c3-9660-f171d6645f3a. This value is the value of an Entity.ID attribute for an Entity in the semantic model that returns "Sales" data. The Groupings collection has five Grouping members named "Product Category", "Product Subcategory", "Product", "Order Year", and "Order Quarter".
-
<SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder"> <Hierarchies> <Hierarchy> <BaseEntity> <!--Sale--> <EntityID>G7d048573-fb87-42c3-9660-f171d6645f3a</EntityID> </BaseEntity> <Groupings>
The following Grouping element specifies a grouping of the "Sale" Entity data by the "Product Category" Entity. The name of the Grouping is specified by the value of the Name attribute, which is "Product Category". The Expression element specifies the expression by which to group the data. The Path collection has three RolePathItem members that reference the "Product", "Product Subcategory", and "Product Category" Role elements via the RoleID elements that have values that match the value of Role.ID attributes for Role elements in the semantic model.
The EntityRef element references the "Product Category" Entity via the EntityID that has a QName value of gacb1e610-3ea3-4922-a2d8-e503e1d0a3d0. This value is the value of an Entity.ID attribute for an Entity in the semantic model that returns "Product Category" data.
The Details collection specifies a single Expression to display for the grouping. This Expression returns the Name of instances of the "Product Category" Entity because the AttributeRef element references the "Name" Attribute via the AttributeID that has a QName value of g06eeff71-1bf2-461f-a84b-ff6268e3f684. This value is the value of an Attribute.ID attribute for an attribute in the semantic model that returns "Name of Product Category" data.
-
<Grouping Name="Product Category"> <Expression Name="Product Category"> <Path> <RolePathItem> <!--Product--> <RoleID>Gbc0575d8-c020-4b4a-be45-bbf88013aca9</RoleID> </RolePathItem> <RolePathItem> <!--Product Subcategory--> <RoleID>G59f93d17-7b9a-4700-a101-9322fdb58109</RoleID> </RolePathItem> <RolePathItem> <!--Product Category--> <RoleID>G4d860b0a-2b57-4967-8fb9-133beee07067</RoleID> </RolePathItem> </Path> <EntityRef> <!--Product Category--> <EntityID>Gacb1e610-3ea3-4922-a2d8-e503e1d0a3d0</EntityID> </EntityRef> </Expression> <Details> <Expression Name="Product Category1"> <AttributeRef> <!--Name--> <AttributeID>G06eeff71-1bf2-461f-a84b-ff6268e3f684</AttributeID> </AttributeRef> </Expression> </Details> </Grouping>
The following Grouping element specifies a grouping of the "Sale" Entity data by the "Product Subcategory" Entity. The explanation of this Grouping is similar to the preceding Grouping element.
-
<Grouping Name="Product Subcategory"> <Expression Name="Product Subcategory"> <Path> <RolePathItem> <!--Product--> <RoleID>Gbc0575d8-c020-4b4a-be45-bbf88013aca9</RoleID> </RolePathItem> <RolePathItem> <!--Product Subcategory--> <RoleID>G59f93d17-7b9a-4700-a101-9322fdb58109</RoleID> </RolePathItem> </Path> <EntityRef> <!--Product Subcategory--> <EntityID>G8f91f894-d044-4da9-a138-772900aee688</EntityID> </EntityRef> </Expression> <Details> <Expression Name="Product Subcategory2"> <AttributeRef> <!--Name--> <AttributeID>G00b169b7-9f08-4f96-babd-5924781607b5</AttributeID> </AttributeRef> </Expression> </Details> </Grouping>
The following Grouping element specifies a grouping of the "Sale" Entity data by the "Product" Entity. The explanation of this Grouping is similar to the preceding Grouping elements.
-
<Grouping Name="Product"> <Expression Name="Product"> <Path> <RolePathItem> <!--Product--> <RoleID>Gbc0575d8-c020-4b4a-be45-bbf88013aca9</RoleID> </RolePathItem> </Path> <EntityRef> <!--Product--> <EntityID>G73aed554-12c3-4750-8981-997fe69705ab</EntityID> </EntityRef> </Expression> <Details> <Expression Name="Product3"> <AttributeRef> <!--Name--> <AttributeID>G64343d16-f565-44c8-884c-681a8716c07e</AttributeID> </AttributeRef> </Expression> </Details> </Grouping>
The following Grouping element specifies a grouping of the "Sale" Entity data by the "Order Year" Entity. The name of the Grouping is specified by the value of the Name attribute, which is "Order Year". The Expression element specifies the expression by which to group the data. The Path collection has a single RolePathItem member, which references the "Sales Order" Role element via the RoleID element that has a value that matches the value of the Role.ID attribute for the "Sales Order" Role in the semantic model.
The AttributeRef element references the "Product Category" Attribute via the AttributeID that has a QName value gb4a8fe12-4ec9-4fa9-8b33-82a9acfea68c. This value is the value of an Attribute.ID attribute for an Attribute in the semantic model that returns "Product Category" data.
-
<Grouping Name="Order Year"> <Expression Name="Order Year"> <Path> <RolePathItem> <!--Sales Order--> <RoleID>Gd807fa2d-8de1-4304-90d7-ea92ecf54d77</RoleID> </RolePathItem> </Path> <AttributeRef> <!--Order Year--> <AttributeID>Gb4a8fe12-4ec9-4fa9-8b33-82a9acfea68c</AttributeID> </AttributeRef> </Expression> </Grouping>
The following Grouping element specifies a grouping of the "Sale" Entity data by the "Order Quarter" Entity. The explanation of this Grouping is similar to the preceding one.
-
<Grouping Name="Order Quarter"> <Expression Name="Order Quarter"> <Path> <RolePathItem> <!--Sales Order--> <RoleID>Gd807fa2d-8de1-4304-90d7-ea92ecf54d77</RoleID> </RolePathItem> </Path> <AttributeRef> <!--Order Quarter--> <AttributeID>Gd7e9b121-ae15-44be-9fec-0a0265c133f4</AttributeID> </AttributeRef> </Expression> </Grouping> </Groupings>
The Filter element that follows has a single filter Expression that contains several nested functions. The value of this Expression is a Boolean because the top-level function And has a Boolean return type.
The result of this filter is to filter the detail data by selecting only those instances where the "Product Category" is in the set {AAEAAAA=, AAMAAAA=, AAQAAAA=} and the "Order Date" is greater than or equal to the value of the "Order Date" parameter. A detailed explanation of the Filter element components follows.
-
<Filter> <Expression Name="expr1"> <Function> <FunctionName>And</FunctionName> <Arguments>
The first argument to the And function in the preceding XML snippet is the expression that contains the function In, which determines whether a value is contained in a given set.
-
<Expression> <Function> <FunctionName>In</FunctionName> <Arguments>
In has as its first argument an Expression that has a reference to the "Product Category" Entity. This reference is given by the EntityRef and EntityID elements. The QName value gacb1e610-3ea3-4922-a2d8-e503e1d0a3d0 is the value of the Entity.ID attribute of an Entity in the semantic model.
-
<Expression> <Path> <RolePathItem> <!--Product--> <RoleID>Gbc0575d8-c020-4b4a-be45-bbf88013aca9</RoleID> </RolePathItem> <RolePathItem> <!--Product Subcategory--> <RoleID>G59f93d17-7b9a-4700-a101-9322fdb58109</RoleID> </RolePathItem> <RolePathItem> <!--Product Category--> <RoleID>G4d860b0a-2b57-4967-8fb9-133beee07067</RoleID> </RolePathItem> </Path> <EntityRef> <!--Product Category--> <EntityID>Gacb1e610-3ea3-4922-a2d8-e503e1d0a3d0</EntityID> </EntityRef> </Expression>
In has as its second argument an Expression that specifies the literal set of three EntityKey values {AAEAAAA=, AAMAAAA=, AAQAAAA=}. The data type of this Literal element is specified as EntityKey by the Literal.DataType element, and the value of this literal is specified as the set of values of the three Values.Value elements.
-
<Expression> <Literal> <DataType>EntityKey</DataType> <Values> <Value>AAEAAAA=</Value> <Value>AAMAAAA=</Value> <Value>AAQAAAA=</Value> </Values> </Literal> </Expression> </Arguments> </Function> <CustomProperties> <CustomProperty Name="qd:FilterCondition" /> </CustomProperties> </Expression>
The second argument to the And function in the preceding XML snippet is the Expression that contains the function GreaterThanOrEquals.
-
<Expression> <Function> <FunctionName>GreaterThanOrEquals</FunctionName> <Arguments>
GreaterThanOrEquals has as its first argument an expression that contains the scalar function Date that has as its argument an expression that contains a reference to the "Order Date" Attribute. This reference is given by the AttributeRef and AttributeID elements. The QName value g1f88a445-ae2b-497e-8b0b-3f797fbc868c is the value of an Attribute.ID attribute of the "Order Date" Attribute in the semantic model.
-
<Expression> <Function> <FunctionName>Date</FunctionName> <Arguments> <Expression> <Path> <RolePathItem> <!--Sales Order--> <RoleID>Gd807fa2d-8de1-4304-90d7-ea92ecf54d77</RoleID> </RolePathItem> </Path> <AttributeRef> <!--Order Date--> <AttributeID>G1f88a445-ae2b-497e-8b0b-3f797fbc868c</AttributeID> </AttributeRef> </Expression> </Arguments> </Function> </Expression>
The second argument of the GreaterThanOrEquals function is an expression that contains a reference to the "Order Date" Parameter via the ParameterRef element with child element ParameterRef.ParameterName with a value of "Order Date".
-
<Expression> <ParameterRef> <ParameterName>Order Date</ParameterName> </ParameterRef> </Expression> </Arguments> </Function> <CustomProperties> <CustomProperty Name="qd:FilterCondition" /> </CustomProperties> </Expression> </Arguments> </Function> <CustomProperties> <CustomProperty Name="qd:Filter" /> <CustomProperty Name="qd:ContextEntityID"> <Value xsi:type="xsd:string">G7d048573-fb87-42c3-9660-f171d6645f3a</Value> </CustomProperty> <CustomProperty Name="qd:AutoChangeBaseEntity" /> <CustomProperty Name="qd:Design"> <Value xsi:type="xsd:string">expr2</Value> </CustomProperty> </CustomProperties> </Expression> </Filter> </Hierarchy> </Hierarchies>
The MeasureGroups collection contains a single MeasureGroup element with a BaseEntity child element that references the "Sale" Entity by the BaseEntity.EntityID element whose value is the QName with value g7d048573-fb87-42c3-9660-f171d6645f3a.
In the MeasureGroup, the Measures collection contains a single Expression that contains the aggregate function Sum with an Expression as its single argument that returns the values of the "Total Due" Attribute as specified by the AttributeRef.AttributeID element with the QName value g39091120-95cf-4639-8b56-be464861440c. Also of interest is that the Measures.Expression element has a CustomProperties.CustomProperty element that specifies implementation-specific properties for model-design tools.
The Path collection in the Expression that is the argument of the Sum function specifies the path by which to reach the entity whose context is used to evaluate the expression. The Path collection has a single member, a RolePathItem that contains a reference to a Role via the RoleID element whose value is the QName value gd807fa2d-8de1-4304-90d7-ea92ecf54d77. This is the GUID of a Role in the semantic model whose name is "Sales Order".
-
<MeasureGroups> <MeasureGroup> <BaseEntity> <!--Sale--> <EntityID>G7d048573-fb87-42c3-9660-f171d6645f3a</EntityID> </BaseEntity> <Measures> <Expression Name="Sum Total Due_Sum"> <Function> <FunctionName>Sum</FunctionName> <Arguments> <Expression> <Path> <RolePathItem> <!--Sales Order--> <RoleID>Gd807fa2d-8de1-4304-90d7-ea92ecf54d77</RoleID> </RolePathItem> </Path> <AttributeRef> <!--Total Due--> <AttributeID>G39091120-95cf-4639-8b56-be464861440c</AttributeID> </AttributeRef> </Expression> </Arguments> </Function> <CustomProperties> <CustomProperty Name="qd:Design"> <Value xsi:type="xsd:string">Sum Total Due</Value> </CustomProperty> </CustomProperties> </Expression> </Measures> </MeasureGroup> </MeasureGroups>
The CalculatedAttributes collection contains a single Expression whose name is "Sum Total Due", as specified by the Name attribute. The Expression evaluates the function Aggregate, which has one argument in the Arguments collection.
The argument is an Expression that has a Path child element and an AttributeRef element. The Expression returns the "Sum Total Due" Attribute because the AttributeRef element references the "Sum Total Due" Attribute via the AttributeID that has a QName value of g61256a47-8571-4aae-b9f5-0389812e8a3e. This value is the value of an Attribute.ID attribute for an Attribute in the semantic model that returns "Sum Total Due" data.
The Path collection specifies the path by which to reach an entity whose context is used to evaluate the expression. The Path collection has a single member, a RolePathItem that contains a reference to a Role via the RoleID element whose value is the QName value gd807fa2d-8de1-4304-90d7-ea92ecf54d77. This value is the value of the Role.ID attribute for a Role in the semantic model whose name is "Sales Order".
-
<CalculatedAttributes> <Expression Name="Sum Total Due"> <Function> <FunctionName>Aggregate</FunctionName> <Arguments> <Expression> <Path> <RolePathItem> <!--Sales Order--> <RoleID>Gd807fa2d-8de1-4304-90d7-ea92ecf54d77</RoleID> </RolePathItem> </Path> <AttributeRef> <!--Sum Total Due--> <AttributeID>G61256a47-8571-4aae-b9f5-0389812e8a3e</AttributeID> </AttributeRef> </Expression> </Arguments> </Function> <CustomProperties> <CustomProperty Name="qd:ContextEntityID"> <Value xsi:type="xsd:string">G7d048573-fb87-42c3-9660-f171d6645f3a</Value> </CustomProperty> </CustomProperties> </Expression> </CalculatedAttributes>
The SemanticQuery has a single Parameter in its Parameters collection. This Parameter has a name of "Order Date" specified by its Name attribute. The data type of the parameter is specified as "DateTime" by the Parameter.DataType element, and the parameter cannot have a NULL value, because the Parameter.Nullable element is not specified. Finally, the Expression specifies the default value of the Parameter element as being the DateTime value 2003-05-01T00:00:00. This is specified by the values of the Literal.DataType and Literal.Value elements for the Literal element that is the content of the Expression being evaluated.
-
<Parameters> <Parameter Name="Order Date"> <DataType>DateTime</DataType> <Expression> <Literal> <DataType>DateTime</DataType> <Value>2003-05-01T00:00:00</Value> </Literal> </Expression> </Parameter> </Parameters> </SemanticQuery>