更新: 2009-04-30

This Transact-SQL procedure creates hierarchies based on dimension properties, which are used to populate hierarchy tables.


[ @RC = ] dbo.bsp_DI_CreateHierarchyFromDimension 
@DimensionTableName = N'dimension_table_name' ,
@HierarchyTableName = N'hierarchy_table_name' ,
@ListOfColumns = N'list_of_coloumns' ,
[ @ColumnSeparator = ] N',' ,
@CurrentSiteId =  'current_site_id'
[,[ @IncludeNoneRow = ] N'T' | N'F ]
[,[ @SaveChanges = ] N'T' | N'F ]


  • **@DimensionTableName** = N'dimension_table_name'
    The name of dimension table (D_) that contains the columns on which the hierarchy is generated. dimension_table_name is nvarchar(128). It has no default.
  • **@HierarchyTableName** = N'hierarchy_table_name'
    The name of the temporary hierarchy table that will be created and populated based on the dimension columns. hierarchy_table_name is nvarchar(128). It has no default.

    This name is used only if SaveChanges is set to T.

  • **@ListOfColumns** = N'list of columns'
    The list of column names from the dimension that you can use to create the hierarchy. list_of_columns is nvarchar(max). It has no default.
  • [@ColumnSeparator** = ] N','
    The column separator. The data type is nchar(1). The default is '
  • **@CurrentSiteId** = N'current_site_id'
    The current site ID of the dimension. Current_iste_id is nvarchar(128). The default is 0, which the root model site.
  • [**@IncludeNoneRow** = ] N'T' | N'F'
    Indicates whether to include NONE row data in the table. T | F is nchar(1) The default is 'F'.
  • [**@SaveChanges** = ] N'T' | N'F'
    Indicates whether to save changes. T | F is nchar(1) The default is 'F'.

Return Value

Returns int value for an error code.


To run this procedure, you must have the fixed database roles of db_datareader and db_datawriter. You must also have explicit execute permissions on the database object.


The stored procedure creates a temporary table to which it will write hierarchy rows. After that, you must copy the data from the temp table to any "H_" table as required.

See Populate a property-based hierarchy for an example of populating a property-based hierarchy.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Planning Server.