Script Logic not selecting complete member set

[Former Member]

Hi

I am hoping somebody can help me with some script logic. The script should move data from the origin cube to the destination cube, change a member name and skip other dimensions that are not included in the destination cube. The code is as follows:

*SELECT(%MEMBERS%, "[ID]", "PRODUCT", "OHTYPE='STUFF'")
*XDIM_MEMBERSET PRODUCT=%MEMBERS% //This is where the product are selected

*WHEN ACCVOLUME
*IS VOLSALES_PALLET

*REC(FACTOR=1,ACCVOLUME="VOLSALES_PALLET_W") //This is where the member name is changed
*ENDWHEN

*DESTINATION_APP=OVERHEAD // Set the destination cube
*ADD_DIM BRAND=0000
*ADD_DIM PROJECT=000000
*ADD_DIM OPTIONAL3=00000000
*ADD_DIM OHCOSTCENTRE=NOCC //Adds and sets the new dimension members ready for import into the destination cube

*RENAME_DIM ACCVOLUME=ACCOH //Rename dimension name to correct one in the destination cube
*SKIP_DIM=ENTITY
*SKIP_DIM=MARKET
*SKIP_DIM=PRODUCT //Skips the dimensions that are not included in the destination cube

*Commit 

The problem I am having is that for some reason not all of the data is being transferred. I have checked the logs and it appears that not every member in the "PRODUCT" dimension is being selected in the query. There are a total of 1146 members in our PRODUCT dimension that meet the criteria in lines 1 and 2 of the above code. However only 964 members appear to have been selected in this query accroding to the log file.

I was wondering if there was limit on the amount of members that can be selected in a script logic query?

Hope someone can help!!

Thanks,

Jamie

[former_member200327]

Hi Jamie,

Is this the whole script you are running? I don't see the part that is moving data from one App to another. The WHEN/REC/ENDWHEN you have at the top is writing data to the source App. To make it move data to another App it should be after all *DESTINANION_APP lines.

There is no limit on number of members selected. Where do you see 964 members instead of 1146: is it in Master Data or Transaction Data? I can see that you restrict MD with SELECT statement. Do you have 1146 members that comply with that selection?

If it's in TD then you should check your App if you have all of them there. If you don't have them in TD, but still want to use all 1146 members for your calculations consider using *WHEN REF DATA = MASTER DATA, but keep in mind that it will affect all other Dimensions in your source App.

Regards,

Gersh

[Former Member]

Hi Gersh

No, there another two blocks of code after the *Comit line but these blocks do the same thing but with a different member. There is nothing further done with this section of data after the *Commit line in the above code.

Yes we have 1146 members in our matser data and I can see in the transaction data that only 946 members are being selected (I found this in the log file produced at run time of the script).

I was hoping to use the logic debugger to step through the code but I don't think this functionality is available through the admin console.

Thanks,

Jamie

[former_member200327]

Jamie,

*DESTINATION_APP itself doesn't move any data, WHEN/REC that follows it does. COMMIT will clear out all you XDIM_MEMBERSET restrictions set above it, so if you have COMMIT between DESTINATION_APP and WHEN you transaction data is not going to be restricted to XDIM_MEMBERSET anymore.

Can you please clarify if you have 1146 different members in your cube in that dimension or in master data?

Regards,

Gersh

[Former Member]

Thanks Gersh

Each block of this code has the COMMIT line after the WHEN/REC and DESTINATION_APP lines so I don't think the member selection criteria is being cleared before the WHEN/REC line is reached.

We have 3395 members in the dimension in total, 1146 of which meet the selection criteria:

*SELECT(%MEMBERS%, "[ID]", "PRODUCT", "OHTYPE='STUFF'")
*XDIM_MEMBERSET PRODUCT=%MEMBERS% //This is where the product are selected

and only 964 of those members have their data transferred meaning that 182 members are being ignored for some reason.

I'm not sure I understand what you mean by "Master Data" in this case?

Thanks,

Jamie

[former_member200327]

Hi Jamie,

Dimension = Master Data; Application = Transaction Data.

So, you have 1146 members selected in %MEMBERS%. Can you check how many of those are in your Application.

You don't need *COMMIT after each block - BPC NW will write data back after each block anyway, but they don't hurt. *COMMIT after DESTINATION_APP hurts - it drops all XDIMs you have before that statement.

Regards,

Gersh

[Former Member]

Hi Gersh

I believe I have 1146 members selected in the %MEMBERS% memberset. Is there a way of confirming that?

There is 946 members in the Application (Transaction Data) according to the log file which is created.

I don't think having COMMIT at the end of each block is a bad thing in this case; we are re-defining the %MEMBERS% memberset after each COMMIT line.

Thanks,

Jamie

[former_member200327]

Hi Jamie,

So, you have 946 different members in that Dimension in your Application, but you want to see records with all 1146 members, right? I guess you understand that in such case those records can't be retrieved from the Application; only way to get them is to generate them. To achieve that you have to use statement *WHEN REF DATA = MASTER DATA before corresponding WHEN/ENDWHEN block. This will create a Cartesian product of all members defined in XDIM_MEMBERSETs. Is that what you want?

Regards,

Gersh

[Former Member]

Hi Gersh

So just to clarify within the product dimension we have 1146 members who have an OHTYPE property of "STUFF". I want all 1146 members with that property to be selected in this script logic. As it stands only 964 of those members are being selected when the script logic is run.

3395 - total members in the Product dimension

1146 - total members in the Product dimension where OHTYPE property = "STUFF"

946 - total amount of members being selected when the script logic is run (should be 1146)

Hope that helps clarify my issue.

Thanks,

Jamie

[former_member200327]

Hi Jamie,

Where do you see those 946 members?

My understanding that you found just that number of members in your Application when you checked it. Why would you expect more members selected than you have in Application if they are not there?

Regards,

Gersh

[Former Member]

Hi sorry for the delayed reply. We have managed to figure out why not all the products were being selected as part of the select statment. As we had created more products members since writing and saving this script logic for some reason it was only doing a select on the product members that were created at that point in time.

To allow the latest member list to be selected from we simply needed to validate and save the script logic once again. Unfortunately it means that whenever we create a new product member we will need to remember to validate and save this script logic.

Thanks for your help.

[former_member200327]

This can not be true. I doubt anybody has similar issue.