更新: 2009-04-30

This Transact-SQL procedure converts labels to MemberIDs for a specified dimension.


[ @RC = ] dbo.bsp_DI_CreateLabelTableForDimension 
@DimensionName = N'model_site_label:dimension_label' ,
@ModelSiteName =  N'model_site_name'
[,[ @OverwriteExistingData = ] N'T' | N'F'] 


  • **@DimensionName** = N'model_site_label:dimension_label'
    The fully qualified label of the dimension. model_site_label:dimension_label is nvarchar(128). It has no default.
  • @ModelName** = N'model_site_name'
    The label of the model site. model_site_name is nvarchar(128)
    .** It has no default.
  • [ **@OverwriteExistingData** = ] N'T' | N'F'
    Indicates whether to overwrite existing data in the table. T | F is nchar(1). The default is 'T'.

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.


When you specify the @ModeltSiteName parameter in bsp_DI_ConvertLabelColumnToMemberIdForDimension, you specify which rows in the dimension will be included in the validation and ID-based conversion. The rows depend on the site to which the members belong. For example, if you specify @ModeltSiteName=N'ASH_Corporate' as a parameter, only the rows with OwnerId_Label = Ash_Corporate will be validated and converted.

If you want to validate and convert rows that have members that belong to a different site, you must specify that site in the modelsite parameter. Conversion of all rows in a dimension table from label-based to ID-based is an iterative process if you have dimension members that are owned at multiple sites.

You can find IDs for model sites in the ModelSiteTree table in the staging database.


This example shows the conversion of label-based table, D_Product_Label, back to an ID-based table in the application database. Only rows with OwnerId_Label = Ash_Corporate will be processed.

USE [Alpine_Ski_House_StagingDB]
DECLARE @return_value int
EXEC @return_value = [dbo].[bsp_DI_ConvertLabelColumnToMemberIdForDimension]
     @DimensionName = N'ASH_Corporate:Product',
     @ModelSiteName = N'ASH_Corporate',
     @OverwriteExistingData = N'T'
SELECT 'Return Value' = @return_value

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.