CSDL Concepts
Conceptual Schema Definition Language (CSDL) is based on the Entity Data Framework, which is an abstraction for representing different types of data in a way that enables disparate data sets to be programmatically accessed, queried, or exported. CSDL is used to represent tabular models for reporting because it is especially well-suited for defining data–driven applications.
This section explains how the entity data model applies to tabular models, and provides examples of how a model is represented in CSDL.
Examples used to illustrate these concepts are taken from the AdventureWorks sample database, available on Codeplex. For more information about the samples, see Adventure Works Samples for SQL Server.
Structure of a Model in CSDL
A CSDL document that describes a report model and its data begins with the xsd statement, followed by the definition of a model.
The model is a namespace, which contains the following major entities, associations, and properties:
The EntityContainer lists the tables in the model.
Each table is listed with the EntityContainer as an EntitySet.
Each relationship between two tables is described as an AssociationSet that defines the relationship end points and the relationship roles.
The EntityType element is extended for BISM to provide additional detail about the tables and the columns in them, including properties for sorting and display purposes.
The Measure element defines calculations that can be used in the model. A measure can be turned into a KPI by adding a set of special display attributes, using the new KPI Element.
There is no separate representation of perspectives. Columns and tables that are not included in a perspective are present in the CSDL but flagged with the Hidden attribute.
Entities, EntitySets, and EntityTypes
The notion of an entity in the Entity Data Framework is extended to represent columns and tables from the data model. The following excerpt shows the list of EntitySet elements in a simple model containing only three tables.
<EntityContainer Name="SimpleModel">
<EntitySet Name="DimCustomer"EntityType="SimpleModel.DimCustomer">
<bi:EntitySet />
</EntitySet>
<EntitySet Name="DimDate" EntityType="SimpleModel.DimDate">
<bi:EntitySet />
</EntitySet>
<EntitySet Name="DimGeography" EntityType="SimpleModel.DimGeography">
<bi:EntitySet />
</EntitySet> />
The EntitySet does not contain information about columns or data in the table. The detailed description of the columns and their properties is provided in the EntityType element.
The EntityType element for each entity (table) includes a collection of properties that define the key column, the data type and length of the column, nullability, sorting behavior, and so forth. For example, the following CSDL excerpt describes three columns in the Customer table. The first column is a special hidden column used internally by the model.
<EntityType Name="Customer">
<Key>
<PropertyRef Name="RowNumber" />
</Key>
<Property Name="RowNumber" Type="Int64" Nullable="false">
<bi:Property Hidden="true" Contents="RowNumber"
Stability="RowNumber" />
</Property>
<Property Name="CustomerKey" Type="Int64" Nullable="false">
<bi:Property />
</Property>
<Property Name="FirstName" Type="String" MaxLength="Max" FixedLength="false">
<bi:Property />
</Property>
To limit the size of the CSDL document that is generated, properties that appear more than once in an entity are specified by a reference to an existing property, so that the property need be listed only once for the EntityType. The client application can get the value of the property by finding the EntityType that matches the OriginEntityType.
Relationships
In the Entity Data Framework, relationships are defined as associations between entities.
Associations always have exactly two ends, each pointing to a field or column in a table. Therefore, multiple relationships are possible between two tables, if the relationships have different end points. A role name is assigned to the end points of the association, and indicates how the association is used in the context of the data model. An example of a role name might be ShipTo, when applied to a customer ID that is related to the customer ID in an Orders table.
The CSDL representation of the model also contains attributes on the association that determine how the entities are mapped to each other in terms of the multiplicity of the association. Multiplicity indicates whether the attribute or column at the end point of a relationship between tables is on the one side of a relationship, or on the many side. There is no separate value for one-to-one relationships. The BISM extensions for CSDL support multiplicity of 0 (meaning the entity is not associated with anything) or 0..1, which means either a one-one relationship or a one-to-many relationship.
The following sample represents the CSDL definition of a relationship between the tables, Date and ProductInventory, where the two tables are joined on the column DateAlternateKey. Notice that, by default, the name of the AssociationSet is the fully qualified name of the columns that are involved in the relationship. However, you can change this behavior when you design the model, to use a different naming format.
<AssociationSet Name="ProductInventory_Date_DateKey" Association="Model.ProductInventory_Date_DateKey">
<End EntitySet="ProductInventory" />
<End EntitySet="Date" />
<bi:AssociationSet />
</AssociationSet>
Visualization and Navigation Properties
An important part of the extensions to the Entity Data Framework for tabular modeling are the properties for defining presentation in the reporting layer, and for navigating the relationships among entities. Typically, when you are creating a data model, you do not consider it important to control how the data is ordered or grouped, or what the default value might be, on the assumption that the client application will specify ordering and other details of presentation. However, Analysis Services tabular models are designed for integration with the Power View reporting client, and includes properties and attributes that support presentation of entities from the data model in the report design surface.
Extensions for visualization include attributes for specifying the default aggregation to use with numerical data, for indicating that a text field points to a URL of an image, or specifying the field used to sort the current field.
Name Properties and Naming Conventions
The CSDL specification provides that each entity has a unique name and an identifier that can be used as a key. In addition, some entities can have captions used for display purposes, and contextual names that change depending on where the entity is used.
The Documentation element provides the opportunity for report designers to furnish a description of the entity, to help business users understand the meaning of the data. Some entities also allow one or more Annotation attributes, which provide extra metadata for consumption by the application or by clients.
When you generate a model the Analysis Services tools, the names that are created for objects follow the Analysis Services conventions for object naming and name uniqueness. However, because CSDL is a part of the Entity Data Framework, which requires that names adhere to conventions for C# identifiers, when the server creates the CSDL for a model, the server takes the names used within the Analysis Services schema and automatically creates new object names that comply with CSDL requirements. The following table describes the operations by which the new names are generated.
Rule |
Action |
Example |
|---|---|---|
No forbidden characters |
Forbidden characters are replaced by underscore. |
Example under construction |
Names must be unique |
If two strings are the same, one is made unique by appending underscore plus a number |
example under construction |
Captions and qualifiers both have translations, and for a given language, one or the other might be present. This means that in cases where a qualifier and name or qualifier and caption are concatenated, the strings could be in two different languages.