0

Top 10 By Measure

Hi

I am trying to resolve the following use case:

  • I have a table in my DW that contains the a list of tables, their size in GB, dates and schema.
  • I need to create a Discover that will show me the top 10 tables by size (in descending order) filtered by date and schema

I cannot get this to work.  The closest I seem to get is a list of the top 10 tables by name (not size).

 

Can anybody advise me on the steps to resolve this issue please.

 

Thanks

Len

3 replies

null
    • alex_scott
    • 2 mths ago
    • Reported - view

    Hi Len, here is a similar example of qty sold by part number. The filter wizard (filter options on the toolbar) will help with this use case. You can define as a fixed Top 10 or use a parameter to allow the user to define the Top X. More information can be found in the help files https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Discover/PRO/Query%20Functions/Filter.htm?Highlight=filter%20wizard

    • Len_Stockdale
    • 1 mth ago
    • Reported - view

    Thanks,

    I tired again using your suggestions and have the same problem as before.   Looking at what is sent to our Netezza server (via Direct query), the SQL looks wrong (see below). Instead of getting a top 10 list by size it seems to be trying to get a list of tables.  The tables in the SQL list are also not the expected ones.

    SELECT SUM("x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_SIZE") "xSUMmzrVNETEZZATABLEula4NGLAOm", "x_V_NETEZZA_TABLE_STATISTICS_"."LAST_UPDATED_DATE" "xVNETEZZATABLESTATISWVTh2jnIOY", "x_V_NETEZZA_TABLE_STATISTICS_"."SCHEMA" "xVNETEZZATABLESTATISjAyjtku8Na", "x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME" "xVNETEZZATABLESTATISQ3E5kePiMz"

    19.

    FROM "REPORTING"."V_NETEZZA_TABLE_STATISTICS" "x_V_NETEZZA_TABLE_STATISTICS_"

    20.

    WHERE ((( ("x_V_NETEZZA_TABLE_STATISTICS_"."LAST_UPDATED_DATE" <= timestamp '2024-09-22 23:59:59') AND ("x_V_NETEZZA_TABLE_STATISTICS_"."LAST_UPDATED_DATE" >= timestamp '2024-09-22 00:00:00')))) AND ("x_V_NETEZZA_TABLE_STATISTICS_"."SCHEMA" IN ('REPORTING')) AND ("x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME" IN ('RELEX_SCM_FORECASTS_DATA', 'INSTDW_EPOS_SALES', 'FACT_PURCHASE_ORDER_LINE_DETAILS', 'FACT_SALES_DATE', 'FACT_NORMAL_SALES_TRANSACTION_LINE', 'RMS_FILE_SURVEYRESPONSE_DETAILS_DATA', 'REX_FILE_PRODUCTLOCATIONPROMOTION', 'FACT_STORE_PRODUCT_DAILY_FORECAST', 'FACT_RANGE_AVAILABILITITY_DATE', 'ABI_ABLIVDTA_POSOLGP'))

    21.

    GROUP BY "x_V_NETEZZA_TABLE_STATISTICS_"."LAST_UPDATED_DATE", "x_V_NETEZZA_TABLE_STATISTICS_"."SCHEMA", "x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME"

    22.

    LIMIT 10000001

     

    I checked an earlier test today and noticed this SQL was also being run.

    SELECT SUM("x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_SIZE") "xSUMmzrVNETEZZATABLEula4NGLAOm", "x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME" "xVNETEZZATABLESTATISQ3E5kePiMz"

    FROM "REPORTING"."V_NETEZZA_TABLE_STATISTICS" "x_V_NETEZZA_TABLE_STATISTICS_"

    GROUP BY "x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME"

    ORDER BY SUM("x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_SIZE") DESC NULLS FIRST, "x_V_NETEZZA_TABLE_STATISTICS_"."TABLE_NAME" ASC

    LIMIT 10

     

    I am making a big assumption that the result of this SQL is being passed to the top SQL. 

    I will keep trying to solve this but any help would be appreciated.

     

    Len

    • Len_Stockdale
    • 1 mth ago
    • Reported - view

    Hi

     

    I think I have solved the issue. I had not selected the filter and sort for all the dimensions. As soon as I did that I started getting the results I expected.

     

    Thanks

    Len

Login to reply

Content aside

  • Status Answered
  • 1 mth agoLast active
  • 3Replies
  • 38Views
  • 2 Following