Drill Through

[shockwave]

Hi All,

I am currently trying out the drill down function in TM1 and just wanted to make sure that what I am trying to to do is possible with my little knowledge in this area. Hoping that you might be able to answer this question i have:

1) I am trying to grab some information from my access database where the field does not exist in TM1. Does this field need to exist in TM1 so I can grab this information. I have a feeling that the question is yes, but am hoping that I am wrong. I have a detailed cube of 6 dimensions and have it at a customer level. I want to pull back information for a sub customer ( the field that does not exist) and show it in the drill down output.

2) if the first anwser is no then if a new dimension ( mobilenumber) is created to fit this detail that I am after, can I put the data against a generic node called 'mobilenumber' when i load the T1 and then somehow drill down to a lower level in the database or does 'mobilenumber' need to exists in the access database as well?? Basically the mobile number dimension would be huge and I dont want to have to create all these new elements when I run my TI. I would like to be able to drill down from the customer level to mobile number detail.

Currently the cube is made up of the following dimensions:
Test_Customer
Test_Class Code
Test_Division Code
Test_Time
Test_MobileNumber
Test_Measures

Drill process:

SELECT CustomerNumber, MobileNumber, ClassCode, Division_Code, Data
FROM BilledCallTM1_MA3_3
WHERE ( CustomerNumber = '?Test_Customer?' AND ClassCode = '?Test_Class Code?' AND Division_Code = '?Test_Division Code?' AND
( IIF( '?Test Time?'='Feb 2009' OR '?Test_Measures?'='Revenue',TRUE,TRUE)))

Any help would be much appreciated.

Cheers

Shock

[Gregor Koch]

Hi Shock

1) The short and simple answer is 'No'

It is the typical situation for a Drill Through to list transactions from a source which are the basis for a balance in TM1 whilst for example including the comments against the transactions. Here the comment field doesn't need to exist in TM1 it is just part of your SELECT statement.

2) Allthough or maybe because the answer to 1) is 'No' I have a hard time to answer your second question.

If I understand you correctly you are trying to (simply) show all mobile numbers for the 'sub customers' of a customer. Yes, you can achieve this with the proper Drill Through. And unless you need to create a cube which allows you analysis of data by groups of mobile numbers (unlikely but maybe clarify) I don't think it is necessary to create a mobile number dimension.

Cheers

[paulsimon]

Shock

If all you want to do is to get the standard relational output then the answer is yes. You just need to write the SQL with the WHERE clause populated with the elements from the cell that they drilled from in the cube, then, if there are multiple records for that combination, as there will be if the table holds data at sub-customer level, then the SQL will return those records.

At my current client I set up a drill from their 7 segment GL balances down to the transactional posting data in the GL, and the drill thru includes a hyperlink, which is just an extra field in the SQL. Clicking on the hyperlink jumps straight in to the entry screen of their purchase requisition system. There is a bit of VBA involved to format the sheet that it creates and to convert the dumb text to a hyperlink, but it wasn't too difficult to do.

Regards

Paul Simon

[shockwave]

Thanks guys for the reply's. Just to clarify I am trying to show all mobile numbers ( sub customers) of a customer. The mobile numbers donot exists in TM1 but I would like the user to be able to dirll into the customer and pull back transactions that show what mobile number it relates to ( because I am not sure whether to keep or 'not keep' the mobile dimension i have added it as one of my dimensions)

1) With my current statement i am not sure what I need to do to modify the SQL statement and require some expert help. Currentlly

These are my dimensions ( lsited again)

Test_Customer
Test_Class Code
Test_Division Code
Test_Time
Test_MobileNumber ( keep or not to keep??)
Test_Measures

The fields that exist in my Access database:

CustomerNumber
MobileNumber
ClasssCode
Division_code
Data

Drill process that i need to modify:


SELECT CustomerNumber, MobileNumber, ClassCode, Division_Code, Data
FROM BilledCallTM1_MA3_3
WHERE ( CustomerNumber = '?Test_Customer?' AND ClassCode = '?Test_Class Code?' AND Division_Code = '?Test_Division Code?' AND
( IIF( '?Test Time?'='Feb 2009' OR '?Test_Measures?'='Revenue',TRUE,TRUE)))

