business intelligence Archives - Online Code Generator

Building Automation for Fraud Detection Using OpenSearch and Terraform

Posted on March 14, 2023May 21, 2023 by Mark FeehilyComments Off

Organizations that interface with online payments are continuously monitoring and guarding against fraudulent activity. Transactional fraud usually presents itself as discrete data points, making it challenging to identify multiple actors involved in the same group of transactions. Even a single actor operating over a period of time can be hard to detect. Visibility is key to prevent fraud incidents from occurring and to give meaningful knowledge of the activities within your environment to data, security, and operations engineers.

Understanding the connections between individual data points can reduce the time for customers to detect and prevent fraud. You can use a graph database to store transaction information along with the relationships between individual data points. Analyzing those relationships through a graph database can uncover patterns difficult to identify with relational tables. Fraud graphs enable customers to find common patterns between transactions, such as phone numbers, locations, and origin and destination accounts. Additionally, combining fraud graphs with full text search provides additional benefits as it can simplify analysis and integration with existing applications.

In our solution, financial analysts can upload graph data, which gets automatically ingested into the Amazon Neptune graph database service and replicated into Amazon OpenSearch Service for analysis. Data ingestion is automated with Amazon Simple Storage Service (Amazon S3) and Amazon Simple Queue Service (Amazon SQS) integration. We do data replication through AWS Lambda functions and AWS Step Functions for orchestration. The design is using open source tools and AWS Managed Services to build resources and is available in this https://github.com/aws-samples/neptune-fraud-detection-with-opensearch GitHub repository under an MIT-0 license. You will use Terraform and Docker to deploy the architecture, and will be able to send search requests to the system to explore the dataset.

Solution overview

This solution takes advantage of native integration between AWS services for scalability and performance, as well as the Neptune-to-OpenSearch Service replication pattern described in Neptune’s official documentation.

Figure 1 An architectural diagram that illustrates the infrastructure state and workflow as defined in the Terraform templates.

The process for this solution consists of the following steps, also shown in the architecture diagram here:

Financial analyst uploads graph data files to an Amazon S3 bucket.

Note: The data files are in a Gremlin load data format (CSV) and can include vertex files and edge files.

The action of the upload invokes a PUT object event notification with a destination set to an Amazon SQS queue.
The SQS queue is configured as an AWS Lambda event source, which invokes a Lambda function.
This Lambda function sends an HTTP request to an Amazon Neptune database to load data stored in an S3 bucket.
The Neptune database reads data from the S3 endpoint defined in the Lambda request and loads the data into the graph database.
An Amazon EventBridge rule is scheduled to run every 5 minutes. This rule targets an AWS Step Functions state machine to create a new execution.
The Neptune Poller step function (state machine) replicates the data in the Neptune database to an OpenSearch Service cluster.
Note: The Neptune Poller step function is responsible for continually syncing new data after the initial data upload using Neptune Streams.

User can access the replicated data from the Neptune database with Amazon OpenSearch Service.
Note: A Lambda function is invoked to send a search request or query to an OpenSearch Service endpoint to get results.

Prerequisites

To implement this solution, you must have the following prerequisites:

An AWS account with local credentials is configured. For more information, check the documentation on configuration and credential file settings.
The latest version of the AWS Command Line Interface (AWS CLI).
An IAM user with Git credentials.
A Git client to clone the source code provided.
A Bash shell.

Docker installed on your localhost.

Terraform installed on your localhost.

Deploying the Terraform templates

The solution is available in this GitHub repository with the following structure:

data: Contains a sample dataset to be used with the solution for demonstration purposes. Information on fictional transactions, identities and devices is represented in files within the nodes/ folder, and relationships between them are represented in files in the edges/ folder.
terraform: This folder contains the Terraform modules to deploy the solution.
documents: This folder contains the architecture diagram image file of the solution.

Create a local directory called NeptuneOpenSearchDemo and clone the source code repository:

mkdir -p $HOME/NeptuneOpenSearchDemo

cd $HOME/NeptuneOpenSearchDemo

git clone https://github.com/aws-samples/neptune-fraud-detection-with-opensearch.git

Change directory into the Terraform directory:

cd $HOME/NeptuneOpenSearchDemo neptune-fraud-detection-with-opensearch /terraform

Make sure that the Docker daemon is running:

docker info

If the previous command outputs an error that is unable to connect to the Docker daemon, start Docker and run the command again.

Initialize the Terraform folder to install required providers:

terraform init

The solution is deployed on us-west-2 by default. The user can change this behavior by modifying the variable “region” in variables.tf file.

Deploy the AWS services:

terraform apply -auto-approve

Note: Deployment will take around 30 minutes due to the time necessary to provision the Neptune and OpenSearch Service clusters.

To retrieve the name of the S3 bucket to upload data to:

aws s3 ls | grep “neptunestream-loader.*d$”

Upload node data to the S3 bucket obtained in the previous step:

aws s3 cp $HOME/NeptuneOpenSearchDemo/neptune-fraud-detection-with-opensearch /data s3:// neptunestream-loader-us-west-2-123456789012 –recursive

Note: This is a sample dataset for demonstration purposes only created from the IEEE-CIS Fraud Detection dataset.

Test the solution

After the solution is deployed and the dataset is uploaded to S3, the dataset can be retrieved and explored through a Lambda function that sends a search request to the OpenSearch Service cluster.

