EPM 10 MS: Copy Package about SQL Sentence

[Former Member]

Hi All,

Need your help and suggestions on how to perform Package Copy (data and comments) on EPM 10 MS on DB2 environment as this is my first time.

I am novice on package with DB2 would really appreciate your help. I need inform 2 input variables where after this have to be in sql sentence.

Step by step approach would be highly appreciated.

Regards

David.

-- SENTENCE SQL--

INSERT INTO TABLE1

USERID, DATEWRITTEN, [KEYWORD], [PRIORITY],

, MONEDA, SOCIEDAD, TIEMPO, MEASURES, COMMENT,

, VERSION_MKTG, ESTUDIO, CUENTA_MKTG)

SELECT SYSTEM_USER, GETDATE(), [KEYWORD], [PRIORITY],

, MONEDA, SOCIEDAD, TIEMPO, MEASURES, COMMENT,

, 'V09', ESTUDIO, CUENTA_MKTG

FROM TABLE1

WHERE VERSION_MKTG='V01'

ESTUDIO='EE022'

TIEMPO='INPUT'

[former_member186498 · Accepted solution]

Hi David,

are you on MS version? Aren't you using a SQL server DB?

Can you please explain better your issue?

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

Is BPC 10 MS about SQL Server 2012.

I have copy diferents DIMENSION "version" with data and comments. I thing that te standard package "COPY" just copy data but not comments. Is possible attach in %SQLDUMP% other sql sentence with a UNION ALL?

Cheers

David

[former_member186498 · Accepted solution]

Hi David,

ok sorry, I was misguided from the DB2.

Yes you can build a custom package starting from the standard package adding or modifying steps.

You can also thing to add another execute SQL task launching a stored proc. for copying comments as I answered in the other thread.

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

But, How do I send variable %TOSELECTION% to SQL task?

Cheers

David.

[former_member186498 · Accepted solution]

Hi David,

sorry for the delay, here an example for category and time

PROMPT(COPYMOVE,%From%,%To%,...,%CATEGORY_DIM%)

exec <YOUR_SP_NAME> "%From%","%To%"

in stored procedure

( 

@Sender_Parameter as nvarchar(1000), 

@Receiver_Parameter as nvarchar(1000)

)

declare

@Category_from nvarchar(20)

@Category_to nvarchar(20)

set @Category_from = @Sender_Parameter

set @Category_to = @Receiver_Parameter

use @Category_from and @Category_to in the rest of stored procedure.

Regards

     Roberto

[Former Member · Accepted solution]

That was really nice, didnt actually know you could trigger Stored Procedures directly from the DataManager, I did something really ugly instead via the script logic...need to rewrite this since I learned better cleaner ways to do it thnx Roberto

My DM package:

(COPYMOVEINPUT,%FROMPER%,%TOPER%,"Select FROM and TO Time to COPY Comments",%TIME_DIM%)

(Execute formulas,USER,%USER%)

(Execute formulas,Environment,%Environment%)

(Execute formulas,Model,%Model%)

(Execute formulas,FORMULASCRIPT,*FUNCTION $FROMPER$=%FROMPER%{cr}{lf}*FUNCTION $TOPER$=%TOPER%)

(Execute formulas,SELECTION,%SELECTIONFILE%)

(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\COPY_COMMENTS.lgf)

(Execute formulas,RUNMODE,1)

(Execute formulas,LOGICMODE,1)

(Execute formulas,CHECKLCK,1)

the parameter I get from data manager contains [TIMEID] in (N'20140600')

since it comes with for example [TIMEID] in (N'20140600') to the script logic I did like this:

*SELECT(%SRCPER%, "[ID]", "TIME", "$FROMPER$")

*SELECT(%DSTPER%, "[ID]", "TIME", "$TOPER$")

*RUN_STORED_PROCEDURE=SP_CopyComments_OTHDISC('%SRCPER%','%DSTPER%')

Brgds

Mattias

[Former Member · Accepted solution]

Hi Roberto,

Thanks for your answer. I have copy data and comments with package COPY.

I attach my code. I don't see that can have wrong.

Cheers

David.

[former_member186498 · Accepted solution]

Hi David,

• in your dtsx file I don't see where is called the sp, it should be an
  EXEC COPIAR_COMMENTS_MKT "%From%","%To%"
