AWS Quicksight – Dashboards

AWS Quicksight – Dashboards ”; Previous Next A dashboard shows the screenshot of the analysis. Unlike Analysis, dashboards are read as only screenshots. One can just use the parameters and filters created in visuals to create new visuals but with same charts To generate a dashboard of the analysis you have created, just click on publish dashboard under share icon. Provide any name to the dashboard and click on “Publish Dashboard” icon. You can opt to share the dashboard with all users in this account or only particular users. Now the dashboard is visible under “All dashboards” tab on home screen. Here is the sample dashboard with a filter attached to it. You can see that there is no option to edit the dashboard except applying filters added while creating visuals. Print Page Previous Next Advertisements ”;

AWS Quicksight – Embedding Dashboard

AWS Quicksight – Embedding Dashboard ”; Previous Next You can also embed your Quicksight dashboards into external applications/web pages or can control user access using AWS Cognito service. To perform user control, you can create user pool and identity pool in Cognito and assign Embed dashboard policies to identity pool. AWS Cognito is an IAM service which allows administrators to create and manage temporary users to provide access to applications. With the use of identity pool, you can manage permissions on these user pools. Let us see how we can generate secure dashboard URL and perform user control − Step 1 – Creating user pools and users Create user pool in AWS Cognito and create users. Go to Amazon Cognito → Manage User Pools → Create a User Pool. Step 2 – Creating an identity pool When user pool is created, next step is to create an identity pool. Go to https://console.aws.amazon.com/cognito/home?region=us-east-1 Click on “Create New Identity Pool”. Enter the appropriate name of an identity pool. Go to the Authentication Providers section and select “Cognito” option. Step 3 – Creating Cognito roles Enter the User Pool ID (your User pool ID) and App Client ID (go to App Clients in user pool and copy id). Next is to click on ‘Create Pool’ and click on ‘Allow’ to create roles of the identity pool in IAM. It will create 2 Cognito roles. Step 4 – Assigning Custom Policy Next step is to assign custom policy to identity roles created in the above step − { “Version”: “2012-10-17”, “Statement”: [ { “Action”: “quicksight:RegisterUser”, “Resource”: “*”, “Effect”: “Allow” }, { “Action”: “quicksight:GetDashboardEmbedUrl”, “Resource”: “*”, “Effect”: “Allow” }, { “Action”: “sts:AssumeRole”, “Resource”: “*”, “Effect”: “Allow” } ] } You can pass dashboard Amazon Resource Name (ARN) under quicksight:GetDashboardEmbedUrl” instead of “*” to restrict user to access only one dashboard. Step 5 – Logging into Cognito application Next step is to login to Cognito application with user credentials in user pool. When user logins into application, Cognito generates 3 tokens − IDToken AccessToken Refresh Token To create a temporary IAM user, credentials are as shown below − AWS.config.region = ”us-east-1”; AWS.config.credentials = new AWS.CognitoIdentityCredentials({ IdentityPoolId:”Identity pool ID”, Logins: { ”cognito-idp.us-east-1.amazonaws.com/UserPoolID”: AccessToken } }); For generating temporary IAM credentials, you need to call sts.assume role method with the below parameters − var params = { RoleArn: “Cognito Identity role arn”, RoleSessionName: “Session name” }; sts.assumeRole(params, function (err, data) { if (err) console.log( err, err.stack); // an error occurred else { console.log(data); }) } Step 6 – Registering the user in Quicksight Next step is to register the user in Quicksight using “quicksight.registerUser” for credentials generated in step 3 with the below parameters − var params = { AwsAccountId: “account id”, Email: ”email”, IdentityType: ”IAM” , Namespace: ”default”, UserRole: ADMIN | AUTHOR | READER | RESTRICTED_AUTHOR | RESTRICTED_READER, IamArn: ”Cognito Identity role arn”, SessionName: ”session name given in the assume role creation”, }; quicksight.registerUser(params, function (err, data1) { if (err) console.log(“err register user”); // an error occurred else { // console.log(“Register User1”); } }) Step 7 – Updating AWS Configuration file Next is to update AWS configuration for user generated in step 5. AWS.config.update({ accessKeyId: AccessToken, secretAccessKey: SecretAccessKey , sessionToken: SessionToken, “region”: Region }); Step 8 – Generating embed URL for Quicksight dashboard With credentials created in step 5, call the quicksight.getDashboardEmbedUrl with the below parameters to generate URL. var params = { AwsAccountId: “Enter AWS account ID”, DashboardId: “Enter dashboard Id”, IdentityType: “IAM”, ResetDisabled: true, SessionLifetimeInMinutes: between 15 to 600 minutes, UndoRedoDisabled: True | False } quicksight.getDashboardEmbedUrl(params,function (err, data) { if (!err) { console.log(data); } else { console.log(err); } }); You have to call “QuickSightEmbedding.embedDashboard” from your application using the above generated URL. Like Amazon Quicksight, embedded dashboard also supports the following features − Drill-down option Custom actions (link to a new tab) On-screen filters Download to CSV Sorting on visuals Email report opt-in Reset dashboard to defaults option Undo/redo actions on the dashboard Print Page Previous Next Advertisements ”;

Developer Responsibilities

AWS Quicksight – Developer Responsibilities ”; Previous Next Following job responsibilities are performed by an AWS Quicksight developer − Person should have relevant work experience in analytics, reporting and business intelligence tools. Understanding customer requirements and design solution in AWS to setup ETL and Business Intelligence environment. Understanding different AWS services, their use and configuration. Proficient in using SQL, ETL, Data Warehouse solutions and databases in a business environment with large-scale, disparate datasets. Complex quantitative and data analysis skills. Understanding AWS IAM policies, roles and administrator of AWS services. Print Page Previous Next Advertisements ”;

AWS Quicksight – Managing Quicksight

AWS Quicksight – Managing Quicksight ”; Previous Next Manage Quicksight is to manage your current account. You can add users with respective roles, manage your subscription, and check SPICE capacity or whitelist domains for embedding. You would require admin access to perform any activity on this page. Under the user profile, you will find the option to manage Quicksight. On clicking Manage subscription, below screen will appear. It will show the users in this account and their respective roles. You also have a search option; in case you want to particularly search for an existing user in Quicksight. You can invite users with valid email address or you can add users with a valid IAM account. The users with IAM role can then login to their Quicksight account and view the dashboard to which they have access. Your Subscriptions will show the edition of Quicksight you are subscribed to. SPICE capacity shows the capacity of their calculation engine being opted for and the amount used so far. There is an option of purchasing more capacity if required. Account settings shows details of Quicksight account − notification email address, AWS resource permissions to Quicksight, or you also have an option to close the account. When you close Quicksight account, it deletes all the data related to below objects − Data Sources Data Sets Analyses Published Dashboards Manage VPC connection allows you to manage and add VPC connection to Quicksight. To add a new VPC connection, you need to provide the following details − Domains and embedding allows you to whitelist the domain on which you want to embed Quicksight dashboards for the users. It only supports https:// domain to whitelist in Quicksight − https://example.com You can also include any subdomains if you want to use by selecting the checkbox shown below. When you click on Add button, it adds the domain to the list of domain names allowed in Quicksight for embedding. To edit an allowed domain, you need to click on Edit button located beside the domain name. You can make changes and click on Update. Print Page Previous Next Advertisements ”;

AWS Quicksight – Overview

