Adding in a list of elements into a dimension via active form

[nxw]

Hi everyone,

I want to create an active form where I can send a list of numeric leaf level elements into a target consolidation within a dimension.

Any ideas ?

I was thinking either using a DBS formula or using an action button linked to bedrock add?



I'm trying to create a form to push new products (by barcode as its the unique identifier) into the 'All Products' consolidation

THanks ,

[lotsaram]

There are lots of different ways that you could use an active form to build a consolidation in a dimension. All of them are going to involve either creating a subset of the elements to add then looping through the elements in the subset and adding them to the C element or having the element names as string data in a cube and creating a view to loop through the data. Any solution is going to also have to involve TI as an action needs to be performed in insert elements, data entry can't do it alone (which is all that a DBS formula is, data entry).

[declanr]

99 times out of 100 I would follow Lotsa's suggestion of needing a TI; in which case people one here would probably be happy to help with that a bit more if you could provide details of the dimensions in question.

But to throw another idea into the mix that would possibly mean not using a TI or consolidation at all; if the "consolidation" is only required to be seen through websheets (or excel perspectives sheets in general - or probably cafe but haven't tested this in that yet) then you can actually use the name of a subset in a DBRW formula (or DBR etc etc etc) and it will return results exactly as if it were a consolidation element as opposed to a subset. Tee caveat being that if you do have an element with the same name as the subset it will just return that instead (as you would expect it to.)

This means that you could have an MDX Subset based on whether the elements in question have a 1 (or similar flag) in them or not e.g:

Code: Select all

/* MDX Subset to return all nodal elements within MyDimName that have MyFlagAttribute populated with a 1 */
{FILTER(
        {[MyDimName].[NodalLevelSubset]},
        [}ElementAttributes_MyDimName].([}ElementAttributes_MyDimName].[MyFlagAttribute])=1
        )
}

Then assuming that Subset in question is called "MySubset" you can have a DBRW that just used "MySubset" in place of an element name e.g.:

Code: Select all

DBRW("MyServerName:MyCubeName",<Element1>,<Element2>,"MySubset",...)

It is a fairly specific set of circumstances where I would use this; for example I have used something recently where a large form can be filtered based on an "Owner" of Jobs and then this is used to make sure the "Total" updates to only show that values that relate to said "Owner". You need to carefully consider when doing something along these lines whether it is clearly understandable what is happening; as it is not a particularly well documented use of subsets and could confuse any developers that follow you in having to manage the application.

[nxw]

Hi all- appreciate your input.

I think perhaps if you look at this screen shot here it may be easier to decode.

I've got 3 screen shots here:



1) MAP Product SKU - is a screen shot of the cube and the respective dimension I want to push it into. It is a mapping dimension for us to feed all our products (SKU/EAN -is also known as the product barcode)

2) Is a screenshot of the form I'm trying to create - it has 3 tabs - first one is pushing the elements into the MAP Product SKU dimension - first tab

3) Is a screenshot of the form I'm trying to create - it has 3 tabs - first one is pushing the elements into the MAP Product SKU dimension - second tab



cheers,

[gtonkin]

Why not leverage what Declan is saying in terms of using the Activeform to populate attributes that you can later use.
Create a TI process that flattens the hierarchy first then rebuilds based on the values in the attribute.
Your Activeform then just needs to allow you to specify the parent for each element. Write the parent to the attribute.
Use an action button to call the TI.
Done.

[nxw]

What i'm more stuck on is screen shot one - what would the process be for inserting elements into a target consolidation and target dimension based on a list in excel?

Does the excel file have to be closed?

[gtonkin]

The Excel file does not need to be closed as the changes should be happening on the server, Excel is merely the interface between the user and TM1.
My suggestion will not work unless you already have the elements in the dimension i.e. they will appear on the Activeform when refreshed. Your TI could add in the parent elements if missing i.e. based on the attribute.
I have had issues with elements added in TM1 not being available immediately in Perspectives/Excel but have not recently experienced on 10.2.2 FP4

[nxw]

Thanks.

I'm not sure what the code would be does this work?

cDimT = 'MAP Product SKU';
cDimLeafTotal = 'All Product SKUs';

DimensionElementComponentAdd ( cDimT , cDimLeafTotal , pProductSKU , 1 );

[BariAbdul]

I am not sure why you are not considering Declan and lotsaram advice,The code you provided basically adding leaf element to consolidation,in which tab you have this code?

[tomok]

I am not sure why you are not considering Declan and lotsaram advice,The code you provided basically adding leaf element to consolidation,in which tab you have this code?

Probably because neither of their posts provide help on how to insert elements into a dimension via Excel. If the product isn't in already in the dimension then their workarounds won't work. The problem you are facing is that an active form can only call a TI process. The TI process can accept parameters from the active form and add those to the dimension and the active form can assign a CELL to the parameter. The key word being cell. You cannot assign a range, i.e., a list of rows, to a parameter, meaning you can only add one item to the dimension per parameter. To add multiple elements you would have to create multiple parameters. For example, you could create an input range of 100 rows and assign a separate parameter for each row and put those parameters into the TI process. In the process you would have to write 100 DimensionElementAdd statements wrapped inside IF statements checking to make sure the parameter is not empty. This will work but is somewhat kludgey.

You could possibly take that same range and concatenate it into an array, separated by commas, and pass that to the TI but this would require you to have code in the TI to parse the array. Also the parameter will be limited to a max character size. I'm not sure how big that is but just that in itself makes it a non-solution for me.

The bottom line is there is no easy way to do what you want in an active form. Good luck.

[qml]

Also the parameter will be limited to a max character size. I'm not sure how big that is but just that in itself makes it a non-solution for me.

