HomeBig DataMeasure the adoption of your Amazon QuickSight dashboards and look at your...

Measure the adoption of your Amazon QuickSight dashboards and look at your BI portfolio in a single pane of glass


Amazon QuickSight is a totally managed, cloud-native enterprise intelligence (BI) service. When you plan to deploy enterprise-grade QuickSight dashboards, measuring consumer adoption and utilization patterns is a crucial ingredient for the success of your BI funding. For instance, understanding the utilization patterns like geo location, division, and job function may also help you fine-tune your dashboards to the proper viewers. Moreover, to return the funding of your BI portfolio, with dashboard utilization, you may scale back license prices by figuring out inactive QuickSight authors.

On this publish, we introduce the newest Admin Console, an AWS packaged resolution that you may simply deploy and use to create a utilization and stock dashboard on your QuickSight property. The Admin Console helps establish utilization patterns of a person consumer and dashboards. It could possibly additionally assist you monitor which dashboards and teams you may have or want entry to, and what you are able to do with that entry, by offering extra particulars on QuickSight group and consumer permissions and actions and QuickSight asset (dashboards, analyses, and datasets) permissions. With well timed entry to interactive utilization metrics, the Admin Console may also help BI leaders and directors make a cost-efficient plan for dashboard enhancements. One other frequent use case of this dashboard is to offer a centralized repository of the QuickSight property. QuickSight artifacts consists of a number of varieties of property (dashboards, analyses, datasets, and extra) with dependencies between them. Having a single repository to view all property and their dependencies might be an vital factor in your enterprise information dictionary.

This publish demonstrates methods to construct the Admin Console utilizing a serverless information pipeline. With fundamental AWS data, you may create this resolution in your individual setting inside an hour. Alternatively, you may dive deep into the supply code to satisfy your particular wants.

Admin Console dashboard

The next animation shows the contents of our demo dashboard.

The Admin Console dashboard consists of six sheets:

  • Touchdown Web page – Offers drill-down into every detailed tabs.
  • Consumer Evaluation – Offers detailed evaluation of the consumer habits and identifies lively and inactive customers and authors.
  • Dashboard Evaluation – Exhibits essentially the most generally seen dashboards.
  • Property Entry Permissions – Offers data on permissions utilized to every asset, comparable to dashboard, evaluation, datasets, information supply, and themes.
  • Knowledge Dictionary – Offers data on the relationships between every of your property, comparable to which evaluation was used to construct every dashboard, and which datasets and information sources are being utilized in every evaluation. It additionally offers particulars on every dataset, together with schema identify, desk identify, columns, and extra.
  • Overview – Offers directions on methods to use the dashboard.

You’ll be able to interactively play with the pattern dashboard within the following Interactive Dashboard Demo.

Let’s have a look at Forwood Security, an modern, values-driven firm with a laser concentrate on fatality prevention. An early adopter of QuickSight, they collaborated with AWS to deploy this resolution to gather BI utility utilization insights.

“Our engineers love this admin console resolution,” says Faye Crompton, Chief of Analytics and Benchmarking at Forwood. “It helps us to know how customers analyze vital management learnings by serving to us to rapidly establish essentially the most incessantly visited dashboards in Forwood’s self-service analytics and reporting device, FAST.”

Answer overview

The next diagram illustrates the workflow of the answer.

The workflow entails the next steps:

  1. The AWS Lambda perform Data_Prepare is scheduled to run hourly. This perform calls QuickSight APIs to get the QuickSight namespace, group, consumer, and asset entry permissions data.
  2. The Lambda perform Dataset_Info is scheduled to run hourly. This perform calls QuickSight APIs to get dashboard, evaluation, dataset, and information supply data.
  3. Each the capabilities save the outcomes to an Amazon Easy Storage Service (Amazon S3) bucket.
  4. AWS CloudTrail logs are saved in an S3 bucket.
  5. Based mostly on the file in Amazon S3 that accommodates user-group data, dataset data, QuickSight property entry permissions data, in addition to dashboard views and consumer login occasions from the CloudTrail logs, 5 Amazon Athena tables are created. Optionally, the BI engineer can mix these tables with worker data tables to show human useful resource data of the customers.
  6. 4 QuickSight datasets fetch the information from the Athena tables created in Step 5 and import them into SPICE. Then, based mostly on these datasets, a QuickSight dashboard is created.

Conditions

For this walkthrough, it’s best to have the next stipulations:

  • An AWS account
  • Entry to the next AWS providers:
  • Fundamental data of Python
  • Fundamental data of SQL

Create resolution sources

We are able to create all of the sources wanted for this dashboard utilizing three CloudFormation templates: one for Lambda capabilities, one for Athena tables, and one for QuickSight objects.

CloudFormation template for Lambda capabilities

This template creates the Lambda capabilities data_prepare and dataset_info.

  • Select Launch Stack and comply with the steps to create these sources.

After the stack creation is profitable, you may have two Lambda capabilities, data_prepare and dataset_info, and one S3 bucket named admin-console[AWS-account-ID]. You’ll be able to confirm if the Lambda perform can run efficiently and if the group_membership, object_access, datasets_info, and data_dictionary folders are created within the S3 bucket underneath admin-console[AWS-account-ID]/monitoring/quicksight/, as proven within the following screenshots.

The Data_Prepare Lambda perform is scheduled to run hourly with the CloudWatch Occasions rule admin-console-every-hour. This perform calls the QuickSight Property APIs to get QuickSight customers, property, and the entry permissions data. Lastly, this perform creates two information, group_membership.csv and object_access.csv, and saves these information to an S3 bucket.

The Dataset_Info Lambda perform is scheduled to run hourly and calls the QuickSight Property APIs to get datasets, schemas, tables, and fields (columns) data. Then this perform creates two information, datasets_info.csv and data_dictionary.csv, and saves these information to an S3 bucket.

  •  Create a CloudTrail log if you happen to don’t have already got one and notice down the S3 bucket identify of the log information for future use.
  •  Be aware down all of the sources created from the earlier steps. If the S3 bucket identify for the CloudTrail log from step 2 is totally different from the one in step 1’s output, use the S3 bucket from step 2.

The next desk summarizes the keys and values you employ when creating the Athena tables with the subsequent CloudFormation stack.

Key Worth Description
cloudtraillog s3://cloudtrail-awslogs-[aws-account-id]-do-not-delete/AWSLogs/[aws-account-id]/CloudTrail The Amazon S3 location of the CloudTrail log
cloudtraillogtablename cloudtrail_logs The desk identify of CloudTrail log
groupmembership s3://admin-console[aws-account-id]/monitoring/quicksight/group_membership The Amazon S3 location of group_membership.csv
objectaccess s3://admin-console[aws-account-id]/monitoring/quicksight/object_access The Amazon S3 location of object_access.csv
dataset information s3://admin-console[aws-account-id]/monitoring/quicksight/datsets_info The Amazon S3 location of datsets_info.csv
datadict s3://admin-console[aws-account-id]/monitoring/quicksight/data_dictionary The Amazon S3 location of data_dictionary.csv

CloudFormation template for Athena tables

To create your Athena tables, full the next steps:

  • Obtain the next JSON file.
  • Edit the file and substitute the corresponding fields with the keys and values you famous within the earlier part.

For instance, seek for the groupmembership key phrase.

Then substitute the situation worth with the Amazon S3 location for the groupmembership folder.

  • Create Athena tables by deploying this edited file as a CloudFormation template. For directions, consult with Get began.

After a profitable deployment, you may have a database known as admin-console created in AwsDataCatalog in Athena and three tables within the database: cloudtrail_logs, group_membership, object_access, datasets_info and data_dict

  • Affirm the tables through the Athena console.

The next screenshot exhibits pattern information of the group_membership desk.

The next screenshot exhibits pattern information of the object_access desk.

For directions on constructing an Athena desk with CloudTrail occasions, see Amazon QuickSight Now Helps Audit Logging with AWS CloudTrail. For this publish, we create the desk cloudtrail_logs within the default database.

  • In spite of everything 5 tables are created in Athena, go to the safety permissions on the QuickSight console to allow bucket entry for s3://admin-console[AWS-account-ID] and s3://cloudtrail-awslogs-[aws-account-id]-do-not-delete.
  • Allow Athena entry underneath Safety & Permissions.

Now QuickSight can entry all 5 tables by way of Athena.

CloudFormation template for QuickSight objects

To create the QuickSight objects, full the next steps:

  • Select Launch Stack to create the QuickSight datasets and dashboard:

  • Present the ARN you famous earlier.

After a profitable deployment, 4 datasets named Admin-Console-Group-Membership, Admin-Console-dataset-info, Admin-Console-Object-Entry, and Admin-Console-CFN-Primary are created and you’ve got the dashboard named admin-console-dashboard. If modifying the dashboard is most popular, use the dashboard save-as choice, then recreate the evaluation, make modifications, and publish a brand new dashboard.

  • Set your most popular SPICE refresh schedule for the 4 SPICE datasets, and share the dashboard in your group as wanted.

Dashboard demo

The next screenshot exhibits the Admin Console Touchdown web page.

The next screenshot exhibits the Consumer Evaluation sheet.

The next screenshot exhibits the Dashboards Evaluation sheet.

The next screenshot exhibits the Entry Permissions sheet.

The next screenshot exhibits the Knowledge Dictionary sheet.

The next screenshot exhibits the Overview sheet.

You’ll be able to interactively play with the pattern dashboard within the following Interactive Dashboard Demo.