AWS Quicksight – Overview ”; Previous Next AWS Quicksight is one of the most powerful Business Intelligence tools which allows you to create interactive dashboards within minutes to provide business insights into the organizations. There are number of visualizations or graphical formats available in which the dashboards can be created. The dashboards get automatically updated as the data is updated or scheduled. You can also embed the dashboard created in Quicksight to your web application. With the latest ML insights, also known as Machine Learning insights, Quicksight uses its inbuilt algorithms to find any kind of anomalies or peaks in the historical data. This helps to get prepared with the business requirements ahead of time based on these insights. Here is quick guide to get started with Quicksight. Below is official product description page from AWS − https://aws.amazon.com/quicksight/ You can also subscribe for an AWS trial account by filling the below mentioned information and click on Continue button. Print Page Previous Next Advertisements ”;

AWS Quicksight – Data Source Limit

AWS Quicksight – Data Source Limit ”; Previous Next When you use different data sources in Quicksight tool, there are certain limits that apply based on data sources. File You can use up to 25GB of total size specified in manifest file. This limit is dependent on the file size after it is imported to SPICE. The number of files supported in manifest file is 1000 and you also have some limit on number of columns in each file. Table and Query When you are querying a large table, it is recommended that you use Where or Having condition to reduce the number of data imported to SPICE. Query result imported into SPICE should not exceed 25 GB. You can deselect some of the columns while importing the data into SPICE. In case your data source contains data type which are not supported in Quicksight, AWS Quicksight skips those values. Person ID Sales Date Amount 001 10/14/2017 12.43 002 5/3/2017 25.00 003 Unknown 18.17 004 3/8/2019 86.02 From the above values, Quicksight will drop the no date value row while importing this data in dataset. Following data types are supported in Quicksight − Database Source Number Data Types String Data Types Date time Data Types Boolean Data Types Amazon Athena,Presto bigint decimal double integer real smallint tinyint char varchar date timestamp Boolean Amazon Aurora, MariaDB,and MySQL bigint decimal double int integer mediumint numeric smallint tinyint char enum set text varchar date datetime timestamp year PostgreSQL bigint decimal double integer numeric precision real smallint char character text varchar varying character date timestamp Boolean Apache Spark bigint decimal double integer real smallint tinyint varchar date timestamp Boolean Snowflake bigint byteint decimal double doubleprecision float float4 float8 int integer number numeric real smallint tinyint char character string text varchar date datetime time timestamp timestamp_* Boolean Microsoft SQL Server bigint bit decimal int money numeric real smallint smallmoney tinyint char nchar nvarchar text varchar date datetime datetime2 datetimeoffset smalldatetime bit Print Page Previous Next Advertisements ”;

AWS Quicksight – Using Data Sources

