Direct Discovery limitation

Mon, 29 Aug 2016 12:23:11 GMT

Hi,

We have an employee(employee are Sales Rep for the current client) Dimension tagged to our fact which stores Beverage Volumes.

The employee dim is SCD2. Thus by default if we drag employee with Volume in a report we get employee history wise Volume Sell.

Let us look at the following Volume Sale transactions (sample)

Employee	Territory	Volume ('000 Litres)	Date
A1	T1	200	22-Dec-15
A1	T1	100	23-Jan-16
A2	T1	30	26-Feb-16
A2	T1	50	02-Mar-16
A3	T1	250	05-Apr-16

Now the reporting requirement is as follows:-

The Volume Sales should be tagged to the latest employee for entire history of transactions.

This means that if we run the report with Reference Date = 5-Apr-16 or greater then in report we should have only the following record:-

A3 - T1 - 630

However if Reference Date chosen = 19-Mar-16 then

A2 - T1 - 380

If Reference Date = 1-Jan-16 then

A1 - T1 - 200

This means tagging back Volume data to the latest employee snapshot for all possible Reference Dates (full calendar). This should not be stored in DB as then the cardinality of join will increase. Also what I have not mentioned here is Employee Dim has a hierarchy as well hence the total possible combinations, prudently speaking, should not be stored but computed at run-time.

Thus we are planning to have a dynamic SQL query (using Direct Discovery) which accepts the Reference Date (chosen in the report) and retrieve the employee dimension tagging of Volume accordingly.

We have tried using the Direct Discovery feature (dynamic SQL) to achieve this but are unable to pass the date paramater in the WHERE clause of the same. Any pointers will be helpful.

"...WHERE START_DATE = $(vStartDate)"

where vStartDate had the expression = GetFieldSelections(PromptFieldName).

topic Direct Discovery limitation in QlikView

Direct Discovery limitation