Introduction to SQL Server Security — Part 5

The series so far:

1. Introduction to SQL Server Security — Part 1
2. Introduction to SQL Server Security — Part 2
3. Introduction to SQL Server Security — Part 3
4. Introduction to SQL Server Security — Part 4
5. Introduction to SQL Server Security — Part 5
6.  

SQL Server provides a number of tools for helping you ensure that your environment and data remain secure. Some of the most important of these come in the form of catalog views, dynamic management views (DMVs), and built-in system functions. By incorporating them into your T-SQL statements, you can retrieve important security-related information about your SQL Server environment, such as which principals are enabled, what permissions are assigned to those principals, or how auditing is implemented on database objects.

In this article, I cover a number of these views and functions and provide examples of how you can use them to retrieve details about your system’s security. I created the statements in SQL Server Management Studio (SSMS), using SQL Server 2017, although most of the information I cover can apply to SQL Server editions going back to 2014 or earlier.

Microsoft divides security-related catalog views into server-level views, database-level views, encryption-related views, and auditing-related views. I’ve taken the same approach here, providing a section for each type. Because there are fewer security-related DMVs and system functions, I’ve provided only a single section for each of them.

In all cases, I cover only a portion of the available views and functions, but enough to give you a sense of how you can use them when administering a SQL Server instance. Just be aware that Microsoft provides plenty of others, so be sure to refer to SQL Server documentation as necessary to learn about those views and functions I haven’t covered here.

Server-Level Catalog Views

SQL Server offers numerous security-related catalog views that operate at the server level. Two of the most useful are sys.server_principals and sys.server_permissions. The sys.server_principals view returns a row for each existing server principal, and the sys.server_permissions view returns a row for each assigned server permission.

You can join these views together to see the permissions granted on specific principals. For example, the following SELECT statement returns the permissions assigned to SQL logins that have been enabled:

SELECT pr.principal_id, pr.name, pe.state_desc, pe.permission_name
FROM sys.server_principals pr INNER JOIN sys.server_permissions pe 
  ON pr.principal_id = pe.grantee_principal_id
WHERE pr.type_desc = 'SQL_LOGIN' AND pr.is_disabled = 0;

The sys.server_principals view includes the type_desc column, which indicates the principal type. SQL Server supports six types of server principals, including SQL_LOGIN, WINDOWS_LOGIN and SERVER_ROLE. The view also returns the is_disabled column, which indicates whether a principal is enabled (0) or disabled (1). Both columns are specified in the WHERE clause to filter out all but enabled SQL logins. Figure 1 shows the results returned by the SELECT statement on my system, which includes only a few test logins.

Figure 1. Permissions granted on SQL logins

You can also use the sys.server_principals view in conjunction with the sys.server_role_members view, which returns a row for each member of a fixed or user-defined server role. Because the sys.server_principals view includes all principals, including roles and logins, you can join the view to itself and to the sys.server_role_members view to retrieve the members of a specific role, as shown in the following example:

SELECT member.principal_id, member.name
FROM sys.server_role_members rm
JOIN sys.server_principals role  
  ON rm.role_principal_id = role.principal_id  
JOIN sys.server_principals member  
  ON rm.member_principal_id = member.principal_id
WHERE role.name = 'sysadmin' AND member.type_desc = 'SQL_LOGIN';

The statement limits the results to the sysadmin role and to SQL logins. Self-joining the sys.server_principals view makes it possible to retrieve both the role and login names. Figure 2 shows the results I received on my system, which indicate that the only two SQL logins are members of the sysadmin role, one of which is the sa account.

Figure 2. SQL logins in the sysadmin role

Another interesting catalog view is sys.system_components_surface_area_configuration, which returns a row for each executable object that can be enabled or disabled through SQL Server’s surface area configuration features. For example, the following statement uses the view to return a list of stored procedures included in the SMO and DMO XPs component:

SELECT database_name, schema_name, object_name
FROM sys.system_components_surface_area_configuration
WHERE state = 1 AND component_name = 'SMO and DMO XPs'
  AND type_desc = 'SQL_STORED_PROCEDURE';

The type_desc column in the WHERE clause specifies the object type (SQL_STORED_PROCEDURE), and the state column indicates that the object should be enabled (1), rather than disabled (0). Figure 3 shows the results returned on my system.

Figure 3. Stored procedures in the surface area configuration

The database_name value indicates which database contains the object. The possible values include master, msdb, and mssqlsystemresource (a read-only database that contains all the system objects).

 

Database-Level Catalog Views

Many of the database-level catalog views work much like their server-level counterparts, except that they’re specific to the current database. To try out some of these views, first create the Test1 database and then add the Sales schema (which will be used in later examples):

USE master;
GO
CREATE DATABASE Test1; 
GO
USE Test1;
GO
CREATE SCHEMA Sales; 
GO

After you create the database, you can run the catalog views within the context of that database. For example, you can use the sys.database_principals view to retrieve details about the existing database principals, and you can use the sys.database_permissions view to retrieve details about assigned database permissions. Similar to what you saw at the server level, you can join these views to see the permissions assigned to specific database principals. For example, the following SELECT statement returns certain types of permissions granted to the public role:

SELECT pm.state_desc, 
  pm.permission_name
FROM sys.database_permissions pm INNER JOIN sys.database_principals pr 
  ON pm.grantee_principal_id = pr.principal_id
WHERE pr.name = 'public' AND pm.class_desc = 'DATABASE';

The statement limits the results to permissions in the DATABASE class. However, the view can also return permissions for such classes as OBJECT_OR_COLUMN, SCHEMA, or DATABASE_PRINCIPAL, depending on what permissions have been assigned at the database level. Figure 4 shows the results that the SELECT statement returned on my system.

Figure 4. Database permissions granted to the public role

One way to check effective permissions without writing scripts yourself is to use Redgate’s SQL Census. It creates a report of who has access to what on your SQL Servers and makes best practice recommendations, like disabling SA accounts. It’s still in development but it’s a good starting point to check on your SQL Server permissions and undertake any necessary cleaning tasks.

SQL Server also provides database-level catalog views that do not have a server counterpart (and vice versa). For example, you can use the sys.master_key_passwords view to retrieve information about the database master key password, if the password was added by using the sp_control_dbmasterkey_password stored procedure.

To see how this works, start by creating a database master key in the Test1 database, as shown in the following example (using a stronger password than the one I’ve included here):

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'tempPW@56789';
GO

After you’ve created the master key, use the sp_control_dbmasterkey_password stored procedure to add a credential that specifies the same password as the one used when creating the database master key:

EXEC sp_control_dbmasterkey_password 
  @db_name = N'Test1',   
  @password = N'tempPW@56789', 
  @action = N'add';  
GO

When SQL Server tries to decrypt the database master key, it first attempts to use the service master key. If this doesn’t work, SQL Server searches the credential store for a master key credential, using that if it exists.

After you’ve created the master key credential, you can use the sys.master_key_passwords view to retrieve information about that credential:

SELECT * FROM sys.master_key_passwords;

The statement returns only the credential_id and family_guid values, as shown in Figure 5. The family_guid column displays the unique ID assigned to the database when it was originally created.

Figure 5. Configured database master key password

You can also use the sys.credentials view to retrieve credential information, as shown in the following example:

SELECT credential_id, name, credential_identity
FROM sys.credentials;

Although the sys.credentials view is a server-level catalog view, you can use it to see credentials created for database master keys. On my system, the SELECT statement returns the results shown in Figure 6.

Figure 6. Credential associated with the Test1 database master key

