Populate a property-based hierarchy

1. 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).

2. 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]