AWS Quicksight – Using Data Sources ”; Previous Next AWS Quicksight accepts data from various sources. Once you click on “New Dataset” on the home page, it gives you options of all the data sources that can be used. Below are the sources containing the list of all internal and external sources − Let us go through connecting Quicksight with some of the most commonly used data sources − Uploading a file from system It allows you to input .csv, .tsv, .clf,.elf.xlsx and Json format files only. Once you select the file, Quicksight automatically recognizes the file and displays the data. When you click on Upload a File button, you need to provide the location of file which you want to use to create dataset. Using a file from S3 format The screen will appear as below. Under Data source name, you can enter the name to be displayed for the data set that would be created. Also you would require either uploading a manifest file from your local system or providing the S3 location of the manifest file. Manifest file is a json format file, which specifies the url/location of input files and their format. You can enter more than one input files, provided the format is same. Here is an example of a manifest file. The “URI” parameter used to pass the location of input file is S3. { “fileLocations”: [ { “URIs”: [ “url of first file”, “url of second file”, “url of 3rd file and so on” ] }, ], } “globalUploadSettings”: { “format”: “CSV”, “delimiter”: “,”, “textqualifier”: “””, “containsHeader”: “true” } The parameters passed in globalUploadSettings are the default ones. You can change these parameters as per your requirements. MySQL You need to enter the database information in the fields to connect to your database. Once it is connected to your database, you can import the data from it. Following information is required when you connect to any RDBMS database − DSN name Type of connection Database server name Port Database name User name Password Following RDBMS based data sources are supported in Quicksight − Amazon Athena Amazon Aurora Amazon Redshift Amazon Redshift Spectrum Amazon S3 Amazon S3 Analytics Apache Spark 2.0 or later MariaDB 10.0 or later Microsoft SQL Server 2012 or later MySQL 5.1 or later PostgreSQL 9.3.1 or later Presto 0.167 or later Snowflake Teradata 14.0 or later Athena Athena is the AWS tool to run queries on tables. You can choose any table from Athena or run a custom query on those tables and use the output of those queries in Quicksight. There are couple of steps to choose data source When you choose Athena, below screen appears. You can input any data source name which you want to give to your data source in Quicksight. Click on “Validate Connection”. Once the connection is validated, click on the “Create new source” button Now choose the table name from the dropdown. The dropdown will show the databases present in Athena which will further show tables in that database. Else you can click on “Use custom SQL” to run query on Athena tables. Once done, you can click on “Edit/Preview data” or “Visualize” to either edit your data or directly visualize the data as per your requirement. Deleting a data source When you delete a data source which is in use in any of the Quicksight dashboards, it can make associated data set unusable. It usually happens when you query a SQL based data source. When you create a dataset based on S3, Sales force or SPICE, it does not affect your ability to use any dataset as data is stored in SPICE; however refresh option is not available in this case. To delete a data source, select the data source. Navigate to From Existing Data Source tab on creating a dataset page. Before deletion, you can also confirm estimated table size and other details of data source. Print Page Previous Next Advertisements ”;

AWS Quicksight – Landing Page

AWS Quicksight – Landing Page ”; Previous Next To access AWS Quicksight tool, you can open it directly by passing this URL in web browser or navigating to AWS Console → Services https://aws.amazon.com/quicksight/ Once you open this URL, on top right corner click on “Sign in to the Console”. You need to provide the below details to login to Quicksight tool − Account ID or alias IAM User name Password Once you login into Quicksight, you will see the below screen − As marked in the above image, Section A − “New Analysis” icon is used to create a new analysis. When you click on this, it will ask you to select any data set. You can also create a new data set as shown below − Section B − The “Manage data” icon will show all the data sets that have already been input to Quicksight. This option can be used to manage the dataset without creating any analysis. Section C − It shows various data sources you have already connected to. You can also connect to a new data source or upload a file. Section D − This section contains icon for already created analysis, published dashboards and tutorial videos explaining about Quicksight in detail. You can click on each tab to view them as below − All analysis Here, you can see all the existing analysis in AWS Quicksight account including report and dashboards. All dashboards This option shows only existing dashboards in AWS Quicksight account. Tutorial videos Other option to open Quicksight console is by navigating to AWS console using below URL − https://aws.amazon.com/console/ Once you login, you need to navigate to Services tab and search for Quicksight in search bar. If you have recently used Quicksight services in AWS account, it will be seen under History tab. Print Page Previous Next Advertisements ”;

AWS Quicksight – Home

AWS Quicksight Tutorial PDF Version Quick Guide Resources Job Search Discussion AWS Quicksight is an AWS based Business Intelligence and visualization tool that is used to visualize data and create stories to provide graphical details of the data. Data is entered as dataset and you can apply filters, hierarchies, and columns to prepare documents. You can choose various charts like Bar charts, Pie charts, etc. to visualize the data effectively. This basic tutorial will help you to understand and learn AWS Quicksight tool. Audience AWS Quicksight is meant for BI developers and business users who can alter data structures and correlations in whatever way they want. They can create data visualizations and stories from multiple data sources. It helps to adapt data to organizational needs to create stories with visualizations. Prerequisites Before you start proceeding with this tutorial, we are assuming that you are already aware of the basics of Business Intelligence and SQL. If you are not exposed to BI concepts, then we will suggest you first to go through our short tutorial on same topics. Print Page Previous Next Advertisements ”;