Audit Sample

RosyCross

My OBIEE version is 12.2.1

My underlying database is Oracle 12c

There is an invoice analysis on a dashboard page that the auditors use to export data to a middleware solution for data sampling.

This works - but the auditors have asked if in addition to the analysis could do this directly, without using the middleware.

i.e. to add a parameter that is a free text percentage value, such that the report would give them a random X% slice of the data for review.

Say the report returns 1000 records with just the base filters on it, and they have also put 10 in the percentage box, is it possible for the report to give them a random set of 100 records?

Is this possible, I do not have the first clue how to impose this kind of sampling in OBIEE?

Also would monetary unit sampling be possible?

Here they would put a monetary amount in a parameter box and it would look down a report pre-ordered by amount and every invoice that was cumulatively greater than that amount would be sampled.

So as before but the invoices in the analysis are presorted ascending by value, with the monetary unit 2000 being entered.

Invoice 100

Invoice 150 (total 250)

Invoice 500 (total 750)

Invoice 800 (total 1550)

Invoice 900 (total > 2000 so this invoice is sampled)

Invoice 1200 (total 1200)

Invoice 1300 (total > 2000 so this invoice is sampled)

Robert Angel

Before Christian says it, this is OBIEE, OBIEE = Business Intelligence, OBIEE = Data Extract and Sampling Tool

But if you must hammer a screw in with the blunt end of a buzz saw then you could look into using evaluate to call dbms_random to generate random numbers, which you would order your analysis by, and then take the top-n values based on your % times a row count.

This should work, but don't take it as something I would recommend.

Most auditors don't trust the source tool to do this, they prefer things like 'Idea' which are 'objective' (sic).

On the monetary unit I know what you are asking but not sure how could achieve the reset functionality on the breach of the 'materiality' value - you might have to resort to an opaque view or materialized view to achieve this.

Or you could look into apex, to call a database procedure to refresh a data set in the ways that you describe.

Any other takers?

Robert Angel

One other idea that came to me, you could mis-use row level security imposed on a copy of your current subject area to achieve your monetary unit sample, but depending on your sample sizes this may run like a dog.

RosyCross

Again thanks for your suggestions, I will trial them out and see how I get on.

[Deleted User]

I don't even have to post here anymore. My work is done.

/me flies away

Robert Angel

Seriously though, I could never hope to fill your shoes....

RosyCross

Why, do you have really big feet?!

[Deleted User]

Maybe his joke got lost in translation, but...he would have small ones. Big ones would "over-fill" them.

Is it Friday already?

Robert Angel

English expression - 'Big Shoes to Fill' - it means you are a hard man to replace.

RosyCross

No I understood, I was teasing too, we say the size of a man's feet indicates the size of a man's.....

socks!

[Deleted User]

I know. I was being snarky and a spelling/grammar/precision n**i