0 Replies Latest reply: Aug 29, 2016 8:23 AM by Palash Mitra RSS

    Direct Discovery limitation

    Palash Mitra

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