Logo_Squid_blanc_600x250_

Redshift: Benchmarking Dense Storage & Dense Compute Clusters

Julien Theulier became an expert on Amazon Redshift. He did a benchmark on Redshift Dense Storage & Dense Compute clusters. Here is the result.

Blog Post Edited : 27/02/2014

Julien Theulier became an expert on Amazon Redshift. He did a benchmark on Redshift dense storage & dense compute clusters. Here is the result.

 

1. What I want to test

Redshift introduced end of january 2014 a new type of clusters based on SSD drives technology. They now offer two distinct configurations: Dense Storage (original one, based on HDD drives) & Dense Compute (based on SSD drives).

MPP architecture allows to have a scalar performance linked to the number of slices. If I take an example of a query running in X seconds on a specific cluster, it should take 2X seconds if the data volume is twice, and it should take X/2 seconds if I increase the number of slices by 2, with respect to the SQL executed.

I would like to know what are the differences between the 2 configurations to help choose between them when designing a solution based on Redshift, taking in account the budget aspect.

 

2. Environment

a. Clusters

For the purpose of this benchmark I will use our current DS cluster (2 DW1.8xlarge) as a reference point and play with 2 distinct DC clusters: 16 DW2.large & 2 DW2.8xlarge.

Table 1

By comparing figures above, I may conclude that, in term of computation capabilities, 2 DW1.8xlarge matches relatively closely one DW2.8xlarge, and having a higher ECU figure for DC cluster should bring an advantage to it even when having the same number of vCPU. The benchmark below will try to validate that. I can also assume that 2 DW2 8xlarge is approx. 16 DW2 large.

b. Data

All clusters will be loaded with the same data (fact & dimensions) using same DDL. I used in this benchmark up to 6 dimensions (from < 10 rows up to 60K rows) & 2 different fact tables (440M rows & 1.35G rows). That still represents a small usage of available storage (600 Gb raw).

c. Test cases

The following test cases will be conducted :

  • Contention: Check the responsiveness of clusters when having multiple queries running in parallel
  • Computation: Compare the “speed” of each clusters for in-database analytics jobs:
    • For building a cube (Group by of dimensions coming from Joins of dimension tables on a fact table) to do drill down analysis;
    • For aggregating metrics (such as avg, sum, count, …) on a temporal dimension to build timelines.

I embedded my test queries as subqueries of a “select count(*) from (…)” statement in order to have the same number of rows (1 in this case) sent back by the database. That way our client (psql) will always retrieve only one row whatever the test case is. Therefore we can assume that the retrieval latency will always be the same.

 

3. Results

The test results are displayed in the line charts below. The Y axis represents the ratio (in %) between the time taken by each test to run, with the time taken by 1 test query to run on the 2 DW1 8xlarge cluster (1 query run on 2 DW1.8xlarge represents 100%).

a. CPU test case: Simple count aggregation query

Test cases consist in running in parallel the same query on different levels of agregation (day, week, month, year, overall) and counting the number of resulting rows. See appendix for the exact queries run.

This is the simplest test case: no join, only one “group by” for one simple metric definition. The dataset is also small enough to reside entirely in memory. It allows us to test CPU performances only (see below).

Raw results (in ms) are:

Table 2

Figures show the number of parallel queries (running at the same time) on the main axis

Table 3

Interestingly this simple test case shows a really similar trend linked to number of vCPUs in clusters (I was expecting an impact from the difference of ECU between the 2 cluster types).

The first slope break occurs after test case 2. This is because union + truncating the date at week, month or year add an extra processing in cases 2, 3 and 4. This can be highligted in the following way:

Table-4-2

Red lines show the overhead when using or not the date truncation function, with the same number of concurrent queries on same data/

I observe also a slope break occurring after test case 4. This is because the query becomes a simple row count on the overall table – no group by is needed. It runs very fast compared to bucketing management.

Note: The minimal amount of storage read in this test case can be estimated in the following way: 4 bytes (date column type in a columnar table) * 440M rows = 168Mb.

I assume the OS can cache easily this amount of data after the 2nd read. The full table (meaning all columns’ storage) is using 150 Gb. See Redshift Columnar Storage for details on how Redshift manage table storage on disks

b. Cube computation

Test case consists in join 5 dimensions on a fact table & group them to build a dimensions cube. I have 2 different test case, the second one involves 309% more rows in the fact table compared to the first case.

