Dimensional Modelling Case Study Part 1 - Age Group (2024-01-12)

SQLServerCentral Newsletter for January 12, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Dimensional Modelling Case Study Part 1 - Age Group]( - [Counting more efficiently]( - [From the SQL Server Central Blogs - Why you should consider Power BI Cleaner]( - [From the SQL Server Central Blogs - Baby Steps and Continuous Improvement]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [Rounding Numerics]( The Voice of the DBA  Continuity Across Restarts There are a lot of database platforms, and [each tries to convince you theirs is better](. As Brent points out in that link, sometimes they just skip comparing themselves to other platforms because it makes them look better. They only look at the platforms they compete well against. For most of us, we often just need basic CRUD operations. I know that most RDBMS platforms would work for us, and sometimes NoSQL ones work as well, though I think that NoSQL isn't necessarily better for many applications (maybe most). You may feel differently, but that's my view. While I use SQL Server, I think the majority of systems I've managed or built could easily run on MySQL, PostgreSQL, or many other platforms. In any case, we want to store some data, get it back, maybe change it, and sometimes delete it. I think that last operation is fairly rare as most of us use soft deletes and because of this, our databases continue to grow like crazy. There was an interesting post from [Brent on MySQL in AWS Aurora]( where the buffer pool cache can survive a restart of the database engine. This is for the cloud version from AWS, not MySQL in general, but that's fairly cool to me. And scary. If you read more, there are some limitations, and the value of this, to me, is limited. However, it's a neat idea, since I could see some sort of restart of the database from a patch for the engine or even a configuration change. For MySQL, clients can't connect while the buffer pool is initialized, which isn't something that we deal with in SQL Server, but I can see this being useful. Or perhaps this helps if an Aurora node goes down and the next one picks up the database engine with a full buffer pool? Not sure if that actually happens from the docs, but if they kept the buffer pool separate enough or up to date on a second node, that would be great. In the real world, I wonder to what extent we find an empty buffer pool at startup to be a problem. Sure the first few queries are slow, but most of the workload isn't the first few queries. It's all the clients that come in the next 5, 10, 20 minutes. As Brent notes, this might not be something we want in SQL Server, but it is good to see cloud-enabled platforms trying something new. Some of those will turn out to not work very well, but for those that do, I bet we see other platforms start to adopt some of the ideas behind the technology. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Dimensional Modelling Case Study Part 1 - Age Group]( Hang Liu from SQLServerCentral Learn how you can handle dimensional modeling in a data warehouse when your data uses different categorization for ages. [External Article]( [Counting more efficiently]( Additional Articles from SimpleTalk Nearly a decade ago, I wrote a post called “Bad habits : Counting rows the hard way.” In that post, I talked about how we can use SQL Server’s metadata to instantly retrieve the row count for a table. [Blog Post]( From the SQL Server Central Blogs - [Why you should consider Power BI Cleaner]( Joyful Craftsmen from Joyful Craftsmen Blog Introduction You have probably created various auxiliary tables, columns or metrics during report development. Before the final deployment of the report into production, it is necessary to delete these auxiliary... [Blog Post]( From the SQL Server Central Blogs - [Baby Steps and Continuous Improvement]( Kenneth.Fisher from SQLStudies It’s the last Friday of the year and I’ve been struggling to think of a good way to close things ... Continue reading [Big Data Analytics cover]( [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Site Owners from SQLServerCentral Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Rounding Numerics I have this code on SQL Server 2019: SET ARITHABORT ON; GO DECLARE @result DECIMAL(5, 2), @value_1 DECIMAL(5, 4), @value_2 DECIMAL(5, 4); SET @value_1 = 1.1234; SET @value_2 = 1.1234 ; SELECT @result = @value_1 + @value_2; SELECT @result; GO What setting of NUMERIC_ROUNDABORT will return an error from this code? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Adding a PK - I I have this code: CREATE TABLE SalesTracking ( SalesDate DATETIME , SalesPersonID INT , CustomerID INT , PONumber VARCHAR(80) , paid bit ) GO CREATE CLUSTERED INDEX SalesTrackingCDX ON dbo.SalesTracking (SalesDate) GO ALTER TABLE dbo.SalesTracking ADD CONSTRAINT SalesTrackingPK PRIMARY KEY (PONumber) GO What happens when I run this on SQL Server 2022? Answer: The code errors out on the ALTER INDEX statement because of the column setting Explanation: The ALTER INDEX statement errors because the column is nullable. Ref: Create Primary Keys - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [How to optimization of a stored proc]( - Hi All, Need some help on optimizing a stored proc. I see some of the update statements written multiple times. Instead of writing multiple updates , can we re-write in one single statement which might avoid reading same table multiple times. This stored proc is doing a lot of logical reads almost like 76TB of […] SQL Server 2017 - Development [Grouping based on chronological periods]( - Hello, I have a table Customers that contains the current value and another table Customers_History contains the history values. The customers have more that 10 fields AS Bellow a sample of data : CREATE TABLE #CUSTOMERS( ID int primary key identity, First_Name varchar(255), Last_Name varchar(255), Nick_name varchar(255), Email varchar(255), Date_Bithday date, Gender varchar(1), Is_Married int, […] [Null value is eliminated by an aggregate or other SET operation]( - Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?  Thank you SQL Server 2012 - T-SQL [parse JSON formatted text into M3U Playlist]( - sorry.. solved.. [Parse out filed as date time ???]( - Hi  I have a field with a start and end date (sometimes no end date) . How can I parse this out to have two fields start and end dates? Data looks like ~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~ desired out is Start Date 12/14/2019 7:02 AM End Date 12/14/2019 3:16 PM  Thanks  SQL Server 2019 - Administration [SQL 2019 data masking]( - I have setup data masking on several fields and can see that the data is masked using t-sql. I need this masking to follow through to report server. I am not seeing the data masked there. any ideas / suggestions would be greatly appreciated SQL Server 2019 - Development [ReportViewerControl.exportReport opening in new Window/Tab]( - Hello, I am using the ReportViewer.aspx page to display a SSRS Report. When exporting the report to any format(CSV,PDF,Excel, etc.) it uses the ReportViewerControl.exportReport function which has the following code: window.open(this.ExportUrlBase+encodeURIComponent(n),"_blank") What I would like to do is stop a new window/tab from being created, so instead of "_blank", "_self" could be used. Any idea […] Reporting Services [copy or duplicate a folder in SSRS web portal]( - Is there a way to copy a folder in SSRS 2019 server web portal to another folder, I want to make a copy of backup for temporary keep. For example I have a folder called myReports which has 20 reports in it, I want to duplicate the folder and all the reports in it to […] Analysis Services [SSAS tabular account impersonation]( - Hello, I'll try my best to summarize the problem and I hope someone can offer me a hand with this. The Enviroment: 2 Domains (let's call them domain_1 and domain_2) Server1 (it is joined to domain_1) This server has SQL Server and SSAS SSAS has a domain account set up on the service Domains have […] Integration Services [On Error Event handler in For Each Loop Container]( - I have a For Each Loop Container that iterates through a list of servers in my environment. Usually it is used to gather information from each of the servers. Inevitably there are a few errors. But my error handler does not write the name of the Server at which the error occurred. Here is my […] [Visual studio 2019 hangs when open excel data source]( - I had Visual studio 2019 installed on windows 10. I have a SSIS package that has been working when I run it in visual studio. But recently when I open the ssis package, and open the excel data source to edit, it freeze there forever, then visual studio shows busy. I have to hard stop […] SQL Server 2022 - Administration [Scheduling AWS RDS instance resizing]( - I know that it is possible to use the AWS Scheduler to stop and start RDS instances. For my purposes we just want to resize it e.g. 16 to 4 processors, so it can still work in low usage periods but will not cost so much. Has anyone ever done anything like this? We do […] SQL Server 2022 - Development [How to query selected multiple rows into multiple colums]( - How to display datas using query data from multiple specific rows in one table sql into multiple columns? For example, insert red data into column1, insert blue data into column2, insert green data into column3, etc. I've used these code : SELECT um_value FROM wp_um_metadata WHERE um_key='first_name' But the result I only got one column. […] [Column Name Is Different in Power BI Than SQL View]( - Hi all, Using Snowflake. When I Describe my View the first columns shows as Nielsen Market Name (KUSA Adjusted) VARCHAR (10000) I connected to the View from Power BI Desktop (PBID) However, in PBID, the name of the column is showing as Nielsen Market Name (USA Adjusted) - the "K" has been dropped from the […] [Basic query performance]( - Hi, I have this pretty basic query that is starting to perform slowly. It returns over 22m records in about 5 minutes. I know its difficult without table defs but any ideas on what I can try to make this return faster?  SELECT [e].[eligibility_id] FROM [dbo].[eligibility] AS [e] -- Table is updated hourly with […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -