Read Data Manager prompt as transformation value during import

[Former Member]

Hi,

I have a file that has two of the columns required to populate dimensions. Another 4 of the dimensions can be added as new columns as they are static. However there are an additional two dimensions (TIME and LEGALENTITY) that I have added to the Import package as prompts however I am having trouble finding a way to link the transformation file to the prompts in the SSIS - any ideas? When running the new import package I am prompted for both TIME and LEGALENTITY, however the package fails with the message:

Mapping Error: The folloiwng doesn't exist in the data file header [LEGALENTITY, TIME]

Kind Regards,

Fiona

[Former Member]

Hi Fiona,

Your requirement seems to be very interesting. I think declaring global variables as Tim suggested will be a good idea, but not sure how it works...

May be the following idea seems to be very simple, but hope it helps,

You can create two dummy members,legal1,Time1ch for Legal Entity and time and in the transformation file, define this members for Legal Entity and time using *NEWCOL(legal1) & *NEWCOL(time1) and in default logic(or you can create a seperate logic and modify the import dts to run the logic after load and process step),you can use the variables passed from the script as

*when LEGALENTITY

*is legal1

*when time

*is time1

*rec(factor=1,legalentity=<variable from prompt>,time=<time value from prompt>) ' replace legal1 & time values with the values passed from prompt

*rec(factor=0) ' Clearing the dummy entries created

*endwhen

*endwhen'

Give it a try if you think it ll be useful,

Kranthi

[Former Member]

Hi,

Thanks for your response, I will try this solution out today. Wouldn't there be a risk that if two people were to run imports at the same time though that I could end up overwritting two entities with the prompt from one import i.e. there could be two sets of data imported into the dummy entity and time simultaneously?

Kind Regards,

Fiona

[Former Member]

Hi Fiona,

I dont think it ll be problem.I think when you run an import package, all resources(members in data region) required for it will be locked and next import throws an error. Try running two import packages, with same data file at same time, then one import should throw an error. But please check once.

Kranthi

[Former Member]

Hi Kranthi,

Thanks so much for your suggestion. I added a dummy member to both my TIME and LEGALENTITY dimensions. I added these as NEWCOL in my transformation file also. I created a copy of the Import.dtsx and added a Logic Script task after LOAD and PROCESS. I added the logic that you suggested below to the LegalApp:

*when LEGALENTITY

*is LegalImport 'Dummy member

*when TIME

*is TimeImport 'Dummy member

*rec(factor=1,legalentity=%LEGALINP%,TIME=%TIMEINP%)

*rec(factor=0)

*endwhen

*endwhen

%LEGALINP and %TIMEINP are both TEXT prompts in my new import package. Ideally I'd prefer if they could be selected from the available dimension members, however I am not sure whether that would work with the above logic.

I run my package and get no warnings. I can see that the data in loaded into the two writeback tables:

dbo.tblFACTWBLegalApp

dbo.tblFACT2LegalApp

However the Dummy entity and time members are still in the records and the prompt records are no where to be found.

I've copied part of the logic log from my import execution below:

Data File:

Debug File:D:\BPC\Data\Webfolders\GroupDev\LEGALAPP\PrivatePublications\v-coenf\TempFiles
DebugLogic_168_.Log

Logic File:D:\BPC\Data\WebFolders\GroupDev\LEGALAPP
..\AdminApp\LEGALAPP\ImpPmtUpd.lgf

Selection:

Run mode:1

Query size:0

Delim:,

Query type:0

Simulation:0

Calc diff.:0

Formula script:

Max Members:

Test mode:0

Is Modelling:1

Number of logic calls:1

-

---

Call no. 1, logic:D:\BPC\Data\WebFolders\GroupDev\LEGALAPP
..\AdminApp\LEGALAPP\ImpPmtUpd.lgf

-

---

signeddata is YTD

-

---

Building sub-query 1

-

---

Query Type:0

Max members:

Region:

DIMENSION:CATEGORY

ACTBUD,ACTINP,ACTMAN,ACTUAL,AprFcst,AugFcst,BUDGET,DecFcst,FebFcst,FORECAST,JanFcst,JulFcst,JunFcst,MarFcst,MayFcst,NBUDGET,NBUDGETV1,NBUDGETV2,NovFcst,OctFcst,SepFcst

Loading TIME.TIMEID

Time to load properties:0.0 sec.

-

---

select ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIMEID,SIGNEDDATA

into #tblTempLogic_719152

from tblFactLEGALAPP

WHERE CATEGORY in (N'ACTBUD',N'ACTINP',N'ACTMAN',N'ACTUAL',N'APRFCST',N'AUGFCST',N'BUDGET',N'DECFCST',N'FEBFCST',N'FORECAST',N'JANFCST',N'JULFCST',N'JUNFCST',N'MARFCST',N'MAYFCST',N'NBUDGET',N'NBUDGETV1',N'NBUDGETV2',N'NOVFCST',N'OCTFCST',N'SEPFCST') AND GROUPS=N'LC'

insert into #tblTempLogic_719152 (ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIMEID,SIGNEDDATA)

select ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIMEID,SIGNEDDATA

from tblFactWBLEGALAPP

WHERE CATEGORY in (N'ACTBUD',N'ACTINP',N'ACTMAN',N'ACTUAL',N'APRFCST',N'AUGFCST',N'BUDGET',N'DECFCST',N'FEBFCST',N'FORECAST',N'JANFCST',N'JULFCST',N'JUNFCST',N'MARFCST',N'MAYFCST',N'NBUDGET',N'NBUDGETV1',N'NBUDGETV2',N'NOVFCST',N'OCTFCST',N'SEPFCST') AND GROUPS=N'LC'

and SOURCE = 0

insert into #tblTempLogic_719152 (ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIMEID,SIGNEDDATA)

select ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIMEID,SIGNEDDATA

from tblFAC2LEGALAPP

WHERE CATEGORY in (N'ACTBUD',N'ACTINP',N'ACTMAN',N'ACTUAL',N'APRFCST',N'AUGFCST',N'BUDGET',N'DECFCST',N'FEBFCST',N'FORECAST',N'JANFCST',N'JULFCST',N'JUNFCST',N'MARFCST',N'MAYFCST',N'NBUDGET',N'NBUDGETV1',N'NBUDGETV2',N'NOVFCST',N'OCTFCST',N'SEPFCST') AND GROUPS=N'LC'

select tmpTable.ACCOUNTL,tmpTable.ACCTDETAIL,tmpTable.CATEGORY,tmpTable.DATASRC,tmpTable.INTCOL,tmpTable.LEGALENTITY,tmpTable.TIMEID,sum(SIGNEDDATA) as SIGNEDDATA

from #tblTempLogic_719152 as tmpTable

group by tmpTable.ACCOUNTL,tmpTable.ACCTDETAIL,tmpTable.CATEGORY,tmpTable.DATASRC,tmpTable.INTCOL,tmpTable.LEGALENTITY,tmpTable.TIMEID

drop table #tblTempLogic_719152

-

---

Time to load Source data:5.5 sec.

102692 records to process

Time to Prepare data process:0.0 sec.

Time to initialize destination:0.0 sec.

Time to get source data:0.0 sec.

-

---

102692 scanned records

102548 skipped records

144 processed records

288 records generated

Time to Scan data:0.5 sec.

-

---

Time to get output data:0.0 sec.

Time to prepare final strings:0.0

Total processing time:0.6

App: LEGALAPP - Records to be posted are 288 (calc diff = 0)

GROUPS,ACCOUNTL,ACCTDETAIL,CATEGORY,DATASRC,INTCOL,LEGALENTITY,TIME,SIGNEDDATA

LC,AA2003000,F_NONE,ACTINP,INPUT,NON_INTERCO,%LEGALINP%,,-10000000.9

LC,AA2003000,F_NONE,ACTINP,INPUT,NON_INTERCO,LEGALIMPORT,TIMEIMPORT, 0

LC,AA2002000,F_NONE,ACTINP,INPUT,NON_INTERCO,%LEGALINP%,,-1000000

LC,AA2002000,F_NONE,ACTINP,INPUT,NON_INTERCO,LEGALIMPORT,TIMEIMPORT, 0

etc....

etc....

[Former Member]

Hi Kranthi,

Do you have any suggestions based on the log above please?

Kind Regards,

Fiona

[Former Member]

Hi Fiona,

May be this could be helpful.

The scope of execution of logic from Run logic can be based on data region selection,from dimension prompts, which is not applicable for your case or from the data file, on selection of this, the logic executes only on the members you are loading.( check import to fact table dts package, you ll know more as it has similar design).

Also,the vaues of the prompts need to be passed to the logic using evmodify package( assign parameters package to be used in the script).

Hope this Helps,

Kranthi

[sorin_radulescu]

If I understood correct you would like to take the parameters collected from prompt and to use these parameters into transformation file.

This is not possible without some changes into package.

So you have two choice in both cases you have to modify the DTS and to add a new step.

1. You can use a VB script code to modify the transformation file where you will add New colums using the parameters collected from prompt

2. You can change the input file adding two columns filled with parameters from prompt.

Regards

Sorin Radulescu

[Former Member]

Hi Sorin,

Thanks for your response. Yes you have understood my requirement correctly. I need to add two additional columns to either my INPUT file or edit my transformation file to insert the prompt values for TIME and ENTITY into the mapping section. Could you advise me on how to go about this - how can I refer to the parameters outlined in the MODIFYSCRIPT within the SSIS Package?

I have created a copy of the Import SSIS and added 1 row in the PROMPT section:

Active: ticked

Type: SELECTINPUT,

Variable Name: %SELECTION%,

Promperty1: Select time period and entity to Load,

Property2: %TIME_DIM%,%ENTITY_DIM%

Is there an advanced data manager document available for customizing SSIS packages?

Kind Regards,

Fiona

[Former Member]

Hi All,

Does anyone by any chance have an example for customising SSIS to use prompts from BPC custom tasks?

Kind Regards,

Fiona

[Former Member]

There are various options for passing user input from a data manager package (either a dimension member set, selected by the user, or some other free-text data entry, or a selected item from a set of radio buttons, etc.) into a custom DTS package.

The standard options are covered in the data manager user documentation, but there's also other options, such as passing user prompts into a DTS package variable, and then using that for any purpose you'd like -- including outside of BPC -- if you want to do some custom DTS programming.

Could you describe in more detail what you'd like to do?

[Former Member]

Hi Tim,

Thanks for your response. I have made a copy of the generic import.dtsx and have included a prompt for LEGALENTITY and TIME. I have an file that I receive that only has two columns available - Account and Value. All other dimension values will be static so I've just mapped them using a transformation file, however the Entity and Time will be different for each import so I've added a prompt to the import package. I now need to take the values for the prompts received and add it to the data set built in the transformation file prior to importing it into the fact table.

My package currently prompts the user and allows me to run the package, however the import fails due the the ENTITY and TIME dimensions not being mapped in the transformation file. I'd like to know what options I have for adding the prompts to my data set. One suggestion was to customize the SSIS package directly and add the prompts to the file before the import, however I can't seem to see how I can read the prompts within the SSIS package to add them to the file.

Kind Regards,

Fiona

[Former Member]

Hi,

Would anyone have any advice for me on the above thread please?

Kind Regards,

Fiona

[Former Member]

Hi Fiona,

I don't know a simple or elegant way to do this, to pass the user's runtime prompt into a field of the import file. The standard import package takes the original data file as in INPUT, and creates a second data file as the OUTPUT of the transformation step. So the "elegant" solution that I would try to achieve, is to somehow insert the user's selected values into that transformation routine.

But I don't know how to do that.

The only way I can think of, is to load the input data set into a temporary staging table in the database, then pass the user's runtime prompts into a series of DTS package variables, and use these via SQL statements to update the staging table. Then insert from that table into the factWB. And by bypassing the standard BPC process, you lose out on the built-in security validations (e.g., ensuring this user has write access to this entity).

Another option I've used: instead of prompting the user for the entity & time in the package, have them add this as a header record in the data file itself, before uploading to the BPC server. (I.e., open the file in Notepad, key in the entity & month, and then save.) Not exactly the Rolls Royce of user interfaces, but it works.

[Former Member]

Hi,

Thanks for getting back to me, currently I am investigating both of those options. The only problem with option 1 is that I'm not sure how to pass the users runtime prompts into the SSIS package as when reviewing the SSIS packages I can't see how to extract the runtime prompts from the custom tasks - do you know how to do that?

I'd like to avoid asking the end-users to add the headers if possible as that leaves more room for errors.

Kind Regards,

Fiona

[Former Member]

The following is an example of a run-time package prompt, that passes the user's selection into my DTS package.

The package is set up with variables of the same names, DB_NAME, DB_SERVER, VERSION, USERID, TRANSTYPE, and COMPANYCODE. I then use these within the package, to do various things (parameters to stored procs, conditional task execution, parameters to connection managers so transports from Dev to QA to Prod require no re-configuration, etc.)

Note that the variables must be in ALL_CAPS. Something buggy in SSIS / SQL 2005 / BPC 5.1 chokes on MixedCase variables, and it took me a whole lot of banging of my head against a very thick, very brick wall to figure out what was causing that.

'DEBUG(ON)
PROMPT(RADIOBUTTON,%MODE%,"Select mode of operation:",,{"Query the source system and allocate to sales channels","Re-allocate to sales channels using revised allocation rates "},{"SKU_QTY_FROM_SOURCE","SKU_QTY_REALLOC"})
PROMPT(TEXT,%Entity%,"Select 4-digit company code (one only)",,"")
PROMPT(TEXT,%VersDest%,"Select version (one only)",,"")
GLOBAL(DB_NAME,%APPSET%)
GLOBAL(DB_SERVER,%SQLSERVER%)
GLOBAL(VERSION,%VersDest%)
GLOBAL(USERID,%TRIMMEDUSER%)
GLOBAL(TRANSTYPE,%MODE%)
GLOBAL(COMPANYCODE,%Entity%)

[Former Member]

And as for part 2 of the puzzle, how to use the variables within SSIS / DTS -- this is standard DTS programming, which isn't exactly intuitive. Just about anything in DTS can be evaluated as an expression, and once you do that, you can drop your variable into the expression. The specifics vary enormously depending on the task or object.

There are some good tutorials on MSDN, and if you're stuck on a specific DTS problem, perhaps we can help here.

[Former Member]

Thanks Tim. I'll give this a go, by your previous reply it sounds like if I create the variables in the SSIS and assign them as Global in BPC the SSIS will populate them automatically. I'm pretty comfortable with SSIS so should be able to complete step 2 without any problems - it was populating the variables that I wasn't sure about. I'll give it a go and update the thread with feedback - thanks again.

[Former Member]

Hi,

I've been investigating this further and have come across two possible issues:

1. If I update the import or transformation files I could end up with locking issues as multiple users will be importing files. The import file is less likely to cause locks as each entity will import their own data, however the file will be currently open when I try to modify it.

2. I noticed that by adding an additional (Data transformation) task to the SSIS package I can no longer modify the package from within Data Manager in BPC and error is returned. I can run the package and see that the steps I have included so far are successful (export entity and time variables to a file on the desktop to confirm global variable usage), however I can't edit the package in BPC to change the prompts now.

I have also started to investigate the option that you suggested in adding headers to the file, does this mean I could have an import file such as:

ENTITYCODE

TIMECODE

Acccount, Intco, Measure,

If so, I'm not sure how to specify that LegalEntity=*NEWCOL(....header?) - how do I point it to the top row? I'd rather now have to ask the customer to add entity and time to the columns and then copy and paste the same entitycode and timecode for all rows.

Thanks again for your help on this,

Kind Regards,

Fiona

[Former Member]

To use the header in your data file, there is an option in the transformation file for HEADER = YES. I forget the precise syntax for this, but it basically allows you to hard-code certain members in the first row of the data file.

For example, here is one header row (entity,time,category) and then 4 data rows (account,product,value)

GreatBritain,2007.JAN,Actual

Sales,BlueCar,1000

COGS,BlueCar,800

Sales,RedCar,1200

COGS,RedCar,900