Populate a property-based hierarchy
A hierarchy in a dimension can be defined by one or more of its properties. If the property value is correctly defined, you can create a dimension hierarchy by using the bsp_DI_CreateHierarchyFromDimension stored procedure.
To populate a hierarchy based on dimension properties
Use the bsp_DI_CreateHierarchyFromDimension stored procedure to create a temporary hierarchy table in the staging database. The stored procedure creates a hierarchy according to specified columns or properties (see the example later in this topic).
Populate the specified hierarchy table from the temporary table created in step 1 of this procedure (see the example later in this topic).
After you populate a hierarchy, you might want to populate more hierarchies or load hierarchy data to the application database. To do this, follow the instructions in Step 5: Loading data from staging to application database.
The following two code samples show step 1 and step 2, as described in the procedures, for populating a hierarchy based on dimension properties.
The first code sample runs the bsp_DI_CreateHierarchyFromDimension stored procedure to create the T_Account_DetailAcct table, a temporary hierarchy that is based on the D_Account dimension, P_DetailAcct columns, and Label.
The second code sample populates the H_Account_DetailAcct hierarchy table from the T_Account_DetailAcct temporary hierarchy. BizSystemFlag is set to 200 to prepare for loading in 规划业务建模器.
USE [Alpine_Ski_House_StagingDB] GO DECLARE @return_value INT EXEC @return_value = [dbo].[bsp_CreateHierarchyFromDimension] @DimensionTableName = N'D_Account', @HierarchyTableName = N'T_Account_DetailAcct', @ListOfColumns = N'P_DetailAcct,Label', @ColumnSeparator = N',', @CurrentSiteId = 0, @IncludeNoneRow = N'F', @SaveChanges = N'T' SELECT 'Return Value' = @return_value GO INSERT INTO [Alpine_Ski_House_StagingDB].[dbo].[H_ Account_DetailAcct] ([MemberId] ,[ParentMemberId] ,[ CurrentSiteId] ,[LoadingControlID] ,[BizSystemFlag]) SELECT [MemberId] ,[ParentMemberId] ,[CurrentSiteId] ,[SequenceNumber] , 200 as BizSystemFlag FROM [Alpine_Ski_House_StagingDB].[dbo].[ T_Account_DetailAcct]