TM1 Forum

Hi all,

I'm trying to create a drill-through process. User is to drill from "PO" cube to "Project" cube.

Dimensions of PO cube: Linenum, Product, Month, Measure_PO
Dimensions of Project cube: Linenum, Product, Project, Currency, Month, Measure_Project

As you can see, Project cube has 2 additional dim. Note that two of the elements in Measure_PO actual contain information that can direct to the elements in Project dim and Currency dim through the use of Picklist.

For example, in PO cube, user can select a currency "USD" from the picklist in "Currency" element in Measure_PO dim.

After I had created the drill process and rule, I noticed I'm unable to drill to a view in Project cube in relation to the Project and Currency elements in Measure_PO dim in PO cube. How can I achieve, like setting up a CellGetS in the process? How can I tell TI to look up the measure in PO cube and direct the view to use that as elements in the two additional dimensions in Project cube?

Many thanks!

In the drill process you will have an automatic parameter for each dimension in the cube you are drilling from. The values of the element names from the intersection you are drilling from will automatically be assigned to each matching parameter. If any of these dimensions are title dimensions in your destination view then the parameters will be automatically assigned as the title element value. That basically is how a drill works.

What you do with row and column dimensions and any dimensions in the destination view that are not in the source cube is up to you. If you want to set project and currency on the way through the easiest way to do it is to get the element names (from CellGetS of your source cube - use the existing parameters) and assign them to subsets used in the destination view. You need to do this on the prolog of the drill process. If any of your dimension names contain spaces you might need to edit the parameter names to remove the spaces.

yes, the automatic-created parameters are: Linenum, Product, Month, Measure_PO. So in order to introduce Currency and Project as title dimensions in the target cube (Project cube), I will have to add two new parameters?

Then, I will use CellGetS for the two manually added parameters in Prolog to retrieve the measure data from source cube.

However, one thing I do not understand is that, in the TI, I do not see any generated view of the target cube. So how can I perform Subsetdestroy, subsetcreate, subsetelementinsert and subsetviewassign? Or is the view in question the one that I had set up and named in the target cube e.g. Drill_Project? I will give it a go, but if you notice anything missing, appreciate if you can post a reply.

Thanks!

No you can't create additional parameters. (Well actually you can but the values won't be picked up by the drill process so there's no point)

OK here's how it works.

Step 1: define your drill process. In the drill process you can either set up the data source of the drill to be a view that you have already created (when the drill is executed the title elements will be set by the matching dimension parameters). You can modify the view in the prolog of the drill process by assigning elements to and subsets for the additional title elements or to change the rowset or column set. Or you can even create an entirely new view from scratch in the prolog if you want to.
Step 2: in the Epilog make sure the view handle is returned to the view you have just created or modified
Step 3: write the drill assignment rule. The string value needs to exactly match the name of the drill process (without the "}Drill_" prefix)
Step 4: make sure your users have READ access to the "}Drill_<your drill name>" process, otherwise they won't be able to execute the drill

hi lotsaram,

I manage to get it working. I notice the subsets created for the currency and Project dim cannot be destroyed as they are now locked to the target view. Not an issue actually, since TM1 does not allow that to happen anyway.

However, I do have another question when I try to refine the drill. I'm expecting the users to ask "Yes, now that I can drill to USD element in the Curency dim (title dimension), but what if I want to change to another currency element in the target view? I can't simply select from the drop-down list from the target view. The only way is to go to Subset Editor to select."

This is because when I did a SubsetInsert, I only insert USD element base on CellGetS from source cube. So when I subsetAssign to the TargetView, the Currency dim will of course has only 1 element.

How do I fix the currency subset to the Default subset, yet after drilling, the SELECTED Currency element is based on CellGetS e.g. USD, while users can still select another currency in the title dimension using drop-down list. In other words, the Currency subset does not get updated, it's just the selected element that is being passed from source cube.

Once again, appreciate your help thus far.

Hi lotsaram,

managed to solve the design issue. I simply use DIMIX with CellGetS to return the index num of the Currency value, then use ViewTitleElementSet to define the title element. Hence, there is no need for me to redefine my Currency subset in the target view.

Thanks for your help!

Hi,

I'm working with TM1 cubes for the first time and i have managed to create a small cube with 3 dimensions (time, company and G L Accounts- measure dimension). I have connected my cube to cognos reporter to develop reports on it. I can easily drill down in dimensions like time and company but i cannot drill in g l accounts dimension. also i can see the hirarchy in my cube browser and the cognos 'avaliable components section'.

kindly suggest.

Thanks & Regards
ADITI

Cognos treats the dimension defined as being the cube's measure dimension in a different way to any other dimensions on the cube.

Create a fourth dimension with a single element and define this as the Measure dimension for the cube. Then when you look at the cube in Cognos your three main dimensions will behave in the same manner.

can you please post your code harry and screenshots of the cube have to create a similar drill path

alaussie wrote:can you please post your code harry and screenshots of the cube have to create a similar drill path

Considering that this thread is 3 years old and harrytm1 has last logged on in May 2013, you are unlikely to get a prompt response to the above.

As to your actual question... Can you tell us what you have tried and what you are stuck on?