topic Direct Discovery of AWS Redshift - QS Application Filtering Slow in Connectivity & Data Prep

Direct Discovery of AWS Redshift - QS Application Filtering Slow

Anonymous — Thu, 22 Mar 2018 17:32:47 GMT

Hi All,

I am currently querying the following table of approx. 200m records in redshift from an EC2 instance within the same region, but filtering on the charts is returning really slow. Performance on the ec2 instance and redshift cluster is good, but interestingly I cannot see the queries triggering on the AWS Redshift console when I filter charts.

Filtering on the charts is really slow, with them rendering after 1 minute or more.

Does anyone know why this might be?

Redshift Table:

The AWS ec2 instance spec:

m4.16xlarge

64 vCPU

256 Gib Memory

The AWS Redshift cluster:

The data warehouse node type of the nodes in this cluster.	dc2.large
CPUNumber of EC2 Compute Units per node.	7 EC2 Compute Units (2 virtual cores) per node
MemoryMemory per node.	15.25 GiB per node
StorageDisk Storage of the Node Type.	160GB SSD storage per node

The DIRECT QUERY code from my Qlik Sense application:

LIB CONNECT TO 'Amazon Redshift ODBC DSN 2 (qlik-sense_administrator)';

direct QUERY
DIMENSION
"market_type" as MARKET_TYPE,
    "gps_indicator" as GPS_INDICATOR,
    "rapid_replenishment_indicator" as RAPID_REPLENISHMENT_INDICATOR,
    "%key_product" as %Key_Product,
    isbn as ISBN,
    state as State,
    company as Company,
    "transaction date" as "Transaction Date"
MEASURE
    "total sales" as "Total Sales",
    "net sales" as "Net Sales",
    "units sold" as "Units Sold",
    "vendor_receipts" as VENDOR_RECEIPTS,
    "vendor_returns" as VENDOR_RETURNS,
    "customer_returns" as CUSTOMER_RETURNS
FROM publytics."public"."sales_str2";

The Data Model (ONLY THE FACT TABLE IS NOT IN-MEMORY AND QUERIED FROM REDSHIFT):

Any suggestions welcome,

Thanks

Re: Direct Discovery of AWS Redshift - QS Application Filtering Slow

karabothari — Thu, 07 May 2020 10:42:51 GMT

Hi Cam1988,

I am also having the exact same issues.
Did you manage to resolve this issue or perhaps share your workarounds that you might have done to improve performance.

Thank you,

Re: Direct Discovery of AWS Redshift - QS Application Filtering Slow

BITechie — Tue, 08 Sep 2020 08:22:12 GMT

are you able to improvise performance?

Re: Direct Discovery of AWS Redshift - QS Application Filtering Slow

karabothari — Tue, 08 Sep 2020 08:49:02 GMT

@BITechie Unfortunately we couldnt, we ended up not going the Direct Discovery route.

We ended up using Dynamic Views and ODAG(with the detail app still pointing to redshift in case a user requires a more detailed selection) for our requirements.
we also used a tool called Dremio as a semantic layer for our QVD generation( This improved perfomance significantly as opposed to generating the QVD's directly from the huge redshift table)