Member tables (or element tables)

A member table has one row for each element of a dimension. These rows also contain the attribute values of each member.

The table can have any name. The name is stored in _Dimensions.Param1 or _Hierarchies.Param1.

This table describes the member table:

Column Type Description
MemberName String(71) The name of the element.
MemberType String(1) The element type:
  • N = Numeric
  • S = String
  • C = Consolidated
MemberOrder Integer The numeric position of the element within the dimension.
Attribute Columns Any Optional.

Describe the attributes of the dimension.

  • There can be any number of these up to 50; there can be none.
  • These columns can be distributed anywhere and need not appear together.
  • They must be listed in the _AttributeFields table in the rows belonging to their dimension.
  • Their attribute tables index by a number (1, 2 or 3) and not relational tables. Also specified in the _AttributeFields table.
Columns for Relations/Hierarchy As in Relationships Table. Optional.

Use if the dimension has a hierarchy and if a separate relationships table is not used.

If this table is used for the relations, then the name of this table must be specified in the Param2 column of _Dimensions or _Hierarchies.

General information

The dimension is loaded with the data in this table as rows.

The table can have any name. This name must be stored either in the _Dimensions table in the Param1 column in the row of the dimension whose name is found in the DimensionName column or in the _Hierarchies table in the Param1 column in the row of the hierarchy whose name is found in HierarchyName.

Attribute names must be stored in the _AttributeFields table for the dimension so the OLAP knows to which dimension the attribute data relates.

Optionally, columns can be used to describe the dimension’s element hierarchy. If used, they must be identical to the columns of the relations table and would be used as an alternative to using a separate relations table. The advantage of placing the relations here (and avoiding a separate table) is system simplicity. The disadvantage is that this does not allow multiple parents of an element. The name of this member table must appear in _Dimensions.Param2 as well as in _Dimensions.Param1.

A dimension is loaded with its elements from its member table using JobType 1 in _Jobs with the dimension name in _Jobs.JobObject. So are any relationships in this table loaded provided the name of this table is in _Dimensions.Param2.

Any attributes are also loaded with _Jobs.JobType 1. Attributes can be loaded separately with _Jobs.JobType = 6. In both these cases the dimension’s name must be in _Jobs.JobObject.

A dimension can have no more than 50 attributes. There should be no more than 50 attribute columns in the member table.

Member table creation with SQL Server

CREATE TABLE [dbo].[my_member_table /* Generic name */](
	[MemberName] [nvarchar](71) NOT NULL,
	[MemberType] [nvarchar](1) NOT NULL,
	[MemberOrder] [int] NULL
	/* Attribute Columns. Can be anywhere. */
	/* Columns from relations table if this does not exist. Can be anywhere. */
)