The results indicate that I have only one credential created on my SQL Server instance, the one for the Test1 database master key. Notice that the credential_id value shown here is the same value shown in Figure 5.

SQL Server also provides the sys.database_scoped_credentials view, which returns a row for each database-scoped credential in the database. The following SELECT statement uses the view within the context of the Test1 database:

SELECT * FROM sys.database_scoped_credentials;

The SELECT statement returns no rows for Test1. This is because the credential created for the database master key exists at the server level, not the database level.

Encryption-Related Catalog Views

The security-related catalog views also include about a dozen specific to SQL Server’s encryption features. One of these views is sys.certificates, which you can use to retrieve details about the certificates that exist in a database. To see the view in action, first create a certificate named Cert1 in the Test1 database, using the subject customer credit cards (or whatever subject you want to use):

USE Test1;
GO
CREATE CERTIFICATE Cert1
WITH SUBJECT = 'customer credit cards';

When you create a certificate without specifying a password, SQL Server uses the database master key to encrypt the certificate, which means that a master key must already exist. (You created the key in the previous section.) You can now use the sys.certificates view to retrieve information about the certificate:

SELECT name CertName,
  certificate_id cert_id,
  pvt_key_encryption_type_desc encrypt_type,
  issuer_name
FROM sys.certificates;

Figure 7 shows the results returned on my system. Notice that the encryption type is ENCRYPTED_BY_MASTER_KEY.

Figure 7. Cert1 certificate in the Test1 database

SQL Server also lets you add asymmetric and symmetric keys to your database. If you’ve added either type of key, you can use the sys.asymmetric_keys or sys.symmetric_keys view to return details about them. For example, suppose you create the following asymmetric key in the Test1 database:

CREATE ASYMMETRIC KEY Akey1
  WITH ALGORITHM = RSA_2048   
  ENCRYPTION BY PASSWORD = 'tempPW@56789';   
GO

The statement adds an asymmetric key named Akey1, using RSA encryption and password protection. You can now use the sys.asymmetric_keys view to retrieve information about the new key:

SELECT name key_name,
  pvt_key_encryption_type_desc encrypt_type,
  algorithm_desc,
  key_length
FROM sys.asymmetric_keys;

Figure 8 shows the results returned by the SELECT statement.

Figure 8. Akey1 asymmetric key in the Test1 database

The SELECT statement returns the key name, encryption type, algorithm, and key length, all of which were specified when creating the asymmetric key.

Auditing-Related Catalog Views

The final category of security-related catalog views includes those specific to SQL Server’s auditing features. If you’ve implemented auditing, these views can be particularly handy, especially the sys.server_audits and sys.database_audit_specifications views. The sys.server_audits view returns information about server audit objects, and the sys.database_audit_specifications view returns information about database audit specifications.

To see both views in action, start by creating and enabling the SrvAudit1 audit and the DbSpec1 database specification in the Test1 database:

USE master;  
GO  
CREATE SERVER AUDIT SrvAudit1  
TO FILE (FILEPATH = 'C:\DataFiles\audit\');  
GO  
ALTER SERVER AUDIT SrvAudit1  
WITH (STATE = ON);  
GO  
USE Test1;
GO  
CREATE DATABASE AUDIT SPECIFICATION DbSpec1 
FOR SERVER AUDIT SrvAudit1
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SELECT, INSERT, UPDATE, DELETE 
  ON Schema::Sales BY public)  
WITH (STATE = ON);  
GO

After you’ve created your server audit object, you can use the sys.server_audits view to view that object, specifying the audit name in your WHERE clause, as shown in the following example:

SELECT audit_id,
  name audit_name,
  create_date,
  type_desc,
  is_state_enabled is_enabled
FROM sys.server_audits
WHERE name = 'SrvAudit1';

The statement returns the results shown in Figure 9. Notice that the type_desc value is FILE, indicating that the audit log is saved to the file system rather than to the Security or Application log. The figure also indicates that the audit is enabled. (The is_enabled value is 1.)

Figure 9. SrvAudit1 audit created on the server

You can then use the sys.database_audit_specifications view to view information about the database audit specification:

SELECT database_specification_id dbspec_id,
  name spec_name,
  create_date,
  is_state_enabled is_enabled
FROM sys.database_audit_specifications;

The statement returns the results shown in Figure 10.

Figure 10. DbSpec1 database audit specification

SQL Server also provides several other catalog views specific to auditing, but their use depends on how you’ve configured auditing on your SQL Server instance. For example, if you create server audit specifications, you can use the sys.server_audit_specifications view to retrieve information about those specifications.

Dynamic Management Views

As with catalog views, SQL Server offers DMVs specific to auditing. One of these views is sys.dm_audit_actions, which lets you retrieve event-related information about audit actions and audit groups. For example, the following SELECT statement uses the view to return the IDs and names of the actions or groups with a class_desc value of LOGIN and a covering_parent_action_name value of LOGIN_CHANGE_PASSWORD_GROUP:

SELECT action_id,
  name action_name
FROM sys.dm_audit_actions
WHERE class_desc = 'LOGIN' AND
  covering_parent_action_name = 'LOGIN_CHANGE_PASSWORD_GROUP';

The class_desc column refers to the object class that the audit action applies to. The covering_parent_action_name column is the audit action or group that contains the row’s audit action. On my system, the SELECT statement returned the results shown in Figure 11.

Figure 11. Audit actions in the audit log

Another DMV specific to auditing is sys.dm_server_audit_status view, which returns information about server audit objects. In the following example, the SELECT statement uses the view to retrieve the ID, name, status, and file size of each defined audit:

SELECT audit_id,
  name audit_name,
  status_desc,
  audit_file_size
FROM sys.dm_server_audit_status;

The only audit I had defined on my system when I ran this statement was the one created in the previous section, giving me the results shown in Figure 12.

Figure 12. SrvAudit1 audit status

Security-related DMVs are not limited to auditing. SQL Server also provides several views specific to encryption, such as the sys.dm_database_encryption_keys view, which returns details about a database’s encryption state and its encryption keys.

You can see how the view works by setting up Transparent Data Encryption (TDE) on the Test1 database. For this, you need to take the following steps:

1. Create a database master key in the master database, if the key doesn’t already exist.
2. Create a certificate in the master database for securing the master key.
3. Create a database encryption key in the Test1 database.

Normally, there would be an additional step to enable TDE on the Test1 database, but that’s not necessary to demonstrate how the sys.dm_database_encryption_keys view works.

To create a database master key in the master database, run the following CREATE MASTER KEY statement, providing a much more robust password, of course:

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'tempPW@56789';
GO

You can then use the sys.symmetric_keys catalog view to view information about the master database key:

SELECT name mkey_name,
  symmetric_key_id mkey_id,
  key_length,
  algorithm_desc
FROM sys.symmetric_keys;

Figure 13 shows the results returned by the SELECT statement on my system. When you create a database master key in the master database, SQL Server also adds a service master key.

Figure 13. Database and service master keys

The next step is to create a certificate in the master database for securing the master key. You can do this easily enough by running the following CREATE CERTIFICATE statement:

CREATE CERTIFICATE TdeCert
WITH SUBJECT = 'TDE certificate';
GO

To verify that the certificate has been created, you can use the sys.certificates view, as shown in the following example:

SELECT name cert_name,
  certificate_id,
  pvt_key_encryption_type_desc encrypt_type,
  issuer_name
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';

Because the master database on my system includes only one certificate, the SELECT statement returns only one row, which is shown in Figure 14.

Figure 14. TdeCert certificate in the master database

