AWSTemplateFormatVersion: "2010-09-09" Description: "This is the template for creating a solution to analyze PCA Solution output using Amazon Athena as described in the blog" Parameters: PcaOutputBucket: Type: "String" Description: Enter the name of the outputbucket created with PCA Stack deployment. You can find this value by looking at the stack outputs for key named 'OutputBucket' ( typically named as 'postcallanalytics-outputbucket-...' ) MinLength : 3 AllowedPattern : "^[a-z0-9-]*$" PcaWebAppHostAddress: Type: "String" Default: "" Description: Enter the base of the PCA portal URL hostname. e.g. d2oco19w12345.cloudfront.net 'https://' prefix will be added automatically PcaWebAppCallPathPrefix: Type: "String" Default: "/dashboard/parsedFiles/" Description: Enter the PCA portal path e.g. /dashboard/parsedFiles/ PcaGlueCatalogDatabaseName: Type: "String" Default: "pca" Description: Enter the DatabaseName parameter used while deploying PCA stack. Default value for PCA stack is 'pca' MinLength : 1 Resources: EventBridgePolicyForPca: Type: "AWS::IAM::ManagedPolicy" Properties: Path: "/service-role/" PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "firehose:PutRecord", "firehose:PutRecordBatch" ], "Resource": [ "${KinesisFirehoseDeliveryStreamForPca.Arn}" ] } ] } EventBridgeRoleForPca: Type: "AWS::IAM::Role" DependsOn: EventBridgePolicyForPca Properties: Path: "/service-role/" AssumeRolePolicyDocument: "{\"Version\":\"2012-10-17\",\"Statement\":[{\"Effect\":\"Allow\",\"Principal\":{\"Service\":\"events.amazonaws.com\"},\"Action\":\"sts:AssumeRole\"}]}" MaxSessionDuration: 3600 ManagedPolicyArns: - !Ref EventBridgePolicyForPca EventsRuleForPca: Type: "AWS::Events::Rule" Properties: Name: !Sub "${AWS::StackName}_Pca" EventPattern: !Sub | { "source": ["aws.s3"], "detail-type": ["Object Created"], "detail": { "bucket": { "name": ["${PcaOutputBucket}"] }, "object": { "key": [{ "prefix": "parsedFiles/" }] } } } State: "ENABLED" Targets: - Arn: !GetAtt KinesisFirehoseDeliveryStreamForPca.Arn Id: "Id6e2fb565-24e4-48af-92ca-26a8ec7e15b0" RoleArn: !GetAtt EventBridgeRoleForPca.Arn EventBusName: "default" ReadPcaOutputRecordsPolicy: Type: "AWS::IAM::ManagedPolicy" Properties: Path: "/" PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "s3:GetObject", "Resource": "arn:aws:s3:::${PcaOutputBucket}/parsedFiles/*" } ] } FirehoseTransformationLambdaForPcaBasicPolicy: Type: "AWS::IAM::ManagedPolicy" Properties: Path: "/" PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Action": [ "logs:CreateLogStream", "logs:CreateLogGroup", "logs:PutLogEvents" ], "Resource": "arn:aws:logs:${AWS::Region}:${AWS::AccountId}:log-group:/aws/lambda/*", "Effect": "Allow", "Sid": "WriteCloudWatchLogs" } ] } FirehoseTransformationLambdaRoleForPca: Type: "AWS::IAM::Role" Properties: Path: "/service-role/" AssumeRolePolicyDocument: !Sub | { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Principal":{ "Service":"lambda.amazonaws.com" }, "Action":"sts:AssumeRole" } ] } MaxSessionDuration: 3600 ManagedPolicyArns: - !Ref FirehoseTransformationLambdaForPcaBasicPolicy - !Ref ReadPcaOutputRecordsPolicy TransformationLambdaFunctionForPca: Type: "AWS::Lambda::Function" Properties: Description: "Kinesis firehose uses this processor for custom transformation logic." Environment: Variables: PcaUrlBase: !Sub "${PcaWebAppHostAddress}${PcaWebAppCallPathPrefix}" PartitionFormat: "%Y/%m/%d" FunctionName: !Sub "${AWS::StackName}-TransformPca" Handler: "index.lambda_handler" Code: ZipFile: | import base64 import json import boto3 import urllib.parse import uuid import os from datetime import datetime print('Loading function') s3 = boto3.resource('s3') urlPrefix = os.environ['PcaUrlBase'] partitionFormat = os.environ['PartitionFormat'] class FileToBeIgnored(Exception): """Raised when the input is to be ignored - e.g. we ignore files other than json """ def __init__(self, key, message=" will not be processed as is not json or it is in an ignored directory"): self.key = key self.message = message super().__init__(self.message) def __str__(self): return f'{self.key} -> {self.message}' class FileProcessingError(Exception): """Raised when we try to process the file """ def __init__(self, key, message="Error during file processing"): self.key = key self.message = message super().__init__(self.message) def __str__(self): return f'{self.key} -> {self.message}' def transformRecord(recordToTransform,recordId): ## This method receives the record to transform and returns the transformed record. #print (recordToTransform) conversationAnalytics = json.loads(recordToTransform)['ConversationAnalytics'] agentMark = "Undefined" customerMark = "Undefined" for speaker in conversationAnalytics['SpeakerLabels']: if speaker['DisplayText']=="Agent": agentMark = speaker['Speaker'] if speaker['DisplayText']=="Customer": customerMark = speaker['Speaker'] conversationTime = conversationAnalytics['ConversationTime'] if len (conversationTime) >19: conversationTime = conversationTime[ 0 : 19 ] transformedRecord= { 'guid': conversationAnalytics['GUID'] if conversationAnalytics['GUID'] is not None else uuid.uuid4() , 'agent': conversationAnalytics['Agent'], 'cust': conversationAnalytics['Cust'], 'conversationlocation': conversationAnalytics['ConversationLocation'], 'languagecode': conversationAnalytics['LanguageCode'], 'conversationtime': conversationTime, 'processtime': conversationAnalytics['ProcessTime'], 'duration': float(conversationAnalytics['Duration']), 'customer_sentimentscore': conversationAnalytics['SentimentTrends'][customerMark]['SentimentScore'], 'agent_sentimentscore': conversationAnalytics['SentimentTrends'][agentMark]['SentimentScore'], 'customer_totaltimesecs': conversationAnalytics['SpeakerTime'][customerMark]['TotalTimeSecs'], 'agent_totaltimesecs': conversationAnalytics['SpeakerTime'][agentMark]['TotalTimeSecs'], 'nontalktime': conversationAnalytics['SpeakerTime']['NonTalkTime']['TotalTimeSecs'] if "NonTalkTime" in conversationAnalytics['SpeakerTime'] else None, 'combinedanalyticsgraph': conversationAnalytics['CombinedAnalyticsGraph'] if "CombinedAnalyticsGraph" in conversationAnalytics else None, 'actionitemsdetectedtext': '\n'.join([item['Text'] for item in conversationAnalytics['ActionItemsDetected']]) if "ActionItemsDetected" in conversationAnalytics else None, 'issuesdetectedtext': '\n'.join([item['Text'] for item in conversationAnalytics['IssuesDetected']]) if "IssuesDetected" in conversationAnalytics else None, 'outcomesdetectedtext': '\n'.join([item['Text'] for item in conversationAnalytics['OutcomesDetected']]) if "OutcomesDetected" in conversationAnalytics else None, 'categoriesdetectedtext': '\n'.join([item['Name'] for item in conversationAnalytics['CategoriesDetected']]) if "CategoriesDetected" in conversationAnalytics else None, 'customentities': conversationAnalytics['CustomEntities'] if "CustomEntities" in conversationAnalytics else None, 'categoriesdetected': [item['Name'] for item in conversationAnalytics['CategoriesDetected']] if "CategoriesDetected" in conversationAnalytics else None, 'url_to_display': urlPrefix + conversationAnalytics['SourceInformation'][0]['TranscribeJobInfo']['TranscriptionJobName'] + ".json" if "SourceInformation" in conversationAnalytics else None, } #print (transformedRecord) encodedData = base64.b64encode(json.dumps(transformedRecord).encode("utf-8")) partitiontime = datetime.strptime(conversationTime, '%Y-%m-%d %H:%M:%S').strftime(partitionFormat) partition_keys = { "partitiontime" : partitiontime } #print (partition_keys) return { 'data': encodedData, 'result': 'Ok', 'recordId': recordId, 'metadata': { 'partitionKeys': partition_keys } } def readRecordFromS3AndTransform(s3Event, recordId): ## Get the location of the log file from S3 event key = urllib.parse.unquote_plus(s3Event['detail']['object']['key'], encoding='utf-8') if (key[-4:] != 'json'): raise FileToBeIgnored (key) bucket = s3.Bucket(s3Event['detail']['bucket']['name']) encodedFileContent = bucket.Object(key).get()['Body'].read() ## Get the log content from the file in S3. try: fileContent = encodedFileContent.decode() except Exception as e: print (e) raise FileProcessingError(key) return transformRecord(fileContent,recordId) def processKFHRecord(eachKFHRecord): ## Each Kinesis Firehose record has the incoming record in 'data' field and an id that needs to be returned with transformed response. recordId = eachKFHRecord['recordId'] eachKFHRecordData = json.loads(base64.b64decode(eachKFHRecord['data'])) #print("Record from firehose:" + json.dumps(eachKFHRecordData, indent=2)) if not ('detail' in eachKFHRecordData): ## Drop the test events that are created at the creation of the S3 Event Notification - there is no data in these records return {'result': 'Dropped','recordId': recordId} else: try: return readRecordFromS3AndTransform(eachKFHRecordData, recordId) except FileToBeIgnored as eFileToBeIgnored: print (eFileToBeIgnored) except FileProcessingError as eFileProcessingError: print (eFileProcessingError) except Exception as e: print ('Unknown Exception') print (e) return {'result': 'Dropped','recordId': recordId} def lambda_handler(event, context): #print (json.dumps(event, indent=2)) output = [processKFHRecord(eachKFHRecord) for eachKFHRecord in event['records']] print("{} record(s) processed successfully".format(len(output))) return {'records': output} MemorySize: 128 Role: !GetAtt FirehoseTransformationLambdaRoleForPca.Arn Runtime: "python3.9" Timeout: 600 TracingConfig: Mode: "PassThrough" KinesisFirehoseServicePolicyForPca: Type: "AWS::IAM::ManagedPolicy" DependsOn: GlueTableForPca Properties: Path: "/service-role/" PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Sid": "", "Effect": "Allow", "Action": [ "glue:GetTable", "glue:GetTableVersion", "glue:GetTableVersions" ], "Resource": [ "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:catalog", "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:database/${PcaGlueCatalogDatabaseName}", "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:table/${PcaGlueCatalogDatabaseName}/pca_output" ] }, { "Sid": "", "Effect": "Allow", "Action": [ "s3:AbortMultipartUpload", "s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket", "s3:ListBucketMultipartUploads", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::${PcaOutputBucket}", "arn:aws:s3:::${PcaOutputBucket}/*" ] }, { "Sid": "", "Effect": "Allow", "Action": [ "lambda:InvokeFunction", "lambda:GetFunctionConfiguration" ], "Resource": "arn:aws:lambda:${AWS::Region}:${AWS::AccountId}:function:${TransformationLambdaFunctionForPca}" }, { "Sid": "", "Effect": "Allow", "Action": [ "logs:PutLogEvents" ], "Resource": [ "arn:aws:logs:${AWS::Region}:${AWS::AccountId}:log-group:/aws/kinesisfirehose/${AWS::StackName}-Pca:log-stream:*" ] } ] } KinesisFirehoseServiceRoleForPca: Type: "AWS::IAM::Role" Properties: Path: "/service-role/" AssumeRolePolicyDocument: !Sub | { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Principal":{"Service":"firehose.amazonaws.com"}, "Action":"sts:AssumeRole" } ] } MaxSessionDuration: 3600 ManagedPolicyArns: - !Ref KinesisFirehoseServicePolicyForPca KinesisFirehoseDeliveryStreamForPca: Type: "AWS::KinesisFirehose::DeliveryStream" DependsOn: - GlueTableForPca - TransformationLambdaFunctionForPca Properties: DeliveryStreamName: !Sub "${AWS::StackName}-Pca" DeliveryStreamType: "DirectPut" DeliveryStreamEncryptionConfigurationInput: KeyType: "AWS_OWNED_CMK" ExtendedS3DestinationConfiguration: BucketARN: !Sub "arn:aws:s3:::${PcaOutputBucket}" BufferingHints: SizeInMBs: 128 IntervalInSeconds: 60 CloudWatchLoggingOptions: Enabled: true LogGroupName: !Sub "/aws/kinesisfirehose/${AWS::StackName}-Pca" LogStreamName: "DestinationDelivery" CompressionFormat: "UNCOMPRESSED" DataFormatConversionConfiguration: SchemaConfiguration: RoleARN: !GetAtt KinesisFirehoseServiceRoleForPca.Arn DatabaseName: !Sub "${PcaGlueCatalogDatabaseName}" TableName: "pca_output" Region: !Ref AWS::Region VersionId: "LATEST" InputFormatConfiguration: Deserializer: OpenXJsonSerDe: {} OutputFormatConfiguration: Serializer: ParquetSerDe: {} Enabled: true DynamicPartitioningConfiguration: RetryOptions: DurationInSeconds: 300 Enabled: true EncryptionConfiguration: NoEncryptionConfig: "NoEncryption" Prefix: "pca-output-base/!{partitionKeyFromLambda:partitiontime}/" ErrorOutputPrefix: "pca-output-erroroutputbase/!{timestamp:yyy/MM/dd}/!{firehose:random-string}/!{firehose:error-output-type}/" RoleARN: !GetAtt KinesisFirehoseServiceRoleForPca.Arn ProcessingConfiguration: Enabled: true Processors: - Type: "Lambda" Parameters: - ParameterName: "LambdaArn" ParameterValue: !GetAtt TransformationLambdaFunctionForPca.Arn - ParameterName: "NumberOfRetries" ParameterValue: "3" - ParameterName: "RoleArn" ParameterValue: !GetAtt KinesisFirehoseServiceRoleForPca.Arn - ParameterName: "BufferSizeInMBs" ParameterValue: "0.2" - ParameterName: "BufferIntervalInSeconds" ParameterValue: "60" S3BackupMode: "Disabled" GlueTableForPca: Type: "AWS::Glue::Table" Properties: DatabaseName: !Sub "${PcaGlueCatalogDatabaseName}" CatalogId: !Ref "AWS::AccountId" TableInput: Owner: "hadoop" TableType: "EXTERNAL_TABLE" Parameters: "projection.partitiontimestamp.format": "yyyy/MM/dd" "projection.partitiontimestamp.interval.unit": " DAYS" EXTERNAL: "TRUE" "projection.partitiontimestamp.interval": "1" transient_lastDdlTime: "1668232972" "parquet.compression": "SNAPPY" "projection.partitiontimestamp.type": "date" "projection.enabled": "true" "projection.partitiontimestamp.range": "NOW-2YEARS,NOW+1DAY" "storage.location.template": !Join - '' - - Fn::Sub: "s3://${PcaOutputBucket}/pca-output-base/" - "${partitiontimestamp}" StorageDescriptor: Columns: - Name: "guid" Type: "string" - Name: "agent" Type: "string" - Name: "cust" Type: "string" - Name: "conversationlocation" Type: "string" - Name: "languagecode" Type: "string" - Name: "conversationtime" Type: "string" - Name: "processtime" Type: "string" - Name: "duration" Type: "float" - Name: "customer_sentimentscore" Type: "float" - Name: "agent_sentimentscore" Type: "float" - Name: "customer_totaltimesecs" Type: "float" - Name: "agent_totaltimesecs" Type: "float" - Name: "nontalktime" Type: "float" - Name: "combinedanalyticsgraph" Type: "string" - Name: "actionitemsdetectedtext" Type: "string" - Name: "issuesdetectedtext" Type: "string" - Name: "outcomesdetectedtext" Type: "string" - Name: "categoriesdetectedtext" Type: "string" - Name: "customentities" Type: "array>>" - Name: "categoriesdetected" Type: "array" - Name: "url_to_display" Type: "string" Location: !Sub "s3://${PcaOutputBucket}/pca-output-base" InputFormat: "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat" OutputFormat: "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat" Compressed: false NumberOfBuckets: -1 SerdeInfo: SerializationLibrary: "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe" Parameters: "serialization.format": "1" Parameters: {} SkewedInfo: SkewedColumnValueLocationMaps: {} StoredAsSubDirectories: false PartitionKeys: - Name: "partitiontimestamp" Type: "string" Retention: 0 Name: "pca_output" ViewPcaCategories: Type: "AWS::Glue::Table" DependsOn: "GlueTableForPca" Properties: DatabaseName: !Sub "${PcaGlueCatalogDatabaseName}" CatalogId: !Ref "AWS::AccountId" TableInput: ViewOriginalText: !Join - '' - - '/* Presto View: ' - Fn::Base64: Fn::Sub: | {"originalSql":"SELECT\n partitiontimestamp\n, guid\n, agent\n, cust\n, conversationtime\n, conversationlocation\n, processtime\n, languagecode\n, duration\n, customer_sentimentscore\n, agent_sentimentscore\n, customer_totaltimesecs\n, agent_totaltimesecs\n, nontalktime\n, category\n, url_to_display\nFROM\n (\"pca_output\"\nCROSS JOIN UNNEST(categoriesdetected) t (category))\n","catalog":"awsdatacatalog","schema":"${PcaGlueCatalogDatabaseName}","columns":[{"name":"partitiontimestamp","type":"varchar"},{"name":"guid","type":"varchar"},{"name":"agent","type":"varchar"},{"name":"cust","type":"varchar"},{"name":"conversationtime","type":"varchar"},{"name":"conversationlocation","type":"varchar"},{"name":"processtime","type":"varchar"},{"name":"languagecode","type":"varchar"},{"name":"duration","type":"real"},{"name":"customer_sentimentscore","type":"real"},{"name":"agent_sentimentscore","type":"real"},{"name":"customer_totaltimesecs","type":"real"},{"name":"agent_totaltimesecs","type":"real"},{"name":"nontalktime","type":"real"},{"name":"category","type":"varchar"},{"name":"url_to_display","type":"varchar"}]} - ' */' TableType: "VIRTUAL_VIEW" Parameters: presto_view: "true" comment: "Presto View" ViewExpandedText: "/* Presto View */" StorageDescriptor: Columns: - Name: "partitiontimestamp" Type: "string" - Name: "guid" Type: "string" - Name: "agent" Type: "string" - Name: "cust" Type: "string" - Name: "conversationtime" Type: "string" - Name: "conversationlocation" Type: "string" - Name: "processtime" Type: "string" - Name: "languagecode" Type: "string" - Name: "duration" Type: "float" - Name: "customer_sentimentscore" Type: "float" - Name: "agent_sentimentscore" Type: "float" - Name: "customer_totaltimesecs" Type: "float" - Name: "agent_totaltimesecs" Type: "float" - Name: "nontalktime" Type: "float" - Name: "category" Type: "string" - Name: "url_to_display" Type: "string" Location: "" Compressed: false NumberOfBuckets: 0 SerdeInfo: {} StoredAsSubDirectories: false Retention: 0 Name: "v_pca_categories" ViewPcaCustomEntities: Type: "AWS::Glue::Table" DependsOn: "GlueTableForPca" Properties: DatabaseName: !Sub "${PcaGlueCatalogDatabaseName}" CatalogId: !Ref "AWS::AccountId" TableInput: ViewOriginalText: !Join - '' - - '/* Presto View: ' - Fn::Base64: Fn::Sub: | {"originalSql":"WITH\n customentityset AS (\n SELECT\n partitiontimestamp\n , guid\n , agent\n , cust\n , conversationtime\n , conversationlocation\n , processtime\n , languagecode\n , duration\n , customer_sentimentscore\n , agent_sentimentscore\n , customer_totaltimesecs\n , agent_totaltimesecs\n , nontalktime\n , url_to_display\n , customentity.name customentity_name\n , customentity.\"values\" customentity_values\n FROM\n (\"pca_output\"\n CROSS JOIN UNNEST(customentities) t (customentity))\n) \nSELECT\n customentityset.partitiontimestamp\n, customentityset.guid\n, customentityset.agent\n, customentityset.cust\n, customentityset.conversationtime\n, customentityset.conversationlocation\n, customentityset.processtime\n, customentityset.languagecode\n, customentityset.duration\n, customentityset.customer_sentimentscore\n, customentityset.agent_sentimentscore\n, customentityset.customer_totaltimesecs\n, customentityset.agent_totaltimesecs\n, customentityset.nontalktime\n, customentityset.url_to_display\n, customentityset.customentity_name\n, customentityvalue customentity_value\nFROM\n (customentityset\nCROSS JOIN UNNEST(customentityset.customentity_values) t (customentityvalue))\n","catalog":"awsdatacatalog","schema":"${PcaGlueCatalogDatabaseName}","columns":[{"name":"partitiontimestamp","type":"varchar"},{"name":"guid","type":"varchar"},{"name":"agent","type":"varchar"},{"name":"cust","type":"varchar"},{"name":"conversationtime","type":"varchar"},{"name":"conversationlocation","type":"varchar"},{"name":"processtime","type":"varchar"},{"name":"languagecode","type":"varchar"},{"name":"duration","type":"real"},{"name":"customer_sentimentscore","type":"real"},{"name":"agent_sentimentscore","type":"real"},{"name":"customer_totaltimesecs","type":"real"},{"name":"agent_totaltimesecs","type":"real"},{"name":"nontalktime","type":"real"},{"name":"url_to_display","type":"varchar"},{"name":"customentity_name","type":"varchar"},{"name":"customentity_value","type":"varchar"}]} - ' */' TableType: "VIRTUAL_VIEW" Parameters: presto_view: "true" comment: "Presto View" ViewExpandedText: "/* Presto View */" StorageDescriptor: Columns: - Name: "partitiontimestamp" Type: "string" - Name: "guid" Type: "string" - Name: "agent" Type: "string" - Name: "cust" Type: "string" - Name: "conversationtime" Type: "string" - Name: "conversationlocation" Type: "string" - Name: "processtime" Type: "string" - Name: "languagecode" Type: "string" - Name: "duration" Type: "float" - Name: "customer_sentimentscore" Type: "float" - Name: "agent_sentimentscore" Type: "float" - Name: "customer_totaltimesecs" Type: "float" - Name: "agent_totaltimesecs" Type: "float" - Name: "nontalktime" Type: "float" - Name: "url_to_display" Type: "string" - Name: "customentity_name" Type: "string" - Name: "customentity_value" Type: "string" Location: "" Compressed: false NumberOfBuckets: 0 SerdeInfo: {} StoredAsSubDirectories: false Retention: 0 Name: "v_pca_customentities" QuickSightDataSourcePca: Type: "AWS::QuickSight::DataSource" DependsOn: - GlueTableForPca - ViewPcaCategories - ViewPcaCustomEntities Properties: DataSourceId: !Sub "${AWS::StackName}-PCA" Name: !Sub "${AWS::StackName}-PCA" AwsAccountId: !Ref AWS::AccountId Type: "ATHENA" DataSourceParameters: AthenaParameters: WorkGroup: "primary" SslProperties: DisableSsl: false QuickSightDataSetPcaCustomEntities: Type: "AWS::QuickSight::DataSet" Properties: DataSetId: !Sub "${AWS::StackName}-PCA-CustomEntities" Name: !Sub "${AWS::StackName}-CustomEntities" AwsAccountId: !Ref AWS::AccountId PhysicalTableMap: 79634643-d48b-425c-837f-6d777df5ab98: RelationalTable: DataSourceArn: !GetAtt QuickSightDataSourcePca.Arn Catalog: "AwsDataCatalog" Schema: !Sub "${PcaGlueCatalogDatabaseName}" Name: "v_pca_customentities" InputColumns: - Name: "partitiontimestamp" Type: "STRING" - Name: "guid" Type: "STRING" - Name: "agent" Type: "STRING" - Name: "cust" Type: "STRING" - Name: "conversationtime" Type: "STRING" - Name: "conversationlocation" Type: "STRING" - Name: "processtime" Type: "STRING" - Name: "languagecode" Type: "STRING" - Name: "duration" Type: "DECIMAL" - Name: "customer_sentimentscore" Type: "DECIMAL" - Name: "agent_sentimentscore" Type: "DECIMAL" - Name: "customer_totaltimesecs" Type: "DECIMAL" - Name: "agent_totaltimesecs" Type: "DECIMAL" - Name: "nontalktime" Type: "DECIMAL" - Name: "url_to_display" Type: "STRING" - Name: "customentity_name" Type: "STRING" - Name: "customentity_value" Type: "STRING" LogicalTableMap: 79634643-d48b-425c-837f-6d777df5ab98: Alias: "v_pca_customentities" DataTransforms: - CastColumnTypeOperation: ColumnName: "partitiontimestamp" NewColumnType: "DATETIME" Format: "yyyy/MM/dd" - CastColumnTypeOperation: ColumnName: "conversationtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss" - CastColumnTypeOperation: ColumnName: "processtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss.SSSSSS" - ProjectOperation: ProjectedColumns: - "partitiontimestamp" - "guid" - "agent" - "cust" - "conversationtime" - "conversationlocation" - "processtime" - "languagecode" - "duration" - "customer_sentimentscore" - "agent_sentimentscore" - "customer_totaltimesecs" - "agent_totaltimesecs" - "nontalktime" - "url_to_display" - "customentity_name" - "customentity_value" Source: PhysicalTableId: "79634643-d48b-425c-837f-6d777df5ab98" ImportMode: "DIRECT_QUERY" FieldFolders: {} QuickSightDataSetPcaCategories: Type: "AWS::QuickSight::DataSet" Properties: DataSetId: !Sub "${AWS::StackName}-PCA-Categories" Name: !Sub "${AWS::StackName}-PCA_Categories" AwsAccountId: !Ref AWS::AccountId PhysicalTableMap: 6aa285cc-5fed-4402-b975-18deb3a09bd2: RelationalTable: DataSourceArn: !GetAtt QuickSightDataSourcePca.Arn Catalog: "AwsDataCatalog" Schema: !Sub "${PcaGlueCatalogDatabaseName}" Name: "v_pca_categories" InputColumns: - Name: "partitiontimestamp" Type: "STRING" - Name: "guid" Type: "STRING" - Name: "agent" Type: "STRING" - Name: "cust" Type: "STRING" - Name: "conversationtime" Type: "STRING" - Name: "conversationlocation" Type: "STRING" - Name: "processtime" Type: "STRING" - Name: "languagecode" Type: "STRING" - Name: "duration" Type: "DECIMAL" - Name: "customer_sentimentscore" Type: "DECIMAL" - Name: "agent_sentimentscore" Type: "DECIMAL" - Name: "customer_totaltimesecs" Type: "DECIMAL" - Name: "agent_totaltimesecs" Type: "DECIMAL" - Name: "nontalktime" Type: "DECIMAL" - Name: "category" Type: "STRING" - Name: "url_to_display" Type: "STRING" LogicalTableMap: 6aa285cc-5fed-4402-b975-18deb3a09bd2: Alias: "v_pca_categories" DataTransforms: - CastColumnTypeOperation: ColumnName: "partitiontimestamp" NewColumnType: "DATETIME" Format: "yyyy/MM/dd" - CastColumnTypeOperation: ColumnName: "conversationtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss" - CastColumnTypeOperation: ColumnName: "processtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss.SSSSSS" - ProjectOperation: ProjectedColumns: - "partitiontimestamp" - "guid" - "agent" - "cust" - "conversationtime" - "conversationlocation" - "processtime" - "languagecode" - "duration" - "customer_sentimentscore" - "agent_sentimentscore" - "customer_totaltimesecs" - "agent_totaltimesecs" - "nontalktime" - "category" - "url_to_display" Source: PhysicalTableId: "6aa285cc-5fed-4402-b975-18deb3a09bd2" ImportMode: "DIRECT_QUERY" FieldFolders: {} QuickSightDataSetPcaMain: Type: "AWS::QuickSight::DataSet" Properties: DataSetId: !Sub "${AWS::StackName}-PCA-Main" Name: !Sub "${AWS::StackName}-PCA_Main" AwsAccountId: !Ref AWS::AccountId PhysicalTableMap: e0f38318-b3d3-4c0d-8904-55b88566c401: RelationalTable: DataSourceArn: !GetAtt QuickSightDataSourcePca.Arn Catalog: "AwsDataCatalog" Schema: !Sub "${PcaGlueCatalogDatabaseName}" Name: "pca_output" InputColumns: - Name: "guid" Type: "STRING" - Name: "agent" Type: "STRING" - Name: "cust" Type: "STRING" - Name: "conversationlocation" Type: "STRING" - Name: "languagecode" Type: "STRING" - Name: "conversationtime" Type: "STRING" - Name: "processtime" Type: "STRING" - Name: "duration" Type: "DECIMAL" - Name: "customer_sentimentscore" Type: "DECIMAL" - Name: "agent_sentimentscore" Type: "DECIMAL" - Name: "customer_totaltimesecs" Type: "DECIMAL" - Name: "agent_totaltimesecs" Type: "DECIMAL" - Name: "nontalktime" Type: "DECIMAL" - Name: "combinedanalyticsgraph" Type: "STRING" - Name: "actionitemsdetectedtext" Type: "STRING" - Name: "issuesdetectedtext" Type: "STRING" - Name: "outcomesdetectedtext" Type: "STRING" - Name: "categoriesdetectedtext" Type: "STRING" - Name: "url_to_display" Type: "STRING" - Name: "partitiontimestamp" Type: "STRING" LogicalTableMap: e0f38318-b3d3-4c0d-8904-55b88566c401: Alias: "pca_output" DataTransforms: - CastColumnTypeOperation: ColumnName: "conversationtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss" - CastColumnTypeOperation: ColumnName: "processtime" NewColumnType: "DATETIME" Format: "yyyy-MM-dd HH:mm:ss.SSSSSS" - CastColumnTypeOperation: ColumnName: "partitiontimestamp" NewColumnType: "DATETIME" Format: "yyyy/MM/dd" - TagColumnOperation: ColumnName: "conversationlocation" Tags: - ColumnGeographicRole: "STATE" - ProjectOperation: ProjectedColumns: - "guid" - "agent" - "cust" - "conversationlocation" - "languagecode" - "conversationtime" - "processtime" - "duration" - "customer_sentimentscore" - "agent_sentimentscore" - "customer_totaltimesecs" - "agent_totaltimesecs" - "nontalktime" - "combinedanalyticsgraph" - "actionitemsdetectedtext" - "issuesdetectedtext" - "outcomesdetectedtext" - "categoriesdetectedtext" - "url_to_display" - "partitiontimestamp" Source: PhysicalTableId: "e0f38318-b3d3-4c0d-8904-55b88566c401" ImportMode: "DIRECT_QUERY" FieldFolders: {} QuickSightAnalysisPca: Type: AWS::QuickSight::Analysis Properties: AnalysisId: !Sub "${AWS::StackName}-PCA-Analysis" Name: !Sub "${AWS::StackName}-PCA_Analysis" AwsAccountId: !Ref AWS::AccountId SourceEntity: SourceTemplate: DataSetReferences: - DataSetArn: !GetAtt QuickSightDataSetPcaCategories.Arn DataSetPlaceholder: "PCA_Categories" - DataSetArn: !GetAtt QuickSightDataSetPcaMain.Arn DataSetPlaceholder: "PCA_Main" - DataSetArn: !GetAtt QuickSightDataSetPcaCustomEntities.Arn DataSetPlaceholder: "PCA_CustomEntities" Arn: "arn:aws:quicksight:us-east-1:111343262070:template/sharedtemplates-PCA" QuickSightTemplatePca: Type: "AWS::QuickSight::Template" Properties: TemplateId: !Sub "${AWS::StackName}-PCA" Name: !Sub "${AWS::StackName}-PCA" AwsAccountId: !Ref AWS::AccountId VersionDescription: "1" SourceEntity: SourceAnalysis: DataSetReferences: - DataSetArn: !GetAtt QuickSightDataSetPcaCategories.Arn DataSetPlaceholder: "PCA_Categories" - DataSetArn: !GetAtt QuickSightDataSetPcaMain.Arn DataSetPlaceholder: "PCA_Main" - DataSetArn: !GetAtt QuickSightDataSetPcaCustomEntities.Arn DataSetPlaceholder: "PCA_CustomEntities" Arn: !GetAtt QuickSightAnalysisPca.Arn QuickSightDashboardPca: Type: "AWS::QuickSight::Dashboard" Properties: DashboardId: !Sub "${AWS::StackName}-PCA-Dashboard" Name: !Sub "${AWS::StackName}-PCA-Dashboard" AwsAccountId: !Ref AWS::AccountId SourceEntity: VersionDescription: "1" DashboardPublishOptions: AdHocFilteringOption: AvailabilityStatus: "DISABLED" ExportToCSVOption: AvailabilityStatus: "ENABLED" SheetControlsOption: VisibilityState: "EXPANDED" SourceEntity: SourceTemplate: DataSetReferences: - DataSetArn: !GetAtt QuickSightDataSetPcaCategories.Arn DataSetPlaceholder: "PCA_Categories" - DataSetArn: !GetAtt QuickSightDataSetPcaMain.Arn DataSetPlaceholder: "PCA_Main" - DataSetArn: !GetAtt QuickSightDataSetPcaCustomEntities.Arn DataSetPlaceholder: "PCA_CustomEntities" Arn: !GetAtt QuickSightTemplatePca.Arn Outputs: ManageAssets: Description: Click to proceed to 'Manage Assets' page of amazon Quicksight. Share the newly created dashboard named "-PCA-Dashboard. Share with your own username to see it part of your dashboards as well. Value: !Sub 'https://${AWS::Region}.quicksight.aws.amazon.com/sn/console/asset-management#' PCADashboard: Description: Click to see the deployed dashboard. Please do not forget to share the dashboard Value: !Sub 'https://${AWS::Region}.quicksight.aws.amazon.com/sn/dashboards/${AWS::StackName}-PCA-Dashboard' KinesisFirehoseCreatedForPCADashboard: Description: Kinesis firehose manages the compression and partitioning of the data in S3 Value: !Sub 'https://console.aws.amazon.com/firehose/home?#/details/${AWS::StackName}-Pca/' EventBridgeRuleForPCADashboard: Description: Event Bridge rule that call Kinesis Firehose for every PCA output file Value: !Sub 'https://${AWS::Region}.console.aws.amazon.com/events/home?region=${AWS::Region}#/rules/${AWS::StackName}_Pca' LambdaForPCADashboard: Description: Lambda function that transform PCA output data to dashboard ready data Value: !Sub 'https://${AWS::Region}.console.aws.amazon.com/lambda/home?region=${AWS::Region}#functions/${AWS::StackName}-TransformPca' S3FolderForPCADashboard: Description: Dashboard data is stored in this folder Value: !Sub 'https://s3.console.aws.amazon.com/s3/buckets/${PcaOutputBucket}?prefix=pca-output-base/' TableCreatedForPCADashboard: Description: This table stores the refined data to be displayed in the dashboard Value: !Sub '${PcaGlueCatalogDatabaseName}' TableCreatedForPCADashboard: Description: This table stores the refined data to be displayed in the dashboard Value: !Sub 'pca_output' ViewCreatedForCategoriesInPCADashboard: Description: This view formats the Categories sub component to tabular form Value: !Sub 'v_pca_categories' ViewCreatedForCustomEntitiesInPCADashboard: Description: This view formats the CustomEntities sub component to tabular form Value: !Sub 'v_pca_customentities'