Free AI web copilot to create summaries, insights and extended knowledge, download it at here

1630

Abstract

  <div><p>awstip.com</p></div>
          </div>
          <div>
            <div style="background-image: url(https://miro.readmedium.com/v2/resize:fit:320/1*UWpMDfPTKxDMR-Wk_Qyfpg.jpeg)"></div>
          </div>
        </div>
      </a>
    </div><p id="ae89">With multidimensional analytics, you need to create complex processes and queries to aggregate key business facts, such as revenue and spend, and analyze them against multiple dimensions of your business metrics, such as product category, geography, and time. With a single SQL statement that leverages ROLLUP, CUBE, and GROUPING SETS in the GROUP BY clause, you can now leverage these same capabilities, making it easier to perform analytics with Amazon Redshift [1][2].</p><p id="eec0">To use the cube function, for example, you can use the following SQL blue print below and also look into the connected source (official <a href="https://aws.amazon.com/blogs/big-data/simplify-online-analytical-processing-olap-queries-in-amazon-redshift-using-new-sql-constructs-such-as-rollup-cube-and-grouping-sets/">blog</a> from AWS) to get deeper knowledge [3]:</p><div id="73ac"><pre><span class="hljs-keyword">SELECT</span> region_nm, nation_nm, <span class="hljs-built_in">sum</span>(acct_balance) <span class="hljs-keyword">as</span> total_balance

FROM supp_sample WHERE region_nm in (‘AFRICA’,’AMERICA’,’ASIA’) GROUP BY CUBE(region_nm,

Options

nation_nm) ORDER BY region_nm, nation_nm;</pre></div>Currently however, AWS offers these features only in all commercial and AWS GovCloud (US) regions where Amazon Redshift is available. The rest of us has to wait till it is also launched for other regions [1].Amazon should not only make already migrated users and companies happy, but also aim to get previously skeptical customers to migrate their legacy systems to the AWS Cloud. Classic Data Warehouses are often built on the basis of cubes, and the “flattening” of such cubes into nested datatypes, for example, has been costly up to now. Now, such structures can be migrated more easily.<h2 id="d704">Sources and Further Readings</h2>[1] Amazon, <a href="https://aws.amazon.com/about-aws/whats-new/2023/02/amazon-redshift-rollup-cube-grouping-sets-group-by-clause/">Amazon Redshift announces general availability of ROLLUP, CUBE, and GROUPING SETS in GROUP BY clause</a> (2023)[2] eduCBA, <a href="https://www.educba.com/cube-in-sql/#:~:text=SQL%20CUBE%20is%20a%20data,sets%20while%20using%20just%20a">Complete Guide to CUBE in SQL with Examples — eduCBA</a> (2023)[3] AWS, <a href="https://aws.amazon.com/blogs/big-data/simplify-online-analytical-processing-olap-queries-in-amazon-redshift-using-new-sql-constructs-such-as-rollup-cube-and-grouping-sets/">Simplify Online Analytical Processing (OLAP) queries in Amazon Redshift using new SQL constructs such as ROLLUP, CUBE, and GROUPING SETS </a>(2023)</article></body>

How Amazon eases the Migration of Legacy Data Warehouses

Amazon rolls out Cubes and other new SQL Functions for Redshift

How you can now use ROLLUP, CUBE, and GROUPING SETS in GROUP BY clause

Modern Data Warehouses such as Snowflake, Google BigQuery and AWS Redshift are characterized by the fact that they combine SQL and NoSQL and are column based. With the new upgrade of AWS that cubes are now also possible, these modern Data Warehouses take over functions that are often not available yet or had to be mapped differently.

Amazon has just announced that Redshift beside the new CUBE function now also supports new SQL functionalities including ROLLUP, and GROUPING SET, to simplify building multi-dimensional analytics applications [1].

Amazon launches Zero ETL for Redshift

How the new Approach now also reaches AWS Cloud

awstip.com

With multidimensional analytics, you need to create complex processes and queries to aggregate key business facts, such as revenue and spend, and analyze them against multiple dimensions of your business metrics, such as product category, geography, and time. With a single SQL statement that leverages ROLLUP, CUBE, and GROUPING SETS in the GROUP BY clause, you can now leverage these same capabilities, making it easier to perform analytics with Amazon Redshift [1][2].

To use the cube function, for example, you can use the following SQL blue print below and also look into the connected source (official blog from AWS) to get deeper knowledge [3]:

SELECT region_nm, nation_nm, sum(acct_balance) as total_balance 
FROM supp_sample WHERE region_nm in (‘AFRICA’,’AMERICA’,’ASIA’) 
GROUP BY CUBE(region_nm, nation_nm) ORDER BY region_nm, nation_nm;

Currently however, AWS offers these features only in all commercial and AWS GovCloud (US) regions where Amazon Redshift is available. The rest of us has to wait till it is also launched for other regions [1].

Amazon should not only make already migrated users and companies happy, but also aim to get previously skeptical customers to migrate their legacy systems to the AWS Cloud. Classic Data Warehouses are often built on the basis of cubes, and the “flattening” of such cubes into nested datatypes, for example, has been costly up to now. Now, such structures can be migrated more easily.

Sources and Further Readings

[1] Amazon, Amazon Redshift announces general availability of ROLLUP, CUBE, and GROUPING SETS in GROUP BY clause (2023)

[2] eduCBA, Complete Guide to CUBE in SQL with Examples — eduCBA (2023)

[3] AWS, Simplify Online Analytical Processing (OLAP) queries in Amazon Redshift using new SQL constructs such as ROLLUP, CUBE, and GROUPING SETS (2023)