Thanks again

Shock

[Steve Rowe]

Hi Shock,

Without a deep(er) knowledge of your SQL DB it's hard to be alot more specific with answers. What you have written looks correct so can you tell the forum what is going wrong? You certainly don't need your mobile number dimension if you only want to make it available after drill through. That said if you do load it then you don't need to bother with the drill through and that solves the problem, though you will have to watch data volumes.

Does your SQL statement evaluate in an Access environment if you substitute values for the parameters? I'm not familiar enough with SQL to be sure about your IIF statement.

Note that there are some issues with TM1 / ODBC not releasing its lock on Access DBs, there are few threads on this and I've encountered this myself. With your data in an access DB even when you get the drill through working you may still fail to deliver this as every drill through will (may?) require a server restart to release the lock.

Cheers,

[paulsimon]

Shock

What we probably need is a list of the fields on your table and their data type. Can you post that?

If your SQL The IIF looks odd. It seems that it will always evaluate to TRUE so why is it there?

Code: Select all

( IIF( '?Test Time?'='Feb 2009' OR '?Test_Measures?'='Revenue',TRUE,TRUE)))

It would seem that the following would have the same effect but Revenue and Feb 2009 don't look like they would be column names in your SQL table.

Code: Select all

AND ( '?Test Time?'='Feb 2009' OR '?Test_Measures?'='Revenue' )

Were you just putting this in because you thought that you needed to use all the dimension variables from the cube in the query? You don't need to do that.

I would guess that you could write:

SELECT CustomerNumber, MobileNumber, ClassCode, Division_Code, Data
FROM BilledCallTM1_MA3_3
WHERE CustomerNumber = '?Test_Customer?'
AND ClassCode = '?Test_Class Code?'
AND Division_Code = '?Test_Division Code?'

Regards


Paul

[shockwave]

Thanks Paul. I did think that i needed all the dim vars from the cube in the query. Killed those fileds that didn't exist and used your query. The data appears when I preview it in my drill process however when I try to drill into it via drill rule it doesn't pick up the mobile number. So, do i need mobilenumber as a dim? Steve, good point about the locking release in access ( i have heards about this b4 but not in relation to tm1), maybe need to source this information from the datawarehouse.

I have listed all the the fields in the acceess database below. They are all text fileds in access except for "data" which is numeric

CustomerNumber
MobileNumber
ClasssCode
Division_code
Data

Perhaps my rule is incorrect? However it appears correct.....here is rule:

['Revenue'] = S:IF( ( ELLEV( 'All Customers', !Test_Customer ) = 0 ) &
( ELLEV( 'All Class Codes', !Test_Class Code ) = 0 ) &
( ELLEV( 'All Divisions', !Test_Division Code ) = 0 ) &
( ELLEV( 'All Time', !Test_Time ) = 0 ) &
( ELLEV( 'All Measures', !Test_Measures ) = 0), 'MA3_minicube_MobileNum', '' );
[] = S:IF( ISLEAF= 1, 'MA3_minicube_MobileNum','' );

When i view the drill output I still cannto see the MobileNumer leve of detail........

Thanks again for you help guys.

Cheers

Shock

[paulsimon]

Shock

So you are now using the following SQL?

SELECT CustomerNumber, MobileNumber, ClassCode, Division_Code, Data
FROM BilledCallTM1_MA3_3
WHERE CustomerNumber = '?Test_Customer?'
AND ClassCode = '?Test_Class Code?'
AND Division_Code = '?Test_Division Code?'

Is the problem that, when you right click on a cell in the cube viewer, that the Drill Thru is not happening, or is it that you are getting a Drill Thru result table, but the Mobile Number is not being shown?

From your description, I will assume that it is the latter.

Is it retrieving Customer records, but with a blank Mobile Number field, or is it retrieving no records?

As the Query has the MobileNumber it in then it must be showing that field.

Is it possible that the inclusion of the ClassCode and Division Code is leading to a combination that does not have a MobileNumber? Perhaps you just need:

Code: Select all

