Understanding the Tabular Object Model

A tabular model is a logical representation of tables and relationships for analytical purposes; the model also includes other features like hierarchies of attributes -to provide a richer drill-up and drill-down experience-, like perspectives -to simplify or focus the model into a smaller portion of it-, like Key Performance Indicators and many other features included. However, all logical representations must have a real ground if they are to be used; this section introduces you from the concepts to the internal implementation using AMO, see Developing with Analysis Management Objects (AMO) if you haven't used AMO before.

The approach here is top-down, all relevant objects in the tabular model are logically mapped to AMO objects and the required interaction or workflow explained; also, a source code sample to create a tabular model using AMO –AMO to Tabular- is available from Codeplex, for those of you who would like to see one of many possible implementations. An important note about the code: the code is provided as a support to the logical concepts explained here and should not be used in a production environment; nor should it be used for other purpose other than the pedagogical one.

Database Representation

A database provides the container object for the tabular model; all objects in a tabular model are contained in the database. In terms of AMO objects a database representation has a one to one mapping relationship with Database and no other main AMO objects are required; however, it is important to note that this doesn't mean that all contained objects in the AMO database object can be used when doing tabular modeling.

See Database Representation(Tabular) for a detailed explanation on how to create and manipulate the database representation.

Connection Representation

A connection establishes the relationship between the data to be included in a tabular model solution and the model itself. In terms of AMO objects a connection has a one to one mapping relationship with DataSourceand no other main AMO objects are required; however, it is important to note that this doesn't mean that all contained objects in the AMO datasource object can be used when doing tabular modeling.

See Connection Representation (Tabular) for a detailed explanation on how to create and manipulate the data source representation.

Table Representation

Tables are database objects that contain all the data in a database. In terms of AMO objects a table has a one to many mapping relationship, a table is represented by the usage of the following AMO objects: DataSourceView, Dimension, Cube, CubeDimension, MeasureGroup and Partition are the main required objects; however, it is important to note that this doesn't mean that all contained objects in the previously mentioned AMO objects can be used when doing tabular modeling.

See Tables Representation (Tabular) for a detailed explanation on how to create and manipulate the table representation.

Calculated Column Representation

Calculated columns are evaluated expressions that generate a column in a table, where a new value is calculated and stored for each row in the table. In terms of AMO objects a calculated column has a one to many mapping relationship, a calculated column is represented by the usage of the following AMO objects: Dimension and MeasureGroup are the main required objects; however, it is important to note that this doesn't mean that all contained objects in the previously mentioned AMO objects can be used when doing tabular modeling.

See Calculated Column Representation (Tabular) for a detailed explanation on how to create and manipulate the calculated column representation.

Calculated Measure Representation

Calculated Measures are stored expressions that are evaluated upon request once the model is deployed. In terms of AMO objects a calculated measure has a one to many mapping relationship, a calculated column is represented by the usage of the following AMO objects: Commandsand CalculationProperties are the main required objects; however, it is important to note that this doesn't mean that all contained objects in the previously mentioned AMO objects can be used when doing tabular modeling.

Note

Also, important distinction, the Measure objects have no relationship with the calculated measures in tabular models. And, they are not supported in tabular models.

See Calculated Measure Representation (Tabular) for a detailed explanation on how to create and manipulate the calculated measure representation.

Hierarchy Representation

Hierarchies are a mechanism to provide a richer drill-up and drill-down experience to the end user. In terms of AMO objects a hierarchy representation has a one to one mapping relationship with Hierarchy and no other main AMO objects are required; however, it is important to note that this doesn't mean that all contained objects in the AMO database object can be used when doing tabular modeling.

See Hierarchy Representation (Tabular) for a detailed explanation on how to create and manipulate the hierarchy representation.

Key Performance Indicator –KPI- Representation

A KPI is used to gauge performance of a value, defined by a Base measure, against a Target value. In terms of AMO objects a KPI representation has a one to many mapping relationship, a calculated column is represented by the usage of the following AMO objects: Commandsand CalculationProperties are the main required objects; however, it is important to note that this doesn't mean that all contained objects in the previously mentioned AMO objects can be used when doing tabular modeling.

Note

Also, important distinction, the Kpi objects have no relationship with the KPIs in tabular models. And, they are not supported in tabular models.

See Key Performance Indicator Representation (Tabular) for a detailed explanation on how to create and manipulate the KPI representation.

Partition Representation

For operational purposes, a table can be divided in different subsets of rows that when combined together form the table; each of those subsets is a partition of the table. In terms of AMO objects a partition representation has a one to one mapping relationship with Partition and no other main AMO objects are required; however, it is important to note that this doesn't mean that all contained objects in the AMO database object can be used when doing tabular modeling.

See Partition Representation (Tabular) for a detailed explanation on how to create and manipulate the partition representation.

Relationship Representation

A relationship is a connection between two tables of data. The relationship establishes how the data in the two tables should be correlated.

In tabular models, multiple relationships can be defined between two tables. When multiple relationships, between two tables, are defined only one can be defined as the default relationship for the model and it is named as the Active relationship; all other relationships are named as Inactive.

In terms of AMO objects all inactive relationships have a representation of a one to one mapping relationship with Relationship and no other main AMO objects are required; for the active relationship other requirements exist and a mapping to the ReferenceMeasureGroupDimension is also required. However, it is important to note that this doesn't mean that all contained objects in the AMO relationship or referenceMeasureGroupDimension object can be used when doing tabular modeling.

See Relationship Representation (Tabular) for a detailed explanation on how to create and manipulate the relationship representation.

Perspective Representation

A perspective is a mechanism to simplify or focus the model into a smaller portion of it for the client application. In terms of AMO objects a relationship representation has a one to one mapping relationship with Perspective and no other main AMO objects are required; however, it is important to note that this doesn't mean that all contained objects in the AMO perspective object can be used when doing tabular modeling.

See Perspective Representation (Tabular) for a detailed explanation on how to create and manipulate the perspective representation.

Warning

Perspectives are not a security mechanism; objects outside the perspective can still be accessed by the user through other interfaces.