How to Optimize Power BI Semantic Model Performance with Vertipaq Analyzer (2024-03-22)

SQLServerCentral Newsletter for March 22, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Optimize Power BI Semantic Model Performance with Vertipaq Analyzer]( - [Database Feature Toggles]( - [From the SQL Server Central Blogs - Friday Flyway Tips – Inserting Column in the Middle of a Table]( - [From the SQL Server Central Blogs - New Pluralsight Course - Certified Kubernetes Administrator - Using kubeadm to Install a Basic Cluster]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [The Most CUs]( The Voice of the DBA  An Experimental Mindset I wrote [a post about Git]( recently since I've found far too many people who didn't know Git well (or at all) and they seemed hesitant to experiment and learn. Is this because of being too busy? Do we have so many people who are working in tech where "it's just a job" and not because they enjoy technology? Or has management in too many places beaten people down to the point they don't want to try things? Perhaps your coworkers are resistant to change (or lazy) and you don't want to discuss new ideas with them. In working with software and databases, I marvel at all the ways in which we can mold and change the system to match the creative ideas we come up with. I'm pleasantly surprised when someone solves the same problem I do, but in a very different way. Even using the same language/tools/frameworks, we can often come up with a variety of solutions that work well. I think we teach each other something when we compare disparate solutions. When I was learning about computers, we were encouraged to experiment. Those who were willing to try things and learn from them were often more excited by the task, and I think these were often those who went on to successful careers. I've found the people who don't like to experiment or try something new, even when there is no risk, are often limited in both their accomplishments and opportunities. I don't expect those who enjoy experimenting with technology to work more than others. I'd prefer that everyone have a balanced life that separates time at work from personal time, and limits the investment in your skills to a reasonable level. Live and enjoy your life outside of technology, but when you do work, make an effort to do your best work, while working to improve what "best" is for you. Try something new this week. Solve a problem, use a new tool, read about new technology, and write a little code. It's good for you. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [How to Optimize Power BI Semantic Model Performance with Vertipaq Analyzer]( Koen Verbeeck from SQLServerCentral Learn how to optimize the size of your Power BI semantic models with the free Vertipaq Analyzer tool. Extra tips & tricks included. [External Article]( [Database Feature Toggles]( Additional Articles from SimpleTalk In software development the concept of feature toggles are used to selectively turn on and off features. They are, for example, used to restrict some newly introduced features to a select group to see how these features work. While this concept has been long used for user-facing application code, it is also a practice that is useful for database code. [Blog Post]( From the SQL Server Central Blogs - [Friday Flyway Tips – Inserting Column in the Middle of a Table]( Steve Jones - SSC Editor from The Voice of the DBA I had a customer question whether Flyway Desktop (FWD) would cause problems if developers were adding columns into the middle of tables. It’s a valid concern, and this post... [Blog Post]( From the SQL Server Central Blogs - [New Pluralsight Course - Certified Kubernetes Administrator - Using kubeadm to Install a Basic Cluster]( aen from Anthony Nocentino Blog We’re kicking off a major refresh of my Certified Kubernetes Administrator series at Pluralsight! The second course “Certified Kubernetes Administrator: Using kubeadm to Install a Basic Cluster” in the updated... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Most CUs Which version of supported SQL Server has the most Cumulative Updates (count) (as of Mar 1, 2024)? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Stewart "Arturius" Campbell) Always On Setup While setting up Always On for SQL server, I write the following script: USE [master] GO CREATE AVAILABILITY GROUP [HR_AG] WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY, BASIC, DB_FAILOVER = ON, DTC_SUPPORT = NONE, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0) FOR DATABASE [HR] REPLICA ON N'DBServ01' WITH (ENDPOINT_URL = N'TCP://DBServ01.xxxxx.mycompany.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)), N'DBServ02' WITH (ENDPOINT_URL = N'TCP://DBServ02.xxxxx.mycompany.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = MANUAL, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)); GO What will the result be if this is executed in an instance of SQL Server 2022? (choose 2) Answer: Succesfully executes in SQL Server Standard, Fais with "'BASIC' failed because it is not supported in the edition of this SQL Server instance" in SQL Server Enterprise Explanation: SQL Server Standard has Basic Always-On Availability groups, which is a watered-down version of Enterprise Edition "Super" Always-On Availability Groups. For this script, the default is "Advanced". For SQL Server Standard we must specifically add "BASIC" in the WITH. Ref: [SQL Server Basic Always On Availability Groups]( [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](. --------------------------------------------------------------- Administration - SQL Server 2014 [Strange Deadlock Graph showing multiple DB but isolated]( - Hi Experts, I am observing strange deadlock behaviour where one deadlock graph have 2 isolated databases involved. Can any experts here shed some light on this?  Regards SQL Server 2019 - Administration [How can we enhance statistics information collection]( - Currently we are collecting database statistics information using select * FROM sys.dm_db_index_physical_stats( 14, NULL, NULL, NULL, 'SAMPLED') For some larger databases this statement takes more than 23 minutes to complete. Is there anything we can do to speed up this process and collect the same data?  [when dc is down can we remove database in dr out of always on secondary replica]( - HI All, primary server is in always on secondary is a replica when primary DC is down due to unexpected power off , can we make the secondary database online by removing them from availability group when dc is down can we remove database in dr out of always on secondary replica thanks naga.rohitkumar [SQL server 2019 alwayson problem]( - Hi, I've SQL server 2019 STD always on set up with 2 nodes and file share witness (located on third server). yesterday we had an network issue that the main switch was down for 15 minutes. after the network is back i see that the whole SQL server alwayson cluster was deleted from the computers […] [Rebuilding/Reorganizing in a very transactional table]( - Hello masters, It's been quite some time since i last posted here but i encountered a really hard opponent. We've got this table, this table has around 50-60m rows of transactions and this is only in one month, we've got this daily process that moves the transactions from the main to the historic one (basically […] [Database Ownership - Does it Matter ?]( - From a practical standpoint, does it matter who is shown as the owner of a database ? What if it's a network user, and their account is deleted when they leave the company? [SSRS 2019 Migration]( - Hello everyone, i have been trying to migrate SSRS 2012 to 2019 using powershell script which i found here [ microsoft/ReportingServicesTools: Reporting Services Powershell Tools (github.com) ] Unfortunately the samples mentioned in the script is not helping as well. Pls find the options i have tried with it & their respective exception Can anyone pls help […] SQL Server 2019 - Development [EXEC (@SQL) AT QASERVERNAME; Fails with read-only database error.]( - All, Need a linked server guru.  Trying the following in a stored procedure: SET @SQL = 'DROP TABLE IF EXISTS DB_NAM.SCHMA_NAME.TBL_NAME'; IF @@SERVERNAME = 'QASERVERNAME' BEGIN; SET @HUBServerName = '[QAHUBSERVERNAME]'; EXEC (@SQL) AT QAHUBSERVERNAME; END; Then I get this: Msg 3906, Level 16, State 2, Line 10 Failed to update database "DB_NAM" because the database […] [ACCIDENTAL DUPLICATE...]( - Accidental Duplicate... my bad... [nested subqueries vs separate queries performance difference]( - Hi everyone I am looking at improving the performance of my queries. There is an approach I am considering but I don't know if it will actually improve things or not. I won't know until I try (which I will) but I am interested in getting some feedback from the community. Query 1 select * […] Reporting Services [pagename property for my tablix doesnt show fields]( - Hi , i went to set page name on a tablix mostly for naming worksheets when report is saved to excel.  From what i can see, ssrs is saying no dataset is attached to the tablix. Which isnt true. Can someone help? at the moment that total line is hidden and i'd have to ask […] [Using Parameter and Booksmarks together?]( - Hello, I have a SSRS 2016 Report with multiple tables on every pages. User should select a particular table and jump to the page of this table. Is it possible to do? I did not find any way to do it. Try: I create a textbox with a Action to Bookmark and the expression of […] SQL Server 2022 - Administration [Log shipping setup failing with error - Access is denied]( - We are setting up a log shipping from the Dev to the Reporting Server. However, the GUI process is failing with the below error. Source VM: XXX-SQL-D Target VM: XXX-RPT-D-DB Error from Log File: Cannot open backup device '\\XXX-SQL-D\i$\MSSQL\Backup\LSTONY\LOG\LSTONY.bak'. Operating system error 5(Access is denied.). RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201) […] SQL Server 2022 - Development [very slow performance using WHERE on Oracle linked server]( - I have a linked server pointing to an Oracle instance using OraOLEDB.Oracle. The table contains about 40K rows. My issue is that while I can get fine performance doing a SELECT or DELETE with no WHERE clause, the moment I add a WHERE it is just horrible. Even selecting a single row takes 20+ minutes. […] [Grouping Count]( - Hi All, I have domain in Column A. Here I need to count how many domain and need them to flag 1, 2, 3 and so on till maximum. How could I do them in SQL Server. Please find the attachment excel file for reference. Thanks   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -