April 19, 2018 at 9:03 pm
I am working as a Database Tester for quite some time. But I never comes across GROUPING SETS in my projects.
What is the significance of GROUPING SETS and when it is used real time in Data-warehouse? Can anyone kindly explain it with example?
Saravanan
April 20, 2018 at 7:39 am
saravanatn - Thursday, April 19, 2018 9:03 PMI am working as a Database Tester for quite some time. But I never comes across GROUPING SETS in my projects.What is the significance of GROUPING SETS and when it is used real time in Data-warehouse? Can anyone kindly explain it with example?
In plain English, it's used for making sub-totals and totals for reporting purposes. It's similar to WITH ROLLUP and WITH CUBE for GROUP BYs but has much more finite control.
A lot more information is available on Google. Here's the link for such a lookup.
https://www.google.com/search?q=grouping+sets+in+sql+server&oq=grouping+sets
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 9:08 am
Jeff Moden - Friday, April 20, 2018 7:39 AMsaravanatn - Thursday, April 19, 2018 9:03 PMI am working as a Database Tester for quite some time. But I never comes across GROUPING SETS in my projects.What is the significance of GROUPING SETS and when it is used real time in Data-warehouse? Can anyone kindly explain it with example?
In plain English, it's used for making sub-totals and totals for reporting purposes. It's similar to WITH ROLLUP and WITH CUBE for GROUP BYs but has much more finite control.
A lot more information is available on Google. Here's the link for such a lookup.
https://www.google.com/search?q=grouping+sets+in+sql+server&oq=grouping+sets
Thanks Jeff and I agree with you . But I googled and have understanding on how Grouping Sets works and I thought of understands under which circumstances it need to be used. I got the answer from you that it was for Reporting purposes and Analytics purposes.
Saravanan
April 20, 2018 at 9:24 am
saravanatn - Friday, April 20, 2018 9:08 AMJeff Moden - Friday, April 20, 2018 7:39 AMsaravanatn - Thursday, April 19, 2018 9:03 PMI am working as a Database Tester for quite some time. But I never comes across GROUPING SETS in my projects.What is the significance of GROUPING SETS and when it is used real time in Data-warehouse? Can anyone kindly explain it with example?
In plain English, it's used for making sub-totals and totals for reporting purposes. It's similar to WITH ROLLUP and WITH CUBE for GROUP BYs but has much more finite control.
A lot more information is available on Google. Here's the link for such a lookup.
https://www.google.com/search?q=grouping+sets+in+sql+server&oq=grouping+setsThanks Jeff and I agree with you . But I googled and have understanding on how Grouping Sets works and I thought of understands under which circumstances it need to be used. I got the answer from you that it was for Reporting purposes and Analytics purposes.
That's kind of what data warehouses are about. Are you all set or are you looking for some other possibility.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 11:46 am
Jeff Moden - Friday, April 20, 2018 9:24 AMsaravanatn - Friday, April 20, 2018 9:08 AMJeff Moden - Friday, April 20, 2018 7:39 AMsaravanatn - Thursday, April 19, 2018 9:03 PMI am working as a Database Tester for quite some time. But I never comes across GROUPING SETS in my projects.What is the significance of GROUPING SETS and when it is used real time in Data-warehouse? Can anyone kindly explain it with example?
In plain English, it's used for making sub-totals and totals for reporting purposes. It's similar to WITH ROLLUP and WITH CUBE for GROUP BYs but has much more finite control.
A lot more information is available on Google. Here's the link for such a lookup.
https://www.google.com/search?q=grouping+sets+in+sql+server&oq=grouping+setsThanks Jeff and I agree with you . But I googled and have understanding on how Grouping Sets works and I thought of understands under which circumstances it need to be used. I got the answer from you that it was for Reporting purposes and Analytics purposes.
That's kind of what data warehouses are about. Are you all set or are you looking for some other possibility.
I am going through links which you provided. I will get back to you if I requires any additional details or information .
Understanding of cubes :
The OLAP cubes used by Commerce Server are created during the unpacking process.
These cubes are populated and processed for data retrieval when the Report preparation data transformation services (DTS) task is run.
Storing data in cubes increases the speed of data retrieval when you run analysis reports using the SQL Server Reporting Services.
Source:
https://msdn.microsoft.com/en-us/library/bb219339(v=cs.70).aspx
Understanding of Grouping Sets:
In T-SQL, you summarize data by using the GROUP BY clause within an aggregate query. This clause creates groupings which are defined by a set of expressions. One row per unique combination of the expressions in the GROUP BY clause is returned, and aggregate functions such as COUNT or SUM may be used on any columns in the query. However, if you want to group the data by multiple combinations of group by expressions, you may take one of two approaches. The first approach is to create one grouped query per combination of expressions and merge the results using the UNION ALLoperator. The other approach is to use the GROUPING SETS operator along with the GROUP BY clause and define each grouping set within a single query.
Source:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/summarizing-data-using-grouping-sets-operator/
Cube & Rollup:
Practical Example which I was looking for mentioned below:
Imagine you are responsible for reporting on a telephone switch that has two million or so calls a day. If you retain all these calls on your OLTP server, you are soon going to find the SQL Server labouring over usage reports. You have to retain the original call information for a statutory time period, but you determine from the business that they are, at most, only interested in the number of calls in a minute. Then you have reduced your storage requirement on the OLTP server to 1.4% of what it was, and the call records can be archived off to another SQL Server for ad-hoc queries and customer statements. That’s likely to be a saving worth making. The CUBE and ROLLUP clauses allow you to even store the row totals, column totals and grand totals without having to do a table, or clustered index, scan of the summary table.
Saravanan
April 20, 2018 at 11:54 am
Key point here... don't confuse OLAP Cubes with the WITH CUBE option of GROUP BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 11:50 pm
Jeff Moden - Friday, April 20, 2018 11:54 AMKey point here... don't confuse OLAP Cubes with the WITH CUBE option of GROUP BY.
Thanks Jeff for pointing out the difference.
Saravanan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply