Planning Analytics

 View Only
Expand all | Collapse all

How does one reference a variable in a process script?

  • 1.  How does one reference a variable in a process script?

    Posted Fri September 06, 2019 03:27 PM
    I am creating a Turbo-integrator process to load data into dimensions and cubes from a data source.

    I have variables created for each column of the data source (file).

    I am then trying to create a dimension as a first step.

    I wrote the following code in the Metadata section-

    DimensionDestroy('Facility');
    DimensionCreate('Facility');
    DimensionElementInsert('Facility', , V_Facility_ID,'S');

    I used the editor "help" to insert these parameters. V_Facility_ID is a reference to the variable that represents the column in the file that stores the data for this dimension.

    However, I am getting an error:

    Error at Metadata Line 3 + Syntax error on or before " , V_Facility_ID,'S' invalid string expression "

    Am I doing something wrong with respect to how I call the variable? Or is this something to do with the data in the column itself?

    Thanks in advance!

    ------------------------------
    Shubho Ghosal
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: How does one reference a variable in a process script?

    Posted Fri September 06, 2019 06:21 PM
    Hi,
    On the data tab where you created the variables for the column of V_Facility_ID did you set the data type to string?   All elements are strings, so in order to add an element to a dimension, the variable you are inserting must be a string.   You could also use the function "NumberToString" to change it to a string. 
    Out of curiosity, why are you adding the element as an "S" type?  That is a string type, and means only string data should be recorded for this element.   As a note, only the last dimension in a cube really has a use for S type elements.    

    Thanks,
    ScottB

    ------------------------------
    Scott Brown
    ------------------------------



  • 3.  RE: How does one reference a variable in a process script?

    Posted Mon September 09, 2019 12:50 AM
    Hi Shubho I'll also add that you shouldn't need to call the destroy or create on the metadata-tab. The metadata tab runs for every record of you data source, you don't want to destroy and recreate the dimension every single time. 


    ------------------------------
    Ryan Clapp
    ------------------------------



  • 4.  RE: How does one reference a variable in a process script?

    Posted Mon September 09, 2019 07:10 AM
    Good call Ryan.  It doesn't make sense to destroy and recreate the dimension for each record in the data source.

    @Shubho Ghosal, One more quick thought on your original question.   Assuming you are using the Ti editor in Architect or Perspectives, you need to set the Contents type of the variable to determine what part of the script the variable can be used in.  I think most modelers probably use the Other  type, which makes the variable available everywhere in the Ti. 


    Again, you probably want to destroy or just empty the dimension in the Prolog section, and then add elements and set the parent-child relationship  between elements in the Metadata section.

    One more small hint...both the Metadata and Data section of the Ti process run once per record in the data source.  Modelers will often try write code only in the Data section and leave Metadata blank for best performance.  Your mileage will vary on this depending on what your Ti code is doing.  You might want to test it both ways (code in Metadata and Data vs code just in Data) to determine which is best.  See the documentation here: https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/r_tm1_ref_tifun_dimensionelementinsertdirect.html and https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc/r_tm1_ref_tifun_dimensionelementcomponentadddirect.html 


    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------



  • 5.  RE: How does one reference a variable in a process script?

    Posted Mon September 09, 2019 01:10 PM
    Thanks, Stuart and Paul. These are all great tips.

    I have removed the destroy and create commands to the Prolog.

    The reason why I am needing to store strings is the following -

    I am creating a model based on a concept named Facility. This model will calculate a cost of liability for the Facility.

    The model is based upon several different "attributes" of the Facility. (For example, Facility Location is an attribute that influences liability cost).

    I need to store these attributes in a way that strings such as Location (Area) are captured in a way that they can be displayed in a Cube View (for users to see), and used in calculations that ultimately calculate the liability cost.

    I initially considered Dimension Member attributes (ones that are added using the Member Attributes pane on the right hand side of the dimension member list), but I did not see a way to display such attributes in a cube view.

    Hence, I am going with an implementation that has a Dimension named Facility, and another Dimension named Facility Attributes.

    They come together in a cube that is used to define the attributes for a facility. The cells within need to be strings.

    I hope this is clear. If there is a different way, I would love to know.

    Finally, with regard to the error I was getting, I am still getting the error, even though my variable is defined as a string, and even after I have moved the Create and Destroy commands to the prolog.

    I am moving to the client tool (Architect) to see if I can resolve my issue there, and see what I am doing wrong in my scripting.

    Thanks for all your help  so far!

    (And if you can provide me some guidance in the other post with regard to how best to deploy models to our users, that would be much appreciated!)

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 6.  RE: How does one reference a variable in a process script?

    Posted Mon September 09, 2019 07:41 PM
    Oh my Lord. 

    The issue is with the fact that just having an empty area for the InsertionPoint parameter is no good.

    It has to be presented as an empty string, or ''.

    This is not clear in the function reference.

    The function reference says:

    DimensionElementInsert(DimName, InsertionPoint, ElName,ElType);

    InsertionPoint

    An existing dimension element. The element being added to the dimension will be inserted immediately before this existing element. If this parameter is empty, the new element is added to the end of the dimension.




    "parameter is empty" could imply this -

    DimensionElementInsert('Facility', , 'Test', 'N'); (Note the empty space in the second parameter)

    That raises the error:

    Syntax error on or before etc.

    However, this works:

    DimensionElementInsert('Facility',  '', 'Test', 'N'); (Notice the empty string in the second place).

    This is definitely an area where the function reference could improve, IMO.

    In any case, I have found a resolution to my issue and will be able to move forward, hopefully.

    Thanks all!

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 7.  RE: How does one reference a variable in a process script?

    Posted Tue September 10, 2019 07:05 AM
    Good catch and thanks for the update!  Yes, a parameter for a Ti function cannot be literally empty.  In this case you need an empty string (empty single quotes).  I will point this post out to our documentation team.  We have been adding samples to our documentation for many Ti functions that will show how they are typically implemented.


    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------



  • 8.  RE: How does one reference a variable in a process script?

    Posted Tue September 10, 2019 04:26 PM
    "I initially considered Dimension Member attributes (ones that are added using the Member Attributes pane on the right hand side of the dimension member list), but I did not see a way to display such attributes in a cube view."
    There are two ways to do this in PAW.   The old style way is to display the attributes cube.  Under Control Objects of a database, go to cubes and then "}ElementAttributes_Facility".    The other way to show attributes is in a cube view that uses the dimension, click on the column or row, right click and choose show attributes.  Then you will be able to display them in the view.

    If you are having users enter these attributes, you probably made the right choice to create a normal cube with Facility in the first dimension and Facility_Attributes as the last dimension.   All Facility elements are N.   The  Facility_Attributes elements are the ones where you would choose string or number, and also the format of the number.   In this example the element Facility Location would be string, but Local Tax Rate would be N with a format of percentage.
    Facility      Facility Location     Local Tax Rate
    "00001"      "Downtown"                10.0%
    "WHSE2"    "Somewhere"                5.1%

    The reason for having a normal cube if the attributes are updated by users, is that locking can occur when true attributes are being updated.  You might consider creating a process that can copy the normal cube data to the attributes cube, so you have the attribute values available.

    Hope this helps.


    ------------------------------
    Scott Brown
    ------------------------------



  • 9.  RE: How does one reference a variable in a process script?

    Posted Tue September 10, 2019 10:34 PM
    Great inputs! Thanks Scott!

    Just one more query - why should the Facility members be N?

    I am entering string values - what am I missing in this N vs S logic?

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 10.  RE: How does one reference a variable in a process script?

    Posted Tue September 10, 2019 10:59 PM
    Good question. Only the last dimension in a cube has to have string elements to allow the cube to store strings. Typically it is not the facility that is a string, it is the information you are storing or the measure.


    In this example Facility is still a numeric (the default option), but the measures dim has some string elements which lets you store string data. 


    ------------------------------
    Ryan Clapp
    ------------------------------



  • 11.  RE: How does one reference a variable in a process script?

    Posted Wed September 11, 2019 11:18 AM
    Got it. It influences how data is rolled up, not in the Facility Attributes cube, which stores strings (and hence does not require the data to be rolled up), but in other cubes, such as Liability Cost, where the data does indeed need to be rolled up.

    In that case, my Facility members do need to be N. I will change that aspect.

    Thanks for the detailed example and explanation, Ryan! Much appreciated!

    ------------------------------
    Shubho Ghosal
    ------------------------------



  • 12.  RE: How does one reference a variable in a process script?

    Posted Wed September 11, 2019 12:38 PM
    Edited by System Fri January 20, 2023 04:25 PM
    Very valuable inputs, Scott.

    I was trying to figure out how to copy the attribute values to the attribute of the dimension member as an alternative.

    Initially tried the following:

    CellPutS(V_Licence_Number, '}ElementAttributes_Facility', V_Facility_ID, 'Licence Number' );

    This gave me a "cube not found" error, obviously, as it is not meant for Attribute Manipulation.

    I then found an entire section on Attribute Manipulation.

    I switched the code to:

    AttrPutS(V_Licence_Number, 'Facility', V_Facility_ID, 'Licence Number' );

    I initially had this code in the metadata section, right after the dimension element insert.

    This did not work.

    I had to move the code to the Data section to make it work.

    Just another lesson learnt. Clearly, the dimension needed to "compile" after the member inserts before the attributes could be updated.

    All in all, many valuable lessons learnt in this one thread that started with a simple error.

    Thanks all!

    ------------------------------
    Shubho Ghosal
    ------------------------------