SELECT CustomerNumber, MobileNumber, ClassCode, Division_Code, Data
FROM BilledCallTM1_MA3_3
WHERE CustomerNumber = '?Test_Customer?' 

If CustomerNumber is unique across the board rather than just within a Class and Division. You should know the data better than I do. Have you tried running the query in Access, replacing the ?Test_Customer?, etc, with values that you know should give a result? Your problem may be that your Access table does not contain the data that you are expecting.

I have listed all the the fields in the acceess database below. They are all text fileds in access except for "data" which is numeric

CustomerNumber
MobileNumber
ClasssCode
Division_code
Data

Perhaps my rule is incorrect? However it appears correct.....here is rule:

['Revenue'] = S:IF( ( ELLEV( 'All Customers', !Test_Customer ) = 0 ) &
( ELLEV( 'All Class Codes', !Test_Class Code ) = 0 ) &
( ELLEV( 'All Divisions', !Test_Division Code ) = 0 ) &
( ELLEV( 'All Time', !Test_Time ) = 0 ) &
( ELLEV( 'All Measures', !Test_Measures ) = 0), 'MA3_minicube_MobileNum', '' );
[] = S:IF( ISLEAF= 1, 'MA3_minicube_MobileNum','' );

When i view the drill output I still cannto see the MobileNumer leve of detail........

I am not sure about your ELLEV functions. Is All Customers the dimension name, or the element at the top of the dimension? It should be the dimension name.

If all you want to do is only allow drilling on cells at the base level then you only need:

Code: Select all

[] = S:IF( ISLEAF= 1, 'MA3_minicube_MobileNum','' );

However, I would suggest, that, as you don't use all the fields that you are checking for base level in your SQL query then you probably only need:

Code: Select all

['Revenue'] = S:
  IF( ( ELLEV( 'All Customers', !Test_Customer ) = 0 ) &
       ( ELLEV( 'All Class Codes', !Test_Class Code ) = 0 ) &
       ( ELLEV( 'All Divisions', !Test_Division Code ) = 0 )
       ,
       'MA3_minicube_MobileNum'
       ,
       '' 
   ) ;

Regards


Paul Simon

[shockwave]

Hi Paul,

Thanks for getting back to me. Yes, the drill through is working however I am not seeing the mobile field in the output.
I have ammended my sql to be a little more specific, but still the cube is not drill through is not showing the mobile number field.
YOu make a comment about the "uniqeness" of the records. The customer number is show on multiple recoreds in my access database, with the customer
number having multiple Mobile number customers see screenshot below in "drill through process"

drillProcesView.jpg (123.23 KiB) Viewed 8324 times

here is a screeshot of my cube that I am drilling down on. note that it does not contain a "Mobile number" dimension:

cubeview.jpg (51.29 KiB) Viewed 8324 times

Still lost as to why I cannot see this filed come through in my drill through ( here is a screen shot of my drill through when I drill down on the cell)

There must be a flaw in my logic / general noobness/ nfi , appoligies if I have missed a point along the way that someone has kindly suggested.

Thanks again

Shock

relationaldrillthroughscreen.jpg (63.49 KiB) Viewed 8317 times

[Steve Rowe]

Shock,
One thing to check is that you are calling the drill process you think you are. If you have been through several iterations it's possible that you got your wires crossed somewhere along the line?

Other than a bug I can't think of another logical reason why you would be going wrong like this....
Cheers,

[shockwave]

Thanks Paul,

its working now. There were some other drill processes that appeared to be causing a conflict. Deleted them in the back end and I am able to drill through to the data. Thanks agin for everyone who has helped me, esp cleaning up some of code.

Cheers
Shock

[shockwave]

Guys, another brick wall. Must be the fact that its Friday afternoon... Doing some testing and have logged in as client but I donot have the ability to drill through to transactional level. I have enable security to Cube and dimensions as well as element level. I have also enabled security for }CubeDrillString. I have only enable it for "Read" is there a security dimension that I might be missing? Maybe something else I might be missing?

Thanks again

Shock

[Steve Rowe]

If you can naivigate to the same place as the end-user as you can as the admin and have enabled read access for the user on the drill process then the only other thing that I can think of is that you may have some security on the Drill Rules cube?

Not sure otherwise