You’ll be able to reference the general public template of the previous dashboard in create-template, create-analysis, and create-dashboard API calls to create this dashboard and evaluation in your account. The general public template of this dashboard with the template ARN is 'TemplateArn': 'arn:aws:quicksight:us-east-1:889399602426:template/admin-console'.

Ideas and tips

Listed here are some superior suggestions and tips to construct the dashboard because the Admin Console to investigate utilization metrics. The next steps are based mostly on the dataset admin_console. You’ll be able to apply the identical logic to create the calculated fields to investigate consumer login actions.

  • Create parameters – For instance, we will create a parameter known as InActivityMonths, as within the following screenshot. Equally, we will create different parameters comparable to InActivityDays, Begin Date, and Finish Date.

  • Create controls based mostly on the parameters – Within the following screenshot, we create controls based mostly on the beginning and finish date.

  • Create calculated fields – As an illustration, we will create a calculated subject to detect the lively or inactive standing of QuickSight authors. If the time span between the newest view dashboard exercise and now’s bigger or equal to the quantity outlined within the Inactivity Months management, the creator standing is Inactive. The next screenshot exhibits the related code. In line with the end-user’s necessities, we will outline a number of calculated fields to carry out the evaluation.

  • Create visuals – For instance, we create an perception to show the highest three dashboard views by reader and a visible to show the authors of those dashboards.

  • Add URL actions – You’ll be able to add an URL motion to outline some further options to electronic mail inactive authors or examine particulars of customers.

The next pattern code defines the motion to electronic mail inactive authors:

mailto:<<electronic mail>>?topic=Alert to inactive creator! &physique=Hello, <<username>>, any creator with out exercise for greater than a month will probably be deleted. Please log in to your QuickSight account to proceed accessing and constructing analyses and dashboards!

Clear up

To keep away from incurring future fees, delete all of the sources you created with the CloudFormation templates.

Conclusion

This publish mentioned how BI directors can use QuickSight, CloudTrail, and different AWS providers to create a centralized view to investigate QuickSight utilization metrics. We additionally introduced a serverless information pipeline to help the Admin Console dashboard.

If you need to have a demo, please electronic mail us.

Appendix

We are able to carry out some further refined evaluation to gather superior utilization metrics. For instance, Forwood Security raised a novel request to investigate the readers who log in however don’t view any dashboard actions (see the next code). This helps their purchasers establish and stop any losing of reader periods charges. Management groups worth the power to reduce uneconomical consumer exercise.

CREATE OR REPLACE VIEW "loginwithoutviewdashboard" AS
with login as
(SELECT COALESCE("useridentity"."username", "split_part"("useridentity"."arn", '/', 3)) AS "user_name", awsregion,
date_parse(eventtime, '%Y-%m-%dTpercentH:%i:%sZ') AS event_time
FROM cloudtrail_logs
WHERE
eventname="AssumeRoleWithSAML"
GROUP BY  1,2,3),
dashboard as
(SELECT COALESCE("useridentity"."username", "split_part"("useridentity"."arn", '/', 3)) AS "user_name", awsregion,
date_parse(eventtime, '%Y-%m-%dTpercentH:%i:%sZ') AS event_time
FROM cloudtrail_logs
WHERE
eventsource="quicksight.amazonaws.com"
AND
eventname="GetDashboard"
GROUP BY  1,2,3),
customers as 
(choose Namespace,
Group,
Consumer,
(case
when Group in (‘quicksight-fed-bi-developer’, ‘quicksight-fed-bi-admin’)
then ‘Writer’
else ‘Reader’
finish)
as author_status
from "group_membership" )
choose l.* 
from login as l 
be part of dashboard as d 
be part of customers as u 
on l.user_name=d.user_name 
and 
l.awsregion=d.awsregion 
and 
l.user_name=u.user_name
the place d.event_time>(l.event_time + interval '30' minute ) 
and 
d.event_time<l.event_time 
and 
u.author_status="Reader"


Concerning the Authors

Ying Wang is a Supervisor of Software program Growth Engineer. She has 12 years of experience in information analytics and science. She assisted prospects with enterprise information structure options to scale their information analytics within the cloud throughout her time as an information architect. At present, she helps buyer to unlock the facility of Knowledge with QuickSight from engineering by delivering new options.

Ian Liao is a Senior Knowledge Visualization Architect at AWS Skilled Providers. Earlier than AWS, Ian spent years constructing startups in information and analytics. Now he enjoys serving to buyer to scale their information utility on the cloud.

Maitri Brahmbhatt is a Enterprise Intelligence Engineer at AWS. She helps prospects and companions leverage their information to achieve insights into their enterprise and make information pushed selections by growing QuickSight dashboards.

RELATED ARTICLES

Most Popular

Recent Comments