This Transact-SQL procedure creates hierarchies based on dimension properties, which are used to populate hierarchy tables.
Syntax
[ @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 ]
Arguments
-
@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.
Permissions
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.
Remarks
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
See Also