Raw results (in ms) are:

Table 5

I start to see a difference between cluster types indeed. Resolving joins usually results in the construction & use of hash tables (most common algorithm with dimension tables joined on a fact table). On large datasets, the database has to spill these tables on disks as they can’t reside in memory allocated for by the database. Having a higher IO throughput will benefit to such operations. I have been able to measure an approximate gain of 30% between 2 DW1 8xlarge & 16 DW2 large for table read operations. It also looks like there is a slight advantage between DW2 large vs DW2 8xlarge but 2 points of measure are not enough to conclude on that.

c. Multiple metrics aggregation with join

This time, the test case consists in aggregating several metrics grouped by period and joining a dimension to the fact table (see SQL in appendix).

Raw results (in ms) are:

Table 6

The shape of performances reflects the previous findings:

  • Date truncation is adding a processing overhead
  • Write/use of hash tables is faster on SSD drives

One interesting thing I can measure here is that a DW2 8xlarge cluster seems 5% slower than an equivalent cluster made of DW2 large nodes. I instantiated a 32 DW2 large cluster to validate this. Running the tests confirmed that 32 DW2 large is 5% faster than 2 DW2 8xlarge.

Because this difference is not visible in the simple scenario, using only CPU resources, this could come from a different SSD hardware configuration or disks specifications between large & 8xlarge. It could also be linked the higher ECU figure on large compare to 8xlarge. Without any details on hardware specification, it’s quite difficult to conclude on that.

d. Miscellaneous: Data Loading Process from S3

When loading data into Redshift using copy command, I observed through the AWS Redshift Console that, for a while, it uses up to almost 3 times more storage than in the final table. I have performed a test on a cluster of 2 DW2 8xlarge, loading 32 files of 4.1Gb each, compressed (gzip) data in S3 into a non compressed table, with options compupdate & statupdate set to off. It took 2h 28m 56.13s to load (that’s really long!), used up to 3.65Tb of storage and resulted in a table of 1.3Tb of uncompressed data. That seems quite long & we can see at least 2 steps from the AWS console (turning COMPUPDATE to on will add an extra step at least):

AWS Console gives the following:

table7

The storage usage goes (approximately) from 0.13Tb to 2Tb (at 13:36) then reach 3.5Tb (at 14:56) & goes down to 1.5Tb at the end of the loading. I believe the gz files are retrieved locally on Redshift hosts to be unzipped before the load occurs on Redshift itself (Inflating files on a Linux box requires around 2Tb of storage). The storage usage & evolution over the time correspond to this scenario. This extra storage usage has to be kept in mind when loading many compressed files in parallel. The drop at the end matches in size the first increase (from 12:33 to 13:36) & may be linked the local files cleaning if this scenario is correct.

According to AWS recommendations, we decided to test 2 more things:

– Load into a compressed table: The first part of the copy process remains the same & the second part tooks approx. 20mns instead of 70 mns but with a 100% CPU utilization instead of 18%. Indeed the compressed table size is around 4 times less than the non compressed table & IO reduction explains mostly the time reduction. The overal load has taken 1 h 41m 34.43s according to the AWS console

That is shown in the figure below:

Table 8

– And also spread data into 64 files (same number than the number of slides in our cluster):’

The overal load has taken 1h 47m 27.48s according to the console. This is quite similar to previous case but the shape of resources consumption is slightly different. I assume this come because the cluster has to retrieve more files from S3. There is no evidence with this test case that having as many files as slices brings a benefits (compared to half files as slices)

Table 9

The copy command is the following:
COPY dw.fact_events
FROM 's3://BUCKET/fact_event_'
credentials 'aws_access_key_id=xxxxxxxx;aws_secret_access_keyxxxxxxxx'
GZIP
REMOVEQUOTES
DELIMITER ','
NULL ''
ESCAPE
BLANKSASNULL
TRIMBLANKS
COMPUPDATE OFF
STATUPDATE OFF;

4. Conclusions

