AWSTemplateFormatVersion: "2010-09-09"
Description: "This is the template for creating a solution to create insights from Amazon Connect Data Lake using Amazon Quicksight"
Parameters:
    ProjectName:
        Type: "String"
        Default: "replacemewithrealprojectname"
        Description: Enter an all-lowercase single-word name which is between 3 and 42 chars long. An S3 bucket will be created with this name to store all collected data.  
        MinLength : 3
        MaxLength : 42
        AllowedPattern : "^[a-z0-9]*$"
    CtrDataLakeSolutionDeployed:
        Type: "String"
        Default: "Yes"
        AllowedValues: 
          - "Yes"
        Description: Have you deployed the CTR datalake solution? (https://aws.amazon.com/blogs/contact-center/analyze-amazon-connect-contact-trace-record-with-amazon-athena-and-amazon-quicksight-part-1/)
    ContactLensDataLakeSolutionDeployed:
        Type: "String"
        Default: "Yes"
        AllowedValues: 
          - "Yes"
          - "No"
        Description: Have you deployed the Contact Lens datalake solution? (https://aws.amazon.com/blogs/contact-center/analyze-amazon-connect-contact-lens-using-contact-trace-record-amazon-athena-and-amazon-quicksight-part-2/)


Conditions:
  CreateCtrDashboardResources: !Equals 
    - !Ref CtrDataLakeSolutionDeployed
    - "Yes"
  CreateContactLensDashboardResources: !Equals 
    - !Ref ContactLensDataLakeSolutionDeployed
    - "Yes"



Resources:
    QuickSightDataSourceS3ThroughAthena:
        Type: "AWS::QuickSight::DataSource"
        DependsOn: 
          - ViewContactLensDenormalized
          - ViewConnectContactlensMatchedcategories
          - ViewConnectCtrContactAttributes
          - ViewConnectCtrDenormalized
        Properties:
            DataSourceId: !Sub "${ProjectName}-Athena"          
            Name: !Sub "${ProjectName}-Athena" 
            AwsAccountId: !Ref AWS::AccountId
            Type: "ATHENA"
            DataSourceParameters:
                AthenaParameters:
                    WorkGroup: "primary"
            SslProperties:
                DisableSsl: false
            Permissions:
              -
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:UpdateDataSourcePermissions"
                  - "quicksight:DescribeDataSource"
                  - "quicksight:DescribeDataSourcePermissions"
                  - "quicksight:PassDataSource"
                  - "quicksight:UpdateDataSource"
                  - "quicksight:DeleteDataSource"


    ViewConnectCtrContactAttributes:
        Type: "AWS::Glue::Table"
        Properties:
            DatabaseName: !Sub "${ProjectName}" 
            CatalogId: !Ref "AWS::AccountId"
            TableInput:
                ViewOriginalText: !Join
                  - ''
                  - - '/* Presto View: '
                    - Fn::Base64: 
                        Fn::Sub: |
                          {"originalSql":"WITH\n  base_attribute_data AS (\n   SELECT DISTINCT\n     \"map_keys\"(CAST(\"json_parse\"(attributes) AS map(varchar,varchar))) keysColumn\n   , attributes\n   , contactid\n   FROM\n     \"${ProjectName}\".\"connect_ctr\"\n) \nSELECT\n  base_attribute_data.contactid\n, attribute_key\n, \"json_extract_scalar\"(base_attribute_data.attributes, \"concat\"('$[\"', attribute_key, '\"]')) attribute_value\nFROM\n  base_attribute_data\n, UNNEST(base_attribute_data.keysColumn) t (attribute_key)\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"contactid","type":"varchar"},{"name":"attribute_key","type":"varchar"},{"name":"attribute_value","type":"varchar"}]}
                    - ' */'
                TableType: "VIRTUAL_VIEW"
                Parameters:
                    comment: "Presto View"
                    presto_view: "true"
                ViewExpandedText: "/* Presto View */"
                StorageDescriptor:
                    Columns:
                      -
                        Name: "contactid"
                        Type: "string"
                      -
                        Name: "attribute_key"
                        Type: "string"
                      -
                        Name: "attribute_value"
                        Type: "string"
                    Location: ""
                    Compressed: false
                    NumberOfBuckets: 0
                    SerdeInfo: {}
                    StoredAsSubDirectories: false
                Retention: 0
                Name: "connect_ctr_contactattributes"

    ViewConnectCtrDenormalized:
        Type: "AWS::Glue::Table"
        Properties:
            DatabaseName: !Sub "${ProjectName}" 
            CatalogId: !Ref "AWS::AccountId"
            TableInput:
                ViewOriginalText: !Join
                  - ''
                  - - '/* Presto View: '
                    - Fn::Base64: 
                        Fn::Sub: |
                          {"originalSql":"SELECT\n  awsaccountid\n, awscontacttracerecordformatversion\n, agent.arn agent_arn\n, agent.aftercontactworkduration agent_aftercontactworkduration\n, agent.aftercontactworkendtimestamp agent_aftercontactworkendtimestamp\n, agent.aftercontactworkstarttimestamp agent_aftercontactworkstarttimestamp\n, agent.agentinteractionduration agent_agentinteractionduration\n, agent.connectedtoagenttimestamp agent_connectedtoagenttimestamp\n, agent.customerholdduration agent_customerholdduration\n, agent.hierarchygroups agent_hierarchygroups\n, agent.longestholdduration agent_longestholdduration\n, agent.numberofholds agent_numberofholds\n, agent.routingprofile.name agent_routingprofile_name\n, agent.username agent_username\n, agentconnectionattempts\n, campaign.campaignid campaign_id\n, channel\n, connectedtosystemtimestamp\n, contactid\n, customerendpoint.address customerendpoint_address\n, customerendpoint.type customerendpoint_type\n, disconnectreason\n, disconnecttimestamp\n, initialcontactid\n, initiationmethod\n, initiationtimestamp\n, instancearn\n, lastupdatetimestamp\n, nextcontactid\n, previouscontactid\n, scheduledtimestamp\n, systemendpoint.address systemendpoint_address\n, systemendpoint.type systemendpoint_type\n, transfercompletedtimestamp\n, transferredtoendpoint.address transferredtoendpoint_address\n, transferredtoendpoint.type transferredtoendpoint_type\n, queue.arn queue_arn\n, queue.name queue_name\n, queue.enqueuetimestamp queue_enqueuetimestamp\n, queue.dequeuetimestamp queue_dequeuetimestamp\n, queue.duration queue_duration\n, recording.type recording_type\n, recording.status recording_status\n, recording.location recording_location\n, recording.deletionreason recording_deletionreason\n, year\n, month\n, day\nFROM\n  \"${ProjectName}\".\"connect_ctr\"\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"awsaccountid","type":"varchar"},{"name":"awscontacttracerecordformatversion","type":"varchar"},{"name":"agent_arn","type":"varchar"},{"name":"agent_aftercontactworkduration","type":"integer"},{"name":"agent_aftercontactworkendtimestamp","type":"varchar"},{"name":"agent_aftercontactworkstarttimestamp","type":"varchar"},{"name":"agent_agentinteractionduration","type":"integer"},{"name":"agent_connectedtoagenttimestamp","type":"varchar"},{"name":"agent_customerholdduration","type":"integer"},{"name":"agent_hierarchygroups","type":"varchar"},{"name":"agent_longestholdduration","type":"integer"},{"name":"agent_numberofholds","type":"integer"},{"name":"agent_routingprofile_name","type":"varchar"},{"name":"agent_username","type":"varchar"},{"name":"agentconnectionattempts","type":"integer"},{"name":"campaign_id","type":"varchar"},{"name":"channel","type":"varchar"},{"name":"connectedtosystemtimestamp","type":"varchar"},{"name":"contactid","type":"varchar"},{"name":"customerendpoint_address","type":"varchar"},{"name":"customerendpoint_type","type":"varchar"},{"name":"disconnectreason","type":"varchar"},{"name":"disconnecttimestamp","type":"varchar"},{"name":"initialcontactid","type":"varchar"},{"name":"initiationmethod","type":"varchar"},{"name":"initiationtimestamp","type":"varchar"},{"name":"instancearn","type":"varchar"},{"name":"lastupdatetimestamp","type":"varchar"},{"name":"nextcontactid","type":"varchar"},{"name":"previouscontactid","type":"varchar"},{"name":"scheduledtimestamp","type":"varchar"},{"name":"systemendpoint_address","type":"varchar"},{"name":"systemendpoint_type","type":"varchar"},{"name":"transfercompletedtimestamp","type":"varchar"},{"name":"transferredtoendpoint_address","type":"varchar"},{"name":"transferredtoendpoint_type","type":"varchar"},{"name":"queue_arn","type":"varchar"},{"name":"queue_name","type":"varchar"},{"name":"queue_enqueuetimestamp","type":"varchar"},{"name":"queue_dequeuetimestamp","type":"varchar"},{"name":"queue_duration","type":"integer"},{"name":"recording_type","type":"varchar"},{"name":"recording_status","type":"varchar"},{"name":"recording_location","type":"varchar"},{"name":"recording_deletionreason","type":"varchar"},{"name":"year","type":"integer"},{"name":"month","type":"integer"},{"name":"day","type":"integer"}]}
                    - ' */'
                TableType: "VIRTUAL_VIEW"
                Parameters:
                    comment: "Presto View"
                    presto_view: "true"
                ViewExpandedText: "/* Presto View */"
                StorageDescriptor:
                    Columns:
                      -
                        Name: "awsaccountid"
                        Type: "string"
                      -
                        Name: "awscontacttracerecordformatversion"
                        Type: "string"
                      -
                        Name: "agent_arn"
                        Type: "string"
                      -
                        Name: "agent_aftercontactworkduration"
                        Type: "int"
                      -
                        Name: "agent_aftercontactworkendtimestamp"
                        Type: "string"
                      -
                        Name: "agent_aftercontactworkstarttimestamp"
                        Type: "string"
                      -
                        Name: "agent_agentinteractionduration"
                        Type: "int"
                      -
                        Name: "agent_connectedtoagenttimestamp"
                        Type: "string"
                      -
                        Name: "agent_customerholdduration"
                        Type: "int"
                      -
                        Name: "agent_hierarchygroups"
                        Type: "string"
                      -
                        Name: "agent_longestholdduration"
                        Type: "int"
                      -
                        Name: "agent_numberofholds"
                        Type: "int"
                      -
                        Name: "agent_routingprofile_name"
                        Type: "string"
                      -
                        Name: "agent_username"
                        Type: "string"
                      -
                        Name: "agentconnectionattempts"
                        Type: "int"
                      -
                        Name: "campaign_id"
                        Type: "string"
                      -
                        Name: "channel"
                        Type: "string"
                      -
                        Name: "connectedtosystemtimestamp"
                        Type: "string"
                      -
                        Name: "contactid"
                        Type: "string"
                      -
                        Name: "customerendpoint_address"
                        Type: "string"
                      -
                        Name: "customerendpoint_type"
                        Type: "string"
                      -
                        Name: "disconnectreason"
                        Type: "string"
                      -
                        Name: "disconnecttimestamp"
                        Type: "string"
                      -
                        Name: "initialcontactid"
                        Type: "string"
                      -
                        Name: "initiationmethod"
                        Type: "string"
                      -
                        Name: "initiationtimestamp"
                        Type: "string"
                      -
                        Name: "instancearn"
                        Type: "string"
                      -
                        Name: "lastupdatetimestamp"
                        Type: "string"
                      -
                        Name: "nextcontactid"
                        Type: "string"
                      -
                        Name: "previouscontactid"
                        Type: "string"
                      -
                        Name: "scheduledtimestamp"
                        Type: "string"
                      -
                        Name: "systemendpoint_address"
                        Type: "string"
                      -
                        Name: "systemendpoint_type"
                        Type: "string"
                      -
                        Name: "transfercompletedtimestamp"
                        Type: "string"
                      -
                        Name: "transferredtoendpoint_address"
                        Type: "string"
                      -
                        Name: "transferredtoendpoint_type"
                        Type: "string"
                      -
                        Name: "queue_arn"
                        Type: "string"
                      -
                        Name: "queue_name"
                        Type: "string"
                      -
                        Name: "queue_enqueuetimestamp"
                        Type: "string"
                      -
                        Name: "queue_dequeuetimestamp"
                        Type: "string"
                      -
                        Name: "queue_duration"
                        Type: "int"
                      -
                        Name: "recording_type"
                        Type: "string"
                      -
                        Name: "recording_status"
                        Type: "string"
                      -
                        Name: "recording_location"
                        Type: "string"
                      -
                        Name: "recording_deletionreason"
                        Type: "string"
                      -
                        Name: "year"
                        Type: "int"
                      -
                        Name: "month"
                        Type: "int"
                      -
                        Name: "day"
                        Type: "int"
                    Location: ""
                    Compressed: false
                    NumberOfBuckets: 0
                    SerdeInfo: {}
                    StoredAsSubDirectories: false
                Retention: 0
                Name: "connect_ctr_denormalized"




    QuickSightDataSetCtrWithAttributes:
        Type: "AWS::QuickSight::DataSet"
        Properties:
            DataSetId: !Sub "${ProjectName}-Connect-Contact-Attr-on-CTR"
            Name: !Sub "${ProjectName}-Connect Contact Attr on CTR"
            AwsAccountId: !Ref AWS::AccountId
            PhysicalTableMap:
                06d764b5-3da7-448b-8130-9201c72260fa:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_ctr_contactattributes"
                        InputColumns:
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "attribute_key"
                            Type: "STRING"
                          -
                            Name: "attribute_value"
                            Type: "STRING"
                88a9f5e0-6108-427d-b7b1-b89d6a439bb4:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_ctr_denormalized"
                        InputColumns:
                          -
                            Name: "initiationtimestamp"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_connectedtoagenttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_arn"
                            Type: "STRING"
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "queue_duration"
                            Type: "INTEGER"
                          -
                            Name: "year"
                            Type: "INTEGER"
                          -
                            Name: "initialcontactid"
                            Type: "STRING"
                          -
                            Name: "queue_enqueuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_numberofholds"
                            Type: "INTEGER"
                          -
                            Name: "systemendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_agentinteractionduration"
                            Type: "INTEGER"
                          -
                            Name: "channel"
                            Type: "STRING"
                          -
                            Name: "agent_routingprofile_name"
                            Type: "STRING"
                          -
                            Name: "customerendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkendtimestamp"
                            Type: "STRING"
                          -
                            Name: "disconnecttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_hierarchygroups"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkduration"
                            Type: "INTEGER"
                          -
                            Name: "lastupdatetimestamp"
                            Type: "STRING"
                          -
                            Name: "transfercompletedtimestamp"
                            Type: "STRING"
                          -
                            Name: "awsaccountid"
                            Type: "STRING"
                          -
                            Name: "day"
                            Type: "INTEGER"
                          -
                            Name: "campaign_id"
                            Type: "STRING"
                          -
                            Name: "instancearn"
                            Type: "STRING"
                          -
                            Name: "recording_location"
                            Type: "STRING"
                          -
                            Name: "agentconnectionattempts"
                            Type: "INTEGER"
                          -
                            Name: "agent_username"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_address"
                            Type: "STRING"
                          -
                            Name: "queue_dequeuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_longestholdduration"
                            Type: "INTEGER"
                          -
                            Name: "connectedtosystemtimestamp"
                            Type: "STRING"
                          -
                            Name: "recording_status"
                            Type: "STRING"
                          -
                            Name: "recording_deletionreason"
                            Type: "STRING"
                          -
                            Name: "awscontacttracerecordformatversion"
                            Type: "STRING"
                          -
                            Name: "disconnectreason"
                            Type: "STRING"
                          -
                            Name: "systemendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_customerholdduration"
                            Type: "INTEGER"
                          -
                            Name: "queue_name"
                            Type: "STRING"
                          -
                            Name: "nextcontactid"
                            Type: "STRING"
                          -
                            Name: "month"
                            Type: "INTEGER"
                          -
                            Name: "customerendpoint_type"
                            Type: "STRING"
                          -
                            Name: "recording_type"
                            Type: "STRING"
                          -
                            Name: "initiationmethod"
                            Type: "STRING"
                          -
                            Name: "queue_arn"
                            Type: "STRING"
                          -
                            Name: "previouscontactid"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkstarttimestamp"
                            Type: "STRING"
                          -
                            Name: "scheduledtimestamp"
                            Type: "STRING"
            LogicalTableMap:
                490fc1e6-2ee1-4def-903a-039152a7f8af:
                    Alias: "Intermediate Table"
                    DataTransforms:
                      -
                        CreateColumnsOperation:
                            Columns:
                              -
                                ColumnName: "CalculatedTotalDuration"
                                ColumnId: "00ff9b6f-5dd1-4ffb-a9d1-58847e7983b3"
                                Expression: "dateDiff({initiationtimestamp},{disconnecttimestamp},\"SS\")"
                      -
                        ProjectOperation:
                            ProjectedColumns:
                              - "awsaccountid"
                              - "awscontacttracerecordformatversion"
                              - "agent_arn"
                              - "agent_aftercontactworkduration"
                              - "agent_aftercontactworkendtimestamp"
                              - "agent_aftercontactworkstarttimestamp"
                              - "agent_agentinteractionduration"
                              - "agent_connectedtoagenttimestamp"
                              - "agent_customerholdduration"
                              - "agent_hierarchygroups"
                              - "agent_longestholdduration"
                              - "agent_numberofholds"
                              - "agent_routingprofile_name"
                              - "agent_username"
                              - "agentconnectionattempts"
                              - "campaign_id"
                              - "channel"
                              - "connectedtosystemtimestamp"
                              - "contactid"
                              - "customerendpoint_address"
                              - "customerendpoint_type"
                              - "disconnectreason"
                              - "disconnecttimestamp"
                              - "initialcontactid"
                              - "initiationmethod"
                              - "initiationtimestamp"
                              - "instancearn"
                              - "lastupdatetimestamp"
                              - "nextcontactid"
                              - "previouscontactid"
                              - "scheduledtimestamp"
                              - "systemendpoint_address"
                              - "systemendpoint_type"
                              - "transfercompletedtimestamp"
                              - "transferredtoendpoint_address"
                              - "transferredtoendpoint_type"
                              - "queue_arn"
                              - "queue_name"
                              - "queue_enqueuetimestamp"
                              - "queue_dequeuetimestamp"
                              - "queue_duration"
                              - "recording_type"
                              - "recording_status"
                              - "recording_location"
                              - "recording_deletionreason"
                              - "year"
                              - "month"
                              - "day"
                              - "contactid[connect_ctr_contactattributes]"
                              - "attribute_key"
                              - "attribute_value"
                              - "CalculatedTotalDuration"
                    Source:
                        JoinInstruction:
                            LeftOperand: "da358775-8dd8-4a08-8d04-224b0c009028"
                            RightOperand: "8db92636-a370-4ffe-911b-bfa532731c13"
                            Type: "LEFT"
                            OnClause: "{contactid} = {contactid[connect_ctr_contactattributes]}"
                8db92636-a370-4ffe-911b-bfa532731c13:
                    Alias: "connect_ctr_contactattributes"
                    DataTransforms:
                      -
                        RenameColumnOperation:
                            ColumnName: "contactid"
                            NewColumnName: "contactid[connect_ctr_contactattributes]"
                    Source:
                        PhysicalTableId: "06d764b5-3da7-448b-8130-9201c72260fa"
                da358775-8dd8-4a08-8d04-224b0c009028:
                    Alias: "connect_ctr_denormalized"
                    DataTransforms:
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkendtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkstarttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_connectedtoagenttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "connectedtosystemtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "disconnecttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "initiationtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "lastupdatetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_enqueuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_dequeuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                    Source:
                        PhysicalTableId: "88a9f5e0-6108-427d-b7b1-b89d6a439bb4"
            ImportMode: "SPICE"
            FieldFolders: {}
            Permissions:
              -
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:UpdateDataSetPermissions"
                  - "quicksight:DescribeDataSet"
                  - "quicksight:DescribeDataSetPermissions"
                  - "quicksight:PassDataSet"
                  - "quicksight:DescribeIngestion"
                  - "quicksight:ListIngestions"
                  - "quicksight:UpdateDataSet"
                  - "quicksight:DeleteDataSet"
                  - "quicksight:CreateIngestion"
                  - "quicksight:CancelIngestion"

    QuickSightDataSetCtr:
        Type: "AWS::QuickSight::DataSet"
        Properties:
            DataSetId: !Sub "${ProjectName}-Connect-CTR"
            Name: !Sub "${ProjectName}-Connect CTR"
            AwsAccountId: !Ref AWS::AccountId
            PhysicalTableMap:
                da358775-8dd8-4a08-8d04-224b0c009028:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_ctr_denormalized"
                        InputColumns:
                          -
                            Name: "awsaccountid"
                            Type: "STRING"
                          -
                            Name: "awscontacttracerecordformatversion"
                            Type: "STRING"
                          -
                            Name: "agent_arn"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_aftercontactworkendtimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkstarttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_agentinteractionduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_connectedtoagenttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_customerholdduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_hierarchygroups"
                            Type: "STRING"
                          -
                            Name: "agent_longestholdduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_numberofholds"
                            Type: "INTEGER"
                          -
                            Name: "agent_routingprofile_name"
                            Type: "STRING"
                          -
                            Name: "agent_username"
                            Type: "STRING"
                          -
                            Name: "agentconnectionattempts"
                            Type: "INTEGER"
                          -
                            Name: "campaign_id"
                            Type: "STRING"
                          -
                            Name: "channel"
                            Type: "STRING"
                          -
                            Name: "connectedtosystemtimestamp"
                            Type: "STRING"
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "customerendpoint_address"
                            Type: "STRING"
                          -
                            Name: "customerendpoint_type"
                            Type: "STRING"
                          -
                            Name: "disconnectreason"
                            Type: "STRING"
                          -
                            Name: "disconnecttimestamp"
                            Type: "STRING"
                          -
                            Name: "initialcontactid"
                            Type: "STRING"
                          -
                            Name: "initiationmethod"
                            Type: "STRING"
                          -
                            Name: "initiationtimestamp"
                            Type: "STRING"
                          -
                            Name: "instancearn"
                            Type: "STRING"
                          -
                            Name: "lastupdatetimestamp"
                            Type: "STRING"
                          -
                            Name: "nextcontactid"
                            Type: "STRING"
                          -
                            Name: "previouscontactid"
                            Type: "STRING"
                          -
                            Name: "scheduledtimestamp"
                            Type: "STRING"
                          -
                            Name: "systemendpoint_address"
                            Type: "STRING"
                          -
                            Name: "systemendpoint_type"
                            Type: "STRING"
                          -
                            Name: "transfercompletedtimestamp"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_address"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_type"
                            Type: "STRING"
                          -
                            Name: "queue_arn"
                            Type: "STRING"
                          -
                            Name: "queue_name"
                            Type: "STRING"
                          -
                            Name: "queue_enqueuetimestamp"
                            Type: "STRING"
                          -
                            Name: "queue_dequeuetimestamp"
                            Type: "STRING"
                          -
                            Name: "queue_duration"
                            Type: "INTEGER"
                          -
                            Name: "recording_type"
                            Type: "STRING"
                          -
                            Name: "recording_status"
                            Type: "STRING"
                          -
                            Name: "recording_location"
                            Type: "STRING"
                          -
                            Name: "recording_deletionreason"
                            Type: "STRING"
                          -
                            Name: "year"
                            Type: "INTEGER"
                          -
                            Name: "month"
                            Type: "INTEGER"
                          -
                            Name: "day"
                            Type: "INTEGER"
            LogicalTableMap:
                da358775-8dd8-4a08-8d04-224b0c009028:
                    Alias: "connect_ctr_denormalized"
                    DataTransforms:
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkendtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkstarttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_connectedtoagenttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "connectedtosystemtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "disconnecttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "initiationtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "lastupdatetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_enqueuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_dequeuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CreateColumnsOperation:
                            Columns:
                              -
                                ColumnName: "CalculatedAgentDuration"
                                ColumnId: "535387cd-a96b-40cc-9780-b4db868b7f4a"
                                Expression: "dateDiff({agent_connectedtoagenttimestamp},{disconnecttimestamp},\"SS\")"
                      -
                        CreateColumnsOperation:
                            Columns:
                              -
                                ColumnName: "CalculatedTotalDuration"
                                ColumnId: "00ff9b6f-5dd1-4ffb-a9d1-58847e7983b3"
                                Expression: "dateDiff({initiationtimestamp},{disconnecttimestamp},\"SS\")"
                      -
                        ProjectOperation:
                            ProjectedColumns:
                              - "awsaccountid"
                              - "awscontacttracerecordformatversion"
                              - "agent_arn"
                              - "agent_aftercontactworkduration"
                              - "agent_aftercontactworkendtimestamp"
                              - "agent_aftercontactworkstarttimestamp"
                              - "agent_agentinteractionduration"
                              - "agent_connectedtoagenttimestamp"
                              - "agent_customerholdduration"
                              - "agent_hierarchygroups"
                              - "agent_longestholdduration"
                              - "agent_numberofholds"
                              - "agent_routingprofile_name"
                              - "agent_username"
                              - "agentconnectionattempts"
                              - "campaign_id"
                              - "channel"
                              - "connectedtosystemtimestamp"
                              - "contactid"
                              - "customerendpoint_address"
                              - "customerendpoint_type"
                              - "disconnectreason"
                              - "disconnecttimestamp"
                              - "initialcontactid"
                              - "initiationmethod"
                              - "initiationtimestamp"
                              - "instancearn"
                              - "lastupdatetimestamp"
                              - "nextcontactid"
                              - "previouscontactid"
                              - "scheduledtimestamp"
                              - "systemendpoint_address"
                              - "systemendpoint_type"
                              - "transfercompletedtimestamp"
                              - "transferredtoendpoint_address"
                              - "transferredtoendpoint_type"
                              - "queue_arn"
                              - "queue_name"
                              - "queue_enqueuetimestamp"
                              - "queue_dequeuetimestamp"
                              - "queue_duration"
                              - "recording_type"
                              - "recording_status"
                              - "recording_location"
                              - "recording_deletionreason"
                              - "year"
                              - "month"
                              - "day"
                              - "CalculatedAgentDuration"
                              - "CalculatedTotalDuration"
                    Source:
                        PhysicalTableId: "da358775-8dd8-4a08-8d04-224b0c009028"
            ImportMode: "SPICE"
            FieldFolders: {}
            Permissions:
              -
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:UpdateDataSetPermissions"
                  - "quicksight:DescribeDataSet"
                  - "quicksight:DescribeDataSetPermissions"
                  - "quicksight:PassDataSet"
                  - "quicksight:DescribeIngestion"
                  - "quicksight:ListIngestions"
                  - "quicksight:UpdateDataSet"
                  - "quicksight:DeleteDataSet"
                  - "quicksight:CreateIngestion"
                  - "quicksight:CancelIngestion"



    QuickSightAnalysisCtr:
        Type: AWS::QuickSight::Analysis
        Properties:
            AnalysisId: !Sub "${ProjectName}-CtrAnalysis"
            Name: !Sub "${ProjectName}-CtrAnalysis"
            AwsAccountId: !Ref AWS::AccountId
            SourceEntity:
                SourceTemplate:
                    DataSetReferences:
                      - 
                        DataSetArn: !GetAtt QuickSightDataSetCtr.Arn
                        DataSetPlaceholder: "Connect CTR Dataset"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetCtrWithAttributes.Arn
                        DataSetPlaceholder: "Connect CTR with Contact Attr Dataset"
                    Arn: "arn:aws:quicksight:us-west-2:111530706466:template/quicksightblog103124-CtrTemplate"
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:RestoreAnalysis"
                  - "quicksight:UpdateAnalysisPermissions"
                  - "quicksight:DeleteAnalysis"
                  - "quicksight:DescribeAnalysisPermissions"
                  - "quicksight:QueryAnalysis"
                  - "quicksight:DescribeAnalysis"
                  - "quicksight:UpdateAnalysis"


    QuickSightTemplateCtr:
        Type: "AWS::QuickSight::Template"
        Properties:
            TemplateId: !Sub "${ProjectName}-CtrTemplate"
            Name: !Sub "${ProjectName}-CtrTemplate"
            AwsAccountId: !Ref AWS::AccountId
            VersionDescription: "1"
            SourceEntity:
                SourceAnalysis:
                    DataSetReferences:
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetCtr.Arn
                        DataSetPlaceholder: "Connect CTR Dataset"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetCtrWithAttributes.Arn
                        DataSetPlaceholder: "Connect CTR with Contact Attr Dataset"
                    Arn: !GetAtt QuickSightAnalysisCtr.Arn
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:DescribeTemplate"


    QuickSightDashboardCtr:
        Type: "AWS::QuickSight::Dashboard"
        Properties:
            DashboardId: !Sub "${ProjectName}-CtrDashboard"
            Name: !Sub "${ProjectName}-CtrDashboard"
            AwsAccountId: !Ref AWS::AccountId
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:DescribeDashboard"
                  - "quicksight:ListDashboardVersions"
                  - "quicksight:UpdateDashboardPermissions"
                  - "quicksight:QueryDashboard"
                  - "quicksight:UpdateDashboard"
                  - "quicksight:DeleteDashboard"
                  - "quicksight:DescribeDashboardPermissions"
                  - "quicksight:UpdateDashboardPublishedVersion"
            SourceEntity:
            VersionDescription: "1"
            DashboardPublishOptions:
                AdHocFilteringOption:
                    AvailabilityStatus: "DISABLED"
                ExportToCSVOption:
                    AvailabilityStatus: "ENABLED"
                SheetControlsOption:
                    VisibilityState: "EXPANDED"
            SourceEntity:
                SourceTemplate:
                    DataSetReferences:
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetCtr.Arn
                        DataSetPlaceholder: "Connect CTR Dataset"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetCtrWithAttributes.Arn
                        DataSetPlaceholder: "Connect CTR with Contact Attr Dataset"
                    Arn: !GetAtt QuickSightTemplateCtr.Arn 


    ViewContactLensDenormalized:
        Type: "AWS::Glue::Table"
        Properties:
            DatabaseName: !Sub "${ProjectName}" 
            CatalogId: !Ref "AWS::AccountId"
            TableInput:
                ViewOriginalText: !Join
                  - ''
                  - - '/* Presto View: '
                    - Fn::Base64: 
                        Fn::Sub: |
                          {"originalSql":"SELECT\n  contactlens_data.accountid\n, contactlens_data.channel\n, contactlens_data.contentmetadata.output contentmetadata_output\n, contactlens_data.jobstatus\n, contactlens_data.languagecode\n, contactlens_data.conversationcharacteristics.sentiment.overallsentiment.customer customer_sentiment\n, contactlens_data.conversationcharacteristics.sentiment.overallsentiment.agent agent_sentiment\n, (contactlens_data.conversationcharacteristics.totalconversationdurationmillis / 1000) totalconversationduration\n, contactlens_data.customermetadata.contactid contactid\n, contactlens_data.customermetadata.instanceid\n, contactlens_data.conversationcharacteristics.talkspeed.detailsbyparticipant.agent.averagewordsperminute agent_averagewordsperminute\n, contactlens_data.conversationcharacteristics.talkspeed.detailsbyparticipant.customer.averagewordsperminute customer_averagewordsperminute\n, contactlens_data.conversationcharacteristics.talktime.totaltimemillis\n, (contactlens_data.conversationcharacteristics.talktime.detailsbyparticipant.agent.totaltimemillis / 1000) agent_talktimeduration\n, (contactlens_data.conversationcharacteristics.talktime.detailsbyparticipant.customer.totaltimemillis / 1000) customer_talktimeduration\n, (contactlens_data.conversationcharacteristics.nontalktime.totaltimemillis / 1000) nontalktimeduration\n, contactlens_data.year\n, contactlens_data.month\n, contactlens_data.day\nFROM\n  \"${ProjectName}\".\"connect_contactlens\" contactlens_data\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"accountid","type":"varchar"},{"name":"channel","type":"varchar"},{"name":"contentmetadata_output","type":"varchar"},{"name":"jobstatus","type":"varchar"},{"name":"languagecode","type":"varchar"},{"name":"customer_sentiment","type":"real"},{"name":"agent_sentiment","type":"real"},{"name":"totalconversationduration","type":"integer"},{"name":"contactid","type":"varchar"},{"name":"instanceid","type":"varchar"},{"name":"agent_averagewordsperminute","type":"integer"},{"name":"customer_averagewordsperminute","type":"integer"},{"name":"totaltimemillis","type":"integer"},{"name":"agent_talktimeduration","type":"integer"},{"name":"customer_talktimeduration","type":"integer"},{"name":"nontalktimeduration","type":"integer"},{"name":"year","type":"integer"},{"name":"month","type":"integer"},{"name":"day","type":"integer"}]}
                    - ' */'
                TableType: "VIRTUAL_VIEW"
                Parameters:
                    comment: "Presto View"
                    presto_view: "true"
                ViewExpandedText: "/* Presto View */"
                StorageDescriptor:
                    Columns:
                      -
                        Name: "accountid"
                        Type: "string"
                      -
                        Name: "channel"
                        Type: "string"
                      -
                        Name: "contentmetadata_output"
                        Type: "string"
                      -
                        Name: "jobstatus"
                        Type: "string"
                      -
                        Name: "languagecode"
                        Type: "string"
                      -
                        Name: "customer_sentiment"
                        Type: "float"
                      -
                        Name: "agent_sentiment"
                        Type: "float"
                      -
                        Name: "totalconversationduration"
                        Type: "int"
                      -
                        Name: "contactid"
                        Type: "string"
                      -
                        Name: "instanceid"
                        Type: "string"
                      -
                        Name: "agent_averagewordsperminute"
                        Type: "int"
                      -
                        Name: "customer_averagewordsperminute"
                        Type: "int"
                      -
                        Name: "totaltimemillis"
                        Type: "int"
                      -
                        Name: "agent_talktimeduration"
                        Type: "int"
                      -
                        Name: "customer_talktimeduration"
                        Type: "int"
                      -
                        Name: "nontalktimeduration"
                        Type: "int"
                      -
                        Name: "year"
                        Type: "int"
                      -
                        Name: "month"
                        Type: "int"
                      -
                        Name: "day"
                        Type: "int"
                    Location: ""
                    Compressed: false
                    NumberOfBuckets: 0
                    SerdeInfo: {}
                    StoredAsSubDirectories: false
                Retention: 0
                Name: "connect_contactlens_denormalized"



    ViewConnectContactlensMatchedcategories:
        Type: "AWS::Glue::Table"
        Properties:
            DatabaseName: !Sub "${ProjectName}" 
            CatalogId: !Ref "AWS::AccountId"
            TableInput:
                ViewOriginalText: !Join
                  - ''
                  - - '/* Presto View: '
                    - Fn::Base64: 
                        Fn::Sub: |
                          {"originalSql":"SELECT\n  contactlens_data.customermetadata.contactid contactid\n, (contactlens_data.conversationcharacteristics.totalconversationdurationmillis / 1000) totalconversationduration\n, matchedcategories_data matched_category\nFROM\n  \"${ProjectName}\".\"connect_contactlens\" contactlens_data\n, UNNEST(contactlens_data.categories.matchedcategories) t (matchedcategories_data)\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"contactid","type":"varchar"},{"name":"totalconversationduration","type":"integer"},{"name":"matched_category","type":"varchar"}]}
                    - ' */'
                TableType: "VIRTUAL_VIEW"
                Parameters:
                    comment: "Presto View"
                    presto_view: "true"
                ViewExpandedText: "/* Presto View */"
                StorageDescriptor:
                    Columns:
                      -
                        Name: "contactid"
                        Type: "string"
                      -
                        Name: "totalconversationduration"
                        Type: "int"
                      -
                        Name: "matched_category"
                        Type: "string"
                    Location: ""
                    Compressed: false
                    NumberOfBuckets: 0
                    SerdeInfo: {}
                    StoredAsSubDirectories: false
                Retention: 0
                Name: "connect_contactlens_matchedcategories"


    QuickSightDataSetContactLens:
        Type: "AWS::QuickSight::DataSet"
        Condition: CreateContactLensDashboardResources
        Properties:
            DataSetId: !Sub "${ProjectName}-Connect-CL-on-CTR"
            Name: !Sub "${ProjectName}-Connect CL on CTR"
            AwsAccountId: !Ref AWS::AccountId
            PhysicalTableMap:
                3d3e1e77-01a0-4025-a305-e33b44d0fda5:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_ctr_denormalized"
                        InputColumns:
                          -
                            Name: "initiationtimestamp"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_connectedtoagenttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_arn"
                            Type: "STRING"
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "queue_duration"
                            Type: "INTEGER"
                          -
                            Name: "year"
                            Type: "INTEGER"
                          -
                            Name: "initialcontactid"
                            Type: "STRING"
                          -
                            Name: "queue_enqueuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_numberofholds"
                            Type: "INTEGER"
                          -
                            Name: "systemendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_agentinteractionduration"
                            Type: "INTEGER"
                          -
                            Name: "channel"
                            Type: "STRING"
                          -
                            Name: "agent_routingprofile_name"
                            Type: "STRING"
                          -
                            Name: "customerendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkendtimestamp"
                            Type: "STRING"
                          -
                            Name: "disconnecttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_hierarchygroups"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkduration"
                            Type: "INTEGER"
                          -
                            Name: "lastupdatetimestamp"
                            Type: "STRING"
                          -
                            Name: "transfercompletedtimestamp"
                            Type: "STRING"
                          -
                            Name: "awsaccountid"
                            Type: "STRING"
                          -
                            Name: "day"
                            Type: "INTEGER"
                          -
                            Name: "campaign_id"
                            Type: "STRING"
                          -
                            Name: "instancearn"
                            Type: "STRING"
                          -
                            Name: "recording_location"
                            Type: "STRING"
                          -
                            Name: "agentconnectionattempts"
                            Type: "INTEGER"
                          -
                            Name: "agent_username"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_address"
                            Type: "STRING"
                          -
                            Name: "queue_dequeuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_longestholdduration"
                            Type: "INTEGER"
                          -
                            Name: "connectedtosystemtimestamp"
                            Type: "STRING"
                          -
                            Name: "recording_status"
                            Type: "STRING"
                          -
                            Name: "recording_deletionreason"
                            Type: "STRING"
                          -
                            Name: "awscontacttracerecordformatversion"
                            Type: "STRING"
                          -
                            Name: "disconnectreason"
                            Type: "STRING"
                          -
                            Name: "systemendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_customerholdduration"
                            Type: "INTEGER"
                          -
                            Name: "queue_name"
                            Type: "STRING"
                          -
                            Name: "nextcontactid"
                            Type: "STRING"
                          -
                            Name: "month"
                            Type: "INTEGER"
                          -
                            Name: "customerendpoint_type"
                            Type: "STRING"
                          -
                            Name: "recording_type"
                            Type: "STRING"
                          -
                            Name: "initiationmethod"
                            Type: "STRING"
                          -
                            Name: "queue_arn"
                            Type: "STRING"
                          -
                            Name: "previouscontactid"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkstarttimestamp"
                            Type: "STRING"
                          -
                            Name: "scheduledtimestamp"
                            Type: "STRING"
                e02e6673-4dfe-44d3-bdd3-b80c4349172e:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_contactlens_denormalized"
                        InputColumns:
                          -
                            Name: "nontalktimeduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_sentiment"
                            Type: "DECIMAL"
                          -
                            Name: "customer_averagewordsperminute"
                            Type: "INTEGER"
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "year"
                            Type: "INTEGER"
                          -
                            Name: "jobstatus"
                            Type: "STRING"
                          -
                            Name: "channel"
                            Type: "STRING"
                          -
                            Name: "accountid"
                            Type: "STRING"
                          -
                            Name: "languagecode"
                            Type: "STRING"
                          -
                            Name: "instanceid"
                            Type: "STRING"
                          -
                            Name: "customer_talktimeduration"
                            Type: "INTEGER"
                          -
                            Name: "month"
                            Type: "INTEGER"
                          -
                            Name: "agent_talktimeduration"
                            Type: "INTEGER"
                          -
                            Name: "totalconversationduration"
                            Type: "INTEGER"
                          -
                            Name: "agent_averagewordsperminute"
                            Type: "INTEGER"
                          -
                            Name: "day"
                            Type: "INTEGER"
                          -
                            Name: "contentmetadata_output"
                            Type: "STRING"
                          -
                            Name: "totaltimemillis"
                            Type: "INTEGER"
                          -
                            Name: "customer_sentiment"
                            Type: "DECIMAL"
            LogicalTableMap:
                a166a3c8-d5b3-494c-8b3b-4d496f602b9b:
                    Alias: "Intermediate Table"
                    DataTransforms:
                      -
                        ProjectOperation:
                            ProjectedColumns:
                              - "awsaccountid"
                              - "awscontacttracerecordformatversion"
                              - "agent_arn"
                              - "agent_aftercontactworkduration"
                              - "agent_aftercontactworkendtimestamp"
                              - "agent_aftercontactworkstarttimestamp"
                              - "agent_agentinteractionduration"
                              - "agent_connectedtoagenttimestamp"
                              - "agent_customerholdduration"
                              - "agent_hierarchygroups"
                              - "agent_longestholdduration"
                              - "agent_numberofholds"
                              - "agent_routingprofile_name"
                              - "agent_username"
                              - "agentconnectionattempts"
                              - "campaign_id"
                              - "channel"
                              - "connectedtosystemtimestamp"
                              - "contactid"
                              - "customerendpoint_address"
                              - "customerendpoint_type"
                              - "disconnectreason"
                              - "disconnecttimestamp"
                              - "initialcontactid"
                              - "initiationmethod"
                              - "initiationtimestamp"
                              - "instancearn"
                              - "lastupdatetimestamp"
                              - "nextcontactid"
                              - "previouscontactid"
                              - "scheduledtimestamp"
                              - "systemendpoint_address"
                              - "systemendpoint_type"
                              - "transfercompletedtimestamp"
                              - "transferredtoendpoint_address"
                              - "transferredtoendpoint_type"
                              - "queue_arn"
                              - "queue_name"
                              - "queue_enqueuetimestamp"
                              - "queue_dequeuetimestamp"
                              - "queue_duration"
                              - "recording_type"
                              - "recording_status"
                              - "recording_location"
                              - "recording_deletionreason"
                              - "year"
                              - "month"
                              - "day"
                              - "accountid"
                              - "channel[connect_contactlens_denormalized]"
                              - "contentmetadata_output"
                              - "jobstatus"
                              - "languagecode"
                              - "customer_sentiment"
                              - "agent_sentiment"
                              - "totalconversationduration"
                              - "contactid[connect_contactlens_denormalized]"
                              - "instanceid"
                              - "agent_averagewordsperminute"
                              - "customer_averagewordsperminute"
                              - "totaltimemillis"
                              - "agent_talktimeduration"
                              - "customer_talktimeduration"
                              - "nontalktimeduration"
                              - "year[connect_contactlens_denormalized]"
                              - "month[connect_contactlens_denormalized]"
                              - "day[connect_contactlens_denormalized]"
                    Source:
                        JoinInstruction:
                            LeftOperand: "f1357500-8400-4c1e-a326-963b301c4f69"
                            RightOperand: "ecb5ab59-de7c-459b-b2c1-fb50d89ae9a2"
                            Type: "INNER"
                            OnClause: "{contactid} = {contactid[connect_contactlens_denormalized]}"
                ecb5ab59-de7c-459b-b2c1-fb50d89ae9a2:
                    Alias: "connect_contactlens_denormalized"
                    DataTransforms:
                      -
                        RenameColumnOperation:
                            ColumnName: "day"
                            NewColumnName: "day[connect_contactlens_denormalized]"
                      -
                        RenameColumnOperation:
                            ColumnName: "month"
                            NewColumnName: "month[connect_contactlens_denormalized]"
                      -
                        RenameColumnOperation:
                            ColumnName: "year"
                            NewColumnName: "year[connect_contactlens_denormalized]"
                      -
                        RenameColumnOperation:
                            ColumnName: "contactid"
                            NewColumnName: "contactid[connect_contactlens_denormalized]"
                      -
                        RenameColumnOperation:
                            ColumnName: "channel"
                            NewColumnName: "channel[connect_contactlens_denormalized]"
                    Source:
                        PhysicalTableId: "e02e6673-4dfe-44d3-bdd3-b80c4349172e"
                f1357500-8400-4c1e-a326-963b301c4f69:
                    Alias: "connect_ctr_denormalized"
                    DataTransforms:
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkendtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkstarttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_connectedtoagenttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "connectedtosystemtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "disconnecttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "initiationtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "lastupdatetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "transfercompletedtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_enqueuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_dequeuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                    Source:
                        PhysicalTableId: "3d3e1e77-01a0-4025-a305-e33b44d0fda5"
            ImportMode: "SPICE"
            FieldFolders: {}
            Permissions:
              -
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:UpdateDataSetPermissions"
                  - "quicksight:DescribeDataSet"
                  - "quicksight:DescribeDataSetPermissions"
                  - "quicksight:PassDataSet"
                  - "quicksight:DescribeIngestion"
                  - "quicksight:ListIngestions"
                  - "quicksight:UpdateDataSet"
                  - "quicksight:DeleteDataSet"
                  - "quicksight:CreateIngestion"
                  - "quicksight:CancelIngestion"


    QuickSightDataSetContactLensCategories:
        Type: "AWS::QuickSight::DataSet"
        Condition: CreateContactLensDashboardResources
        Properties:
            DataSetId: !Sub "${ProjectName}-Connect-Categories-on-CTR"
            Name: !Sub "${ProjectName}-Connect Categories on CTR"
            AwsAccountId: !Ref AWS::AccountId
            PhysicalTableMap:
                7b3d42ee-bbae-4d51-adf2-726e67fb0b70:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_contactlens_matchedcategories"
                        InputColumns:
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "totalconversationduration"
                            Type: "INTEGER"
                          -
                            Name: "matched_category"
                            Type: "STRING"
                7f0d6b15-95d5-47e9-b1ee-9460ed4df7f9:
                    RelationalTable:
                        DataSourceArn: !GetAtt QuickSightDataSourceS3ThroughAthena.Arn
                        Catalog: "AwsDataCatalog"
                        Schema: !Sub "${ProjectName}" 
                        Name: "connect_ctr_denormalized"
                        InputColumns:
                          -
                            Name: "initiationtimestamp"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_connectedtoagenttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_arn"
                            Type: "STRING"
                          -
                            Name: "contactid"
                            Type: "STRING"
                          -
                            Name: "queue_duration"
                            Type: "INTEGER"
                          -
                            Name: "year"
                            Type: "INTEGER"
                          -
                            Name: "initialcontactid"
                            Type: "STRING"
                          -
                            Name: "queue_enqueuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_numberofholds"
                            Type: "INTEGER"
                          -
                            Name: "systemendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_agentinteractionduration"
                            Type: "INTEGER"
                          -
                            Name: "channel"
                            Type: "STRING"
                          -
                            Name: "agent_routingprofile_name"
                            Type: "STRING"
                          -
                            Name: "customerendpoint_address"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkendtimestamp"
                            Type: "STRING"
                          -
                            Name: "disconnecttimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_hierarchygroups"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkduration"
                            Type: "INTEGER"
                          -
                            Name: "lastupdatetimestamp"
                            Type: "STRING"
                          -
                            Name: "transfercompletedtimestamp"
                            Type: "STRING"
                          -
                            Name: "awsaccountid"
                            Type: "STRING"
                          -
                            Name: "day"
                            Type: "INTEGER"
                          -
                            Name: "campaign_id"
                            Type: "STRING"
                          -
                            Name: "instancearn"
                            Type: "STRING"
                          -
                            Name: "recording_location"
                            Type: "STRING"
                          -
                            Name: "agentconnectionattempts"
                            Type: "INTEGER"
                          -
                            Name: "agent_username"
                            Type: "STRING"
                          -
                            Name: "transferredtoendpoint_address"
                            Type: "STRING"
                          -
                            Name: "queue_dequeuetimestamp"
                            Type: "STRING"
                          -
                            Name: "agent_longestholdduration"
                            Type: "INTEGER"
                          -
                            Name: "connectedtosystemtimestamp"
                            Type: "STRING"
                          -
                            Name: "recording_status"
                            Type: "STRING"
                          -
                            Name: "recording_deletionreason"
                            Type: "STRING"
                          -
                            Name: "awscontacttracerecordformatversion"
                            Type: "STRING"
                          -
                            Name: "disconnectreason"
                            Type: "STRING"
                          -
                            Name: "systemendpoint_type"
                            Type: "STRING"
                          -
                            Name: "agent_customerholdduration"
                            Type: "INTEGER"
                          -
                            Name: "queue_name"
                            Type: "STRING"
                          -
                            Name: "nextcontactid"
                            Type: "STRING"
                          -
                            Name: "month"
                            Type: "INTEGER"
                          -
                            Name: "customerendpoint_type"
                            Type: "STRING"
                          -
                            Name: "recording_type"
                            Type: "STRING"
                          -
                            Name: "initiationmethod"
                            Type: "STRING"
                          -
                            Name: "queue_arn"
                            Type: "STRING"
                          -
                            Name: "previouscontactid"
                            Type: "STRING"
                          -
                            Name: "agent_aftercontactworkstarttimestamp"
                            Type: "STRING"
                          -
                            Name: "scheduledtimestamp"
                            Type: "STRING"
            LogicalTableMap:
                3f0c3829-6cf6-40ee-97b1-5270d32002ef:
                    Alias: "Intermediate Table"
                    DataTransforms:
                      -
                        CreateColumnsOperation:
                            Columns:
                              -
                                ColumnName: "CalculatedAgentDuration"
                                ColumnId: "535387cd-a96b-40cc-9780-b4db868b7f4a"
                                Expression: "dateDiff({agent_connectedtoagenttimestamp},{disconnecttimestamp},\"SS\")"
                      -
                        CreateColumnsOperation:
                            Columns:
                              -
                                ColumnName: "CalculatedTotalDuration"
                                ColumnId: "00ff9b6f-5dd1-4ffb-a9d1-58847e7983b3"
                                Expression: "dateDiff({initiationtimestamp},{disconnecttimestamp},\"SS\")"
                      -
                        ProjectOperation:
                            ProjectedColumns:
                              - "awsaccountid"
                              - "awscontacttracerecordformatversion"
                              - "agent_arn"
                              - "agent_aftercontactworkduration"
                              - "agent_aftercontactworkendtimestamp"
                              - "agent_aftercontactworkstarttimestamp"
                              - "agent_agentinteractionduration"
                              - "agent_connectedtoagenttimestamp"
                              - "agent_customerholdduration"
                              - "agent_hierarchygroups"
                              - "agent_longestholdduration"
                              - "agent_numberofholds"
                              - "agent_routingprofile_name"
                              - "agent_username"
                              - "agentconnectionattempts"
                              - "campaign_id"
                              - "channel"
                              - "connectedtosystemtimestamp"
                              - "contactid"
                              - "customerendpoint_address"
                              - "customerendpoint_type"
                              - "disconnectreason"
                              - "disconnecttimestamp"
                              - "initialcontactid"
                              - "initiationmethod"
                              - "initiationtimestamp"
                              - "instancearn"
                              - "lastupdatetimestamp"
                              - "nextcontactid"
                              - "previouscontactid"
                              - "scheduledtimestamp"
                              - "systemendpoint_address"
                              - "systemendpoint_type"
                              - "transfercompletedtimestamp"
                              - "transferredtoendpoint_address"
                              - "transferredtoendpoint_type"
                              - "queue_arn"
                              - "queue_name"
                              - "queue_enqueuetimestamp"
                              - "queue_dequeuetimestamp"
                              - "queue_duration"
                              - "recording_type"
                              - "recording_status"
                              - "recording_location"
                              - "recording_deletionreason"
                              - "year"
                              - "month"
                              - "day"
                              - "contactid[connect_contactlens_matchedcategories]"
                              - "totalconversationduration"
                              - "matched_category"
                              - "CalculatedAgentDuration"
                              - "CalculatedTotalDuration"
                    Source:
                        JoinInstruction:
                            LeftOperand: "da358775-8dd8-4a08-8d04-224b0c009028"
                            RightOperand: "6f1dc5cb-0167-48dd-84bf-73289f87203a"
                            Type: "RIGHT"
                            OnClause: "{contactid} = {contactid[connect_contactlens_matchedcategories]}"
                6f1dc5cb-0167-48dd-84bf-73289f87203a:
                    Alias: "connect_contactlens_matchedcategories"
                    DataTransforms:
                      -
                        RenameColumnOperation:
                            ColumnName: "contactid"
                            NewColumnName: "contactid[connect_contactlens_matchedcategories]"
                    Source:
                        PhysicalTableId: "7b3d42ee-bbae-4d51-adf2-726e67fb0b70"
                da358775-8dd8-4a08-8d04-224b0c009028:
                    Alias: "connect_ctr_denormalized"
                    DataTransforms:
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkendtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_aftercontactworkstarttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "agent_connectedtoagenttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "connectedtosystemtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "disconnecttimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "initiationtimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "lastupdatetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_enqueuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                      -
                        CastColumnTypeOperation:
                            ColumnName: "queue_dequeuetimestamp"
                            NewColumnType: "DATETIME"
                            Format: "yyyy-MM-dd'T'HH:mm:ss'Z'"
                    Source:
                        PhysicalTableId: "7f0d6b15-95d5-47e9-b1ee-9460ed4df7f9"
            ImportMode: "SPICE"
            FieldFolders: {}
            Permissions:
              -
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:UpdateDataSetPermissions"
                  - "quicksight:DescribeDataSet"
                  - "quicksight:DescribeDataSetPermissions"
                  - "quicksight:PassDataSet"
                  - "quicksight:DescribeIngestion"
                  - "quicksight:ListIngestions"
                  - "quicksight:UpdateDataSet"
                  - "quicksight:DeleteDataSet"
                  - "quicksight:CreateIngestion"
                  - "quicksight:CancelIngestion"




    QuickSightAnalysisContactLens:
        Type: AWS::QuickSight::Analysis
        Condition: CreateContactLensDashboardResources
        Properties:
            AnalysisId: !Sub "${ProjectName}-ContactLensAnalysis"
            Name: !Sub "${ProjectName}-ContactLensAnalysis"
            AwsAccountId: !Ref AWS::AccountId
            SourceEntity:
                SourceTemplate:
                    DataSetReferences:
                      - 
                        DataSetArn: !GetAtt QuickSightDataSetContactLens.Arn
                        DataSetPlaceholder: "Connect CL on CTR"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetContactLensCategories.Arn
                        DataSetPlaceholder:  "Connect Categories on CTR"
                    Arn: "arn:aws:quicksight:us-west-2:111530706466:template/quicksightblog103124-ContactLensTemplate"
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:RestoreAnalysis"
                  - "quicksight:UpdateAnalysisPermissions"
                  - "quicksight:DeleteAnalysis"
                  - "quicksight:DescribeAnalysisPermissions"
                  - "quicksight:QueryAnalysis"
                  - "quicksight:DescribeAnalysis"
                  - "quicksight:UpdateAnalysis"


    QuickSightTemplateContactLens:
        Type: "AWS::QuickSight::Template"
        Condition: CreateContactLensDashboardResources
        Properties:
            TemplateId: !Sub "${ProjectName}-ContactLensTemplate"
            Name: !Sub "${ProjectName}-ContactLensTemplate"
            AwsAccountId: !Ref AWS::AccountId
            VersionDescription: "1"
            SourceEntity:
                SourceAnalysis:
                    DataSetReferences:
                      - 
                        DataSetArn: !GetAtt QuickSightDataSetContactLens.Arn
                        DataSetPlaceholder: "Connect CL on CTR"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetContactLensCategories.Arn
                        DataSetPlaceholder:  "Connect Categories on CTR"
                    Arn: !GetAtt QuickSightAnalysisContactLens.Arn
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:DescribeTemplate"

    QuickSightDashboardContactLens:
        Type: "AWS::QuickSight::Dashboard"
        Condition: CreateContactLensDashboardResources
        Properties:
            DashboardId: !Sub "${ProjectName}-ContactLensDashboard"
            Name: !Sub "${ProjectName}-ContactLensDashboard"
            AwsAccountId: !Ref AWS::AccountId
            Permissions:
              - 
                Principal: !Sub "arn:aws:quicksight:${AWS::Region}:${AWS::AccountId}:group/default/${ProjectName}-Admins"
                Actions:
                  - "quicksight:DescribeDashboard"
                  - "quicksight:ListDashboardVersions"
                  - "quicksight:UpdateDashboardPermissions"
                  - "quicksight:QueryDashboard"
                  - "quicksight:UpdateDashboard"
                  - "quicksight:DeleteDashboard"
                  - "quicksight:DescribeDashboardPermissions"
                  - "quicksight:UpdateDashboardPublishedVersion"
            SourceEntity:
            VersionDescription: "1"
            DashboardPublishOptions:
                AdHocFilteringOption:
                    AvailabilityStatus: "DISABLED"
                ExportToCSVOption:
                    AvailabilityStatus: "ENABLED"
                SheetControlsOption:
                    VisibilityState: "EXPANDED"
            SourceEntity:
                SourceTemplate:
                    DataSetReferences:
                      - 
                        DataSetArn: !GetAtt QuickSightDataSetContactLens.Arn
                        DataSetPlaceholder: "Connect CL on CTR"
                      -   
                        DataSetArn: !GetAtt QuickSightDataSetContactLensCategories.Arn
                        DataSetPlaceholder:  "Connect Categories on CTR"
                    Arn: !GetAtt QuickSightTemplateContactLens.Arn 


