Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
12 replies Latest Post - ‏2012-09-19T08:11:26Z by vipirailo
vipirailo
vipirailo
63 Posts
ACCEPTED ANSWER

Pinned topic Create cycle in TI

‏2012-09-17T17:12:58Z |
Hi, all.
I have Staff dimension with Manager attribute.
Let's say some members have 'John Goodwin' as a manager.
I need to organize the loop to execute some Statement for all members of Staff having 'John Goodwin'.
Pls, help how it can be done in TI process.
Updated on 2012-09-19T08:11:26Z at 2012-09-19T08:11:26Z by vipirailo
  • some_tm1_guy
    some_tm1_guy
    177 Posts
    ACCEPTED ANSWER

    Re: Create cycle in TI

    ‏2012-09-17T18:40:47Z  in response to vipirailo
    The easiest way would be to create a subset based on the attribute value and assign the subset as the data source for the TI.

    A data source for a TI can be created / manipulated / assigned on the prolog so this can all be dynamic and automated.
  • SystemAdmin
    SystemAdmin
    658 Posts
    ACCEPTED ANSWER

    Re: Create cycle in TI

    ‏2012-09-17T23:02:02Z  in response to vipirailo
    H vipirailo,

    As suggested, you could dynamically create a subset in the Prolog tab of TI by using the SubsetCreatebyMDX command and then process all records in the Metadata / Data tabs.

    Alternatively, you could read in each of the dimension values and test the attribute either using the ATTRS command or reading directly from the attribute cube via a DB command. You would need to do this within an If statement to prevent all records being acted upon.

    Hope this helps.

    Rod
    • vipirailo
      vipirailo
      63 Posts
      ACCEPTED ANSWER

      Re: Create cycle in TI

      ‏2012-09-18T02:03:38Z  in response to SystemAdmin
      Hi, Rod.
      I don't know any mdx operator to query member with defined attribute yet
      Suppose i use simple constructio like this:
      SubsetCreatebyMDX('All_members', '{TM1SORT( {TM1SUBSETALL(Staff )}, ASC)} ' );

      My question is what i have to select in data source tab? Is it ODBO (MDX query), IBM Cognos TM1 (dimension subset) or None?
      IF it is ODBO what are the connection parameters (ODBO Provider, ODBO Location, ODBO Datasource, ODBO Catalog, ODBO UserID, ODBO Password)
      • SystemAdmin
        SystemAdmin
        658 Posts
        ACCEPTED ANSWER

        Re: Create cycle in TI

        ‏2012-09-18T02:53:16Z  in response to vipirailo
        Hi vipirailo,

        Try setting your TI process up like this:
        Firstly create a public subset in your Staff dimension that will be used by the TI process i.e $ti_temp

        Datasource Tab:
        • Set Datasource Type to IBM Cognos TM1 (this could be labelled ICAS) > Dimension Subset
        • Select $TI_Temp subset from your Staff dimension

        Advanced > Prolog Tab:
        • Add in the following lines (you will need to update the logic accordingly)
        SubsetDestroy('Staff', '$ti_temp');
        SubsetCreatebyMDX('$ti_temp', '{FILTER( {TM1SUBSETALL( Staff )}, Staff.Manager = "John Goodwin")}');

        This will create the subset for the selected manager and then your Metadata and Data tabs will use the values in the revised subset. You could add additional functionality by making the Manager name a parameter in the TI process.

        Hope this helps.

        Rod
  • vipirailo
    vipirailo
    63 Posts
    ACCEPTED ANSWER

    Re: Create cycle in TI

    ‏2012-09-18T05:45:58Z  in response to vipirailo
    Hi, Rod and thanks a lot, it definitely helped.
    But i have one more question. To be honest it will be several ones.
    1. I don't know how can i use varying manager. Let say i have v_manager = ATTRS('Staff', TM1USER, 'manager_name'). But i can't use v_manager in your MDX because of quotes, i think
    2.I'm developing current TI as an element of custom workflow process. So i need the list of emails of collegues, manager and directs of current TM1 user. And my quest is how can i combine all these condition in one TI. Employees having the same manager plus manager as is plus employees having current user as a manager
    3.How about concurrent process. What will happened if more than one user run the TI process simultaneously?
    • SystemAdmin
      SystemAdmin
      658 Posts
      ACCEPTED ANSWER

      Re: Create cycle in TI

      ‏2012-09-18T06:46:37Z  in response to vipirailo
      Hi vipirailo,

      Happy to have helped. In response to your other questions.

      1: Firstly you need to create a run time parameter ( Advanced > Parameters tab) or create a variable in the Prolog tab. For this example I have called it Manager. You then need to use the EXPAND command as follows.
      SubsetCreatebyMDX('$ti_temp', EXPAND('{FILTER( {TM1SUBSETALL( Staff )}, Staff.Manager = "%Manager%")}'));

      2: Not sure I follow your process but you can just add another filter to the SubsetCreatebyMDX command i.e. {FILTER( {FILTER( {TM1SubsetBasis()}, Staff.Manager = "John Goodwin")}, Staff.Level = "Supervisor")}

      3: To get around that issue each temporary object (i.e. subset) would need to be named uniquely. One way to do this is to create a variable with a timestamp i.e. vSubset = '$temp' | TIMST(NOW, '\Y\m\d\h\i\s');

      Hope this helps.

      Rod
      • vipirailo
        vipirailo
        63 Posts
        ACCEPTED ANSWER

        Re: Create cycle in TI

        ‏2012-09-18T07:04:55Z  in response to SystemAdmin
        Hi, Rod
        #1 done, thanks
        #2 I have to create the result set as an union of all conditions.I guess Filter(Filter will create an intersection, am i right?
        #3 What name of subset should i select in Data source name? Can i use variables there?
        • SystemAdmin
          SystemAdmin
          658 Posts
          ACCEPTED ANSWER

          Re: Create cycle in TI

          ‏2012-09-18T07:16:04Z  in response to vipirailo
          Hi vipirailo,

          Yes, just create a filter that is a union of your requirements.

          As for the dynamic subset. After you have created the subset, on the Prolog tab, use the DataSourceNameForServer command to update the subset name i.e. DataSourceNameForServer = vSubset;

          Hope this helps.

          Rod
          • vipirailo
            vipirailo
            63 Posts
            ACCEPTED ANSWER

            Re: Create cycle in TI

            ‏2012-09-18T19:34:53Z  in response to SystemAdmin
            Hi,Rod.
            All done. Thank you for the time you've spent to help me. By some reason i can't use DataSourceNameForServer because of errors thrown but DataSourceNameForClient works well
            • some_tm1_guy
              some_tm1_guy
              177 Posts
              ACCEPTED ANSWER

              Re: Create cycle in TI

              ‏2012-09-18T20:48:12Z  in response to vipirailo
              DataSourceNameForServer is for TEXT data sources for SUBSET you need to set DataSourceDimensionsubset
            • SystemAdmin
              SystemAdmin
              658 Posts
              ACCEPTED ANSWER

              Re: Create cycle in TI

              ‏2012-09-19T01:28:10Z  in response to vipirailo
              Hi vipirailo,

              Thanks, I am pleased it is all working.

              As per some_tm1_guy's post, my last entry was incorrect, it should have been DatasourceDimensionSubset instead of DatasourceNameForServer i.e. the synatax would be DatasourceDimensionSubset=vSubset; Thanks to some_tm1_guy for picking that up.

              Also, if you haven't already, you may also want to consider adding a line to your epilog tab to remove the temporary subset after you have finished i.e. SubsetDestroy('Staff', vSubset);

              Rod
              • vipirailo
                vipirailo
                63 Posts
                ACCEPTED ANSWER

                Re: Create cycle in TI

                ‏2012-09-19T08:11:26Z  in response to SystemAdmin
                Thanks Rod for clarification. I rewrite that part, although it works with DatasourceNameForClient.
                For those who interested i have a notice that construction like this:
                
                SubsetCreatebyMDX(
                '$ti_temp', 
                '{FILTER( {TM1SUBSETALL( Staff )}, Staff.Manager = "John Goodwin")}');
                

                doesn't work for alias property and can produce error for some attributes. So i have to write:
                
                SubsetCreatebyMDX(
                '$ti_temp', 
                '{FILTER( {TM1SUBSETALL( Staff )}, [Staff].CurrentMember.Properties("Manager") = "John Goodwin")}');
                

                instead of previous one

                PS: I don't know MDX language except sentenses that kindly gave me Rod. So it's just my findings