Dimensional Modeling Case Study Part 2 - Days Dimension (2024-02-07)

SQLServerCentral Newsletter for February 7, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Dimensional Modeling Case Study Part 2 - Days Dimension]( - [Query Exercise: Find the Best Time for Maintenance]( - [From the SQL Server Central Blogs - How to Call REST API using VS Code]( - [From the SQL Server Central Blogs - Your gradient fill bar charts in Power BI have poor color contrast, but you can fix them]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Building a BacPac]( The Voice of the DBA  Ransomware vs. AI Ransomware has been a growing and shrinking problem in the modern world. Every time I think that some new defenses and protections are preventing ransomware from being a problem, I see another issue. Recently, I saw [Subway got hit with]( with an attack and a few friends have recently noted their companies were restoring systems after a portion of their network was locked down. With the advent of Rasnsomware-as-a-service, where criminals deploy software and then sell access to others, better detection and protection become more important. As with any software, criminal human operators will use the ransomware software in different ways. That means that we don't necessarily have a simple threat that can be easily programmed against with anti-virus technology. Microsoft has been [using AI technology to help them]( track and combat ransomware campaigns. Since there are similarities between how ransomware is used by different individuals and how it appears in systems, AI technology can be helpful here. There aren't the same simple signatures on files that we've seen in the past with viruses, but rather more complex patterns. Humans might discover how ransomware appears in their environment with lots of knowledge on what their network ought to look like, but this pattern matching across many different networks and organizations is something that AI/ML might do quicker and at scale. Once successful, ransomware can be hard to recover from, so early detection is important. In the article, it seems that Microsoft is capturing lots of traffic and analyzing it for patterns, with multiple types of anomalous activity, and then aggregating this across devices to guess whether this is an attack or not. In some of their testing, they find the ability to stop an attack with only a few percent of assets getting encrypted. That's not perfect, but better than finding 90% of your nodes are encrypted over morning coffee. I suspect this is just the latest escalation in cyber attacks and defenses. I'm sure that hackers will come up with new and novel ways to cause problems, but I do think that this is a place where AI, especially ML, technology can be useful to provide better security. I also think that database technology, especially graph queries, is particularly helpful here. I hope we learn more about how they are building protections as I think this is likely a great data analysis story. Once again, the database is critical to making software better because all of that data has to be stored and queried somewhere. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Dimensional Modeling Case Study Part 2 - Days Dimension]( Hang Liu from SQLServerCentral Learn how you can model days in a dimension that might need to be aggregated in different ways for your data warehouse operations. [External Article]( [Query Exercise: Find the Best Time for Maintenance]( Additional Articles from Brent Ozar Unlimited Blog If we’ve gotta take the database down for maintenance – perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware – when’s the best time to do it? [Blog Post]( From the SQL Server Central Blogs - [How to Call REST API using VS Code]( DataOnWheels from DataOnWheels Sometimes you need a fast way to test your API call and responses, and for that VS code is the perfect tool to do that. For today’s demo, we... [Blog Post]( From the SQL Server Central Blogs - [Your gradient fill bar charts in Power BI have poor color contrast, but you can fix them]( Meagan Longoria from Data Savvy Since conditional formatting was released for Power BI, I have seen countless examples of bar charts that have a gradient color fill. If you aren’t careful about the gradient... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Building a BacPac What option do I use in SqlPackage.exe to create a .bacpac file containing schema and data? 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) Checking Permissions I want to check what permissions I have on a SQL Server 2022 instance and database. Can I do this with fn_my_permissions and get server and database permissions? SELECT * FROM sys.fn_my_permissions (NULL, NULL) AS fmp; Answer: No, this returns server permissions only Explanation: This does return server permissions, but not database permissions. To get those, you must use DATABASE for the second parameter. Ref: sys.fn_my_permissions - [ [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 2016 - Administration [install sp3+]( - I have a SQL Server on version 13.0.5622.0 and need to get it up to date security patch wise. It appears I need to install sp3 and then the hotfix 13.0.7000.253 Azure feature sp3 Then the next three Azure security features 13.0.7016.1 13.0.7024.30 13.0.7029.3 Can someone confirm? [SQL DB 2 TB]( - I have a SQL database of 2 TB running on production. There is a need to refresh this database on Q environment on a daily basis. Cannot implement SQL to SQL replication coz InfoSec has an issue doing so (from prod to non prod)...Backup/restore takes incredibly long time. Any thoughts/suggestion/advice? SQL Server 2016 - Development and T-SQL [dont seem to be able to deploy an ssis project from vs 2022 to sql server 2019]( - Hi, from vs 2022 when i choose the deployment wizard for my ssis project i see this.  I am not sure what the path is supposed to be but i entered the folder name you see in the 2nd image after i added that folder to the catalog from ssms. When i deploy i […] [Script to Evaluate Datatype Usage or choice]( - Hey All, I'm trying to find a script that could be run against all tables to evaluate the datatype of each column. The idea would be to get feedback on whether performance could be improved if the column was switched to different datatype. Also, it could find situations in which a large datatype is being […] SQL Server 2019 - Administration [SPNS & Webserver connections.]( - Morning all, Appreciate a pointer on this! I have a web app, connecting via IIS on a webserver to SQL. I'm having authentication issues that I presume are SPN related so will start here. I have kerberos connections in my SQL server already. It's only the webApp I'm having issues with. When setting the SPN […] [SSMS]( - Hi I got a prompt saying that a new version of ssms was available so I downloaded it and installed it. The old version is still installed. I assumed that once I downloaded the new version then the old one would go away but it didn't. Is this expected? Can I remove the previous version […] SQL Server 2019 - Development [How to substract value from its own column in same tabl through condition basis]( - I have a table where i want to subtract the value from same column value through condition basis.. My table is like this i have group the table through item_id through the query SELECT Item_id, sum(qty) FROM [#TempSOR_mb] group by Item_id and get the result as per below : i want to subtract subitem_id 203 […] SQL Azure - Administration [Grant execute to all table types at once]( - In our database we have a lot of table types. I'm looking for a way to grant EXECUTE on all tables types at once. I'd also be interested in granting it to all table types in one particular schema. So far, everything I can find gives the syntax for granting execute to one table type […] Reporting Services [Connect SSRS with Microsoft fabric lakehouse]( - how to connect SSRS with Microsoft fabric lakehouse? Reporting Services 2008 Development [Teams meeting Required to explain a list that has a table embedded in it.]( - I was told to produce a report broken down into customers with the name and address at top of the letter and a list of invoices and each break down of items listed in the invoice. Looked like a list with a table embedded and the invoice number staggered with line items and cost below. […] Integration Services [Get rid off master key]( - Hello everyone, I'm currently working with Visual Studio 2022 and SQL Server 2019 Enterprise Edition. I am encountering an issue while attempting to deploy SSIS packages to the Integration Catalog, specifically an error related to creating a master key or opening in a new session. I'm seeking advice on how to resolve this problem. None […] [dtexecui.exe not found]( - Hi All, I have VS 2022 installed along with SQL server data tools enabled. I also installed SQL Server integration service project 2022 downloaded and installed. Sql server 2019 enterprise version is already installed. I could not find dtexecui.exe utility tool any where in the machine. What am I missing ? When I opened SQL […] SQL Server 2022 - Administration [DNS resolution for DB listener]( - We have a SQL Server Always On Availability Group (AG) configuration with the following components: 1. Nodes: Node1: ZZZDB1.abc.def.gh (IP: 11.89.12.83) Node2: ZZZDB2.abc.def.gh (IP: 11.89.12.46) 2. Failover Cluster: Failover Cluster Name: ZZZDBCLST IP Addresses: 11.89.12.53 11.89.12.87 3. Always On Database Listener: Listener Name: ZZZDBAG IP Addresses: 11.89.12.54 11.89.12.98 In multi-subnet configuration, where each node and […] SQL Server 2022 - Development [Performance optimization by excluding JOIN with tables only with one record]( - We are adding condition that check of the table variable contains one or less records and at this case switching JOIN to adding conditon to WHERE clause, for example: IF EXISTS(SELECT 1 FROM @TableTypePlayersTypes) BEGIN SELECT @RowCount = COUNT(*) FROM @TableTypePlayersTypes IF @RowCount <=1 BEGIN UPDATE PlayersTypes SET LastModifiedDateTime = @dt WHERE PlayersRestrictionID = (SELECT […] [How to replace a specific part of string contains value in side whole string]( - Good Morning, CREATE TABLE MyTable (ID int, DATA NVARCHAR(MAX)) INSERT INTO MyTable values (1, 'ID:12345, Name: Chris , Tiger, Sal:3000, City: NYC') INSERT INTO MyTable values (2, 'ID:23456, Name: Scott Lowski, Sal:4000, City: BOSTON') INSERT INTO MyTable values (3, 'ID:34567, Name: Mat, Linker Tiger, Sal:1000, City: LONDON') INSERT INTO MyTable values (4, 'ID:45678, Name: Tiger, […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -