TM1 Drill Through to relational table

[andre lie]

Hi all, I have a number of questions related to drill to relational table. Appreciate if you can answer some or all. I have tried what i question but seems not working

I have a cube with a drill through set up to a relational table. The origination cube has 2 dimensions. In the drill through process - datasource tab - query, I have this statement:

SELECT * from sales
where month='?dim1?'

My questions are:

1. Can I add another parameter to the drill through process, so that it has 3 parameters: 2 parameters from the elements in origination cube and the value of the third one should be input on the fly when we drill. This third parameter will add to where clause, so that the returned result is smaller

2. Can I manipulate the dimension element in the where clause?

For example instead of
SELECT * from sales
where month='?dim1?'

I want to set the where clause to month = an alias of element in dim1. What's the syntax. I tried where month='?ATTRS('dim1',dim1,'Alias')?' but no working

3. In the query tab, I have this:
SELECT * from sales
where month='?dim1?'

Can I overwrite the query in the query tab with what I have in the prolog? I understand there is ODBCOutput command that we can use. The prupose is so that I can perform manipulation of parameters and then define the final query.

4. The drill from excel worksheet always return 65536 rows in 2003. The worse is that the first row will be overwritten by the 65537 row if the drill returns more than 65536 rows.

But in the websheet, the drill can return more than 65536 rows.

Any idea on how to handle limitaion in Excel drill?

Thanks
Andre

[Michel Zijlema]

Hi all, I have a number of questions related to drill to relational table. Appreciate if you can answer some or all. I have tried what i question but seems not working

I have a cube with a drill through set up to a relational table. The origination cube has 2 dimensions. In the drill through process - datasource tab - query, I have this statement:

SELECT * from sales
where month='?dim1?'

My questions are:

1. Can I add another parameter to the drill through process, so that it has 3 parameters: 2 parameters from the elements in origination cube and the value of the third one should be input on the fly when we drill. This third parameter will add to where clause, so that the returned result is smaller

2. Can I manipulate the dimension element in the where clause?

For example instead of
SELECT * from sales
where month='?dim1?'

I want to set the where clause to month = an alias of element in dim1. What's the syntax. I tried where month='?ATTRS('dim1',dim1,'Alias')?' but no working

3. In the query tab, I have this:
SELECT * from sales
where month='?dim1?'

Can I overwrite the query in the query tab with what I have in the prolog? I understand there is ODBCOutput command that we can use. The prupose is so that I can perform manipulation of parameters and then define the final query.

4. The drill from excel worksheet always return 65536 rows in 2003. The worse is that the first row will be overwritten by the 65537 row if the drill returns more than 65536 rows.

But in the websheet, the drill can return more than 65536 rows.

Any idea on how to handle limitaion in Excel drill?

Thanks
Andre

Hi Andre,

Regarding question 1:
You cannot add a parameter to a drill-through process. A way to work around this is to 'misuse' one of the available parameters in case you don't need that one (f.i. the drill-through process is defined on a single measure of a measures dimension - so the measure is explicit - so you don't need the '?measures?' parameter; you can use this parameter to pass through another variable). Another way could be loading two variable values on a single parameter and use the SQL SUBSTR function in the query to select the parameter1 part and parameter2 part.

Regarding question 2:
You can set the alias selection on the Prolog tab (which is executed before the SQL query is executed). So, f.i. you can add the line dim1 = ATTRS('dim1',dim1,'Alias') to the prolog to change the dim1 value to its alias.

Regarding question 3:
You cannot change the SQL query on the Prolog tab, but you can manipulate the parameter values on the Prolog tab (see answer on question 2) before execution of the query.

Regarding question 4:
You can't change the row limitation in Excel 2003. However in my opinion it is not a sensible thing to have a drill-through process return thousands of rows. You can zoom in on data in the cube and when at a certain point further details are missing you can do a drill-through - but I would assume you would by then have reached a point where the regarding cell is build up from max. a few thousands of records.

HTH,

Michel

[andre lie]

Hi Michel,

Thanks for your input. I have tried your suggestion no. 2 regarding modifying parameter value in the prolog tab. It works well. Appreciate it if you can give me a few more hints.

I have a cube, with a date dimension among others, updated daily . This daily cube has actually summarized its data source to a certain extent. The detailed transactions are not stored in TM1 cube but in the relational database. The number of detailed transactions is very huge (200-300 millions daily) and that's why I think it is possible when I drill through to the relational table, it still gives me hundreds of thousands of records and cause TM1 hung or unacceptable query time.