These days the limit is exactly 32,767 characters that can be passed from an Excel cell as a TI parameter via the Action Button (and only because that is Excel's limit on stings in cells). Hardly a limiting factor. Concatenating multiple strings, passing them as a single TI parameter and then decoding them within the TI is a powerful technique and I have used it many a time with good results.

[tomok]

These days the limit is exactly 32,767 characters that can be passed from an Excel cell as a TI parameter via the Action Button (and only because that is Excel's limit on stings in cells). Hardly a limiting factor. Concatenating multiple strings, passing them as a single TI parameter and then decoding them within the TI is a powerful technique and I have used it many a time with good results.

Then this sounds like the best solution for the OP. I didn't realize the string could be so large. Good to know. I'll revise my original suggestion to concatenate with a comma though. You should probably use a TAB, or some other ASCII value that could never actually be part of an element name.

[qml]

I'll revise my original suggestion to concatenate with a comma though. You should probably use a TAB, or some other ASCII value that could never actually be part of an element name.

Very good points. The separator needs to be carefully chosen on the basis of what characters are guaranteed not to be used in element names in a given environment. Only the OP can know that. Personally I avoid commas, semicolons and white characters like tabs and instead go for rarely used printable characters that are easily typed with the local keyboard layout. Something from this list always works well: ~, ¬, ^.

[lotsaram]

The problem you are facing is that an active form can only call a TI process. The TI process can accept parameters from the active form and add those to the dimension and the active form can assign a CELL to the parameter. The key word being cell. You cannot assign a range, i.e., a list of rows, to a parameter, meaning you can only add one item to the dimension per parameter. To add multiple elements you would have to create multiple parameters. For example, you could create an input range of 100 rows and assign a separate parameter for each row and put those parameters into the TI process. In the process you would have to write 100 DimensionElementAdd statements wrapped inside IF statements checking to make sure the parameter is not empty. This will work but is somewhat kludgey.

You could possibly take that same range and concatenate it into an array, separated by commas, and pass that to the TI but this would require you to have code in the TI to parse the array. Also the parameter will be limited to a max character size. I'm not sure how big that is but just that in itself makes it a non-solution for me.

The bottom line is there is no easy way to do what you want in an active form. Good luck.

Actually I don't think it needs to be anywhere near that hard. Assuming the consolidation is a "simple" one, and by that I mean a flat list with all elements of equal weight=1, ... then you need only 2 parameters the subset name or the MDX string for the member list and the name of the consolidation.
TI would
- remove all existing children from consolidation (if it exists)
- generate subset from MDX
- loop through elements in subset adding them to the consolidation

All up pretty simple. By assuming it is a simple flat rollup and we aren't adding any intermediate levels along the way then cycling through a subset is all you need. I believe this is all that the OP wants to do. But maybe not quite so simple for an absolute beginner, but you have to start somewhere, a good learning exercise in any case.

[gtonkin]

...I want to create an active form where I can send a list of numeric leaf level elements into a target consolidation within a dimension...

...I'm creating a form for my colleagues to use to enter new products (by barcode as its the unique identifier) into the 'All Products' consolidation...

Based on the above, the N-Level elements appear to exist and require classification within an intermediate level between itself and All Products - If the N-Level elements did not exist, an Active Form would be of little use. Not sure how the AF is driven but that is another issue.

@NXW -
1) Are you trying to add new elements or simply classify "orphan" elements within the All Products hierarchy?
2) Is the classification temporary (create subset using MDX and update as and when required) or more permanent i.e. rollup N-Levels into a parent and then into All Products?

[nxw]

hI gtonkin,

Thanks everyone for your feedback so far and being patient - it is really new to me so I probably am going to have a lot of dumb questions .

1) I am trying to add new elements to the consolidation 'ALL Product SKU' within the MAP Product SKU dimension.

2) The classification is permanent and is always going to be used.

So the category team just want to add new products into the consolidation, then fill out the fields. So just like a form, then run a process (which I haven't written) which is equivalent to a 'submit' button as if you were filling out a form and sending the details to the same data set.

[gtonkin]

1) I am trying to add new elements to the consolidation 'ALL Product SKU' within the MAP Product SKU dimension.

Just to clear up an ambiguity - Do the "new" elements already exist in the dimension but unclassified or are you trying to create them from the Excel template?
My understanding was that the exist in the dimension but need classification. Please clarify.

[nxw]

They are new elements that do not exist in the dimension - I'm trying to push them in from the active form.

[lotsaram]

They are new elements that do not exist in the dimension - I'm trying to push them in from the active form.

Well then that's a little bit different from what it sounded like to me that you had described till now. If these are new elements that don't yet exist in the dimension then how on earth do you expect to be able to show the elements as members of the row set of an active form?? The only way you could do this would be to have a generic dimension with placeholder elements (e.g. item1, item2, ... itemN) and have a string measure to enter the new element names. Then the TI code would need to do a DimensionElementInsert as well as DimensionElementComponentAdd.

However I would not recommend this. It sounds like a very bad approach. If you have new elements coming into the dimension then this should be automated via a data source and as new elements are recognized they get automatically added both as N elements and to the "Total Whatever" consolidation. Doing this manually is just asking for problems with master data.

[qml]

What lotsaram says is very right. You don't want to let users manually add any new elements to your dimension, typos and all. You have to have a metadata management solution in some form, even if it's something simple like a Dimension Worksheet (xdi) maintained by an admin / power user. Only then can you think of giving users a tool to rearrange the existing elements.

Then the TI code would need to do a DimensionElementInsert as well as DimensionElementComponentAdd.

Strictly speaking, DimensionElementComponentAdd is enough as it will add the child element if it doesn't exist.