0

How to modify the WHERE clause conditions of SELECT statement before sending the query to database

Hi

Assume I have a fact table (FactSales) and two dimension tables (DimDate and DimProduct) setup in a star configuration with following relationships

FactSales.Date = DIMDate.Date

FACTSales.ProductID = DIMProduct.ProductID and FACTSales.ProductName = DIMProduct.ProductName

 

 

Please let me know whether the following requirement can be implemented. If so please help me to get it done.

When the user runs the workflow/report , I want to pass a date and product name(s) I am interested in so the result set only gives what is matching them.

So the query going to be similar to

Select * from FACTSales Where Date=<date selected> and ProductName in (<Product Names selected>)

** I do not want to bring all  data from FACTSales and filter within the results.

** if user wants to get data for a different date and a different set of products then he has to rerun the workflow/report and answer the date and product prompts first. Then a new WHERE clause will be applied to SELECT statement and bring data from database.

Thanks

3 replies

null
    • samuel_alma
    • 7 mths ago
    • Reported - view

    First, from your diagram, you only need to join on ProductID to Fact. The ProductName to fact is redundant (and technically wrong).

    Next, build your semantic model as is, and expose the date attribute from DimDate; ProductName from DimProduct; and the QTY and SALES measures from fact. Hopefully you have some other columns to use as well so you can build richer analyses.

    Open the model in Discover and build the report you want. This includes adding the measures to the report and other attributes (columns).

    Then put the date and productname attributes into the "Filter" drop zone. As you make different filter selections from the date and product slicers (or any other adjustments), the query engine will automatically build and issue the select queries you are after.

    The query engine executes in the source database with the filters, so it NEVER returns all the fact data. It will only any pass back the final result.

    • vinay_valeti
    • 7 mths ago
    • Reported - view

    I am not sure if we have a prompt option in pyramid, 

    Did you try this option

    1. build a direct query model with the tables that you are interested in and define joins in the model

    2. Create a discover with the date and product filter. you can make the date filter single select filter to avoid multiple value selection and make product filter multi select filter

    3. This should fire a query to the database when ever you change the filter options

      • samuel_alma
      • 7 mths ago
      • Reported - view

      Slicer Prompts can be enabled with 'pre-query slicers'. I think this is only in 2023.

Login to reply

Content aside

  • Status Answered
  • 7 mths agoLast active
  • 3Replies
  • 52Views
  • 3 Following