Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions

 SQLServerCentral Newsletter for September 11, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions]( - [How to Stop a SSIS Package Execution from Code]( - [Self-service and Delegation with SQL Clone 4 Teams]( - [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( - [From the SQL Server Central Blogs - Restoring an Expired Certificate–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Are My SQL Server Indexes Being Used?]( Question of the Day - [Formatting the date]( [Redgate Database Devops]( The Voice of the DBA  MTTD There are a lot of Mean-Time-To-xxxx acronyms. Many of us have heard of the mean time between failures (MTBF) for disk drives. Some of us use that information when considering which model to buy. In the DevOps world, there are also the mean time to failure (MTTF) and mean time to resolve/repair (MTTR). There is one more that I think is very interesting, and that is the MTTD: the mean time to detect an issue. This is the average amount of time it takes you to detect there is a problem after the problem occurs. There was an outage at Monzo recently due to a database upgrade, which was [recounted on their blog](. In this case, their MTTD, or rather actual time to detect, was a minute. I think that is amazing. In fact, I'm somewhat skeptical that an alert is raised, someone looks at it, the customer service desk calls the Ops team (who were upgrading servers), and the Ops person realizes in the space of a minute or two that there is an issue. It's possible, but I have found that help desk personnel that discover something can take a few minutes to verify the issue and then scramble to find the on-call phone number. Relaying information can take a minute or two, so if this is accurate, huge props to the IT staff at Monzo. Many of us strive to high a high availability number for our systems, especially databases. This is one of the drivers for the growing use of availability groups in SQL Server systems: to ensure the database is highly available to clients. In determining availability, we often speak of the percentage of time that a system is available. The holy grail is five 9s, or an uptime of 99.999% of the year. This gives you just over 5 minutes of downtime a year. In the case of the Monzo outage, which took place in July 2019, the alert is reported at 13:14 and the incident was declared at 13:15pm, one minute later. The time to diagnose the issue (maybe another MTTxx item) was 63 minutes, just over an hour. At this point, availability is arguably down to 99.988%. The actual fix was completed at 113 minutes, or 99.978%. That's the number if nothing else happens this year. If you're attempting to get to 5 9s of reliability, you get less than 6 minutes of downtime a year. Can you figure out what's wrong in 6 minutes? Much less fix it? That's a difficult task. I think 4 9s, giving you 52-ish minutes of downtime, is realistic, but very hard. Most of us can likely handle 3 9s, which allows for 8:30:00 of downtime a year. While I've exceeded that before, it's been rare. We have a lot of HA (high availability) options in SQL Server, and there are many successful implementations that achieve high levels of availability for the database. The network and the application are another story, but I think the quality of those areas has increased over the years as well. Doing HA well is hard, and if you aren't 100% sure of what you're doing, or your system is very valuable, you might engage a consultant, like [Allan Hirt](, to ensure that you've configured things well. SQL Server runs well in HA configurations, but getting it set up can be more difficult than you expect. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [Stairway to DAX and Power BI]( [Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions]( Bill Pearson from SQLServerCentral.com As a part of his "Function / Iterator Pairs" mini-series, Business Intelligence architect, Analysis Services Maestro, SQL Server MVP, and author Bill Pearson introduces the DAX MAX() and MAXX() functions, discussing similarities and differences. He then provides some hands-on exposure to the use of each, particularly in combination with other DAX functions, in generating "largest numeric values" to meet differing needs within our PowerPivot model designs. [How to Stop a SSIS Package Execution from Code]( Additional Articles from MSSQLTips.com In this tip we look at a way to stop the execution of a SSIS data flow task that is running by executing an outside process to stop the data flow immediately. [Self-service and Delegation with SQL Clone 4 Teams]( Additional Articles from Redgate SQL Clone 4 introduces a new access control feature called Teams, allowing granular control over the SQL Server instances, images and clones to which each group of users has access. Here James Murtagh explains how Teams makes it easier to manage the safe distribution of database copies throughout the organization, to the various teams that need them for development, testing, training or analysis. [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( Press Release from Redgate Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". From the SQL Server Central Blogs - [Restoring an Expired Certificate–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. One common task that you might need to... From the SQL Server Central Blogs - [Are My SQL Server Indexes Being Used?]( SQLEspresso from SQLEspresso Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Formatting the date If I have this code: import time myString = '02/08/2019' myDatetime = time.strptime(myString,"format_string") What is the correct way to enter the format_string for a date in August? 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) Replication Basics Which database keeps track of the changes made to data and ensures they are replicated to other databases? Answer: distribution database Explanation: The distribution database is responsible for tracking the changes made in publishers and ensuring they are sent to each subscriber. Ref: - Stairway to SQL Server Replication: Level 1 - Introduction to SQL Server Replication - - Configure Distribution -  [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 - Development [using different databases / schema with stored procedure]( - We set up a server with different databases. One for more financial data (ASAP_be), one for hr data (PRP_be), one for structural data (SARA_be). When I give users the right only to execute stored procedures (with no access rights directly to the tables themselves), this works well, when the stored procedure only uses tables from […] SQL Server 2016 - Administration [Lock pages in memory setting]( - Hi Experts, I have a doubt on lock pages in memory. We have sql 2012/2016 64-bit enviroments. Question is, do we need explicity enable lock pages for 64bit sql servers or it is not required? As per my knowledge it is used for 32-bit systems as virtual address space is low. Thanks, Sam [3.5 TB SQL Migration from 2012 to 2016]( - Hello! What is the efficient method to migration 3.5TB SQL database from 2012 to 2016? Backup and Restore is not our choice as it would take application down for long time. Will setting up transaction replication work? Or have server team copy the Data and log files from current server to new server would be […] Administration - SQL Server 2014 [how to find network issues for sql prod server ?]( - Hi Experts, Is there a dirty trick or easy way to check network issues or slow network for the database server without using any 3rd party monitoring tool? Can we use continous ping from some quite non-prod server to prod sql server ? can we do that or is there a better way to do […] Development - SQL Server 2014 [Safe/correct way to reduce balance from account]( - i have a table: id int,customerid int,balance. i want to reduce for a "customerid" from his balance,assuming his balance-@amount to reduce>=0. what is the corret/safe way to do it, assuming the reduce can be called more then once at the same time from different applications? SQL Server 2012 - T-SQL [Insert into Select with Conditions]( - Hello, I think my requirement is fairly straight forward, but cant seem to get the syntax right or find a solution. Im, trying to do a Insert into Select , that will allow duplicates only if a field value in a row is null. hope that makes sense. - thanks Greg SQL Server 2008 - General [Unsure how to define this query]( - I need to make a query that will select all items only with ALL their warehouses datelastsale earlier than 2016-09-01. If Wh1, Wh2 are before 2016-09-01 or blank, but Wh3 is 2017-09-01 I don't want this item to show. I first thought of using this but I just can't put my finger on making int […] [Unsure how to define this query]( - I need to make a query that will select all items only with ALL their warehouses datelastsale earlier than 2016-09-01. If Wh1, Wh2 are before 2016-09-01 or blank, but Wh3 is 2017-09-01 I don't want this item to show. I first thought of using this but I just can't put my finger on making int […] Reporting Services [External Images]( - So I'm trying to get an external image to show after I deployed the report. When I run the unpublished report in Visual Studio it's not an issue. I have the Image Properties picture value to be somethings like this "File://L:/EQuipmentdata/100305/100305/thumbs/100305_tn.jpg" and it displays in design and preview. No problem. I deploy it and the […] [Row Count text box outputting above my data, i need it below]( - Row Count text box outputting above my data, i need it below. I have a Tablix with my report data at the top of my report. Immediately below that i have a ROW_COUNT text box. When I output the final report to Excel, the row_count is below the tablix as expected. When the client (and […] SSRS 2016 [Subscriptions - writing report Windows File Share]( - Is it possible to write a report to a Windows File Share without using an  account/password? thank you, beth [Subscriptions - what causes them to go into Pending state?]( - We often have subscriptions that go immediately into a Pending state after being triggered by a schedule. For example, I have a subscription that was executed at 12:52PM, and at 1:44PM, it is still Pending. thank you, beth [Subscription - how to pass list of values?]( - I have a multi-value parameter on a report, and I cannot figure out how to pass multiple values in a subscription parameter, i.e., I pass this value in parameter partner_list; 57590,12100 which produces this error; library!WindowsService_105!1f64!09/10/2019-12:36:40:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'partner_list' is not a […] SSDT [Connect to AWS]( - Hi All, I am completely new to AWS and i'm having trouble getting SSIS to connect to the ODBC driver. I have set up the "Amazon Redshift (x64) driver in the ODBC "System DSN" window and that connects fine. I have connected SSMS to the driver via a linked server using the provider "Microsoft OLE […] Integration Services [Possible double hop issue - SSIS Package / SSIS Catalogue]( - Wanted to check if anyone else was able to resolve this and how they did it. In a nutshell we have a package stored in SSIS catalogue on Server – S1 which reads files from Server – S2 ( NAS share) and then the  package stored in SSIS catalogue is executed via a C# app […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -