Tracking spreadsheet retrievals

Tracking spreadsheet retrievals

Feiga
Feiga

December 20th, 2011, 7:57 pm #1

For redesign purposes, I am trying to determine which dimensions in an ASO read-only cube are actually being used below the top level. The data is confidential. I can ask users to send me blanked out copies of their retrieval sheets, but then I can't rely on everyone co-operating and may wind up eliminating a dimension the most important users need.

I've investigated query tracking but, based on my reading, it doesn't provide the information I need for ASO in a useable form.

Does anyone know of a way of getting this information without relying on users?

Quote
Share

Joined: November 15th, 2007, 1:21 pm

December 20th, 2011, 8:01 pm #2

I believe that Accelatis is working on a tool that intercepts Smart View requests at the APS, and keeps a record of the metadata. Pretty much exactly what you're looking for.

Query tracking should work, is the XML output the problem?

Quote
Like
Share

Fay
Fay

December 20th, 2011, 10:13 pm #3

Thanks for your response Jeff.

The users here essentially just use the EXCEL add-in. Would Accelatis work with that. Of course the organization isn't too keen on 3rd party software either.

I think XML would not be an issue if it can be read with a text editor. But from what I've read Query tracking doesn't provide a metadata log for ASO applications, only BSO. My understanding was that for ASO query tracking worked only as a way of optimizing aggregations but that no useable log was provided.

Is that correct?


Quote
Share

Alan Hutchins
Alan Hutchins

December 22nd, 2011, 8:24 am #4

For redesign purposes, I am trying to determine which dimensions in an ASO read-only cube are actually being used below the top level. The data is confidential. I can ask users to send me blanked out copies of their retrieval sheets, but then I can't rely on everyone co-operating and may wind up eliminating a dimension the most important users need.

I've investigated query tracking but, based on my reading, it doesn't provide the information I need for ASO in a useable form.

Does anyone know of a way of getting this information without relying on users?
First off I have never used ASO, so my apologies if this does not help you.

If it were BSO, then I would write 2 macros :

Macro 1 - set all settings to standard, but set Navigate without data to on.
Macro 2 - revert all settings to standard

2 retrieve sheets:

Retrieve 1
Retrieve 2 - just top level dimensions only.

Ask users to run Macro 1, and then do one of their normal retrieves in sheet Retrieve 1, and disconnect. Then ask them to run Macro 2 and retrieve sheet "Retrieve2", and disconnect. Then send the workbook to you for evaluation.

That way you can see what they are looking at without the confidential data and Retrieve 2 should be just meaningless numbers.

I would also send out a note stating that you are asking for their assistance in determining whar gets used, as you want to review the performance of the system.

You never know, they might co-operate.

HTH
Quote
Share

Fay
Fay

December 22nd, 2011, 5:39 pm #5

Thanks for your suggestion Alan. We are planning to do something like what you indicated if there is no way to get this information from a log.

You know users...sigh! Sometimes the most important ones, the one who will be most upset if a dimension they need gets removed, are too busy to respond.
Quote
Share

Joined: November 15th, 2007, 1:21 pm

December 23rd, 2011, 3:15 pm #6

Thanks for your response Jeff.

The users here essentially just use the EXCEL add-in. Would Accelatis work with that. Of course the organization isn't too keen on 3rd party software either.

I think XML would not be an issue if it can be read with a text editor. But from what I've read Query tracking doesn't provide a metadata log for ASO applications, only BSO. My understanding was that for ASO query tracking worked only as a way of optimizing aggregations but that no useable log was provided.

Is that correct?

I think you're right about query tracking not working with ASO, I haven't used it since it was introduced in "7X".

Since the Excel AddIn doesn't use the APS, my Accelatis suggestion won't help you either.

Unless you want to look into intercepting and interpreting network traffic (way out of my knowledge zone), I think you're stuck with the unreliable approach of users forwarding metadata to you.

Good luck!
Quote
Like
Share


Confirmation of reply: