2-Step Drill Design
Posted: Wed Feb 08, 2012 2:55 am
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!
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 datae.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!