The final step is to create a database encryption key in the Test1 statement. For this, you can use the following CREATE DATABASE ENCRYPTION KEY statement, specifying the TdeCert certificate you created in the master database:

USE Test1;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TdeCert;

When you run this statement, you should receive the following warning about backing up the certificate:

Warning: The certificate used for encrypting the database encryption key 
has not been backed up. You should immediately back up the certificate 
and the private key associated with the certificate. If the certificate
ever becomes unavailable or if you must restore or attach the database
on another server, you must have backups of both the certificate and 
the private key or you will not be able to open the database.

This completes the steps you need to take to prepare your database to try out the sys.dm_database_encryption_keys view. The following SELECT statement uses the view to retrieve the encryption state, key algorithm, key length and encryption type of the database encryption key:

SELECT encryption_state,
  key_algorithm,
  key_length,
  encryptor_type
FROM sys.dm_database_encryption_keys
WHERE DB_NAME(database_id) = 'Test1';

If you’ve been following along, the statement should return the results shown in Figure 15.

Figure 15. Database encryption key for the Test1 database

I realize that this is a long way to go about testing the sys.dm_database_encryption_keys view, but SQL Server supports only a handful of security-related DMVs, and I wanted to be sure you got to see some of them in action. That said, the TDE example I used here is actually based on one I created for the Simple Talk article Encrypting SQL Server: Transparent Data Encryption (TDE), which provides more specific information about enabling TDE on a SQL Server database.

Security-Related Functions

In addition to the catalog views and DMVs, SQL Server provides a number of security-related system functions. For example, you can use the sys.fn_builtin_permissions table-valued function to return details about the server’s built-in permission hierarchy or a subset of that hierarchy, as shown in the following SELECT statement:

SELECT class_desc,
  covering_permission_name,
  parent_class_desc
FROM sys.fn_builtin_permissions(DEFAULT)
WHERE permission_name = 'DELETE';

In this case, the sys.fn_builtin_permissions function takes DEFAULT as an argument, which means the function will return a complete list of built-in permissions. However, the WHERE clause limits those results to the DELETE permission. Figure 16 shows the results that were returned on my system.

Figure 16. Built-in DELETE permissions on the SQL Server instance

SQL Server also provides a number of system functions for working with user and login accounts. To demonstrate how some of these work, first create the sqllogin1 login and then create the sqluser1 user in the Test1 database, based on the sqllogin1 login:

USE Test1;
GO
CREATE LOGIN sqllogin1 
WITH PASSWORD = 'tempPW@56789';
GO
CREATE USER sqluser1 FOR LOGIN sqllogin1;
GRANT SELECT, INSERT, DELETE
ON SCHEMA::Sales TO sqluser1;  
GO

The script also includes a GRANT statement that assigns the SELECT, INSERT, and DELETE permissions to sqluser1 on the Sales schema. You can verify these permissions by running the fn_my_permissions table-valued function within the security context of sqluser1:

EXECUTE AS USER = 'sqluser1'; 
SELECT permission_name 
FROM fn_my_permissions ('Sales', 'SCHEMA');  
REVERT;  
GO

The EXECUTE AS USER statement changes the security context to sqluser1, and the REVERT statement changes the security context back to the original user. Because the SELECT statement runs under the context of sqluser1, it returns the results shown in Figure 17, which verify the permissions assigned to that user.

Figure 17. Permissions granted to sqluser1

SQL Server also provides a number of scaler functions for verifying a user’s identity, including the following:

• The SUSER_NAME function returns the user’s login identification name.
• The SUSER_ID function returns the user’s login identification number.
• The SUSER_SID function returns the user’s login security identification number (SID).
• The USER_NAME function returns the user’s database user account name.
• The USER_ID function returns the user’s database user identification number.

For each of these functions, you can provide a parameter value or you can provide no value, in which case the function uses the current user or login account. You can test this out by calling the functions within the security context of sqluser1:

EXECUTE AS USER = 'sqluser1'; 
SELECT SUSER_NAME() login_name,
  SUSER_ID() login_id,
  SUSER_SID() login_sid,
  USER_NAME() dbuser_name,
  USER_ID() dbuser_id;
REVERT;  
GO

Figure 18 shows the results I received on my system. Notice that the results list the correct user name and the login name associated with that user.

Figure 18. User and login names and IDs for sqluser1

SQL Server also provides the IS_MEMBER scalar function for verifying whether the current user is the member of a specified group or role. For example, the following SELECT statement uses the function to determine whether sqluser1 is a member of the db_owner role:

EXECUTE AS USER = 'sqluser1'; 
SELECT USER_NAME() dbuser_name,
  CASE
    WHEN IS_MEMBER('db_owner') = 1 THEN 'member'
    WHEN IS_MEMBER('db_owner') = 0 THEN 'not member'
    WHEN IS_MEMBER('db_owner') = NULL THEN 'not valid'
  END AS is_member;
REVERT;  
GO

The IS_MEMBER function can return only one of three values:

• If 0 is returned, the user is not a member of the specified group or role.
• If 1 is returned, the user is a member of the specified group or role.
• If NULL is returned, the group or role is not valid.

Figure 19 shows the results returned by the SELECT statement. As expected, the user is not a member of the db_owner role (unless you added the user to the role).

Figure 19. Verifying sqluser1 membership

Another fun built-in function is PWDCOMPARE, which lets you compare an existing password to a specified password. In this way, you can test for blank passwords or inadequate or common passwords, such as pa$$word.

To try out the function, first create the sqllogin2 login with a blank password (not in a production environment):

CREATE LOGIN sqllogin2 
WITH PASSWORD = '' , CHECK_POLICY = OFF;
GO

Next, run the following SELECT statement, using the PWDCOMPARE function in the WHERE clause to return any logins with a blank password:

SELECT principal_id, 
  name login_name
FROM sys.sql_logins
WHERE PWDCOMPARE('', password_hash) = 1;

The first argument passed into the PWDCOMPARE function is the unencrypted password, which in this case is an empty string. The second argument tells the function to use the password encryption hash. The function returns a 1 if the specified password matches the user’s actual password. Otherwise, the function returns 0. In this case, the WHERE clause specifies that the function must return 1 for the row to be returned. Figure 20 shows the results I received on my system.

Figure 20. Comparing login passwords

The SELECT statement should return only the sqllogin2 login. If your results include other logins, you might want to reevaluate your current security strategy.

SQL Server Security Views and Functions

SQL Server provides a number of security-related catalog views, DMVs, and system functions in addition to what I covered here, and you certainly should take the time to learn about what’s out there. These views and functions can be very useful when trying to understand and troubleshoot security on a SQL Server instance. The more familiar you are with what’s available, the easier it will be for you to do your job and the more effectively you can ensure the security of your data.

 

The post Introduction to SQL Server Security — Part 5 appeared first on Simple Talk.

from Simple Talk http://bit.ly/2Iz5osA
via

Comparing SQL Server Instances: Objects by Name

It is all to easy to build a database on a server and then expect it to be fully functional. Not so fast: There are a number of possibilities in terms of server-based functionality that can defeat you. Databases are likely to have scheduled jobs and alerts, and may have server triggers or even message queues. They are going to have different credentials and logins, proxy accounts, shared schedules and so on. Database applications will sometimes consist of several database components or may require linked servers to be set up. For this reason, it is important to be certain what this server-based functionality is, where it is, and to ensure that it is scripted out into source control so it can be used when provisioning a server for the application.

I’ve written in the past about how to script out server code. However, there is a different problem which is to work out the difference in configuration between two servers. Once you know the differences between the server configuration of a SQL Server instance that holds a working database application and the instance on which you wish to build a version of the application, then you can produce the relevant scripts to provide the database with the required working environment. This is a fairly common DevOps requirement, but one that seems to have few tools to help with the task. The possible reasons for this become apparent as soon as you look at the settings, properties and attributes of the server. Not only are there a great number of them, but few are likely to be relevant. When you are just starting out with this problem, It is much better to have an overview of the differences rather than become overwhelmed with a tsunami of possibly irrelevant data.

To get an overview, I prefer to examine what SMO regards as the server collections. These include such things as databases, endpoints, agent jobs, alerts and linked servers. The simplest comparisons are on the actual names. This tells you whether the two servers have, for example, a linked server of the same name. It is a start, but two servers can have, say, the same agent job with the same name, but that do different things, or are at different versions. You will still need to script them out and check that the scripts are the same, though you should then beware of false negatives due to headers with dates in them.-scripts will show up as different when the difference is actually just the date you did the scripting!

There is an important distinction to be made. The server can consist of a range of objects, such as a database. If we examine the name, we can tell fairly well what databases should be on the server we are provisioning. However, if you compare two databases, you can say whether the names are the same or different, but we are not saying whether the databases with the same name are identical or different. It is the same with settings: we can say that servers have the same settings, but they may have wildly different values. Now the fact that they have different values may, or may not, be important to the provisioning process. It would be foolhardy to say what is important because that will depend on your circumstances. After all, the fact that the server has a different name is very unlikely to be interesting.

We therefore will concentrate, in this script, on comparing the names of the objects in the various collections. This will at least tell you if a component is missing and is a lot quicker than tooling about with SSMS’s object browser!

For this work, there is a useful built-in Cmdlet called Compare-Object. Once you’ve understood the way it works it is very handy for doing comparisons. Its only problem is that it is not the most intuitive visual way of reporting differences, so we use the Compare-Object’s ‘SideIndicator’ for building up the results and then convert the results in one go, into something that is easier to understand. Note that I’m not listing objercts that are equal, just those that exist only on one server or the other. That is a knob you can twiddle but I’d only do that where there are likely to be just a limited number of objects.

Obviously, this would be wrapped into a function in general use. I’ve unwrapped it here to make it easier to investigate.

$Data = @{

"source" = @{
    #this is the source server server you are comparing
    'Server' = 'MySourceServer'; #The SQL Server instance
    'instance' = '\'
    'username' = 'ArthurGig'; #leave blank if windows authentication
  }
"target" = @{
    #this is the server you are comparing it with
    'Server' = 'MyTargetServer'; #The SQL Server instance
    'instance' = '\'
    'username' = 'PhilipJFactor'; #leave blank if windows authentication
  }
}

$BadChars = '[\\\/\:\.]' #characters that we don't want in filenames

set-psdebug -strict # to catch subtle errors
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load sqlserver module
$popVerbosity = $VerbosePreference #remember current verbosity setting
$VerbosePreference = "Silentlycontinue"
# the import process is very noisy if you are in verbose mode
Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality
if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.XEvent") -eq $null)
{
  throw "Could not load library for Extended Events."
}
$VerbosePreference = $popVerbosity
<#  ----  now pick up the credentials if using SQL Server Authentication #>
@($Data.source,$Data.target)|foreach{
# get credentials if necessary
    if ($_.username -ne '') #then it is using SQL Server Credentials
    { #have we got them stored locally
      $SqlEncryptedPasswordFile = `
      "$env:USERPROFILE\$($_.username)-$($_.Server+$_.Instance -replace $BadChars, '').txt"
      # test to see if we know about the password in a secure string stored in the user area
      if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
      {
        #has already got this set for this login so fetch it
        $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString
        $SqlCredentials = `
        New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)
      }
      else #then we have to ask the user for it
      {
        #hasn't got this set for this login
        $SqlCredentials = get-credential -Credential $SqlUserName
        $SqlCredentials.Password | ConvertFrom-SecureString |
        Set-Content $SqlEncryptedPasswordFile
            }
    $_.Credentials=$SqlCredentials #save them with the server data
    }
}

$ms = 'Microsoft.SqlServer'
$My = "$ms.Management.Smo" #

<# now we use the information we have and the credentials to connect 
to the servers #>
@($Data.source,$Data.target)|foreach{
        if ($_.username -eq '') #dead simple if using windows security
    { $s = new-object ("$My.Server") $_.Server+$_.Instance  }
    else # if using sql server security we do it via a connection object
    {
      $ServerConnection = new-object "$ms.Management.Common.ServerConnection" (
        "$($_.Server)$($_.Instance)" , $_.username, $_.Credentials.Password)
      $s = new-object ("$My.Server") $ServerConnection
    }
    $_.ServerObject=$s
}

$sourceName=$Data.source.ServerObject.Name #for the result headings
$TargetName=$Data.Target.ServerObject.Name #for the result headings
<# now we start by collecting all the possible properties, excluding any that
we know give trouble. and we go through each collection, comparing the
names between the two servers #>
$ComparisonList=$Data.source.ServerObject|gm -MemberType 'property'|
 where {$_.definition -like '*collection*'}| #all the collection objects
   select name | #filter out the ones that cause problems
     where {$_.Name -notin @('SystemMessages','OleDbProviderSettings')}|
       foreach  { #for each collection name ...
     $currentType=$_.Name #we now get a list of objects for each
     $sourceList=$Data.source.ServerObject.$currentType|select name
     $TargetList=$Data.target.ServerObject.$currentType|select name
     #we check that they both have objects in the list
     if ($SourceList -ne $null -and $TargetList -ne $null)
         {Compare-Object $sourceList $TargetList -Property name|
           select name,SideIndicator, @{Name="Type"; Expression = {$CurrentType}}}
     elseif ($TargetList -ne $null) # well it is easy, only in the source
         { $TargetList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'=>'}},
             @{Name="Type"; Expression = {$CurrentType}}}
     elseif ($SourceList -ne $null) # we know they are only in the target
         { $SourceList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'<='}}, 
             @{Name="Type"; Expression = {$CurrentType}}}
}

#Now we get all the collections in the jobserver
$ComparisonList+=$Data.source.ServerObject.Jobserver|gm -MemberType 'property'|
 where {$_.definition -like '*collection*'} | #get all the jobserver collection objects
# all the Agent objects we want to script out
    Foreach {
     $currentType=$_.Name #get a list of all the objects
     $sourceList=$Data.source.ServerObject.JobServer.$currentType|select name
     $TargetList=$Data.target.ServerObject.JobServer.$currentType|select name
     if ($SourceList -ne $null -and $TargetList -ne $null)
         {Compare-Object $sourceList $TargetList -Property name|
           select name,SideIndicator, @{Name="Type"; Expression = {$CurrentType}}}
     elseif ($TargetList -ne $null) 
         { $TargetList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'=>'}},
             @{Name="Type"; Expression = {$CurrentType}}}
     elseif ($SourceList -ne $null) 
         { $SourceList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'<='}}, 
             @{Name="Type"; Expression = {$CurrentType}}}
}
# finally, we pick up the XEvents which are stored separately
$Data.source.SqlConn = $Data.source.ServerObject.ConnectionContext.SqlConnectionObject
$Data.Target.SqlConn = $Data.Target.ServerObject.ConnectionContext.SqlConnectionObject

$Data.Source.XEstore = New-Object  Microsoft.SqlServer.Management.XEvent.XEStore $Data.Source.SqlConn
$Data.Target.XEstore = New-Object  Microsoft.SqlServer.Management.XEvent.XEStore $Data.Target.SqlConn

