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'.
Returns int value for an error code.
To run this procedure, you must have the fixed database roles of
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.