USP_UDM_OLAPDATASOURCE_CREATEORUPDATE

Inserts or updates a row in OLAPDATASOURCE. Used by the Report Mart deployment utility.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@SOURCENAME	nvarchar(255)	IN
@SOURCEDESCRIPTION	nvarchar(2048)	IN
@MARTKEY	nvarchar(255)	IN
@CONNECTIONSTRING	nvarchar(2048)	IN
@IMPERSONATE	bit	IN
@IMPERSONATEUSERACCOUNTSID	varbinary	IN
@IMPERSONATEUSERPWD	nvarchar(128)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@IMPERSONATELOCALUSERNAME	nvarchar(255)	IN
@UDMDEPLOYMENTMANIFEST	xml	IN
@ETLSERVERNAME	nvarchar(128)	IN
@ETLJOBNAME	nvarchar(255)	IN
@ETLJOBSTEPNAME	nvarchar(255)	IN
@MARTMANIFEST	xml	IN
@ETLDATABASENAME	nvarchar(128)	IN
@ISOLAP	bit	IN
@INCLUDESELECTIONS	bit	IN
@VALIDATIONPROCESSID	uniqueidentifier	IN
@ETLMAXPARALLELISM	tinyint	IN
@DATEQUERYNAME	nvarchar(128)	IN
@DATEQUERYKEYCOLUMN	nvarchar(128)	IN
@SUPPORTSROLLINGDATES	bit	IN

Definition

Copy



CREATE procedure dbo.[USP_UDM_OLAPDATASOURCE_CREATEORUPDATE]

/* Used by the OLAP Report Deployment utility */

@ID uniqueidentifier = null output,
@SOURCENAME nvarchar(255) ,
@SOURCEDESCRIPTION nvarchar(2048)='' ,
@MARTKEY nvarchar(255) ='',
@CONNECTIONSTRING nvarchar(2048),
@IMPERSONATE bit=0,
@IMPERSONATEUSERACCOUNTSID varbinary(85) = null,
@IMPERSONATEUSERPWD nvarchar(128)=null,
@CHANGEAGENTID uniqueidentifier = null,
@IMPERSONATELOCALUSERNAME nvarchar(255) = null,
@UDMDEPLOYMENTMANIFEST xml = null,
@ETLSERVERNAME nvarchar(128)='',
@ETLJOBNAME nvarchar(255)='',
@ETLJOBSTEPNAME nvarchar(255)='',
@MARTMANIFEST xml,
@ETLDATABASENAME nvarchar(128)='',
@ISOLAP bit=1,
@INCLUDESELECTIONS bit = 0,
@VALIDATIONPROCESSID uniqueidentifier=null,
@ETLMAXPARALLELISM tinyint = 1,
@DATEQUERYNAME nvarchar(128)='',
@DATEQUERYKEYCOLUMN nvarchar(128)='',
@SUPPORTSROLLINGDATES bit=0

as

set nocount on;

if @CHANGEAGENTID is null
  exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

if @ID is null
  set @ID=newid();

if @IMPERSONATE =1 and (
            (coalesce(@IMPERSONATEUSERPWD,'') = '')
            or
            (@IMPERSONATEUSERACCOUNTSID is null and @IMPERSONATELOCALUSERNAME is null)
            )
  begin

    raiserror('Calling sp dbo.USP_UDM_OLAPDATASOURCE_CREATEORUPDATE with @IMPERSONATE =1 requires a non-blank @IMPERSONATEUSERPWD and @IMPERSONATEUSERACCOUNTSID OR @IMPERSONATELOCALUSERNAME',16,10);
    return 10;

  end

if @IMPERSONATE =0
  set    @IMPERSONATEUSERPWD='';
else
begin

    exec dbo.USP_GET_KEY_ACCESS;  

  set @IMPERSONATEUSERPWD = EncryptByKey(Key_GUID('sym_BBInfinity'), @IMPERSONATEUSERPWD);

  close symmetric key sym_BBInfinity;

end