$sourceList=$Data.source.XEStore.Sessions | select name
$TargetList=$Data.Target.XEStore.Sessions | select name

if ($SourceList -ne $null -and $TargetList -ne $null)
         {$ComparisonList+= Compare-Object $sourceList $TargetList -Property name|
           select name,SideIndicator, @{Name="Type"; Expression = {'Xevent Sessions'}}}
     elseif ($TargetList -ne $null) 
         { $ComparisonList+= $TargetList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'=>'}},
             @{Name="Type"; Expression = {'Xevent Sessions'}}}
     elseif ($SourceList -ne $null) 
         { $ComparisonList+= $SourceList|
           select Name, 
             @{Name="SideIndicator"; Expression = {'<='}}, 
             @{Name="Type"; Expression = {'Xevent Sessions'}}}

<# Now we have the entire list we then list them out.#>
$ComparisonList|
  select @{Name=$sourceName; Expression = {if ($_.sideIndicator -eq '<=') {$_.Name} else {''}}}, 
         @{Name=$TargetName; Expression = {if ($_.sideIndicator -eq '=>') {$_.Name} else {''}}},
         @{Name="Type"; Expression = {$_.Type}}

Here is a result from running it on a couple of development servers. The first two columns have the names of the two servers at the top, and the third column has the type of object we’re investigating

DeepThink\              BigThought\                   Type           
----------              -----------                   ----           
                        Nell                          Credentials    
                        Dan McGrew                    Credentials    
                        Abednego                      Databases      
                        Antipas                       Databases      
                        Archaelus                     Databases      
                        Daniel                        Databases      
                        Meshach                       Databases      
                        RedGateMonitor                Databases      
                        ServerEvents                  Databases      
                        WebsiteUsage                  Databases      
AdventureWorks2012                                    Databases      
contacts                                              Databases      
Customers                                             Databases      
MarineMammals                                         Databases      
NorthWind                                             Databases      
WSLSOURCE                                             LinkedServers  
ReportingServer                                       LinkedServers 
                        Wheezy                        LinkedServers     
                        BIGTHOUGHT\Administrator      Logins         
                        BIGTHOUGHT\Nell               Logins         
                        BIGTHOUGHT\Posh               Logins         
                        PercyTheGreenEngine           Logins         
MSSecurityMtr                                         Logins         
DEEPTHINK\Administrator                               Logins         
DEEPTHINK\Nell                                        Logins         
                        HostDistribution              Properties     
                        HostRelease                   Properties     
                        HostServicePackLevel          Properties     
                        HostSku                       Properties     
                        RG_SQLLighthouse_DDLTrigger   Triggers       
                        208 Error                     Alerts         
                        InvalidObjectError            Alerts         
Business                                              JobCategories  
                        CheckClones                   Jobs           
                        CheckConfiguration            Jobs           
                        InvalidObjectDetected         Jobs           
                        RunPowerShellScript           Jobs           
                        Nell                          ProxyAccounts  
                        PoshProxy                     ProxyAccounts  
                        RunItEveryMinute              SharedSchedules
                        AllErrors                     Xevent Sessions
                        allLogins                     Xevent Sessions
                        AllSQLStatementsExecuted      Xevent Sessions
                        AllWarnings                   Xevent Sessions
                        BatchCompleted                Xevent Sessions
                        CheckingSPsAndSQLStatements   Xevent Sessions
                        MonitorErrors                 Xevent Sessions
                        PermissionsErrors             Xevent Sessions
                        QueryTimeouts                 Xevent Sessions
                        QuickSessionStandard          Xevent Sessions
                        QuickSessionTSQL              Xevent Sessions
                        sqlmonitor_session            Xevent Sessions
                        UncompletedQueries            Xevent Sessions
                        WhoChangedWhat                Xevent Sessions
MonitorSuspiciousErrors                               Xevent Sessions
Recompile_Histogram                                   Xevent Sessions
Recompiles                                            Xevent Sessions

 

The post Comparing SQL Server Instances: Objects by Name appeared first on Simple Talk.

from Simple Talk http://bit.ly/2UTFQNB
via

Overview of Azure Cosmos DB

Cosmos Database (DB) is a horizontally scalable, globally distributed, fully managed, low latency, multi-model, multi query-API database for managing data at large scale. Cosmos DB is a PaaS (Platform as a Service) offering from Microsoft Azure and is a cloud-based NoSQL database. Cosmos DB is sometimes referred to as a serverless database, and it is a highly available, highly reliable, and high throughput database. Cosmos DB is a superset of Azure Document DB and is available in all Azure regions.

With Cosmos DB you can distribute the data to any number of Azure regions, i.e., the data can be replicated to the geolocation from where your users are accessing, which helps in serving data quickly to users with low latency.

Features of Cosmos DB

Globally Distributed

With Azure Cosmos DB, your data can be replicated globally by adding Azure regions with just one click.

Linearly Scalable

Linear Scalability is the ability to handle the increased load by adding more servers to the cluster. Cosmos DB can be scaled horizontally to support hundreds of millions of transactions per second for reads and writes.

Schema-Agnostic Indexing

Cosmos DB’s database engine is schema agnostic, and this enables automatic indexing of the data. Cosmos DB automatically indexes all the data without requiring schema and index management.

Multi-Model

Cosmos DB is a multi-model database, i.e., it can be used for storing data in Key-value Pair, Document-based, Graph-based, Column Family-based databases. Irrespective of which model you choose for your data persistence, global distribution, provisioning throughput, horizontal partitioning, and automatic indexing capabilities are the same.

Multi-API and Multi-Language Support

Microsoft has released SDKs for multiple programming languages including Java, .NET, Python, Node.js, JavaScript, etc. Cosmos DB has Multi API support, i.e., SQL API, Cosmos DB Table API, MongoDB API, Graph API, Cassandra API, and Gremlin API.

Multi-Consistency Support

Cosmos DB supports 5 consistency levels, i.e., Eventual, Prefix, Session, Bounded and Strong. Multi Consistency is discussed in detail later in the article.

Indexes Data Automatically

Cosmos DB indexes data automatically on ALL fields in all documents by default without the need for secondary indexes, but you can still create custom indexes. Azure’s automatic indexing capability indexes every property of every record without having to define schemas and indices upfront. This capability works across every data model.

High Availability

Cosmos DB provides 99.999% availability for both reads and writes for multi-region accounts with multi-region writes. Cosmos DB provides 99.99% availability for both reads and writes for single-region accounts. Cosmos DB automatically fails over, if there is a regional disaster. Your application may also programmatically failover if there is such a case.

Guaranteed Low Latency

Azure Cosmos DB always guarantees 10 milliseconds latency at the 99^th percentile for reads and writes for all consistency levels. Data can be geographically distributed to any number of Azure regions, so the stored data can be available nearest to the customers, which reduces the possible latency in retrieving the data.

Multi-Master Support

Cosmos DB supports multi-master, which means the writes, in addition to reads, can be scaled elastically across any number of Azure regions across the world. With the multi-master feature, you can choose that all data servers act as write servers. To enable the multi-master feature for your applications, you need to enable multi-region writes. Follow these instructions to configure the multi-master feature.

Multi-master support is now available in all Azure regions.

How Cosmos DB Works

Assume you have a website that’s used by users across multiple geographic locations and the database writes are into the database located in US region (in non-multi-master mode). This database is considered as a primary. When the data is written to the US database, the users in the US location will be able to get the data faster compared to other users in other geo locations due to network latency. If the multi-master feature is enabled, the data is concurrently written to all the selected regions simultaneously.