Confirm the Lambda function that sends a request to OpenSearch was deployed correctly:

aws lambda get-function –function-name NeptuneStreamOpenSearchRequestLambda –-query ‘Configuration.[FunctionName, State]’

Invoke the Lambda function to see all records present in OpenSearch that are added from Neptune:

aws lambda invoke –function-name NeptuneStreamOpenSearchRequestLambda response.json

The results of the Lambda invocation are stored in the response.json file. This file contains the total number of records in the cluster and all records ingested up to that point. The solution stores records in the index amazon_neptune. An example of a node with device information looks like this:

{
“_index”: “amazon_neptune”,
“_type”: “_doc”,
“_id”: “1fb6d4d2936d6f590dc615142a61059e”,
“_score”: 1.0,
“_source”: {
“entity_id”: “d3”,
“document_type”: “vertex”,
“entity_type”: [
“vertex”
],
“predicates”: {
“deviceType”: [
{
“value”: “desktop”
}
],
“deviceInfo”: [
{
“value”: “Windows”
}
]
}
}
}

Cleaning up

To avoid incurring future charges, clean up the resources deployed in the solution:

terraform destroy –auto-approve

The command will output information on resources being destroyed.

Destroy complete! Resources: 101 destroyed.

Conclusion

Fraud graphs are complementary to other techniques organizations can use to detect and prevent fraud. The solution presented in this blog post reduces the time financial analysts would take to access transactional data by automating data ingestion and replication. It also improves performance for systems with growing volumes of data when compared to executing a large number of insert statements or other API calls.

Adding CDK Constructs to the AWS Analytics Reference Architecture

Posted on January 14, 2023May 21, 2023 by Mark FeehilyComments Off

In 2021, we released the AWS Analytics Reference Architecture, a new AWS Cloud Development Kit (AWS CDK) application end-to-end example, as open source (docs are CC-BY-SA 4.0 International, sample code is MIT-0). It shows how our customers can use the available AWS products and features to implement well-architected analytics solutions. It also regroups AWS best practices for designing, implementing and operating analytics solutions through different purpose-built patterns. Altogether, the AWS Analytics Reference Architecture answers common requirements and solves customer challenges.

In 2022, we extended the scope of this project with AWS CDK constructs to provide more granular and reusable examples. This project is now composed of:

Reusable core components exposed in an AWS CDK library currently available in Typescript and Python. This library contains the AWS CDK constructs that can be used to quickly provision prepackaged analytics solutions.
Reference architectures consuming the reusable components in AWS CDK applications, and demonstrating end-to-end examples in a business context. Currently, only the AWS native reference architecture is available but others will follow.

In this blog post, we will first show how to consume the core library to quickly provision analytics solutions using CDK Constructs and experiment with AWS analytics products.

Building solutions with the Core Library

To illustrate how to use the core components, let’s see how we can quickly build a Data Lake, a central piece for most analytics projects. The storage layer is implemented with the DataLakeStorage CDK construct relying on Amazon Simple Storage Service (Amazon S3), a durable, scalable and cost-effective object storage service. The query layer is implemented with the AthenaDemoSetup construct using Amazon Athena, an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. With regard to the data catalog, it‘s implemented with the DataLakeCatalog construct using AWS Glue Data Catalog.

Before getting started, please make sure to follow the instructions available here for setting up the prerequisites:

Install the necessary build dependencies
Bootstrap the AWS account
Initialize the CDK application.

This architecture diagram depicts the data lake building blocks we are going to deploy using the AWS Analytics Reference Architecture library. These are higher level constructs (commonly called L3 constructs) as they integrate several AWS services together in patterns.

To assemble these components, you can add this code snippet in your app.py file:

import aws_analytics_reference_architecture as ara
…

# Create a new DataLakeStorage with Raw, Clean and Transform buckets
storage = ara.DataLakeStorage(scope=self, id=”storage”)

# Create a new DataLakeCatalog with Raw, Clean and Transform databases
catalog = ara.DataLakeCatalog(scope=self, id=”catalog”)

# Configure a new Athena Workgroup
athena_defaults = ara.AthenaDemoSetup(scope=self, id=”demo_setup”)

# Generate data from Customer TPC dataset
data_generator = ara.BatchReplayer(
scope=self,
id=”customer-data”,
dataset=ara.PreparedDataset.RETAIL_1_GB_CUSTOMER,
sink_object_key=”customer”,
sink_bucket=storage.raw_bucket,
)

# Role with default permissions for any Glue service
glue_role = ara.GlueDemoRole.get_or_create(self)