Learnings from this benchmark are the following:

  • SSD seems 30% faster than HDD when database processes large hash joins (IO reads/writes).
  • There is a potential 5% performance drop going from a 32 DW2 large to a 2 DW2 8xlarge.
  • I could not verify, on a simple aggregation case using only CPU power, that the announced ECU ratio between DW1 & DW2 is approx. 0.65 (70 vs 104/112). I observed similar performances between the 2.
  • You can expect a ratio of 4 between uncompressed and compressed data. But loading into compressed tables will use all your CPU
  • There is the same number of vCPU & slices within nodes, multiple concurrent queries share the same CPU resource, there is still a direct contention on that resource.

In short terms, Dense Compute clusters are at least 2 times faster than Dense Storage clusters for a similar budget (Dense Compute is 12.3% cheaper than Dense Storage – 8xlarge cluster size for 3 year reservation). The counterpart results in 6 times less storage for that gain.

From my point of view, Dense Compute clusters bring a real advantage when used by In Database computation for reporting & analytics tools, compared to Dense Storage clusters. The counterpart, storage capacity reduction, is partially saved with compression. Although, 2.5TB per 8xlarge node (around 10TB uncompressed) is still a good amount of data to be managed for reporting.

Based on that, a solution made only with DW2 clusters or few DW1 + many DW2 clusters could be also evaluated. DW1 clusters would be used for regular raw data loadings & long term history retention, also ad-hoc analyses on raw data. Processed & aggregated data would go to DW2 clusters to limit concurrency between batch loading jobs, aggregated data layers refresh, history maintenance on one side and reporting requests made by many end-users on the other side.

The direct contention on CPU may cause a hard time designing a reporting solution for many concurrent users/application, specially if a response time SLA is not an option.

 

Appendix