To solve these latency issues, the data has to be replicated to the user’s nearest region so that users can fetch the data faster from the nearest region. For example, if a user is located in Mumbai, India, then this user should be able to access data from the Mumbai region. Set up read databases in the nearest region of your users and replicate the data from the primary database to this secondary read database. When data is written to the primary database, the data is replicated to read databases, which are globally distributed.

The challenge to achieving the planet scale database architecture is Consistency (Consistency is a property that indicates all replicas of the database are in sync and maintain the same state of a given object at any given point of time). For example, if the data is written to the primary database in the U.S, then it takes a few milliseconds to sync this data to other regions from the primary. During this synchronization period, users reading from the U.S get the latest data, while users from another region may get old data, i.e., the data is not consistent between primary and replicas. This is called as Eventual Consistency.

Consistency Levels in Cosmos DB

Azure Cosmos DB offers 5 consistency levels so that you can choose the right consistency level for your application.

Eventual:

In eventual consistency, the data that is written to the primary node is propagated to read-only secondary nodes, which are globally distributed, eventually. It takes some time to get the data available to the users of the readable secondary. This consistency level is suitable for applications that are not mission critical. It gives low latency and better performance, but there is a possibility that these users may see stale data for a brief period, and the data may not be in order.

Consistent Prefix:

Clients read the data in the same sequence (D, C, B, A) that has been committed (A, B, C, D). You’ll start seeing the updated data as it starts to go and the data is the right order. However, a complete data set is not guaranteed.

Session:

Committed users will see the data that they just committed. However, users in other geo locations may not be able to get this data version until replication happened. This is the most widely used consistency level.

Bounded Staleness:

It is an indicator to define how much staleness period you set. If you set staleness period as 2 hours, then even though the data is replicated to all secondary nodes, the clients still see the old value. If you set staleness=0, then it’s a strong consistency.

Strong:

Strong consistency always guarantees the latest copy of the data (i.e., highly consistent data) for clients irrespective of where they are reading from but gives a relatively low performance. This consistency level scoped to a single region. This consistency level is suitable for mission-critical applications (e.g., stock trading applications),

Eventual consistency may result in inconsistencies in the data the clients read due to the data being replicated to secondary nodes eventually, but it gives better performance. Strong consistency results in consistent data served to clients but gives poor performance.

While creating Azure Cosmos DB, you can choose the default consistency level, but this can be changed while reading the data from Cosmos DB from your application.

Consistency vs Performance on different consistency levels.

Cosmos DB Containers

Azure Cosmos DB Container is schema-agnostic and the unit of scalability. A container is horizontally partitioned and replicated across multiple regions. Based on the partition key, added items in the container and provisioned throughput are distributed automatically across a set of logical partitions. Items in the container don’t need to be similar items (e.g., Person, Vehicle, Business Entity, etc.) and they can have arbitrary schemas. Containers can have fixed or unlimited collections. Fixed collections limit you to a single partition, and don’t need a partition key set since everything is stored in a single partition. In unlimited, collections are not limited in the number of partitions.

Azure Cosmos DB allows you to set TTL (Time-To-Live) for each item in the container or set the same TTL for all the items in the container. Once the TTL is expired, those items will be deleted from the container, and the query on this container will not return the expired items. You may follow the steps outlined here to enable the TTL on items in the containers.

Following diagram outlines the relation among Cosmos DB account, Database, Collection, and Items.

Cosmos DB’s Multi-Model Capabilities

Cosmos DB supports the following five data models: Key-Value, Column-Family, Document, and Graph database models. Regardless of which data model you use, core content model of the Cosmos DB engine is based on ARS (Atom-Record-Sequence, which defines persistent layer for key-value pairs) and projects different data models in different APIs. Cosmos DB exposes the data in JSON format.

Depending on the type of application, use an appropriate data model. If an existing MongoDB or Cassandra Database based applications are to be migrated to Cosmos DB, it requires minimal to no code changes in application code. If an application requires a relationship between entities, then a graph data model works better. Each of the supported data models has an API to integrate with Cosmos DB. The data model is determined based on the selected API for the database. You have to choose the most relevant API for your application at the time of creating the container (i.e. “database instance”). Based on the chosen API, the desired data model (graph, key-value, document or column) is projected on the underlying data store. Because of the way the data is stored and retrieved, you can use only one API against a container; multiple APIs usage is not possible.

Take a look now into each of the data models.

Key-Value Pair Data Model (Table API):

In this data model, each entity consists of a key and a value pair. The value itself can be a set of key-value pairs. This is very similar to a table in a relational database where each row has the same set of columns. The Key-Value pair solution is supported on standard Azure table API. This Table API is primarily helpful for existing Azure Table Storage customers in migration to Cosmos DB.

Column-Family Data Model:

This data model supports Cassandra API. Existing Cassandra implementations can be easily and quickly moved to Cosmos DB and use column family format, which is used in Cassandra. Column Family data model is similar to key-value data model except that the items in the data model adhere to the defined schema.

Document Data Model:

This model supports SQL API and MongoDB APIs; both of these APIs give the document data model. These two APIs are different, though they are similar in data modeling. SQL API allows building transactional stored procedures, triggers, and user-defined functions. SQL API stores entities in JSON in a hierarchical key-value document. MongoDB API stores in BSON (Binary encoded version of JSON, which extends JSON with additional data types and multi-language support).

SQL API works with Document DB protocols, whereas MongoDB API works with MongoDB APIs. Both these APIs allow interacting with the documents in the database. If you already have a MongoDB solution and you want to make it scale out or globally aware, you can switch to Cosmos DB and use MongoDB API for interacting with the documents.

The max document size in Cosmos DB is 2 MB unlike the max document size of 16MB in MongoDB.

Graph Data Model:

Graph database implements a collection of interconnected entities and relationships. Microsoft has chosen to use Gremlin API from Apache Tinkerpop open source project. Gremlin API allows you to interact with a Graph database globally scaled and provides a graph traversal language, which enables to efficiently query across many relationships exist in a graph database.

Best Practices in Using Cosmos DB for Better Performance

• Partition Key: Partition Key acts as a logical partition of your data. There are logical and physical partitions in Cosmos DB. Each Partition has a limit of 10GB. If this limit is exceeded per partition, “Partition key reached maximum size of 10 GB” error will be thrown. If you see this error, then it indicates that you have reached the limit for your partition in the collection. To resolve this issue, you have to recreate the collection and choose your partition key such that all the data items stored against that key are under the 10 GB limit and transfer your data from old collection to new.
• Getting data from the same partition will be much faster than getting data from multiple partitions. Cross-partition queries add latency.
• Use Table API for migrating existing Azure Table Storage customers into Cosmos DB quickly. Document Data model SQL API is more capable than Table API.
• Cosmos DB document size is limited to 2 MB and is not supposed to be used for content storage. For larger payload storage, use Azure Blob Storage instead.
• To get better performance, relax consistency as needed.
• Tune indexing policy appropriately for faster writes.
• Cosmos DB is rate limited. If you have set low RU (Read Units)/s and executing a large query, the results from Cosmos DB may be slow.

Summary

Understanding Cosmos DB’s features such as multi-model, multi API and different levels of consistency levels helps in selecting the right API and model for your application. Choosing the right API model and the right level of consistency for your application will help improve the performance of the queries. This article discusses different features and consistency levels of Cosmos DB and also some of the best practices.

 

The post Overview of Azure Cosmos DB appeared first on Simple Talk.