I am thinking to provide a drill through with 2 options: one is to select all records filtered by the dimension elements as parameters, and second one is to select a single or a few records only if we know which records we want to zoom in (I will use a dummy element in one of dimensions that need not be passed as parameter and create a separate drill assignment rule for this dummy element).

For the first option my SQL query is simple, for instance:
SELECT * from sales
where month='?dim1?'

But for the second option, my SQL query should be:
SELECT * from sales
where month='?dim1?' AND id in ('detail1','detail2',...'detailN')

I plan that user input the value of 'detail1','detail2',...'detailN' into a parameter cube. In drill through process, in the prolog I will read that cube and create a string ('detail1','detail2',...'detailN').

So in query portion in data source tab, I code:
SELECT * from sales
where month='?dim1?' AND id in '?dim2?'

where in the prolog i will assign dim2=('detail1','detail2',...'detailN')

My issues:
1. Seems this kind of sql query does not work (I get SQL query failed when trying to save the process). TM1 check sql query syntax to certain extent seems.

2. Even though no. 1 is working, I still have to deal with a case if more than 1 user trying to do same drill through and updating the same parameter cube since the parameter cube is supposed to be used by more than 1 user

Regards
Andre

[Michel Zijlema]

But for the second option, my SQL query should be:
SELECT * from sales
where month='?dim1?' AND id in ('detail1','detail2',...'detailN')

I plan that user input the value of 'detail1','detail2',...'detailN' into a parameter cube. In drill through process, in the prolog I will read that cube and create a string ('detail1','detail2',...'detailN').

So in query portion in data source tab, I code:
SELECT * from sales
where month='?dim1?' AND id in '?dim2?'

where in the prolog i will assign dim2=('detail1','detail2',...'detailN')

My issues:
1. Seems this kind of sql query does not work (I get SQL query failed when trying to save the process). TM1 check sql query syntax to certain extent seems.

2. Even though no. 1 is working, I still have to deal with a case if more than 1 user trying to do same drill through and updating the same parameter cube since the parameter cube is supposed to be used by more than 1 user

Hi Andre,

I haven't found time to test whether the IN (...) construct will work, but as the variable instantiation is just inserting a string inside the query text I would think this should work.
A thing to pay attention to is the fact that the text that you want to insert contains quote characters: ('detail1', 'detail2', ..., 'detailN').
To build a string like that the code should look something like: dim2 = '(''' | vardetail1 | ''', ''' | ... | vardetailN | ''')'; or dim2 = '(' | Char(39) | vardetail1 | Char(39) | ', ' | ... | vardetailN | Char(39) | ')';
Also note that the SQL interpreter could expect a space after the comma's in the list.

Regarding your second issue - maybe another forum member has some thoughts on this. I thought that the the TM1User() function could be of help, but this appears to be a worksheet function only.


Michel

[andre lie]

Hi Michel,

1. I manage to sort out the sql syntax. Syntax similar like this works:
....
and FactSalesQuota.employeekey in ?msisdn?

Previously my syntas was (using single quote):
and FactSalesQuota.employeekey in '?msisdn?'

2. TM1User() works in TI process. So i modify the msisdn parameter in the prolog by reading from a control cube. The control cube has a }clients dimension. So every user can write parameter value to the correct element in the control cube.

Thing that i haven't tried is whether we can build a dynamic subset in the }clients dimension which display only element which is equal to TM1User(), otherwise we need to create a security group for each user, which is not effective i guess considering that hundreds of users exist.

Regards
Andre

[Michel Zijlema]

Thing that i haven't tried is whether we can build a dynamic subset in the }clients dimension which display only element which is equal to TM1User(), otherwise we need to create a security group for each user, which is not effective i guess considering that hundreds of users exist.

Hi Andre,

You can use

Code: Select all

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Clients] )}, USERNAME )}

for this. See here.

Michel

[andre lie]

Michel,

Thanks for the hint. I test {TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Clients] )}, USERNAME )}. It seems it only works the first time you create that dynamic subset. If you log out, log in under different user name, then open the dynamic subset, it does not give you the correct element.

What i have tested working is by running a TI process before opening the cube:

In the prolog tab,

SubsetDestroy('}Clients','subset1');
mdx='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Clients] )}, TM1User() )}'
SubsetCreatebyMDX('subset1',mdx);

With TI process it resolves to the correct element according the user who logs in but one more step for user to perform.

Thanks
Andre