• also the standard select at the end of the package is missing (see please the standard copy package)
• if you see how the standard copy build the select you will see that the %SELECTION% variable contain the whole "where string conditions", so you should simply put "WHERE @Version_from" and for the to field you need the String Functions to extract the values you need.
• never tried, but if you want just copy the comments you can just add the same select as the fact table substituting the fact with the comment table (without fac2 and factwb), using the standard tasks you avoid to extract the values (the convert task should do it for you.

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

Where have i to declarate the command execute "EXEC"?

Send attach with my package. Is standard package copy with a extra for comments.

I prove as Mattias said in last msg, with a logic_script but not run.

    "RUN_STORED_PROCEDURE = COPIAR_COMMENTS ('%FROMVERSION%','%TOVERSION%','%FROMESTUDIO%','%TOESTUDIO%')

Cheers

David

[former_member186498 · Accepted solution]

Hi David,

I would try first understanding how standard copy works, then building a small package that copy only the comments, i.e. substituting the select with the comment table and see if works and at the end add the rest.

"PROMPT(COPYMOVEINPUT,%FROMVERSION%,%TOVERSION%,"Selecciona la VERISON de inicio y destino",VERSION_MKTG)

PROMPT(COPYMOVEINPUT,%FROMESTUDIO%,%TOESTUDIO%,"Selecciona el ESTUDIO de inicio y destino",ESTUDIO)

PROMPT(COPYMOVEINPUT,%SELECTION%,%TOSELECTION%,"Seleccionar los las dimensiones a copiar datos",%DIMS%)"

I don't think 3 copymoveinput will work, you have to use only one

PROMPT(COPYMOVEINPUT,%SELECTION%,%TOSELECTION%,"Seleccionar los las dimensiones a copiar datos",%DIMS%), this is generic,

or if you need only these 2 dimensions

PROMPT(COPYMOVEINPUT,%SELECTION%,%TOSELECTION%,"Seleccionar VERSION  y ESTUDIO",%VERSION_MKTG_DIM%%ESTUDIO_DIM%)           please note the syntax

See please SSIS Packages and  custom SSIS package and custom tasks to better understand the SSIS.

Regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

Sorry for my delay, I couldn't answer before. I have a log error and not show the parameters send.

In %seletion% and %toselection% send dimension "VERSION_MKTG, ESTUDIO". I don't know where I have the error.

Cheers.

David

LogicScript is : *RUN_STORED_PROCEDURE= COPIAR COMMENTS1('%SELECTION%','%TOSELECTION%')

package.txt.zip

Store_Procedure.txt.zip

[former_member186498 · Accepted solution]

Hi David,

probably all these answer has generate a little confusion.

Try to resume:

look at the standard copy package just to understand how it works (you can choose several dims, bpc don't know how many and which one you choose but at the end he use a select with a generic WHERE %TOSELECTION%, so you put only 'V01', 'EE022' and the copymoveinput is able to built alone the where with VERSION_MKTG='V01' and ESTUDIO='EE022'

You have used in the stored proc insert into comment but in the package you built the select ... from fact tables.

I wrote before

• never tried, but if you want just copy the comments you can just add the same select as the fact table substituting the fact with the comment table (without fac2 and factwb), using the standard tasks you avoid to extract the values (the convert task should do it for you.

So please retry starting from a simple example with just one dim choosen and only comments, using standard copy modifying only the select (from CommentMARKETING  instead of fatcs table) and see if works otherwise use the stored procedure but remember what you have inside %SELECTION% and %TOSELECTION% when you build the insert. After when is working try adding second dimension and also the copy of the fact tables.

Regards

     Roberto

[Ch-risB · Accepted solution]

Hi Roberto,

Referring to your bullet point, we tried to do this, but failed on the time dimension.  The %SELECTION% seems to be stuck on TIMEID as the field name, which is OK for FACT tables, but no good for COMMENT tables where the field is TIME.

We switched the %FACTDIM% option on the PROMPT to %DIMS% which includes TIME and not TIMEID and the %SELECTION% temp files in private publications show TIME, but the package somehow still refers to TIMEID in the SQL WHERE statements and then fails.

The same issue effects the %SELECTIONTO%.

Do you have a suggestion for correcting this?  Or where the package is getting TIMEID from?

Otherwise we will have to look at building a stored procedure from scratch.

Thanks

Chris