Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Direct Discovery limitation

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)

EmployeeTerritoryVolume ('000 Litres)Date
A1T1

200

22-Dec-15

A1T1

100

23-Jan-16

A2T1

30

26-Feb-16

A2T1

50

02-Mar-16

A3T1

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).

0 Replies