Example for the full query used for the simple aggregation scenario (CPU based test). Commenting a set of unions allow to build the 5 points of measure : 
select count(*) from (
--Query 1
SELECT
CAST(DATE_TRUNC('day', a1."session_start_date") AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
SUM(1) AS "indicator_alias_6"
FROM
"dw"."session" a1
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
GROUP BY 1,2,3,4
UNION
--Query 2
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(DATE_TRUNC('week', a1."session_start_date") AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
SUM(1) AS "indicator_alias_6"
FROM
"dw"."session" a1
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
GROUP BY 1,2,3,4
UNION
--Query 3
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(DATE_TRUNC('month', a1."session_start_date") AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
SUM(1) AS "indicator_alias_6"
FROM
"dw"."session" a1
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
GROUP BY 1,2,3,4 UNION
--Query 4
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(DATE_TRUNC('year', a1."session_start_date") AS DATE) AS "YY"
,
SUM(1) AS "indicator_alias_6"
FROM
"dw"."session" a1
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
GROUP BY 1,2,3,4
UNION
--Query  5
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
SUM(1) AS "indicator_alias_6"
FROM
"dw"."session" a1
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
) a
;

Query for second scenario (Cube aggregation) is the following:
SELECT
a4."name" AS "Parent Account"
,
a5."name" AS "Account Type"
,
a6."name" AS "Country Name"
,
a2."name" AS "Account Name"
FROM
"dw"."session" a1 --replace “dw”.”session” by “dw”.”displays” for the 2nd test case
LEFT OUTER JOIN "dw"."accounts" a2
LEFT OUTER JOIN "dw"."account_hier" a3
INNER JOIN "dw"."accounts" a4 ON a3."parent_id"=a4."acnt_id" ON (a3."child_id"=a2."acnt_id")
INNER JOIN "dw"."account_types" a5 ON a2."type_id"=a5."type_id"
INNER JOIN "dw"."countries" a6 ON a2."country_id"=a6."country_id" ON a1."acnt_id"=a2."acnt_id"

GROUP BY a4.”name” , a5.”name” , a6.”name” , a2.”name”
) a
;
Example for the full query used for the join + complex aggregation scenario.
Commenting a set of unions allow to build the 5 points of measure :
select count(*) from (
--Query  1
SELECT
CAST(DATE_TRUNC('day', a1."session_start_date") AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
COUNT(*) AS "Count"
,
SUM( CASE  WHEN (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_0"
,
SUM( CASE  WHEN (a2."format_code"='html'  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_1"
,
SUM( CASE  WHEN (a2."format_code" IN ('pdf','ftp')  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_2"
,
SUM( CASE  WHEN (a2."format_desc"='Abstract'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_3"
,
SUM( CASE  WHEN (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ) THEN (1)  ELSE (0)  END ) AS "indicator_alias_4"
FROM
"dw"."displays" a1
INNER JOIN "dw"."format_type" a2 ON (a2."format_desc"=a1."delivery_format")
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
AND
((NOT(UPPER(a1."source_type") IN ('Unknown'))))
GROUP BY 1,2,3,4
UNION
--Query  2
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(DATE_TRUNC('week', a1."session_start_date") AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
COUNT(*) AS "Count"
,
SUM( CASE  WHEN (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_0"
,
SUM( CASE  WHEN (a2."format_code"='html'  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_1"
,
SUM( CASE  WHEN (a2."format_code" IN ('pdf','ftp')  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_2"
,
SUM( CASE  WHEN (a2."format_desc"='Abstract'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_3"
,
SUM( CASE  WHEN (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ) THEN (1)  ELSE (0)  END ) AS "indicator_alias_4"
FROM
"dw"."displays" a1
INNER JOIN "dw"."format_type" a2 ON (a2."format_desc"=a1."delivery_format")
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
AND
((NOT(UPPER(a1."source_type") IN ('Unknown'))))
GROUP BY 1,2,3,4
UNION
--Query  3
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(DATE_TRUNC('month', a1."session_start_date") AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
COUNT(*) AS "Count"
,
SUM( CASE  WHEN (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_0"
,
SUM( CASE  WHEN (a2."format_code"='html'  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_1"
,
SUM( CASE  WHEN (a2."format_code" IN ('pdf','ftp')  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_2"
,
SUM( CASE  WHEN (a2."format_desc"='Abstract'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_3"
,
SUM( CASE  WHEN (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ) THEN (1)  ELSE (0)  END ) AS "indicator_alias_4"
FROM
"dw"."displays" a1
INNER JOIN "dw"."format_type" a2 ON (a2."format_desc"=a1."delivery_format")
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
AND
((NOT(UPPER(a1."source_type") IN ('Unknown'))))
GROUP BY 1,2,3,4
UNION
--Query  4
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(DATE_TRUNC('year', a1."session_start_date") AS DATE) AS "YY"
,
COUNT(*) AS "Count"
,
SUM( CASE  WHEN (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_0"
,
SUM( CASE  WHEN (a2."format_code"='html'  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_1"
,
SUM( CASE  WHEN (a2."format_code" IN ('pdf','ftp')  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_2"
,
SUM( CASE  WHEN (a2."format_desc"='Abstract'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_3"
,
SUM( CASE  WHEN (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ) THEN (1)  ELSE (0)  END ) AS "indicator_alias_4"
FROM
"dw"."displays" a1
INNER JOIN "dw"."format_type" a2 ON (a2."format_desc"=a1."delivery_format")
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
AND
((NOT(UPPER(a1."source_type") IN ('Unknown'))))
GROUP BY 1,2,3,4
UNION -
-Query  5
SELECT
CAST(NULL AS DATE) AS "DD"
,
CAST(NULL AS DATE) AS "WW"
,
CAST(NULL AS DATE) AS "MM"
,
CAST(NULL AS DATE) AS "YY"
,
COUNT(*) AS "Count"
,
SUM( CASE  WHEN (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_0"
,
SUM( CASE  WHEN (a2."format_code"='html'  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_1"
,
SUM( CASE  WHEN (a2."format_code" IN ('pdf','ftp')  AND  (a2."counter_flag"='Y'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ))) THEN (1)  ELSE (0)  END ) AS "indicator_alias_2"
,
SUM( CASE  WHEN (a2."format_desc"='Abstract'  AND  (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END )) THEN (1)  ELSE (0)  END ) AS "indicator_alias_3"
,
SUM( CASE  WHEN (((a1."last_event_seconds") IS NULL)  OR  a1."last_event_seconds">= CASE  WHEN (a1."delivery_format" IN ('Text','Text+Images')) THEN (10)  WHEN (a1."delivery_format" IN ('DocImage','PageImage')) THEN (30)  ELSE (0)  END ) THEN (1)  ELSE (0)  END ) AS "indicator_alias_4"
FROM
"dw"."displays" a1
INNER JOIN "dw"."format_type" a2 ON (a2."format_desc"=a1."delivery_format")
WHERE
((a1."session_start_date">=DATE '2013-02-09'  AND  a1."session_start_date"<=DATE '2013-11-30'))
AND
((NOT(UPPER(a1."source_type") IN ('Unknown'))))
);