if exists (select [ID] from dbo.[OLAPDATASOURCE] where [ID] = @ID)
  begin

  update dbo.[OLAPDATASOURCE]
    set    
    [SOURCENAME] = @SOURCENAME,
    [SOURCEDESCRIPTION] = @SOURCEDESCRIPTION,        
    [MARTKEY] = @MARTKEY,
    [CONNECTIONSTRING] = @CONNECTIONSTRING,
    [IMPERSONATE] = @IMPERSONATE,        
    [IMPERSONATEUSERACCOUNTSID] = @IMPERSONATEUSERACCOUNTSID,
    [IMPERSONATEUSERPWD] = @IMPERSONATEUSERPWD,        
    [IMPERSONATELOCALUSERNAME] = @IMPERSONATELOCALUSERNAME,
    [CHANGEDBYID] = @CHANGEAGENTID,
    [UDMDEPLOYMENTMANIFEST] = @UDMDEPLOYMENTMANIFEST,
    [ETLSERVERNAME] = @ETLSERVERNAME,
    [ETLJOBNAME] = @ETLJOBNAME,
    [ETLJOBSTEPNAME] = @ETLJOBSTEPNAME,
    [MARTMANIFEST] = @MARTMANIFEST,
    [ETLDATABASENAME] = @ETLDATABASENAME,
    [ISOLAP] = @ISOLAP,
    [INCLUDESELECTIONS] = @INCLUDESELECTIONS,
    [VALIDATIONPROCESSID] = @VALIDATIONPROCESSID,
    [ETLMAXPARALLELISM] = @ETLMAXPARALLELISM,
    [DATEQUERYNAME] = @DATEQUERYNAME,
    [DATEQUERYKEYCOLUMN] = @DATEQUERYKEYCOLUMN,
    [SUPPORTSROLLINGDATES] = @SUPPORTSROLLINGDATES
    where [ID] = @ID;

  end
else 
  begin

    insert into dbo.[OLAPDATASOURCE]
    ([ID],[SOURCENAME],[SOURCEDESCRIPTION],[MARTKEY],[CONNECTIONSTRING],[IMPERSONATE],[IMPERSONATEUSERACCOUNTSID],[IMPERSONATEUSERPWD],[ADDEDBYID],[CHANGEDBYID],[IMPERSONATELOCALUSERNAME],[UDMDEPLOYMENTMANIFEST],[ETLSERVERNAME],[ETLJOBNAME],[ETLJOBSTEPNAME],[MARTMANIFEST],[ETLDATABASENAME],[ISOLAP],[INCLUDESELECTIONS], [VALIDATIONPROCESSID], [ETLMAXPARALLELISM], [DATEQUERYNAME], [DATEQUERYKEYCOLUMN], [SUPPORTSROLLINGDATES])
    values
    (@ID,@SOURCENAME,@SOURCEDESCRIPTION,@MARTKEY,@CONNECTIONSTRING,@IMPERSONATE,@IMPERSONATEUSERACCOUNTSID,@IMPERSONATEUSERPWD,@CHANGEAGENTID,@CHANGEAGENTID,@IMPERSONATELOCALUSERNAME,@UDMDEPLOYMENTMANIFEST,@ETLSERVERNAME,@ETLJOBNAME,@ETLJOBSTEPNAME,@MARTMANIFEST,@ETLDATABASENAME,@ISOLAP,@INCLUDESELECTIONS,@VALIDATIONPROCESSID,@ETLMAXPARALLELISM,@DATEQUERYNAME,@DATEQUERYKEYCOLUMN,@SUPPORTSROLLINGDATES);

  end

  --Populate tables used by OLAP and ETL business processes


  exec dbo.[USP_ADDETLREFRESHPARAMETERS] @ID, @CHANGEAGENTID;

  if @ISOLAP = 1
  begin
    exec dbo.[USP_ADDOLAPREFRESHPARAMETERS] @ID, @CHANGEAGENTID;
  end

  --Populate tables for validation parameters

  exec dbo.[USP_ADDOLAPDATASOURCEVALIDATIONPARAMETERS] @ID, @CHANGEAGENTID;

return 0;