Creating Custom Reclass Data Manager Package

[Eyal_Feiler]

Hi Experts,

I would like to use a Data Manager Package to execute script logic to perform a series of reclassification adjustments.

#1 Copied the default Script dtsx package and gave it a new name.  I assume this is ok because I am simply connecting it to a new script that I wrote below.

#2 The result of the script is an error writing to the CTRL_FLAG.  This is not part of the calculation below

Why is this occurring? 

Included below is script logic (in process), DM logic ( I deactivated the Work Status just in case this was causing the issue), and the tbllog result.

I tried debugging using the logic debugger but did not understand the result.

Version used: BPC 10.1 MS SP3 Patch 1.

Thanks in advance.

Regards,

Eyal

'DEBUG(ON)

'PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when running logic.",1,{"Yes, check for work status settings before running logic","No, do not check work status settings"},{"1","0"})

'PROMPT(SELECTINPUT,,,,%CATEGORY_DIM%%ENTITY_DIM%%TIME_DIM%)

PROMPT(SELECTINPUT,,,,%TIME_DIM%)

TASK(Execute formulas,USER,%USER%)

TASK(Execute formulas,Environment,%Environment%)

TASK(Execute formulas,Model,%Model%)

TASK(Execute formulas,SELECTION,%SELECTIONFILE%)

'TASK(Execute formulas,LOGICFILE,

TASK(Execute formulas,LOGICFILE,%ModelPath%\..\AdminApp\%Model%\RECLASS_SOLO.lgx)

)

TASK(Execute formulas,CHECKLCK,%CHECKLCK%)

Script Logic

//**SELECT ("%AUDITID_LIST%","[ID]","AUDITID","DATASRC_TYPE='I'")

//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// Reclass #1

//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

*XDIM_MEMBERSET TIME =%TIME_SET%

*XDIM_MEMBERSET FLOW = PL99

*XDIM_MEMBERSET CONSOSCOPE = G_NONE

*XDIM_MEMBERSET AUDITID= INPUT

//*XDIM_MEMBERSET AUDITID=%AUDITID_LIST%  // SOLO AUDITID BEFORE RECLASS

*XDIM_MEMBERSET CURRENCY = LC

*XDIM_MEMBERSET ENTITY = 1120

*XDIM_MEMBERSET ACCOUNT = 932_04,937_02

*WHEN ACCOUNT

   *IS "932_04"

         *REC(FACTOR=1,ACCOUNT="808_02",AUDITID="REC_C1",FLOW="PL99")

*ENDWHEN

*COMMIT

//*WHEN ACCOUNT

//   *IS "937_02"

// *REC(FACTOR=1,ACCOUNT="808_02",AUDITID="REC_C1",FLOW="PL99")

// *ENDWHEN

//*COMMIT

//*WHEN ACCOUNT

//   *IS "932_04"

// *REC(FACTOR=-1,ACCOUNT="932_04",AUDITID="REC_C1",FLOW="PL99")

// *ENDWHEN

//*COMMIT

//*WHEN ACCOUNT

//   *IS "932_04"

// *REC(FACTOR=1,ACCOUNT="937_02",AUDITID="REC_C1",FLOW="PL99")

// *ENDWHEN

//*COMMIT

Result:

Total Step:              1

Execute formulas:  Failed in 0 seconds

Default Formulas:   completed in 0 sec

[Selection]

--------------------------------------------------------------

(Member selection)

Time: 2015.DEC

[Message]

--------------------------------------------------------------

Execute formulasError

Must specify a member for all dimensions in : CTRL_FLAG,INPUT,%CATEGORY%,G_NONE,LC,9999,F99,I_NONE,M_NONE,TC_NONE,2015.DEC,1

Tbllogic result

1. System.Data.SqlClient.SqlException (0x80131904😞 Login failed for user 'DS-INVEST\MDBPC-D$'.

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at OSoft.Services.Platform.SysAdminDataBaseDM.SQLDataHelper.DbOpen()

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataAccess.DbOpen()

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.IsOpenDataBase(String sAppSet, Boolean bExtraDB, Boolean bForcedLogon)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.Connect(String strAppSet, Boolean bExtraDB)

   at OSoft.Services.Platform.SysAdminDataBaseDM.DataSwitch.Connect(String strAppSet)

ClientConnectionId:34577f26-eff2-4eed-878c-43e4a618d1f5

Error Number:18456,State:1,Class:14

[Eyal_Feiler · Accepted solution]

Another option:  Should we just write our own Stored Procedure(s) and activate via the DM package?
If so are there any specific requirements regarding the way the procedures should be written?

Thanks

Eyal

[former_member208963 · Accepted solution]

Could you try to modify this part of the data manager:

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

And also include first a filter for 1 member of each dimension in the scope, and then you can add the rest or modify or remove...

Add the category

*XDIM_MEMBERSET CATEGORY= your category

[Eyal_Feiler · Accepted solution]

Hi Robert,

Thanks for your response:

limited the script logic to my data - *XDIM_MEMBERSET CATEGORY = ACTUAL

and modified to refer to the lgf.

Same result below...

Execute formulasError

Must specify a member for all dimensions in : CTRL_FLAG,INPUT,%CATEGORY%,G_NONE,LC,9999,F99,I_NONE,M_NONE,TC_NONE,2015.DEC,1

Any ideas?  Why is this doing it?  Seems like a vestige of the default script?

Eyal

[former_member208963 · Accepted solution]

- So you created an scriptlogic in the administration named RECLASS_SOLO.lgf

- You validated and save the logic

- When you try to debug the logic with logic debugger (Simulate + Select at least one member in the filter) you are getting this error?

Regards

[Eyal_Feiler · Accepted solution]

Hi Robert,

I checked again - I validated the logic. and ran the debugger.  Attaching what I did below.

Here is the log - no reference to the CTRL flag.

   If I understand correctly from the log -it's running through the 3 FACT tables - no reference to CTRL.

Also, ran full optimize yesterday

Any ideas?  Again this task is not calling anything related to the default on the back end? and it's not trying to run default after correct?

Thanks

Eyal

****************************************************************************************************

Start time --->08:51:39  -  Date:2016-03-15  (build version:10.1.3.0)

****************************************************************************************************

User:DS-INVEST\EyalF

Environment:MEITAVDASH

Model:Consolidation

Logic mode:1

Logic by:

Scope by:

Data File:

Debug File:U:\MSSQL\Data\WebFolders\MEITAVDASH\Consolidation\PrivatePublications\EyalF\TempFiles\DebugLogi...

Logic File:RECLASS_SOLO.LGF

Selection:DIMENSION:Account

|932_04

|DIMENSION:AuditID

|INPUT

|DIMENSION:Category

|ACTUAL

|DIMENSION:Consoscope

|G_NONE

|DIMENSION:Currency

|LC

|DIMENSION:Entity

|1120

|DIMENSION:Flow

|PL99

|DIMENSION:Interco

|ALL_INTERCO

|DIMENSION:MIgzar

|M_ALL

|DIMENSION:TC

|TC_ALL

|DIMENSION:Time

|2015.DEC

|

Run mode:1

Query size:2

Delim:,

Query type:0

Simulation:1

Calc diff.:0

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Work status Check:0

Task name:ManualInput

Number of logic calls: 1

----------------------------------------------------------------------------------------------------

Call no. 1, logic: U:\MSSQL\Data\WebFolders\MEITAVDASH\AdminApp\Consolidation\RECLASS_SOLO.LGF

----------------------------------------------------------------------------------------------------

signeddata is YTD

-------------------------

Building sub-query 1

-------------------------

Query Type: 0

Max members:

Region:

DIMENSION:ACCOUNT

932_04

DIMENSION:AUDITID

INPUT

DIMENSION:CATEGORY

ACTUAL

DIMENSION:CONSOSCOPE

G_NONE

DIMENSION:CURRENCY

LC

DIMENSION:ENTITY

1120

DIMENSION:FLOW

PL99

DIMENSION:INTERCO

ALL_INTERCO

DIMENSION:MIGZAR

M_ALL

DIMENSION:TC

TC_ALL

DIMENSION:TIME

1. 2015.DEC

DIMENSION:TIME

1. 2015.DEC

DIMENSION:CATEGORY

ACTUAL

DIMENSION:FLOW

PL99

DIMENSION:CONSOSCOPE

G_NONE

DIMENSION:AUDITID

INPUT

DIMENSION:CURRENCY

LC

DIMENSION:ENTITY

1120

DIMENSION:ACCOUNT

932_04,937_02

Loading TIME.TIMEID

Time to load properties: 0.00 sec

----------------------------------------------------------------------------------------------------

select ACCOUNT,AUDITID,FLOW,SIGNEDDATA

into #tblTempLogic_914986

from tblFactConsolidation

WHERE  ACCOUNT in (N'932_04',N'937_02') AND  AUDITID=N'INPUT' AND  CATEGORY=N'ACTUAL' AND  CONSOSCOPE=N'G_NONE' AND  CURRENCY=N'LC' AND  ENTITY=N'1120' AND  FLOW=N'PL99' AND  INTERCO=N'ALL_INTERCO' AND  MIGZAR=N'M_ALL' AND  TC=N'TC_ALL' AND  TIMEID=N'20151200'

insert into #tblTempLogic_914986 (ACCOUNT,AUDITID,FLOW,SIGNEDDATA)

select ACCOUNT,AUDITID,FLOW,SIGNEDDATA

from tblFactWBConsolidation

WHERE  ACCOUNT in (N'932_04',N'937_02') AND  AUDITID=N'INPUT' AND  CATEGORY=N'ACTUAL' AND  CONSOSCOPE=N'G_NONE' AND  CURRENCY=N'LC' AND  ENTITY=N'1120' AND  FLOW=N'PL99' AND  INTERCO=N'ALL_INTERCO' AND  MIGZAR=N'M_ALL' AND  TC=N'TC_ALL' AND  TIMEID=N'20151200'

and SOURCE = 0

insert into #tblTempLogic_914986 (ACCOUNT,AUDITID,FLOW,SIGNEDDATA)

select ACCOUNT,AUDITID,FLOW,SIGNEDDATA

from tblFAC2Consolidation

WHERE  ACCOUNT in (N'932_04',N'937_02') AND  AUDITID=N'INPUT' AND  CATEGORY=N'ACTUAL' AND  CONSOSCOPE=N'G_NONE' AND  CURRENCY=N'LC' AND  ENTITY=N'1120' AND  FLOW=N'PL99' AND  INTERCO=N'ALL_INTERCO' AND  MIGZAR=N'M_ALL' AND  TC=N'TC_ALL' AND  TIMEID=N'20151200'

select tmpTable.ACCOUNT,tmpTable.AUDITID,tmpTable.FLOW,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_914986 as tmpTable

group by tmpTable.ACCOUNT,tmpTable.AUDITID,tmpTable.FLOW

drop table #tblTempLogic_914986

----------------------------------------------------------------------------------------------------

Time to load Source data: 0.02 sec.

No record to process

Simulated call 1 completed in 0.25 sec.

Run completed in 0.27 sec.

****************************************************************************************************

End time --->08:51:39  -  Date: 2016-03-15

****************************************************************************************************

[former_member208963 · Accepted solution]

I don't see any error in the logic now when you debugged... the thing is that is not processing any records because you don't have the correct scope (No records no process).

So, I would try to find the correct scope and debug until you get what you want (Simulation of the records calculated with your logic)

Form that point, start the creation of the package, a copy of default dtsx (as you already did) rename it

Open this package. and edit its code (Modify Script-Advance)

Paste the Data Manager code, save it, and try to Run the package again.

By the way, Sometimes in DEV need to restart the IIS when I create/rename new packages.

Regards

[Eyal_Feiler · Accepted solution]

Hi Robert,

Thanks for the assistance.

I had to copy the dtsx package in the following screen below the DM, in order to copy the package.  Once I did that I could change the default script name and call the script logic.

Copying the DM package at the DM level (unlike NW) just refers to the same package, so when you change the package logic you are actually updating the original package (in my case default...).

Thanks

Eyal