from Simple Talk http://bit.ly/2IOfQvN
via

The Future of Medicine

If you are a Star Trek fan, you’ve seen the future of medicine, or at least how the writers imagine it will be. Painless, needless injections are given by hypospray devices, right through clothing with no chance of cross-contamination. The medical tricorder provides an accurate diagnosis in seconds. Broken bones, illnesses, and injuries are quickly healed. Replacement parts, like Captain Picard’s artificial heart, are no big deal and can last for hundreds of years. There is even a holographic doctor filling in when no humanoid one is available, complete with a lousy bedside manner.

Dr. “Bones” McCoy, the first Star Trek physician, was appalled by the state of 20^th-century medicine during a time-travel trip to the 1980s. Of course, we can feel the same as we look back over the history of medicine. Consider formerly popular treatments, like bloodletting. Today we wonder how anyone would think that was a good idea. (Actually, bloodletting is still used for a small number of conditions.)

Even today, we use modern technology like lasers, recombinant DNA, artificial intelligence, and 3D printers to produce treatments that replace some of the surgical procedures and pharmaceuticals used just a few years ago. I remember when synthetic human insulin, made in part by altering the DNA of bacteria, became available in the 1980s, replacing insulin extracted from beef and pork sources. This is even more remarkable if you realize that insulin was only discovered 60 years earlier. Before that discovery, juvenile onset diabetes (now known as Type I) cases were typically fatal in a short time.

Despite the current innovations, it may seem like we are a long way from Star Trek medicine, but we may be closer than you think. Many of us are measuring and tracking data like heart rate, blood pressure, and glucose levels with our smartphones. You can run an EKG (records heart rhythm) with an Apple Watch. Data from fitness trackers have even been used to assist in diagnosis and treatment.

The handful of metrics that can be collected with fitness devices and smartphones is just the beginning. In 2017, the XPRIZE Foundation awarded $2.6 million to an organization that came closest to meeting the requirements of a 21st-century Tricorder. The idea was to create a small non-invasive device that non-medical personnel could use to diagnose thirteen medical conditions and monitor five vital signs. Since no team was able to meet all the criteria for the grand prize, the remaining funds are being used to help develop the most promising devices. In the near future, doctors will be using small devices to diagnose illness in emergency rooms without blood tests and costly scans. Patients will be monitoring conditions like congestive heart failure at home.

The technology available today was beyond the imaginations of doctors and patients just a few decades ago. Thanks to Star Trek, we have a way to see the future now.

 

The post The Future of Medicine appeared first on Simple Talk.

from Simple Talk http://bit.ly/2KJgmO1
via

Using the FILTER Function in DAX

The series so far:

1. Creating Calculated Columns Using DAX
2. Creating Measures Using DAX
3. Using the DAX Calculate and Values Functions
4. Using the FILTER Function in DAX

The FILTER function works quite differently than the CALCULATE function explained in the previous article. It returns a table of the filtered rows, and sometimes it is the better approach to take.

I’ll spend most of this article explaining how to create the following measures:

The columns above show, respectively:

1. The city name;
2. Total sales for the city in question (the filter context);
3. Total sales for the city in question for 2018;
4. Total sales for the city in question for the USA;
5. Total sales for the city in question for the USA for 2018; and
6. The percentage each city’s sales contributes to the total.

First, I’ll show you how to set up the example. Then I’ll dive into the syntax of the FILTER function. I’ll finish by highlighting the differences between the FILTER and CALCULATE functions.

A Quick Refresher

To work through the examples in this article, you’ll need to create a simple Power BI report containing a single table and then create and show a series of measures. Here’s a quick run-through of how to get started.

First, create a Power BI report based on the tables used in the previous articles. You can load them either from the SQL Server database given or the Excel workbook. You should now have something like this (if your diagram looks a bit different, you may not have updated your instance of Power BI to include the March 2019 update, which included the new Model View):

Now create a table in Report view to list out the city names. Make sure the Table visualization is selected and click CityName in the Fields list.

Switch to the Home ribbon and select Enter Data. This will add a new table to your report to contain your measures. To understand why you might want to do this, see this previous article in this series:

Give this table a name. Here I’ve called mine All measures:

Click Load. Now add the following measure to your All measures table. You can right-click on the table and choose New measure to do this:

Sales = SUMX(
    // multiply the price of each transaction by
    // its quantity, and sum the result
    Sales,
    [Price]*[Quantity]
)

Choose to display this measure in your table:

You should now be able to see the total value of sales for each city:

What happens if you want to show the sales for American cities only? Or sales taking place in 2018 only?

The FILTER Function

The measure for the sales column shown above, giving total sales for each city, is as follows:

Sales = SUMX(
    Sales,
    [Price]*[Quantity]
)

What this does (as readers of this series of articles will know) is to iterate down the rows in the Sales table, calculating the price multiplied by the quantity for each and summing the result for each city to get this:

To get the sales in 2008, you could use a CALCULATE function so that this measure would work:

2018 sales using CALCULATE = CALCULATE(
    SUMX(
        Sales,
        [Price]*[Quantity]
    ),
    YEAR(Sales[SalesDate]) = 2018
)

This takes the filter context for each city, and further reduces it to consider only those rows where the sales occurred in 2018 to get this:

Another way to solve the problem, however, is to treat the sales for the current filter context as a table and filter it accordingly. Consider the example of sales for New York. Here’s the underlying data for this city:

The total figure for sales for New York for 2018 is 25.98 (18.98 + 7.00). One way to get to this would be to follow these steps when compiling the data for New York. Firstly, get the data for the filter context:

Secondly, filter this data to include only those sales for 2018, by iterating down each row deciding whether to include it. This will include only the shaded area below:

This leaves this table, which is the one whose sales Power BI will sum:

Here’s the formula to accomplish this:

2018 sales = SUMX(
    FILTER(
        Sales,
        YEAR(Sales[SalesDate])=2018
    ),
    [Price]*[Quantity]
)

This will give exactly the same results as the formula using CALCULATE above. The CALCULATE function will run more quickly because it doesn’t have to iterate down each row in the table testing a condition. At this point, you may be asking yourself what the point of the FILTER function is. I’ll return to this later in this article.

Linking tables within the Filter Function

Take a look at how to show total sales for the USA for each city. The Sales, City and Country tables are related as follows:

What’s needed is to iterate down the rows in the sales table, calculating the sales (price times quantity) for each but only where the country name is USA. Here’s the formula to do this:

American sales = SUMX(
    FILTER(
        Sales,
        RELATED(Country[CountryName])="USA"
    ),
    [Price]*[Quantity]
)

The expression gives these results:

The question is – why use the RELATED function when the DAX formulae using filter context automatically link tables together? The answer is that within this formula, row context, not filter context, is used. The shaded lines in the formula below iterate over each row in the Sales table returned for the filter context, creating a row context for each:

Because within the shaded bit of the formula DAX has to create a row context for each row in the sales table, it then has to use the RELATED function to bring in the country name from the Country table.

Combining Filters Without Nesting

It’s now time to look at how to combine criteria: how to show sales which happened in 2018 and which took place in the USA. I’ll show in a bit how to do this by nesting one FILTER function within another, but for now, I’ll show ways to combine criteria. There are two basic ways to do this in DAX – either by using && or the AND function (or if either of two conditions can be true, using || or the OR function).

Here’s a version of the measure using the AND function:

2018 American sales = SUMX(
    FILTER(
        Sales,
        AND(
            RELATED(Country[CountryName])="USA",          
            YEAR(Sales[SalesDate])=2018
        )
    ),
    [Price]*[Quantity]
)

