DAX for SQL Folks: Part II - Translating SQL Queries to DAX Queries (2020-04-30)

 SQLServerCentral Newsletter for April 30, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [DAX for SQL Folks: Part II - Translating SQL Queries to DAX Queries]( - [Free Training Week: How I Use the First Responder Kit – sp_Blitz]( - [From the SQL Server Central Blogs - Disk Performance Metrics in AWS and Azure]( - [From the SQL Server Central Blogs - Using Azure Functions in Azure Data Factory]( Question of the Day - [Bad Error Handling]( The Voice of the DBA  Daily Coping Tip Take a minute to remember what really matters to you and why I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. A Security Regression Another breach from Marriott. This time, from a franchise property. Apparently [they discovered a large amount of information was being accessed by two employees](. They disabled the accounts and investigated. It appears that some personal information, though not financial data, was accessed. I assume there is still some active work being done here, but no update in almost two months. I ran across this on [Bruce Schneier's blog](, where he questions if Marriot is taking security seriously. It's a fair question, especially after Marriot had [a huge breach in 2014](. Do we think that a second data breach is grounds for questioning security. I certainly think it is, because anyone that has had an incident should be extra careful. In this case, I'm not quite sure this is a sign of lax security. The disclosure notes that employee credentials were used at a franchise property. Does this mean that the employees inappropriately accessed data? Or that someone got employee user/pwd data and used it from a franchise property? In either case, this is mostly on the franchisee, and also difficult to detect. We could argue that 45 days is too long for suspicious activity to take place, and I think it is. If this occurred off Marriott property, or from non-franchisee equipment that connected to the wifi at a hotel, then I think Marriot has not taken security seriously. I know employees get new phones, laptops, etc., but any access from a new device ought to have some security associated with it. I know when I connect to Redgate resources, there is some 2FA, along with requiring the user/password combination. If this isn't in place, that's a huge issue. Certainly getting employee credentials might be possible from observation. If employees log on, anyone watching, or surreptitiously recording the process, could get credentials. That alone shouldn't be enough to log on, and if it is, Marriott (and their franchisees) aren't taking security seriously. If this was employee misbehavior, however, that's more difficult to track. I'd hope that employees can't get lots of guest data in a short period of time, and there are monitoring systems in place to track large (or frequent) database queries. The bottom line here is Marriott likely needs more security, and probably deserves some larger fine here. Not having more GDPR-like regulation in the US might make this hard, and I hope the US starts to better protect the humans whose data is being processed by companies. They certainly should have strong physical security, which many hotels might not have. I've certainly encountered plenty of unoccupied front desks (and concierge desks) at hotels late at night. Perhaps Marriott needs better, and more frequent, pen testing by humans against their systems at franchisee locations to ensure they have implemented enough security. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [DAX for SQL Folks: Part II - Translating SQL Queries to DAX Queries]( Frank A. Banin from SQLServerCentral Introduction In response to my approach in the popular MDX Guide for SQL Folks series, I am using SQL as a good frame of reference for starting or developing a new approach for improving your Data Analysis Expression(DAX) language learning experience. This is useful for developers starting to learn the DAX language to more advanced developers who […] [Free Training Week: How I Use the First Responder Kit – sp_Blitz]( Additional Articles from SQLServerCentral When you’re first approaching a SQL Server, start with sp_Blitz. We’ll cover my favorite parameters and what I look for in the results, and then start building our notes in Markdown for our eventual health check recommendations for the rest of our team. From the SQL Server Central Blogs - [Disk Performance Metrics in AWS and Azure]( nelsonaloor from PracticalSQLDba In the last two posts, we have discussed the different types of storage disks available in the AWS and Azure cloud platforms. We also discussed the characteristics that affect... From the SQL Server Central Blogs - [Using Azure Functions in Azure Data Factory]( Rayis Imayev from Data Adventures (2020-Apr-19) Creating a data solution with Azure Data Factory (ADF) may look like a straightforward process: you have incoming datasets, business rules of how to connect and change them and...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Bad Error Handling I have this code I'm using to test some error handling. BEGIN TRY BEGIN TRAN SELECT 'Did this run?', 1/0 AS AnException COMMIT END TRY BEGIN CATCH SELECT ERROR_MESSAGE() THROW END CATCH What results should I see when this executes? 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) The R Select I have loaded the dplyr library in R. I have a dataset, called counties, with census data. I run the head() function, and see this: > head(counties) # A tibble: 6 x 40 census_id state county region metro population men women hispanic white black 1 1001 Alab~ Autau~ South Metro 55221 26745 28476 2.6 75.8 18.5 2 1003 Alab~ Baldw~ South Metro 195121 95314 99807 4.5 83.1 9.5 3 1005 Alab~ Barbo~ South Nonm~ 26932 14497 12435 4.6 46.2 46.7 4 1007 Alab~ Bibb South Metro 22604 12073 10531 2.2 74.5 21.4 5 1009 Alab~ Blount South Metro 57710 28512 29198 8.6 87.9 1.5 6 1011 Alab~ Bullo~ South Nonm~ 10678 5660 5018 4.4 22.2 70.7 # ... with 29 more variables: native , asian , pacific , # citizens , income , income_err , income_per_cap , # income_per_cap_err , poverty , child_poverty , # professional , service , office , construction , # production , drive , carpool , transit , walk , # other_transp , work_at_home , mean_commute , employed , # private_work , public_work , self_employed , family_work , # unemployment , land_area I want to select just the state, county, and population values for the first few rows. Which of these statements will work? Answer: select(counties, state, county, population) Explanation: The SELECT command will do this. You can specify the column names or numbers. The third answer doesn't work because R is 1-based, not 0-based. Ref: SELECT - [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 [Reporting Services Question]( - Someone recently told me that from SQL Server 2017 you are only allowed to install one instance of SQL Server reporting services on an individual server. I am no reporting services expert but I believed in the Reporting config manager you were able to select different instances you wished to connect to on a single […] SQL Server 2017 - Development [CASE Statement]( - Hi All, I have a simple case statement that won't work and I can't see where the error is, missing a bracket or something not sure. The syntax error is appearing at the, END as 'Measure' Thanks for any help. DECLARE @1startdate DATE = '2014-01-01' , @2Endate DATE = '2019-09-30' ,@3Policy VARCHAR(50) = 'Policy_View' ,@4Channel […] SQL Server 2016 - Administration [How to skip duplicate record and keep on running until the end?]( - I used code below to insert data into a table from another table. INSERT INTO tUser (Name,Email) SELECT Name,Email FROM tUSER_NEW T WHERE T.EMAIL NOT IN (SELECT EMAIL FROM tUser) But, this code will stop running once there is a duplicate record. How to skip duplicate record and keep on running until the end? [Scripting Query Store]( - I could not find such script but I am sure it should exist. If I want to ask a DBA of a particular database of a particular server to send me ALTER DATABASE etc.. script that would allow me run it and recreate his precise Query Store settings in my database. I want to just […] [Restore Encrypted Database on another instance]( - In order to restore an encrypted database on another instance, do I need the entire chain on the new instance, ie: Service Master Key, Database Master, and the DEK key, the last 2 from the old server? I created the master key on the new server, as well as the service master, (then set up […] Development - SQL Server 2014 [How to create table with 3rd row data as columns with separate cama(,) column]( - As above image is my table data, i have to create a table from 3rd row separate cama as a column and create new table and the same after 5th row should take data and load into creted new table.. can you please please help anyone .. Please help me on this topic SQL 2012 - General [Howto retain few backups and delete old backup files]( - Hi, I have created a dynamic backup script to take backup for 2 databases. something like as follows: use master go declare @dbname varchar(100) set @dbname = 'db1' declare @cmd varchar(1000) set @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''C:\backup\'+@dbname+'_FULL'+'_'+convert(char(8),getdate(),112)+'_'+REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')+'.BAK'' WITH INIT, STATS =5, BUFFERCOUNT = 100;' print @cmd EXEC (@cmd) go declare @dbname […] [DBCC Integrity Check Error - insufficient system memory in resource pool]( - We have Sql server STD 2012 SP2 and from last 3 days, we are getting error while running the DBCC Integrity Check for both User and system Databases, we are using Ola Maintenance solution script. We have Total Physical Memory 8 GB, Total Virtual Memory 9.25 GB (showing in system Information Available Physical Memory 925 […] SQL Server 2019 - Administration [SQL server upgradation]( - Hi Experts , We are using SQl server 2012 database for our application . Management is planning to migrate our application to Azure cloud. So we are planning upgrade server to SQl server 2019 or Azure database. So i want to know which one is better on the basis of scalability, which instance has more […] SQL Server 2019 - Development [Collapse record and get the number of days]( - here is my data. I am trying to get the dates in single row like below. Any help appreciated. result I need [using OleDbConnection can we connect to both sqlserver and mdb]( - Hi All, In administrative Tools ODBC Data Sources (64-bit) configuration ,Please find the attached screenshot where i have a datasource configured. Example: Standarad Part Library  32/64 -bit   Server Standarad Part Library0904  32/64 -bit  Microsoft Access Driver (*.mdb) How to  establish connection for both SQL Server and Microsoft Access Driver in VB .net […] Integration Services [Package migration from version 6 to version 3 failed with error 0xC001700A]( - Forum, Im trying to execute and migrate a package which was developed in SQL 2012 on to a SQL 2008 server , i see the version mismatches from the error is there an alternative way to make it working ? . Im planning on having the BIDS installed and rewite the complete SSIS package. This […] [Cannot extract Filename from Filepath using GetFileName in C#]( - Hi there I have a SSIS package with a connection manager as follows: Package.Connections[Output_ExistingFile].Properties[ConnectionString]" ValueType= \\acslonitstaging\Segmentation\PushPortfolio\Results\Existing_SVPP_XXXX_00001_00021_20200424_231245.csv I want to extract the filename  [Existing_SVPP_XXXX_00001_00021_20200424_231245.csv] in a C# Component Ive tried the following string fileName; string path = Dts.Connections["Output_ExistingFile"].ConnectionString; string result; result = path.GetFileName(fileName); MessageBox.Show(Message1); However this is not working Is there an alternative way to extract […] COVID-19 Pandemic [Daily Coping 27 Apr 2020]( - Today's tip is: connect with nature. Breathe and notice life continuing. My thoughts: [Daily Coping 24 Apr 2020]( - Today's tip is: Thank three people you’re grateful to and tell them why. My thoughts:   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -