Drill Thru Cube - DB Transactions - Invoice

[LanceTylor]

Hi All,

I wanted to see whether anyone has established a streamlined process for drilling through to invoices. We are currently on version 10.2.2 FP3, Windows 2008 R2 (moving to Windows 2012 in a month).

Requirement
Drill from cube to transactions to invoice. Transactions are sourced from a database table which contain a url to (scanned) invoices.

Current Process
Within TM1 Web users drill to transaction level from anywhere within the cube (this is restricted through rules to limit transactions to maximum of 5,000 rows). By default, a new tab is opened and the list of transactions is displayed along with URL to the invoice (but they can’t click on the link). In order to get to the invoice users must export to excel, click f2 to convert cells from text to the link and then click on the link (Hyperlink formula is used).

If possible, users would like a better interface and avoid having to export to excel to get to the invoice each time. This is happening during the month-end process which means there could be up to a 100k+ transactions per day (limited to max of 5k each time - Average being 100-200).

The options as I see it (in theory) are as follows:
1. Create a Transactions cube that is populated during the drill thru process and figure out a way to render isolated transactions through an active form over the web. Using an active form we can create a Hyperlink directly to the invoice
2. Find a way to configure the default webpage that is rendered following the execution of a database transactions drill thru process (this is the ideal process)
3. Other options?
4. Use existing process

I wanted to see if anyone else has done anything similar and may have other suggestions?

Appreciate your feedback.

Thanks
Lance

[RJ!]

Are users randomly looking for Invoices while exploring the cube or are they likely to be looking for something specific when they go into the Cube?

If it's something specific, we have something set up where the user will use ISB (InSpreadsheet Browser) to open a preset View and we have a VBA code that will leverage the result of the ISB View to create an SQL Query that fetches the Transaction Drill. In your example you'd just need some VBA in Excel to convert the Text to a hyperlink & your done...

Although maybe over the top in terms of complexity, I'd love to see a REST API solution to your issue though.

[Wim Gielis]

Are users randomly looking for Invoices while exploring the cube or are they likely to be looking for something specific when they go into the Cube?

If it's something specific, we have something set up where the user will use ISB (InSpreadsheet Browser) to open a preset View and we have a VBA code that will leverage the result of the ISB View to create an SQL Query that fetches the Transaction Drill. In your example you'd just need some VBA in Excel to convert the Text to a hyperlink & your done...

Although maybe over the top in terms of complexity, I'd love to see a REST API solution to your issue though.

RJ!

This would be useful in Excel but not really in TM1 Web, no ?

[RJ!]

Realised that after reading the OP again, though he did ask for other options...

[macsir]

I think transaction cube and active form is the way to go. I have used this method to drill down to transaction level data. The drawback of it is that the cube would be huge and for displaying transactions, if the value is same as previous line in one column, TM1 would put empty avoiding duplicate. This is little annoying as it is not a pure exported detailed transaction view for the end user. But the better thing is performance of drilling is not compromised.

[LanceTylor]

Thanks for your responses.

(End) users are currently being encouraged to use TM1 Web to provide the pivoting (adhoc) capability that Perspectives can't easily handle (without going into architect/server explorer). One interface at a time. However, its good to know Perspectives is an alternative, helps build out potential options.

I will likely attempt the transaction cube to active form, its good to know someone has implemented this type of solution. I think if its possible to do using RESTAPI then that would be a great alternative but unfortunately my skills in the area are somewhat limited......its just another skill set a TM1 Architect will have understand and use (Tool belt is getting a full especially as the product continues to evolve).

Thanks again

Lance