Here’s the same measure, but using the && symbols:

2018 American sales using && = SUMX(
    FILTER(
        Sales,
        RELATED(Country[CountryName])="USA" &&
        YEAR(Sales[SalesDate])=2018
    ),
    [Price]*[Quantity]
)

Personally, I’d use the AND (or OR) functions any time, as they work in the same way as their Excel counterparts, and it’s easier to indent and comment formulae. However, you should use whichever floats your particular boat.

Even more sales have dropped from the figures:

Combining Conditions by Nesting Functions

The other way to solve this would have been to nest one function within another:

2018 American sales using nesting = SUMX(
    FILTER(
        FILTER(
            Sales,
            RELATED(Country[CountryName])="USA"
        ),
        YEAR(Sales[SalesDate])=2018
    ),
    [Price]*[Quantity]
)

Consider what this does for the New York row in the table:

Filter context restricts the data to sales for the current city in question.

The inner FILTER function iterates over each row in the table of data for the filter context, picking out only rows where the country is in the USA.

The outer FILTER function then iterates over each row in the table of sales for the USA for the filter context and applies a further constraint that the sales year must be 2018.

Depending on your data, nesting FILTER functions could speed up processing. If the vast majority of sales were outside the USA, the inner condition could eliminate nearly all rows for each city in the filter context, with the result that Power BI would only need to test the sales date for the few remaining rows.

Using ALL to Remove Any Filters

Every example shown so far has taken the set of rows for the current filter context and applied additional constraints to pick out only certain rows. However, you can use the ALL function when filtering to work with the entire table, rather than just the data for the current filter context. You could use this, for example, to show the percentage contribution of each city’s sales to the grand total:

Here’s the formula for the above measure:

% of all sales = DIVIDE(
    // calculate the total sales for the current filter context
    SUMX(
        Sales,
        [Price]*[Quantity]
    ),
    // divide this by total sales for all cities
    SUMX(
        ALL(Sales),
        [Price]*[Quantity]
    )
)

Incidentally, if you’re wondering how to get the nice percentage format, just select the measure you’ve created:

You can then set the formatting in the Modeling tab on the menu:

The Difference Between CALCULATE and FILTER

To see the difference between the way in which CALCULATE and FILTER filter data, consider this example:

The first measure applies the filter context (so it only calculates sales for the city in question), and applies an additional constraint that the city should be New York:

New York FILTERED = CALCULATE(
    // work out total sales
    // for the filter context
    SUMX(
        Sales,
        [Price]*[Quantity]
    ),
    
    // but whittle the filter context
    // down to show only those cities
    // within it called New York
    FILTER(
        City,
        City[CityName]="New York"
    )
)

The second measure replaces the filter context with a new constraint that the city should be New York, which results in the same figure appearing in every row:

New York CALCULATED = 
    CALCULATE(
    
        // work out total sales
        // for the filter context
        SUMX(
            Sales,
            [Price]*[Quantity]
        ),
        
        // changing the city criteria
        // so it is New York, not 
        // whatever the filter context
        // originally said
        City[CityName]="New York"
)

Debugging Using the FILTER Function (Method 1)

To make debugging easier, first add a couple of calculated columns to the Sales table, to give the city name and sales year. The formulae are shown below:

Here’s the formula for the City column. It just looks up the name of each city in which sales took place:

Here’s the formula for the Sales year column. It gives the year in which each sale took place:

These two columns will make it easier to check what’s going on when debugging.

The FILTER function creates virtual tables which, under normal circumstances, you never see, but you can use a tool like DAX Studio to show the rows these virtual tables contain. I’ve covered how to download and use DAX Studio in a previous article in this series, but here’s a quick refresher. When you run DAX Studio, choose to connect to an open Power BI report:

Type in a DAX query in the top right-hand window and press the F5 key to run this. The results will appear beneath it. For the example below, I’m just listing out the contents of the Sales table:

Incidentally, if you’re wondering what those long date table names are, you’re not the only one. I presume they are created behind the scenes to provide the built-in date hierarchy included in the March 2019 update of Power BI.

You can evaluate any table, including one which is returned from a filter function. A good thing to ask might be: which sales were in the United States? You can do this by copying this part of the measure you created earlier:

Precede this with the word EVALUATE in DAX Studio, and you’ll get this:

Run this to get the following output:

That’s looking good, so now you can repeat this technique with the outer bit of the FILTER function:

This gives only 3 rows:

From this, it’s easy to see why you get the figures for this measure.

Debugging Using the FILTER Function (Method 2)

Another way to debug a DAX formula using the FILTER function (or any other DAX formula, for that matter) is to use variables. I’ve already covered this for scalar variables (ones holding a single value) in the previous article in this series on measures, but did you know a variable can hold an entire table?

Here’s another way to write the nested FILTER function:

US sales in 2018 = 
// create a variable to hold the sales in the USA
VAR UsaSalesTable =  FILTER(
    Sales,
    RELATED(Country[CountryName])="USA"
)
// create another variable to filter this to show
// only sales in 2008
VAR UsaSales2018 = FILTER(
    UsaSalesTable,
    YEAR(Sales[SalesDate])=2018
)
// finally, calculates sales for these figures
RETURN SUMX(
    UsaSales2018,
    [Price]*[Quantity]
)

The advantage of breaking the complicated formula down into different parts is that you could then test each in isolation.

Why Would You Use the FILTER Function?

I promised I would return to this question: why would you use the FILTER function when the CALCULATE function seems to offer a better alternative? There are at least four advantages:

I’ve already shown that it’s easier to debug DAX expressions that use the FILTER function.

I think expressions using the FILTER function are easier to understand than equivalent expressions just using CALCULATE.

Learning the FILTER function will help you to understand the EARLIER function, which will be the subject of the next article in this series.

There are some problems which the CALCULATE function won’t solve (an example follows).

To illustrate the last point, suppose that you want to create a measure showing total sales for cities having two or more purchases. Here are the figures that this should return:

There are no sales recorded for Chicago, LA and Rio in the new measure because they each only witnessed a single sale.

Assume in all of the following that [Number of purchases] is a measure with this formula:

Number of purchases = COUNTROWS(Sales)

Here’s a measure which you could use to try to solve this problem (although it won’t work):

Sales for multiple purchases = CALCULATE(
    // calculate total sales where ...
    SUMX(
        Sales,
        [Price]*[Quantity]
    ),
    // ... the number of purchases is more than 1
    [Number of purchases] > 1
)

If you type in this measure, you’ll see the following error message:

This isn’t a brilliant description of the problem, which is that you can’t use a measure in the filtering part of a CALCULATE function; you can only refer to columns. You can, however, solve this problem by rewriting it to incorporate a FILTER function:

Sales for multiple purchases = CALCULATE(
    // calculate total sales but ...
    SUMX(
        Sales,
        [Price]*[Quantity]
    ),
    // only where the number of 
    // purchases is more than 1
    FILTER(
        City,
        [Number of purchases] > 1
    )
)

This will calculate total sales, but only for those cities where the number of purchases was more than 1.

Summary

The FILTER function in DAX allows you to iterate down the rows of any table, creating a row context for each and testing whether the row should be included in your calculation. You can combine filters using keywords like AND and OR and also nest one filter within another. The FILTER function allows you to perform some tasks which the CALCULATE function can’t reach, and also (in my opinion) lets you create formulae which are easier to understand.

The post Using the FILTER Function in DAX appeared first on Simple Talk.

from Simple Talk http://bit.ly/2V5gknB
via