2-Step Drill Design

[fleaster]

Hi all,
just thought I'd run this past the brains ...we have a core Financials cube from which users normally drill through an ODBC link to retrieve transactions data
e.g. drill from an Expense Reporting line to the Debit/Credit entries that make up the balance

...usually however, there is a second step required
ie once the user has highlighted the entry they are interested in, they need may isolate the batch number of the transaction in order to find out what the "other side" of the entry was or what other accts were affected in the batch. This step however, may involve going to run a separate SQL query on the underlying transactions database.

My thought to automate this a bit more is:

1/ user drills from reporting line to list of transactions - this creates a temporary cube (perhaps incorporating userid somewhere in the view?) with the required data (automatically populated from ODBC)

2/ from this temp cube, the user can then drill again on various dimensions to the underlying ODBC database via relational link e.g. drill on the batch number to get the full list of transactions contained in a batch for that day etc

...Any thoughts on this process as to whether it is workable or could be done better?

thanks!

[lotsaram]

This sounds like a good solution to me provided the first step of the drill is fast enough from the user point of view. Just thinking aloud but I don't think you would need client int the "transaction drill source" cube as the user is ultimately drilling from a value to a query of the underlying DR/CR transactions. Both the original value and the transactions in the GL don't have anything to do with the user so it doesn't seem relevant to partition the data by user. You could just incrementally write to this background cube without on the fly clearing if your concern was users resetting each other's views and maybe do an overnight cubecleardata to stop the cube getting too big.

[fleaster]

thanks for the feedback

...yes I think the speed of the process is critical... might go do some testing to see if its efficient or not