# Crawler to create tables automatically
crawler = glue.CfnCrawler(self, id=’ara-crawler’, name=’ara-crawler’,
role=glue_role.iam_role.role_arn, database_name=’raw’,
targets={‘s3Targets’: [{“path”: f”s3://{storage.raw_bucket.bucket_name}/{data_generator.sink_object_key}/”}],}
)

# Trigger to kick off the crawler
cfn_trigger = glue.CfnTrigger(self, id=”MyCfnTrigger”,
actions=[{‘crawlerName’: crawler.name}],
type=”SCHEDULED”, description=”ara_crawler_trigger”,
name=”min_based_trigger”, schedule=”cron(0/5 * * * ? *)”, start_on_creation=True,
)

In addition to this library construct, the example also includes lower level constructs (commonly called L1 constructs) from the AWS CDK standard library. This shows that you can combine constructs from any CDK library interchangeably.

For use cases where customers have a need to adjust the default configurations in order to align with their organization specific requirements (e.g. data retention rules), the constructs can be changed through the class parameters as shown in this example:

storage = ara.DataLakeStorage(scope=self, id=”storage”, raw_archive_delay=180, clean_archive_delay=1095)

Finally, you can deploy the solution using the AWS CDK CLI from the root of the application with this command: cdk deploy. Once you deploy the solution, AWS CDK provisions the AWS resources included in the Constructs and you can log into your AWS account.

Go to the Athena console and start querying the data. The AthenaDemoSetup provides an Athena workgroup called “demo” that you can select to start querying the BatchReplayer data very quickly. Data is stored in the DataLakeStorage and registered in the DataLakeCatalog. Here is an example of an Athena query accessing the customer data from the BatchReplayer:

Accelerate the implementation

Earlier in the post we pointed out that the library simplifies and accelerates the development process. First, writing Python code is more appealing than writing CloudFormation markup code, either in json or yaml. Second, the CloudFormation template generated by the AWS CDK for the data lake example is 16 times more verbose than Python scripts.

❯ cdk synth | wc -w
2483

❯ wc -w ara_demo/ara_demo_stack.py
154

Demonstrating end-to-end examples with reference architectures

The AWS native reference architecture is the first reference architecture available. It explains the journey of a fake company, MyStore Inc., as it implements its data platform solution with AWS products and services . Deploying the AWS native reference architecture demonstrates a fully working example of a data platform from data ingestion to business analysis. AWS customers can learn from it, see analytics solutions in action, and play with retail dataset and business analysis.

More reference architectures will will be added to this project in Github later.

Business Story

The AWS native reference architecture is faking a retail company called MyStore Inc. that is building a new analytics platform on top of AWS products. This example shows how retail data can be ingested, processed, and analyzed in streaming and batch processes to provide business insights like sales analysis. The platform is built on top of the CDK Constructs from the core library to minimize development effort and inherit from AWS best practices.

Here is the architecture deployed by the AWS native reference architecture:

The platform is implemented in purpose-built modules. They are decoupled and can be independently provisioned but still integrate with each other. The global platformMyStore’s analytics platform has been able to deploy the following modules thanks to:

Data Lake foundations: This mandatory module (based on DataLakeCatalog and DataLakeStorage core constructs) is the core of the analytics platform. It contains the data lake storage and associated metadata for both batch and streaming data. The data lake is organized in multiple Amazon S3 buckets representing different versions of the data. (a) The raw layer contains the data coming from the data sources in the raw format. (b) The cleaned layer contains the raw data that has been cleaned and parsed to a consumable schema. (c) And the curated layer contains refactored data based on business requirements.

Batch analytics: This module is in charge of ingesting and processing data from a Stores channel generated by the legacy systems in batch mode. Data is then exposed to other modules for downstream consumption. The data preparation process leverages various features of AWS Glue, a serverless data integration service that makes it easy to discover, prepare, and combine data for analytics, machine learning, and application development via the Apache Spark framework. The orchestration of the preparation is handled using AWS Glue Workflows that allows managing and monitoring executions of Extract, Transform, and Load (ETL) activities involving multiple crawlers, jobs, and triggers. The metadata management is implemented via AWS Glue Crawlers, a serverless process that crawls data sources and sinks to extract the metadata including schemas, statistics and partitions. It saves them in the AWS Glue Data Catalog.

Streaming analytics: This module is ingesting and processing real time data from the Web channel generated by cloud native systems. The solution minimizes data analysis latency but also to feed the data lake for downstream consumption.

Data Warehouse: This module is ingesting data from the data lake to support reporting, dashboarding and ad hoc querying capabilities. The module is using an Extract, Load, and Transform (ELT) process to transform the data from the Data Lake foundations module. Here are the steps that outline the data pipeline from the data lake into the data warehouse. 1. AWS Glue Workflow reads CSV files from the Raw layer of the data lake and writes them to the Clean layer as Parquet files. 2. Stored procedures in Amazon Redshift’s stg_mystore schema extract data from the Clean layer of the data lake using Amazon Redshift Spectrum. 3. The stored procedures then transform and load the data into a star schema model.

Data Visualization: This module is providing dashboarding capabilities to business users like data analysts on top of the Data Warehouse module, but also provides data exploration on top of the Data Lake module. It is implemented with Amazon Quicksight, a scalable, serverless, embeddable, and machine learning-powered business intelligence tool. Amazon QuickSight is connected to the data lake via Amazon Athena and the data lake via Amazon Redshift using direct query mode, in opposition to the caching mode with SPICE.

Project Materials

The AWS native reference architecture provides both code and documentation about MyStore’s analytics platform:

Documentation is available on GitHub and comes in two different parts:

The high level design describes the overall data platform implemented by MyStore, and the different components involved. This is the recommended entry point to discover the solution.
The analytics solutions provide fine-grained solutions to the challenges MyStore met during the project. These technical patterns can help you choose the right solution for common challenges in analytics.

The code is publicly available here and can be reused as an example for other analytics platform implementations. The code can be deployed in an AWS account by following the getting started guide.

Conclusion

In this blog post, we introduced new AWS CDK content available for customers and partners to easily implement AWS analytics solutions with the AWS Analytics Reference Architecture. The core library provides reusable building blocks with best practices to accelerate the development life cycle on AWS and the reference architecture demonstrates running examples with end-to-end integration in a business context.

Because of its reusable nature, this project will be the foundation for lots of additional content. We plan to extend the technical scope of it with Constructs and reference architectures for a data mesh. We’ll also expand the business scope with industry focused examples. In a future blog post, we will go deeper into the constructs related to Amazon EMR Studio and Amazon EMR on EKS to demonstrate how customers can easily bootstrap an efficient data platform based on Amazon EMR Spark and notebooks.

Announcing SQL Server to Snowflake Migration Solutions

Posted on March 17, 2022May 21, 2023 by Mark Feehily0

It’s Spring (or at least it will be soon), and while nature may take the Winter off from growing it’s product, Mobilize.Net did not. As Snowflake continues to grow, SnowConvert continues to grow as well. Last month, Mobilize.Net announced SnowConvert for Oracle, the first follow-up to the immensely popular SnowConvert for Teradata. This month? It’s time for SnowConvert for SQL Server.

SQL Server has been Microsoft’s database of choice since before Windows was in existence. It has provided a lightweight option for thousands of application’s back-end, and has evolved to be a comprehensive database platform for thousands of organizations. As an on-solution, SQL Server carried many developers and organization through the 90s and early 2000s. But like other on-prem solutions, the cloud has come. Even Microsoft has taken it’s database-ing to the cloud through Azure and Synapse. Snowflake has taken the lead as the Data Cloud, and SnowConvert is the best and most experienced way to help you get there.

If you have SQL Server, I would hope the SQL you have written for SQL Server is not quite as old as the first version of windows. But even if it is and the architects of that original SQL are nowhere to be anymore, SnowConvert’s got you covered. SnowConvert automates any code the conversion of any DDL and DML that you may have to an equivalent in Snowflake. But that’s the easy part. The hard problem in a code migration for databases is the procedural code. That mean Transact SQL for MSSQL Server. And with T-SQL, SnowConvert again has you covered.

Procedures Transformed

SnowConvert can take your T-SQL to functionally equivalent JavaScript or Snowflake Scripting. Both our product page and documentation have more information on the type of transformation performed, so why not show you what that looks like on this page? Let’s take a look at really basic procedure from the Microsoft Adventure Works database and convert into functionally equivalent JavaScript. This is a procedure that does an update to a table:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int],
@NationalIDNumber [nvarchar](15),
@BirthDate [datetime],
@MaritalStatus [nchar](1),
@Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
UPDATE [HumanResources].[Employee]
SET [NationalIDNumber] = @NationalIDNumber
,[BirthDate] = @BirthDate
,[MaritalStatus] = @MaritalStatus
,[Gender] = @Gender
WHERE [BusinessEntityID] = @BusinessEntityID;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END;

Pretty straightforward in SQL Server. But how do you replicate this functionality in JavaScript automatically? Of course, by using SnowConvert. Here’s the output transformation:

CREATE OR REPLACE PROCEDURE HumanResources.uspUpdateEmployeePersonalInfo (BUSINESSENTITYID FLOAT, NATIONALIDNUMBER STRING, BIRTHDATE DATE, MARITALSTATUS STRING, GENDER STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
// This section would be populated by SnowConvert for SQL Server’s JavaScript Helper Classes. If you’d like to see more of the helper classes, fill out the form on the SnowConvert for SQL Server Getting Started Page.
// END REGION

/*** MSC-WARNING – MSCEWI1040 – THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/* SET NOCOUNT ON*/
;
try {
EXEC(` UPDATE HumanResources.Employee
SET NationalIDNumber = ?
, BirthDate = ?
, MaritalStatus = ?
, Gender = ?
WHERE BusinessEntityID = ?`,[NATIONALIDNUMBER,BIRTHDATE,MARITALSTATUS,GENDER,BUSINESSENTITYID]);
} catch(error) {
EXEC(`CALL dbo.uspLogError(/*** MSC-WARNING – MSCEWI4010 – Default value added ***/ 0)`);
}
$$;

SnowConvert creates multiple helper class functions (including the EXEC helper called in the output procedure) to recreate the functionality that is present in the source code. SnowConvert also has finely tuned error messages to give you more information about any issues that may be present. You can actually click on both of the codes in the output procedure above to see the documentation page for that error code.

Want to see the same procedure above in Snowflake Scripting? Interested in getting an inventory of code that you’d like to take the cloud? Let us know. We can help you get started and understand the codebase you’re working with. If you’re already familiar with SnowConvert in general, SnowConvert for SQL Server has all the capabilities that you’ve come to expect. From the ability to generate granular assessment data to functionally equivalent transformations built upon a semantic model of the source code, SnowConvert for SQL Server is ready to see what you can throw at it. Get started today!

10 KPI Templates and Dashboards for Tracking KPI’s

Posted on March 10, 2022May 21, 2023 by Mark Feehily0

Introduction
What Instruments Do We Need to Build an Effective Dashboard for KPIs?

The Top Dashboards for Tracking KPIs
Sing App Admin Dashboard
Retail Dashboard from Simple KPI
Light Blue React Node.js
Limitless Dashboard
Cork Admin Dashboard
Paper Admin Template
Pick Admin Dashboard Template
Able Pro Admin Dashboard
Architect UI Admin Template
Flatlogic One Admin Dashboard Template

Introduction

KPIs or Key Performance Indicators are a modern instrument to make a system (business, for example) work effectively. KPIs show how successful the business is, or how professional the employee is. It works with the help of measurable values, that are intended to show the success of achieving your strategic goals. KPIs are measurable indicators that you should track, calculate, analyze, and represent. If you read this article, it means that you want to find or build an app to help you in all operations above. But before we list the top dashboard KPI templates, it’s essential to understand how exactly to choose a set of indicators that boost the growth of a business. For KPIs to be useful, they should be relevant to a business. That is crucial not only for entrepreneurs who try to improve their businesses but also for the developers of the software for tracking KPIs. Why?

Developers need to be aware of what instruments they should include in the app so the users will be able to use KPI’s easily and effectively. Since there are much more than a handful of articles and approaches on how to find the right performance indicators, what KPIs to choose, how to track them, development of a quality web application can be complicated.

However, from our point of view, the most challenging part of such an app is building a dashboard that displays all necessary KPIs on a single screen. We have explored the Internet, analyzed different types of tools to represent KPIs, found great dashboards, and make two lists: one consists of the charts and instruments you should definitely include in your future app, the other is top dashboards we found that contain elements from the first top. Each KPI template on the list is a potent tool that will boost your metrics considerably. Let’s start from the first list.

Enjoy reading!

What Instruments Do We Need to Build an Effective Dashboard for KPIs?

Absolute numerical values and percentage (in absolute amount)

With the help of percentage, you can make it more informative by adding the comparison of KPI with the previous periods.

The source: https://vuestic.epicmax.co/admin/dashboard

Non-linear chart

One of the core charts.

The source: https://visme.co/blog/types-of-graphs/

Bar chart

Another core element to display KPIs.

The source: http://ableproadmin.com/angular/default/charts/apex

Stacked Bar Graphs

It’s a more complex instrument, but more informative respectively.

Progress bars

Can be confused with a horizontal bar chart. The main difference: a horizontal bar chart is used to compare the values in several categories, while a progress bar is supposed to show the progress in a single category.

The source: https://vinteedois.com.br/progress-bars/

Pie charts

The source: https://www.cleanpng.com/png-pie-chart-finance-accounting-financial-statement-3867064/preview.html

Donut chart

You can replace pie charts with a donut chart, the meaning is the same.

The source: http://webapplayers.com/luna_admin-v1.4/chartJs.html

Gauge chart

This chart helps users to track their progress towards achieving goals. It’s interchangeable with a progress bar.

The source: https://www.datapine.com/kpi-examples-and-templates/finance

Pictograms

Instead of using an axis with numbers, it uses pictures to represent a relative or an absolute number of items.

The source: https://www.bootstrapdash.com/demo/corona/jquery/template/modern-vertical/pages/charts/justGage.html

Process behavior chart

Especially valuable for financial KPIs. The mainline shows measurement over time or categories, while two red lines are control limits that shouldn’t be surpassed.

The source: https://www.leanblog.org/2018/12/using-process-behavior-charts-to-compare-red-bead-game-willing-workers-and-baseball-teams/

Combined bar and line graph

The source: https://www.pinterest.co.uk/pin/254031235216555663/

Some additional tools:

These tools are also essential for building a dashboard for tracking KPI: calendar, dropdowns, checkboxes, input fields. The option to create and download a report will also be helpful.

The Top Dashboards for Tracking KPIs

Sing App Admin Dashboard

The source: https://demo.flatlogic.com/sing-app-vue/#/app/main/visits

If you look through a huge number of KPI templates and don’t find one that you need, you should take a look at Sing app. Sing is a premium admin dashboard template that offers all necessary opportunities to turn data into easy to understand graphs and charts. Besides all charts and functions listed above, with Sing, you get such options as downloading graphs in SVG and PNG format, animated and interactive pointer that highlights the point where the cursor is placed, and change the period for values calculation inside the frame with the graph!

MORE INFO
DEMO

Retail Dashboard from Simple KPI

The source: https://dashboards.simplekpi.com/dashboards/shared/NT0B93_AnEG1AD7YKn60zg

That is a dashboard focused on the retail trade sphere. It already contains relevant KPIs and Metrics for that sector, so you need just to download it and use it. Since it’s an opinioned dashboard you will not get a great customization option. If you are a retailer or trader you should try that dashboard to track the performance when selling goods or services.

MORE INFO
DEMO

Light Blue React Node.js

The source: https://flatlogic.com/templates/light-blue-react-node-js/demo

It is a React Admin dashboard template with Node.JS backend. The template is more suited for KPIs that reflect goals in web app traffic analysis, revenue and current balance tracking, and sales management. However, Light blue contains a lot of ready-to-use working components and charts to build a necessary dashboard. It’s very easy to customize and implement, both beginners in React and professional developers can benefit from that template and get a track on KPIs, metrics, and business data.

MORE INFO
DEMO

Limitless Dashboard

The source: http://demo.interface.club/limitless/demo/Template/layout_1/LTR/default/full/index.html

Limitless is a powerful admin template and a best-seller on ThemeForest. It goes with a modern business KPI dashboard that simplifies the processes of monitoring, analyzing, and generating insights. With the help of that dashboard, you can easily monitor the progress of growing sales or traffic and adjust the sales strategy according to customer behavior. Furthermore, the dashboard contains a live update function to keep you abreast of the latest changes.

MORE INFO
DEMO

Cork Admin Dashboard

The source: https://designreset.com/cork/ltr/demo4/index2.html

That is an awesome bootstrap-based dashboard template that follows the best design and programming principles. The template provides you with more than 10 layout options and Laravel Version of the extremely rare dashboard. Several pages with charts and two dashboards with different metrics ensure you have the basic elements to build a great dashboard for tracking KPI.

MORE INFO
DEMO

Paper Admin Template

The source: https://xvelopers.com/demos/html/paper-panel/index.html

This template fits you if you are looking for a concrete solution since Paper goes with eleven dashboards in the package! They all are unnamed so it will take time to look through them, but that time will be less than time for building your dashboard. Every dashboard provides a simple single-screen view of data and allows sharing it with your collages.

MORE INFO
DEMO

Pick Admin Dashboard Template

The source: http://html.designstream.co.in/pick/html/index-analytic.html

Pick is a modern and stylish solution for the IT industry. It’s a multipurpose dashboard that helps you to gain full control over the performance.

MORE INFO
DEMO

Able Pro Admin Dashboard

The source: http://ableproadmin.com/angular/default/dashboard/analytics

If you believe that the most qualified products are the most rated products, take a look at Able pro. Able pro is a best-rated bootstrap admin template on Themeforest. The human eye captures information within the graph blazingly fast! With that dashboard, you can go much deeper into the understanding of KPIs and make the decision-making process much easier.

MORE INFO
DEMO

Architect UI Admin Template

The source: https://demo.dashboardpack.com/architectui-html-pro/index.html

Those who download Architect UI make the right choice. This KPI template created with hundreds of build-in elements and components, and three blocks of charts. The modular frontend architecture makes dashboard customization fast and easy, while animated graphs provide insights about KPIs.

MORE INFO
DEMO

Flatlogic One Admin Dashboard Template

The source: https://templates-flatlogic.herokuapp.com/flatlogic-one/html5/dashboard/visits.html

Flatlogic one is a one-size-fits-all solution for any type of dashboard. It is a premium bootstrap admin dashboard template that has been released recently in July 2020. It goes with two developed dashboards that serve well as KPI templates: analytics and visits. But it also offers four additional pages with smoothly animated charts for any taste and needs. The dashboard is flexible and highly customizable, so you easily get the benefit from that template.

MORE INFO
DEMO

Thanks for reading.

You might also like these articles:

14+ Best Node.js Open Source Projects

8 Essential Bootstrap Components for Your Web App

Best 14+ Bootstrap Open- Source Projects

The post 10 KPI Templates and Dashboards for Tracking KPI’s appeared first on Flatlogic Blog.

Welcome Snowflake Scripting!

Posted on February 10, 2022May 21, 2023 by Mark Feehily0

The Snowflake Data Platform is full of surprises.

Since Snowflake got its start,
you have had the ability to create stored procedures.
However, this capability was limited to using JavaScript to write those stored procedures.

Using JavaScript opens up great potential, but JS is a new language and some data engineers may
face a steep learning curve.

Well… not anymore. Let’s welcome SNOWFLAKE SCRIPTING!!!!!

What is Snowflake Scripting?

Snowflake has just extended their SQL dialect to allow programmatic instructions.
For example, you can now write conditional blocks:

CREATE OR REPLACE TABLE TEST(LINE VARCHAR);
INSERT INTO TEST(LINE) VALUES(‘FIST LINE’);

EXECUTE IMMEDIATE $$
DECLARE
COUNT INT;
BEGIN
SELECT COUNT(*) INTO :COUNT FROM TEST;
IF (COUNT < 2) THEN
INSERT INTO TEST(LINE) VALUES(‘SECOND LINE’);
RETURN ‘INSERTED’;
END IF;
RETURN ‘NOT INSERTED. COUNT = ‘ || :COUNT;
END;
$$;

Ok. Now that we’ve seen a conditional block, let’s see in more detail what Snowflake Scripting brings to the table.

Variable Declaration

Snowflake Scripting provides a declare section just before your
BEGIN/
END block. You can
declare variables in that section. If you want them to have an initial value, you can use the DEFAULT clause. Here’s an example:

EXECUTE IMMEDIATE $$
DECLARE
VAR1 VARCHAR DEFAULT ‘Hello World’;
BEGIN
return VAR1;
END;
$$;

You could also declare them inline with your code using a let statement and “:=”. Here’s an example of that:

EXECUTE IMMEDIATE $$
BEGIN
let VAR1 VARCHAR := ‘Hello World’;
return VAR1;
END;
$$;

Passing variables to SQL statements in Snowflake Scripting

Binding variables is something that was a little more complicated in the JavaScript world. But in Snowflake Scripting, it’s super easy to
pass variables to SQL statements. Just remember to use a semicolon (‘:’) before the variable name as shown here:

EXECUTE IMMEDIATE
$$
BEGIN
let VAR1 VARCHAR := ‘Hello World’;
CREATE OR REPLACE TABLE TEST AS select :VAR1 as LINE;
END;
$$;

Reading values into variables

Retrieving results is also easy. You can
read values into a variable as shown below, but just like before, remember to use a semicolon (‘:’) character before the variable name.

EXECUTE IMMEDIATE
$$
BEGIN
let VAR1 INT := 0;
select 1000 INTO :VAR1;
return VAR1;
END;
$$;

This will print something like this:

+—————–+

| anonymous block |

|—————–|

| 1000 |

+—————–+

And what about non-scalar values? What about doing a query?

That can be done too:

execute immediate
$$
BEGIN
CREATE OR REPLACE TABLE MYTABLE as SELECT $1 as ID, $2 as Name FROM VALUES(1,‘John’),(2,‘DeeDee’);
LET res RESULTSET := (select Name from MYTABLE ORDER BY ID);
LET c1 CURSOR for res;
LET all_people VARCHAR := ”;
FOR record IN c1 DO
all_people := all_people || ‘,’ || record.Name;
END FOR;
RETURN all_people;
END
$$;

And it will print something like:

+—————–+

| anonymous block |

|—————–|

| ,John,DeeDee |

+—————–+

The results from a query are called a
RESULTSET. To iterate on the results of a query, you can open a cursor for that
RESULTSET and the user a
FOR with the cursor variable.

Conditional Logic

Snowflake Scripting brings operators to branch on conditions. You can use both
IF and
CASE. For example:

EXECUTE IMMEDIATE
$$
DECLARE
VAR1 INT DEFAULT 10;
BEGIN
IF (VAR1 > 10) THEN
RETURN ‘more than 10’;
ELSE
RETURN ‘less than 10’;
END IF;
END;
$$;

With this being the result:

+—————–+

| anonymous block |

|—————–|

| less than 10 |

+—————–+

Snowflake Scripting can be
super convenient to
simplify some administrative tasks.
For example, I usually have this code on a Snowflake worksheet and I need to change it each time I need to create a test database.

create database database1;
create warehouse database1_wh;
create role database1_role;
grant ownership on database database1 to database1_role;
grant ownership on schema database1.public to database1_role;
grant ownership on warehouse database1_wh to database1_role;
grant role database1_role to user USER1;

I can use a Snowflake Scripting block and then I only need to change the database and user 🙂

execute immediate
$$
declare
client varchar default ‘database1’;
user varchar default ‘user1’;
sql varchar;
begin
execute immediate ‘create database if not exists ‘ || client;
execute immediate ‘create warehouse if not exists ‘ || client || ‘_wh’;
execute immediate ‘create role if not exists ‘ || client || ‘_role’;
execute immediate ‘grant ownership on database ‘ || client || ‘ to ‘ || client || ‘_role’;
execute immediate ‘grant ownership on schema ‘ || client || ‘.public to ‘ || client || ‘_role’;
execute immediate ‘grant ownership on warehouse ‘ || client || ‘_wh to ‘ || client || ‘_role’;
execute immediate ‘grant role ‘ || client || ‘_role to user ‘ || user;
end;
$$;

Snowflake Scripting for me and all of us here at Mobilize.Net is a game changer. It really makes it easier for people coming from Teradata, Oracle, or SQL Server to start enjoying the Snowflake Data Platform.

As a matter of fact, Mobilize.Net
SnowConvert is being updated so you can start modernizing your
Oracle,
Teradata, and SQL Server to Snowflake Scripting. So we hope you enjoy it as much as I am enjoying it.

Thanks for reading!

Cloud Data Warehouse Explained

Posted on October 5, 2021June 3, 2023 by 0

Possibly the single-most wildly anticipated and over-hyped IPO of 2020 was Snowflake (SNOW:NYSE), a hot provider of cloud data warehouse software. (Snowflake, in keeping with all hot young startups, likes to re-invent the standard terminology in their space so they have decided that “data warehouse” is out and “data cloud” is in.)

If you’re late to the party or didn’t get the memo, you might be asking yourself what all the fuss is about. As someone who got to the part way late–all the peeled shrimp had already been eaten–let me try to help you out.

Data warehouse, data lakes, data clouds: where does it end?

There’s a straight line from the wadded up grocery list in my pocket to a data cloud, and it looks something like this: from a list of things to a table (things with properties) to a database (multiple tables) to a data warehouse (multiple databases) to a data lake (data warehouses plus the kitchen sink) to data cloud (who needs hardware?).

Application software developers are usually intimately familiar with databases, whether Access, SQL Server, Oracle, MySQL, or whatever. It’s hard to write a useful application that doesn’t rely on some kind of data, and probably 90+ percent of business applications are just front ends for databases, allowing CRUD (create, replace, update, and delete) functions with associated business rules and logic. Our Salmon King Seafood demo app is exactly that.

But as businesses grow, they tend to have multiple databases–one for finance, one for HR, one for sales, one for manufacturing, and on and on. While reporting from a single database is relatively easy (that’s what SQL is for), reporting across multiple databases can be tricky, especially if the schemas aren’t properly normalized across different tables in different databases. Suppose, for example, the sales database tracks contract dates as DDMMYYY and the finance database tracks receipt dates as MMDDYYYY: any report that wants to examine the lag between a signed contract and receipt of funds will have to do some process to normalize the dates from the two sources. Multiply that trivial example by multiple databases, often in different platforms with different data type definitions, SQL extensions, and software versions and it can get messy fast.

But that’s not all. Many production databases are heavily transaction focused–think of a sales database for a large e-commerce site. If your website is taking orders 24×7 because you sell worldwide, when can you run big queries without hurting responsiveness for customers trying to make a purchase?

A data warehouse is not a database

The answer is a data warehouse, which pulls together data from multiple databases, normalizes it, cleanses it, and allows for queries across different data sources using a common data model. This not only reduces the hit on production databases, but, because the ETL (extract, transform, and load) processes create normalized data across all the tables in the warehouse, information consumers (management, business analysts, and even partners) can produce rich reports with minimal impact on the IT organization.

Since their widespread adoption dating back to the 80s and 90s, enterprise data warehouses (EDWs) have become de rigueur in larger organizations. Companies like IBM, Oracle, and Teradata gobbled up market share with high-performance appliances for ingesting, processing, and running queries on enterprise data.

Enter the cloud

Like a tornado suddenly developing from a dark cloud and that proceeds to rip apart everything in its path, the gathering storm of IoT and public cloud upended a lot of EDW strategies.

Seemingly overnight organizations have petabytes of data flooding in from connected devices all over the world. Drawing insights from that data requires both a high-performance computing platform as well as vast capacities in both structured and unstructured data. Data lakes allow for extremely large storage of unstructured data for consumption by AI models to create business intelligence. Cloud storage and elastic cloud processing slashes the investment and direct costs of storing and processing these vast rivers of raw data.

In the “old” model you might have to build up capacity by adding more appliances and disk storage to handle your peak loads, with a public cloud EDW the compute capacity can expand or contract with usage while maintaining acceptable performance for running big queries or scripts. Plus, BLOB (binary large object) storage costs for AWS or Azure are quite inexpensive compared to arrays of on-prem disk drives.

The only real downside of public cloud-based EDWs was stickiness–if your warehouse was in AWS is was difficult to move it to Azure, and vice versa.

Along comes Snowflake

Last year Snowflake was the hottest thing no one had ever heard of. Overnight the CEO becomes a newly minted billionaire (who hasn’t gone into space yet, but it’s early days) and the stock price went through the roof on day 1.

Why?

Well, hype mainly. Yet the hype had some basis in reality. Let’s look at the things that make the Snowflake cloud data warehouse different:

Cloud agnostic: You can run your Snowflake instance in Azure, AWS, or wherever you want. You can easily move it from one cloud provider to a different one; something that isn’t possible if you’re using Azure or Amazon Redshift. In general, customers are wary of vendor lock-ins.
Only pay for what you use: Snowflake’s pricing model is focused on your compute usage, rather than storage. And unlike many other EDW solutions, you only pay for what you use. If you’ve got a ginormous query you only run once a month, you don’t have to pay the rest of the month for that surge capacity.
Shared data: Snowflake dramatically reduced the complexity or time it takes to share data among disparate sources. They’re building a public data marketplace with some government databases already in place. Doesn’t really matter if your data is in AWS and the partner’s data you want to consume is in their private cloud: Snowflake makes it really easy to get it.
Super fast performance: They claim queries run much much faster on Snowflake than on other systems. YMMV.
Mix and match data: whether it’s a data lake of unstructured data from IoT devices or highly structured data from finance systems, Snowflake makes it cheap and easy to store, access, and understand that data. Queries across disparate data systems, housed in different architectures and sources, are simple.

Enterprise data warehouses are the domain of very large companies, which are often the acquirers in merger and acquisition (M&A) deals. If BigCo running an EDW in AWS acquires SmallerCo who in turn is running their EDW in Azure, then it will be hard to combine those EDWs without Snowflake. In this situation, a Snowflake data cloud will work seamlessly across both AWS and Azure, eliminating the need for BigCo to convert the SmallerCo data lake and data warehouse to its own system. Further, the Snowflake architecture makes it easy to query very large data sets without the need to pay for the capacity to do so even when it’s idle most of the time.

If Snowflake’s so great, why isn’t everyone using it?

Recently I was watching a show featuring an architect in Dublin, Ireland and in one episode he’s redoing a house for a family that moved from California; they get a shipping container delivered with all their furniture and appliances from the US. My immediate thought was that those appliances weren’t going to be much use in a country with 220v and different plug standards.

Changing platforms or systems can be hard. And changing data warehouses can be really hard.

We’ll get into this in more detail in a subsequent post, but basically consider the things you have to do for a migration from, say Teradata to Snowflake:

Create the new Snowflake data warehouse, training the existing user base on the new platform
Duplicate all the data in the existing Teradata instance and move it into Snowflake
Extract the code (stored procedures, BTEQ scripts, and so forth) from the Teradata instance
Convert the code: Teradata SQL to Snowflake SQL, BTEQ to Python, procedures to JavaScript. This can be the most challenging part of the migration.
Identify and transfer all the associated processes like ETL and BI reporting/analysis
Run the new system in parallel with the existing Teradata system and compare all the results for a thorough QA review.
When everything checks out, turn off Teradata and enjoy the new cloud data warehouse.

For a large data warehouse system, with lots of ETL and BI processes depending on it, this can be a very challenging project to say the least. This is why Snowflake turned to Mobilize.Net to get help with the code conversion part, relying on our ability to create automated tools that can migrate over 90% of the code in the Teradata system. Our Snowconvert tool solves a big piece of the problem to get from an existing data warehouse to the Snowflake data cloud. If you’d like learn more, let us know.