Today’s companies compete in ever more competitive and evolving markets. Finance companies are facing growing pressure to go beyond pure accounting management. Instead, handle everything like accounting, cash, assets, and programs. Besides, it handles cash, recruitment, programs, and planning.It handles  all to promote global development of the organization.

 It can achieve sustainability and provide strategic guidance. Besides, companies meet the dynamic and rigorous enforcement and regulatory oversight criteria. Traditional approaches also tackle these core functions across fragmented structures. This makes it hard to provide feedback to frontline managers. Thus, it is difficult to check performance. This makes it difficult to plan for the future in real-time.So in order to overcome these difficulties we need financial management as follows.

Workday financial management 

Workday has developed a single framework using the in-memory and object data model. It facilitates transaction management, multidimensional reporting, aggregation, planning, and compliance. It uses a clear UI that is accessible from desktop or mobile devices.

 You can manage Workday Financial Management on an agile, global platform. It delivers the core financial management capabilities from a cloud solution to organizations. It goes far beyond s to gain greater transparency. This improves financial restructuring and shorten closing time. Besides, it instills internal control and audit ability. Thus, it gains continuity through global operations. For more info Workday Online Training

Areas of Capabilities and Workday Features:

The workday software is capable in the following areas.

• Accounting and Financing

• Sales Management

• Financial Reporting and Restructuring

• Financial Planning

• Ventures  

• Procurement  

• Inventory

• Project Management

• Project Billing

• Audit and Internal Monitoring Primary Advantages

• Get a more comprehensive and reliable image of the market

• Team members and members

 Workday lets you catch the specifics of each financial transaction. This includes who, when, where, and why to represent and educate all teams . I hope you have learnt about the areas of workday financial management.If not move to know discuss features.

Features:

The features are as follows.

Built for Change

 The revolutionary Workday helps you to adapt to changes in organization, business processes. Besides workday reports, occur even after implementation, without any extra cost.

 Built for Finance and Business Users

 The UI enables business, financial professionals to use the software with minimal training. The workday experience also helps managers and line management. It also provides quick access to knowledge that affects their day-to-day business decisions.

 Workday Financial Planning takes advantage of real-time financial data. This is to streamline the planning process and speed up time to action. This helps companies to build and install financial business plans.

 It becomes difficult managing various systems, or downloading cross-functional teams. You may develop coordination. Besides, you can take action within Workday on budgets and forecast everything. More often than not priorities and market conditions shift. This leads to an organization altering its strategies. Besides projections and share them with key stakeholders.

 Automate and track your cash flow:

 It shows you real-time cash flows and transactions help you handle capital. Besides, make good finance, payment, and money collection decisions. The settlement engine for Workday gives you visibility in all transactions. The transactions include spending, sales, finance, and payroll. You can also predict cash flow into the future by understanding the inflow and outflow.

It documents all the rich operating details related to transactions. You can expect accounting details of a general global ledger. Thus, it provides perspectives beyond conventional aspects of accounting. It meets criteria for global regulatory and financial reporting.

 You can integrate Global capabilities into the core system. This includes multi-currency, multi-language, multi-bank, and more. Besides it meets international needs today and into the future.

 Streamline your account map for a speedier, more precise financial closing. The working day has operating agencies, business divisions to complete inter-company transactions. Besides performing eliminations, assignments, changes and streamlined reporting with ease.

 Fixed Assets

 Big and Small Assets are massive, costly, and on the floor. The conventional fixed-asset structures manage the accounting of large, immovable assets. You can design to help companies manage the high-value, low-cost tools. Besides there are so many companies now rely on today’s workforce. These include computers, mobile phones, and web-conference accounts. Besides, it also has security badges and other “utility belt” products. Workday combines the separate domains of fixed assets. The inventories with the capacity to manage those products. They may be small and inexpensive but still important to your business.

Real-Time Financial Consolidation and Reporting

 Workday captures the company aspects at the point of sale. It preserves them through transactional management. Besides, it has financial reporting to gather rich market intelligence. Additionally, the Workday in-memory system generates and monitors real-time accounting and financial reports. This removes the need for batch processing. This reduces the time spent on reconciliation.

 It also simplifies financial restructuring. You can perform this operation whenever accounting is necessary. This is to ensure that operating and financial statements represent the current image. Workday also makes the close process simple to control. This is to track, with checklists and dashboards that provide consistency in information. You can gather interpret information to ensure that the team has the trust that numbers. Besides, it requires processes, and reporting.

 If unified and accessible, you can interact with the data. This is through operational and statutory reporting. You can report into comprehensive transactions for further review. Then you can take similar measures. Therefore you should design financial scorecards to represent the most realistic financial image. This is with metrics to provide back-to-business full insights. Therefore, finance will react quickly and efficiently. Thus, it motivates the rest of the business with a clear source of truth.

Proactive Audit and Compliance

 Workday remains the traditional bolt-on governance, risk, and compliance (GRC) system model. This only meets the various requirements around the globe. Besides, you can build the fabric of Workday. You may call it the business process framework (BPF). It is the cornerstone of internal control and governance. Companies can customize and envision internal business processes with BPF. This includes an “always on” audit method to constant capture. Imagine who took what action in the system. This is when, where, and with which business process all without extra costs.

 The integrated business process system manages each transaction. It collects all the required information, follows the approvals programmed. Then this ensures that the audit accounting effect of each transaction immediately upon approval. You can pre-configure Auditor reports and dashboards to track activities. Besides there is enforcement for the reports. Thus enables real-time monitoring of patterns. These include such as a growing amount of expenditures without receipts. You can drill down from those dashboards to take further action to rectify any areas of concern.

 Organizations around the world can accommodate local regulations. This increases visibility of the processes and reduces our business processes, audit trails. Besides providing safety models for integrating into Workday, this eliminates the need for a separate solution.

Key Benefits of Workday financial management 

• Offer specific, personalized financial insights to executives and business managers. Thus, these are accessible on the computer of their choice.

• Offer best in class and ubiquitous “always-on” audit capabilities.

• Accept improvements in structure, process and reporting without business interruption.

• Provide a clear and easy-to-use interface for all users. These include staff, administrators, administrators and auditors.

Conclusion:

I hope you people have got an enough idea regarding the workday financial management.  The Workday Training Program helps to learn more concepts. These include concepts of Accounting and Finance, Revenue Control, Financial Reporting. Besides, it has concepts like Restructuring, Financial Planning, Project Billing and many more. ITGuru training will help you become a specialist in Workday Financial Management. This is with Real-time assignments and use cases. Our team of experts will provide you with the best possible help and guidance.

As a Network Engineer, I hated filling out tickets. Anytime a router would reboot or a power outage took place at a remote site, the resulting ticket generation took up about 50% of my day. If there had been a way to automate ticket creation, I would have saved a lot of time. The only thing unique to provide would have been case-specific comment sections needing additional information about the issue.

While ticket creation was a vital activity, automating this was not an option at the time. This is surprising because my management was always asking me to include more information in my tickets. Tickets were often reviewed months later and sometimes never got created or did not have much relevant information included.

Fast forward to today, companies are now data mining from tickets with a standard set of facts that are pulled directly from the device during ticket creation, such as network platform, software version, uptime, etc.  Network operations (NetOps) teams now use massive amounts of ticket data to make budget decisions.

For example, if there were 400 network outages due to power issues, NetOps could then make a case to spend $40,000 on battery backups, having proved that it would prevent around 400 outages a year. Having access to these metrics is extremely valuable for making informed business decisions. For more info Servicenow Training

This first blog in the series covers how Ansible automates change requests from ServiceNow, a popular cloud-based SaaS provider. For convenience, ServiceNow provides developers a test instance to use Ansible Playbooks, which is utilized for this and future blog posts. You can sign up for your own free developers instance at the ServiceNow Developer portal.

Creating a ServiceNow ticket

The Ansible distribution includes the snow_record module that makes it easy to open and close ServiceNow tickets. The pysnow Python library will first need to be installed to use this module.

The next requirement is getting the username, password and instance for authentication to your recently created developer cloud based ServiceNow instance.

NOTE: the instance should look something like this instance: dev99999 not the full URL

instance:_http://dev99999.service-now.com as shown below.

change_request_vars.yml

#snow_record variables
 
sn_username: admin
sn_password: my_password
sn_instance: dev99999
 
#data variables
 
sn_severity: 2
sn_priority: 2

name: Create ticket with notes
  hosts: localhost
  gather_facts: no
  connection: local

  tasks:
  - name: include vars
    include_vars: change_request_vars.yml

  - name: Create a change request
    snow_record:
      state: present
      table: change_request
      username: "{{ sn_username }}"
      password: "{{ sn_password }}"
      instance: "{{ sn_instance }}"
      data:
        severity: "{{ sn_severity }}"
        priority: "{{ sn_priority }}"
        short_description: "This is a test opened by Ansible"
    register: new_incident

  - debug: 
      var: new_incident.record

Leveraging the ServiceNow API

The table parameter determines what type of ticket will be opened. A great way to determine the other parameters available is to view the JSON dictionary the ServiceNow API sends back after you have created your ticket. I am using register to give a variable name to that dictionary and then using debug to view it in the terminal. The following is just a portion of the full dictionary for the sake of brevity:

This is very handy in spelling out the parameters you can add under the data section of your task. If you want to see just one parameter of the dictionary, for example the ticket number, you can simply modify your debug to look like the following:- debug: var=new_incident.record.number

This variable (var) is defined as pulling from the stored register new_change_request to then show the dictionary named record and the parameter of that dictionary called number. For more info Servicenow Certification

You could do the same thing with any parameter of the record dictionary such as close_code, state, comments, and many others.

Validating changes in ServiceNow web portal

Next, log into your developers instance of ServiceNow and view the Change>all section in the left menu bar. You should see your change request in the list.

Notice that the short description has been filled out by our Ansible Playbook task: This is a test opened by Ansible as well as the priority 2 – High.

Closing a ServiceNow ticket

Now that we’ve demonstrated the opening of ServiceNow tickets, we should demonstrate closing or resolving the ticket as well. This is done by specifying the state parameter in another Ansible task. This is where it can get tricky because state is a parameter of the record dictionary as well as a parameter of the snow_record module. Please be mindful of this multi-purpose parameter used in Ansible.

The following is a snippet from the record dictionary when we created our ticket:

Notice the original state was -5. The Ansible task below will change it to -3, which results in a ticket state changing from New to Authorize.

To get in-depth knowledge, enroll for a live free demo on Servicenow Online Training

More precisely, the structured query language, or SQL, is a language that has been specifically designed for the domain of relational database management systems. This means it is a tool for handling large amounts of data stored in databases. Furthermore, its efficient coding structure has become a ‘role model’ to many other programming languages. Therefore, I can assert that there isn’t a programmer around the globe that hasn’t at least heard of SQL. Let alone that there are so many people either using this language or basing their work involving data manipulation on SQL…

So… Have you heard about Tableau, too?

If you are interested or working within the sphere of business intelligence and data science, I can bet that you have. Or, if you haven’t, let me say in my defense, that you should have!

Tableau is an absolutely wonderful, highly intuitive software that is easy-to-understand even for beginners. Its goal is to help users project their data by offering a huge variety of data visualization tools to choose from. Moreover, this can happen very quickly – just by dragging and dropping the relevant objects you see on the interface.

So, to sum up, we can say that on one hand we have the very well established structured query language, SQL, and on the other – we have Tableau, which is a drag-and-drop software tool allowing you to create awesome visualizations.

Hence, we could pose ourselves the following question:

How do we connect SQL and Tableau?

To answer this, let’s first analyse the bigger picture from a technological perspective to understand why we would want to do that.

Today, the world of programming is represented by various technologies allowing us to approach and solve diverse kinds of problems. And that is a huge advantage.

More precisely, programming allows us to connect and exchange information between servers, software applications and frameworks operating in different domains.

During the last few decades, the list of programming languages and software applications has grown so much that it seems to be endless.

Nevertheless, when we look closer, we can see that each of these technologies boils down to the following structure: they involve the administration and manipulation of a given amount of data to produce a specific type of output that will help businesses improve decision making. For more info Tableau Training

So, when we look at the bigger picture, we can see that today there’s an abundance of software, each relevant for its domain. And from a technological perspective, this is amazing. But what does the picture look like from a business point of view?

Well, imagine you want to solve the following business task:

“Considering the following dataset, create a bar chart showing the breakdown of male and female workers in a company.”

If you have your data in Excel, that seems to be an easy one, right? You can select the columns containing the data of interest, and then insert a chart or graph to your liking.

However, this will not always happen in real life.

Companies often use one software tool for database administration, another tool for computations, a third one for visualizations and so on. The reasons for using a variety of tools can be numerous:

• to optimize the capacity of the chosen software
• practical reasons
• cost reduction
• data security
• historical reasons, or, quite simply,
• because a tool which deals with all domains together does not exist!

And here comes the good news that will unsnarl all complications that may arise because of the last point we made.

All these software applications and languages do not live alone, they are not detached from the entire IT ecosystem.

They can be connected, and they can communicate. That is, they can exchange information between one another.

And if the thought that comes to mind right now is that you would probably need to implement some sort of integration, then you are spot on. In programming terms, you can integrate two or more programming languages and tools, with the idea of extracting a particular type of output.

So, let’s narrow down our theoretical discussion to the integration of SQL and Tableau.

An analyst may desperately need to integrate SQL and Tableau because these two tools can serve a common goal. And this goal is namely answering such business questions as the one mentioned before.

To begin, you can store an enormous amount of data in a database. Then, you can manipulate it with SQL. So, using this language, you will create and maintain the foundations of your analysis.

However, looking at hundreds and hundreds of thousands of rows of numbers and text is typically not so helpful when you are trying to interpret the data.

This is where Tableau can help.

Tableau’s main functions include quickly connecting to a server such as the SQL server, extracting the necessary data, applying relevant calculations, and then visualizing the obtained information.

Speaking in a more technical language, we can say this software will allow you to create graphs, charts, reports, and dashboards – operations that are a must for any business intelligence analyst out there. In fact, it is namely the reports and the dashboards that allow end users such as company executives and general managers to understand the core of a business and extract insights about it.

Hence, to sum up this subsection, integrating SQL and Tableau is about taking your data from the depths of your database, to its esteemed, beautiful representation in Tableau.

Cool! Enough about theory. Let’s get to the highpoint of the article – namely, exploring the most notable ways in which people can connect SQL and Tableau Online Training

Data Visualization

Features in Microstrategy relate to the interface results for data. Although it is seen as a relatively complex tool in general, some engineering designs let users get more from data. For example, there is the ability to add reference lines to scattershot graphs, making some visual results more easily understandable. In terms of recall, a Recently Viewed Objects feature helps guide users back to previously discovered results.

At the same time, this platform can require more expertise in terms of formatting and displaying results. There is generally a bigger need to work with the interface tools to craft something that’s transparent and easily digestible. That means Microstrategy may be less useful for some kinds of tasks that are aimed more at pushing easily aggregated data points than really digging into a full field of enterprise data.

Many reviewers would agree that the top benefits of the Tableau platform involve the ease of use, quick visual builds, and useful and transparent dashboard results that this BI tool is known for. Tableau can, in general, be used by nearly anyone without a lot of advanced technical knowledge. This, in a sense, makes this platform a leader for quick and easy analysis, and for creating visual results a wider audience can use.

The intuitive and user-friendly interface of the Tableau data visualization platform places it above its competitor. Get more info Tableau Training

Tableau data visualizations.

Analytics

MicroStrategy promotes tools for data discovery, enterprise reporting and real-time telemetry, in addition to mobile operations and versatile dashboards. There is also an ability to use metrics as attributes, which can improve the ways company leaders use developed analytics in this platform.

Tableau has the functionality to create customized data operations for individual clients and customers. Other key aspects of the Tableau platform involve the ability to make quick changes or provide alternatives for data results that help drive data use on the fly. At the same time, it is hard to bill Tableau as a full enterprise platform, or at least a Big Data leader, based on existing analytical limitations. Some suggest that this tool should be combined with other more comprehensive Big Data handling offerings, especially for extremely large data sets.

When comparing Tableau vs. MicroStrategy, the latter has the best analytics tools when compared to Tableau. It is more scalable and able to manage Big Data sets.

Data analytics by MicroStrategy.

Online Analytical Processing (OLAP)

MicroStrategy users are able to analyze semi-structured and unstructured OLAP data — the solution is backed by Apache Drill capabilities. Additionally, metrics creation, grouping and filters provide organizational functions. The platform also offers tools such as Intelligent Cubes that help direct work with relational databases.

WIth Tableau, users can access existing databases or add their own data into a cube-like structure. Filters allow the creation of your preferred view of data, while data exploration features like drilling help pinpoint the information you need.

Tableau and MicroStrategy tie for their equivalently functional OLAP systems.

Document Management

The MicroStrategy document management system converts reports into CSV and PDF formats, as well as Microsoft Excel and Access files. The system’s version control features provide viewing access to all past document history.

Specific features that often get mentioned in rave reviews of Tableau are related to the ability to push data into web formats, which is helpful for various kinds of marketing and outreach. In addition to these web formatting features, Tableau can export as a PDF, PNG image, human-readable crosstab CSV file or machine-readable data CSV file. Version control is supported by Tableau document management.

Both of these business intelligence tools have similar document management abilities, resulting in a tie for this feature.

Decision Services

MicroStrategy keeps your business running smoothly with regulatory features, managing finances, security and legal compliance. Analysis of fiscal metrics provides a view into the financial state of your business and guides decision-makers toward the best course of action concerning money matters. Built-in governance tools ensure all business processes are in-line with laws and regulations. The system assesses risk possibility and fraud metrics, protecting your data with embedded security measures.

Tableau promotes its active community of users as a resource for discovering more about this BI option. Consulting services provide further support to the Tableau user community. Tapping into the software’s powerful analytics engine, Tableau users get the added benefit of financial and risk assessment, in addition to everyday business analysis. Unfortunately, a setback of Tableau decision services is the lack of regulatory compliance management.

The deciding difference between MicroStrategy and Tableau decision services is the support of regulatory compliance. For this feature, MicroStrategy has the advantage.

To get in-depth knowledge, enroll for a live free demo on Tableau Online Training

ETL is a type of data integration that refers to the three steps (extract, transform, load) used to blend data from multiple sources. It’s often used to build a data warehouse. During this process, data is taken (extracted) from a source system, converted (transformed) into a format that can be analyzed, and stored (loaded) into a data warehouse or other system. Extract, load, transform (ELT) is an alternate but related approach designed to push processing down to the database for improved performance.

ETL gained popularity in the 1970s when organizations began using multiple data repositories, or databases, to store different types of business information. The need to integrate data that was spread across these databases grew quickly. ETL became the standard method for taking data from disparate sources and transforming it before loading it to a target source, or destination.

In the late 1980s and early 1990s, data warehouses came onto the scene. A distinct type of database, data warehouses provided integrated access to data from multiple systems – mainframe computers, minicomputers, personal computers and spreadsheets. But different departments often chose different ETL tools to use with different data warehouses. Coupled with mergers and acquisitions, many organizations wound up with several different ETL solutions that were not integrated.

Over time, the number of data formats, sources and systems has expanded tremendously. Extract, transform, load is now just one of several methods organizations use to collect, import and process data. ETL and ELT are both important parts of an organization’s broader data integration strategy. For more ETL Testing Certification

Why ETL Is Important

Businesses have relied on the ETL process for many years to get a consolidated view of the data that drives better business decisions. Today, this method of integrating data from multiple systems and sources is still a core component of an organization’s data integration toolbox.

ETL is used to move and transform data from many different sources and load it into various targets, like Hadoop.

• When used with an enterprise data warehouse (data at rest), ETL provides deep historical context for the business.
• By providing a consolidated view, ETL makes it easier for business users to analyze and report on data relevant to their initiatives.
• ETL can improve data professionals’ productivity because it codifies and reuses processes that move data without requiring technical skills to write code or scripts.
• ETL has evolved over time to support emerging integration requirements for things like streaming data.
• Organizations need both ETL and ELT to bring data together, maintain accuracy and provide the auditing typically required for data warehousing, reporting and analytics. 

ETL and Traditional Uses

ETL is a proven method that many organizations rely on every day – such as retailers who need to see sales data regularly, or health care providers looking for an accurate depiction of claims. ETL can combine and surface transaction data from a warehouse or other data store so that it’s ready for business people to view in a format they can understand. ETL is also used to migrate data from legacy systems to modern systems with different data formats. It’s often used to consolidate data from business mergers, and to collect and join data from external suppliers or partners.

ETL With Big Data – Transformations and Adapters

Whoever gets the most data, wins. While that’s not necessarily true, having easy access to a broad scope of data can give businesses a competitive edge. Today, businesses need access to all sorts of big data – from videos, social media, the Internet of Things (IoT), server logs, spatial data, open or crowdsourced data, and more. ETL vendors frequently add new transformations to their tools to support these emerging requirements and new data sources. Adapters give access to a huge variety of data sources, and data integration tools interact with these adapters to extract and load data efficiently.

To get in-depth knowledge, enroll for a live free demo on ETL Testing Online Training

What is ServiceNow?

ServiceNow is a cloud-based IT Service Management tool. It offers a single system of record for IT services, operations, and business management.

2) What is the full form of CMDB?

The full form of CMDB is Configuration Management Database.

3) Name all the products of Services now

ServiceNow offers various type of tools which is design according to the need of a specific user.

• Business Management Applications
• Custom Service Management
• IT Service Automation Application
• HR management

4) What is the use of record matching and data lookup features in ServiceNow?

Data lookup and record matching allow you to define field value based on a specific condition in place of writing scripts. For more info Servicenow Training

5) Explain the term “Business Rule.”

The business rule is server-side scripting. It executes whenever any record is inserted, modified, deleted, displayed or queried. The vital point to keep for creating a business rule is that when and on what action it suppose to execute. You can apply the business rule ‘on display,’ ‘on before’ or ‘on after’ when action is performed.

6) Can you call a business rule with the help of a client script?

Yes, it is possible to call a business rule using a client script. However, you can also use glide ajax for the same.

7) What is domain separation in ServiceNow?

Domain separation is useful ServiceNow method. It helps you to separate data into logically-defined domains. It also provides an option to separate administration.

For example, John is the CEO of two companies, and he is using ServiceNow single instance for both of these businesses. He doesn’t want that user of one business can see data of other business. Here you need to use domain separation to isolate the records from both businesses.

8) State some best practices you should follow while using Service now

Here, are some of the best practices which you need to follow while using Service now:

• You should replace spreadsheets and email with collaborative workspaces.
• You should automate every business processes of your organization.
• You can easily develop a modern work environment using ServiceNow.
• You should aim to enhance, structure and automate the workflow to streamline service delivery.

9) What is a data policy concerning ServiceNow?

You can enforce online data policies by assigning read-only attributes for all the fields. Data policies are almost similar to UI policies. However, the difference between two is that UI policy only applies to data entered on a form by using a standard browser. On the other hand, data policies can apply rules for every data entered into the system.

10) How many types of search options are given in ServiceNow?

Five types of search options in ServiceNow are:

• Lists: Use to find records in a list.
• Global Text search: Helps you records in multiple task tables from a single search field.
• Knowledgebase: Helps you to find knowledge articles.
• Navigation filter: Allows you to filter the items in the application navigator.
• Search scenes: It is a custom module which is created only by administrators.

11) What is the use of HTML Sanitizer?

The HTML sanitizer automatically cleans up markup in HTML fields. It helps to eliminate code and protect against security concerns like cross-site scripting attacks.

12) What is a record producer?

A record producer a catalog item which helps you to create task-based records from the Service Catalog. For example, you can create a change record or a problem record with the help of record producer. It offers an alternative way to create records through the Service Catalog.

To get in-depth knowledge, enroll for a live free demo on Servicenow Online Training

Modern data lakes depend on extract, transform, and load (ETL) operations to convert bulk information into usable data. This post walks through implementing an ETL orchestration process that is loosely coupled using AWS Step Functions, AWS Lambda, and AWS Batch to target an Amazon Redshift cluster.

Because Amazon Redshift uses columnar storage, it is well suited for fast analytical insights using the convenient ANSI SQL queries. You can rapidly scale your Amazon Redshift clusters up and down in minutes to meet the demanding workloads for both your end-user reports and timely data refresh into the data warehouse.

AWS Step Functions makes it easy to develop and use repeatable workflows that scale well. Step Functions lets you build automation workflows from individual Lambda functions. Each function performs a discrete task and lets you develop, test, and modify the components of your workflow quickly and seamlessly.

An ETL process refreshes your data warehouse from source systems, organizing the raw data into a format you can more readily use. Most organizations run ETL as a batch or as part of a real-time ingest process to keep the data warehouse current and provide timely analytics.

A fully automated and highly scalable ETL process helps minimize the operational effort that you must invest in managing the regular ETL pipelines. It also ensures the timely and accurate refresh of your data warehouse. You can tailor this process to refresh data into any data warehouse or the data lake. For more info ETL Testing Training

This post also provides an AWS CloudFormation template that launches the entire sample ETL process in one click to refresh the TPC-DS dataset. Find the template link in the Set up the entire workflow using AWS CloudFormation section.

Architectural overview

The following diagram illustrates the architectural overview of the different components involved in the orchestration of the ETL workflow. This workflow uses Step Functions to fetch source data from Amazon S3 to refresh the Amazon Redshift data warehouse.

.

Here are the core components of the workflow:

• Amazon CloudWatch triggers the ETL process based on a schedule, through the AWS CLI, or using the various AWS SDKs in a Lambda function.
• The ETL workflow uses Step Functions for a multi-step ETL process and manages AWS services into serverless workflows. You can build and easily iterate these using JSON-based templates. For example, a typical ETL process may involve refreshing dimensions first and later refreshing the fact tables. You can declare your order of operations using a Step Functions state machine.
• A Lambda function lets you build microservices to coordinate job submission and monitoring without needing to write code for workflow logic, parallel processes, error handling, timeouts, or retries.
• AWS Batch runs several ETL jobs such as transforms and loads into Amazon Redshift. AWS Batch manages all the infrastructure for you, avoiding the complexities of provisioning, managing, monitoring, and scaling your batch computing jobs. It also lets you wait for the jobs to complete.
• The source data in Amazon S3 refreshes an Amazon Redshift data warehouse through a PL/SQL container. To specify the ETL logic, I use.sql files that contain the SQL code for a particular step. For example, a .sql file for a typical dimension table refresh contains steps to load the data from Amazon S3 to a temporary staging table and INSERT/UPDATE the target table. Before beginning, review a sample dimensional table .sql file. Learn Practical skills from ETL Testing Certification

You can execute the workflow and monitor it using the state machine. You can trigger the ETL according to a schedule or an event (for example, as soon as all the data files arrive in S3).

Prerequisites

Before you get started, create a Docker image that can execute .sql files. AWS Batch creates resources for executing the ETL steps using this Docker image. To create the Docker image, you need:

If this is your first time using AWS Batch. Create an environment to build and register the Docker image. For this post, register this image in an Amazon ECR repository. This is a private repository by default, making it useful for AWS Batch jobs.

Building the fetch and running psql Docker image

To build the Docker image, follow the steps outlined in the post Creating a Simple “Fetch & Run” AWS Batch Job.

Use the following Docker configuration and fetch and run psql scripts to build the images.

1. DockerFetchRunPsqlUbundu
2. fetch_and_run_psql.sh

Follow the steps in the post to import the Docker image into the ECR container registry. After you complete the previous steps, your Docker image is ready to trigger a .sql execution for an Amazon Redshift cluster.

Example: ETL process using TPC-DS dataset

This example uses a subset of the TPC-DS dataset to demonstrate a typical dimensional model refresh. Here is the Entity Relationship diagram of the TPC-DS data model that I use for this ETL application:

The ETL process refreshes table data for the Store_Sales fact table along with the Customer_Address and Item dimensions for a particular dataset date.

Setting up the ETL workflow using Step Functions

Step Functions make complicated workflows more straightforward. You can set up dependency management and failure handling using a JSON-based template. Workflows are just a series of steps, with the output of one step acting as input into the next.

This example completes various dimensional table transforms and loads before triggering the Fact table load. Also, a workflow can branch out into multiple parallel steps whenever needed. You can monitor each step of execution as it happens, which means you can identify and fix problems quickly.

This illustration outlines the example ETL process set up through Step Functions:

In the above workflow, the ETL process checks the DB connection in Step 1 and triggers the Customer_Address (Step 2.1) and Item_dimension (Step 2.2) steps, which execute in parallel. The Store_Sales (Step 3) FACT table waits for the process to complete the dimensional tables. Each ETL step is autonomous, allowing you to monitor and respond to failures at any stage.

I now examine the Store_Sales step (Step 3) in detail. Other steps follow a similar pattern of implementation.

To get in-depth knowledge, enroll for a live free demo on ETL Testing Online Training

ServiceNow integrates with many third party applications and data sources.

The most common integrations are with CMDB, Incident Management, Problem Management, Change Management, User Administration, and Single Sign-on. A variety of techniques can be used, most notably Web Services, JDBC, LDAP, Excel, CSV, and Email, as well as any industry standard technologies that use SOAP, REST, or WSDL. Additionally, API and command-line integrations can be done using a MID Server. ServiceNow has performed the following integrations with enterprise systems and platforms. For more info Servicenow Training

This podcast offers additional information on integrations.

Technologies

the Now Platform is based on service-oriented architecture (SOA), in which all data objects can use web services to access bi-directional data-level integration. The interface is also direct and dynamic because all modifications to existing objects and all new objects are automatically published as a Direct Web Service. A more indirect web service creation and usage can be achieved through Mapped Web Service where a transform map is used to gather incoming web service data into the final targeted tables. Finally, an advanced Scripted Web Service technique is available for defining process-based web services, where data is irrelevant, but serves more as a trigger for a process or a composite of actions that execute at the server.

Additionally the platform offers a rich interface for loading external data using import sets. Using this feature, you can load from various data sources such as HTTPS, FTPS, and SCP using file formats such as XML, CSV, and Microsoft Excel XLS files. Information can also be pulled from a data source using a direct JDBC connection, provided the network connectivity allows. Learn more skills from Servicenow Course Online

Information can be pulled from the platform to an external platform using an ODBC Driver.

Forms, lists, and reports on the platform can be accessed directly using a URL, which facilitates integration on the UI level between two or more web applications.

A handful of single sign-on technologies is identified and implemented out of the box to allow fast integration with your portal, however, the technique is customizable in a script to allow for flexibility in the different SSO environments our customers have.

Integration between ServiceNow Instances

There are times when you find you need to perform a specific integration between your instance and another ServiceNow instance. Instance-to-Instance integrations are a snap because all of the integration points exist between the two instances.

Integration options

• Nearly all ServiceNow customers obtain additional value by integrating with third-party applications.ServiceNow customers have the following options for integrating with third-party applications.

• Activate a ServiceNow provided integration.
• Install a certified integration from the ServiceNow Store.
• Install an integration from Share
• Contact your sales representative for available custom-built integrations.
• Build your own custom integration using the platform’s integration interfaces.

Note: Certified integrations have passed a set of interoperability, security, and performance test criteria defined by ServiceNow.

To get in-depth knowledge, enroll for a live free demo on Servicenow Online Training

When you spend a lot of time building dashboards, you tend to develop an approach or ways that make it easy to create the next one. After a while, you realize there are always ways to improve your design process in general. Today, I will answer share my Tableau dashboard best practices to help you through the whole process.

In the post, I also spoke about how there was no flow in the dashboard because we have information about sales and customer satisfaction thrown together.

Tableau Dashboard Best Practices

In this post, I will share my Tableau dashboard best practices by rebuilding the same Excel dashboard using the Superstore data set with a focus on the Sales information. I will walk you through my thought process in recreating it to meet dashboard design and Tableau Dashboard best practices.

1. Gather document requirements

The first thing I do before starting out any development work is to capture the relevant information needed to design and build the dashboard. This is probably the hardest Tableau Dashboard Best Practices. It is hard to identify what information you need. So, it is best to list questions first. Then, find data that can answer them.

For this dashboard, I came up with the requirements from the existing Excel dashboard. I wanted to create a Sales dashboard that a user can easily read to find out how well the business is performing right away, focusing on metrics such as Sales, Order Quantity and Shipping Cost. Learn more from Tableau Training

2. Make sketches

One of my must-do Tableau dashboard best practices is drafting sketches. Before I build dashboards, I like to have a basic idea of what it should look like. I usually sketch out the design to make it easy when I start to build. Below are some of my attempts at sketching out the design and although my sketching skills are not so great, it makes it easier for me get the layout the way I want as well the chart types I want to use.

Another reason why I love to sketch is that it helps me to avoid over clutter. In the examples above, I restricted the chart types to just 3; bar charts, line charts, and donut/ gauge charts.

3. Choose chart types and analysis

One of the Tableau Dashboard best practices that a lot of data analyst get wrong is the selection of chart types. It is important to select the correct chart types for your views. There are 3 major metrics that we analyzed in the original Excel dashboard. I kept it the same for the makeover dashboard but divided them into sections that made the analysis easy to understand and read.

It is always good to know what type of analysis you want to perform and the type of charts that are best suited for communicating the insights you discover. This is the stage to ask the questions I want answers to. For example, how do Sales perform in each region?

4. Divide the dashboard into sections

Dividing dashboards into sections is one of the most crutial Tableau Dashboard best practices. You need to divide it into bite size pieces for easier analysis and understanding. But with tons of data, you might find yourself drowning. The key is to go back to the questions you want answered and then get back to the drawing board.

I decided to divide the makeover dashboard into 4 major sections; the KPI section, the bar charts to analyze categorical data, line charts to show the various trends and gauge charts to compare performance in the various segments.

5. Build your views

Having an idea of what views to include allows you to save a lot of time in the development process. If the above Tableau Dashboard Best Practices hasn’t worked for you, below are more tips for you to succeed. For more skills Tableau Server Training

KPI charts

Using BANs is one of the coolest Tableau Dashboard Best Practices that can help show KPIs that lets you track right away how well your business is performing. You can simply build this by selecting the various dimensions and using the Show Me text table option or simply use the Measure Names and Values to build this.

You can check out this excellent post by Tableau zen master Steve Wexler that recommends the use of BANs in our dashboards and the reasons for that.

Use bar charts

Using bar charts is one of the Tableau Dashboard best practices that can help show how the major metrics look compared to various regions. Be sure to put labels on top of the charts and place them in boxes to easily show that there are separate metrics on your bar charts.

You can build horizontal chart in Tableau by placing the dimension field, in this case, Region, on the Row Shelf and the measure or metric (Sales) on the Column Shelf.

Use line charts

Using line chart is one of the Tableau dashboard best practices that will allow you  to display the trend of the metrics you want to measure. Just as with the bar charts, put labels on top of the line charts and put them into separate boxes to make it easy to know what trend you’re looking at. Get more info at Tableau Server Online Training

Use gauge charts

Using gauge charts is one of the coolest Tableau Dashboard best practices in showing nformation. If you want a cool way to show what the percentage of data, use it to your advantage. Initially, I had decided on a donut chart but this did not look good in the final view the way I imagined it.

Since I wanted them to show in a vertical format, I settled on using the multiple gauge chart.

6. Bring it all together

After creating all the views, the next most important Tableau dashboard best practices are how well you can them together on the dashboard and make it both appealing and functional. You can achieve this by considering the following options below;

7. Select a layout and arrangement

This is when our must-do tableau dashboard best practices becomes useful. Since I had already sketched the layout of my dashboard, arranging the various views on the dashboard becomes a lot easier. It is best practice to design your dashboard to a grid layout. 

Before I start placing my charts on the dashboard, I use layout containers to create boxes for them as shown below. For this dashboard, I used a 1500 x 900 layout.

7. Do some formatting

If you are not formating your dashboard, then you are missing out on one of the most important Tableau best practices. After creating views, I like to remove all grid lines from the charts to make them look clean. I also remove the borders from the individual charts and use the layout containers instead or sometimes just leave the borders around the chart as-is and not use a border around the container.

8. Use dashboard actions for interactivity

Using dashboard actions is one of the Tableau dashboard best practices that can improve interactivity. Therefore, it is a huge plus!

I decided to use dashboard actions to filter the charts based on a region bar clicked. Dashboard actions can be implemented using the Dashboard menu. To create a dashboard action, click on the Dashboard menu from your dashboard and select Dashboard actions. In the dialog box select the filter option and select the sheets to apply the actions on.

9. Include the necessary filters

Try exploring Tableau for more interactive filters. This Tableau Dashboard Best Practices can lead to more gold discoveries within the app.

The other interactive feature I used was a parameter that filters the dashboard based on the current month, last quarter and last 12 months. Below are the steps I used to create this filter.

First, I created a parameter to use as a reference date to create the various calculations against. I used this to be able to select what my current date was.

After creating this parameter, I created the various calculations for the current month, last quarter and last 12 months.

After this, I created this date range parameter

And then a calculation to link the date range parameter and the various date ranges i.e. current month, last quarter and last 12 months.

On the dashboard, I placed the Date range filter on the top right-hand side of the dashboard to let users know it is a global filter.

To get in-depth knowledge, enroll for a live free demo on Tableau Online Training

The most common mistake people make when building an ETL system – or arguably any technology project – is that they jump into buying technology and writing code before thinking through the needs of their organization.

Before you start building your ETL architecture, consider some fundamental questions. What sources do you need to extract data from? What warehousing technology are you using? What’s the analytical skill level within your organization? Why is this project important to your overarching company goals? The answers to these questions should help you determine your overall data architecture, a necessary outline to establish prior to starting your project.

Enterprise architect and consultant Chris Lockhart describes the importance of architecture-first thinking like this:

“A comprehensive architecture that is made up of things like mission, vision, goals, objectives, requirements, roles, activities, capabilities, services, technical components and yes, physical components is a holistic thing. It is something we bake, not something served up in piecemeal ingredients. We don’t eat flour and call it bread just because everyone around us happens to be an expert in flour. An architecture is not a single one of those ingredients. It is the result of an intentional effort to bring those parts together so that they positively impact the matter at hand. Talking about servers and middleware and network all day, no matter how appealing to some (including myself) will never go far in solving business problems. Frankly it will just make you look like the stereotype Dilbert character. Your organization will lose all credibility with the folks who pay the bills.”

To Lockhart, architecture is the vision and the planning toward that vision.

Here are five things you should do when designing your ETL architecture:

• Understand your organizational requirements
• Audit your data sources
• Determine your approach to data extraction
• Build your cleansing machinery
• Manage the ETL process For more info ETL Testing Online Training

Understand your organizational requirements

There comes a time in every business’s life when joining data across disparate data sets on a one-off basis is no longer tenable: You’re suddenly overloaded with spreadsheets, or you start to calculate how much time you’re spending on perpetually-breaking ETL scripts. Before you jump into a solution, make sure you understand why ETL is a priority right now for your organization.

1. Data sources: Which data sources does your team most want to work with? How much time are you willing to dedicate to building a new integration when the situation arises? For many startups and growing businesses, data sources are fairly standardized. You’ll likely be extracting data from transactional databases such as MongoDB and MySQL and SaaS tools like Salesforce and Google Analytics.
2. Usage: Where will the data be loaded, who will be using it, and how will they be using it? If you’re provisioning data for analytics, that’s going to look different than if you’re provisioning data to power your product, as businesses like Uber or Airbnb do.
3. Latency: How often do your end users need fresh data? For systems alerting, you need your data to be updated every second. If you’re delivering data for financial reporting, once a week might be enough. If your data scientists want to work on a data model, you may only need to deliver data once.

Answering these foundational questions will help you make the first critical decision about your ETL architecture: Should you build it or buy it? If you’re extracting data from standard sources and using it primarily for business analytics, buying it is often the right choice. There’s no need to reinvent the wheel. On the other hand, if you’re Uber and you need to make real-time decisions based on location data from hundreds of thousands of cars, you may need a custom solution.

It’s important during this process to understand what data sources are a top priority. For most companies, consistent financial data around things like revenue, MRR, and unit economics will be the top priority. Identifying these priorities provides you with information you need to make tradeoffs around data completeness vs. speed of project completion. Learn more from ETL Testing Certification

Determine your approach to data extraction

Two questions should guide your approach to data extraction:

1. What sources are you extracting data from?
2. What methods of data extraction will you use?

The Extraction section of this website covers these points in detail.

Build your cleansing machinery

In the Transform section of this site, we explore how and why improved data warehouse processing has impacted the transformation stage of ETL. Modern ETL systems leave the bulk of transformations for the analytics stage, and focus instead on loading minimally processed data into the warehouse. This increases the flexibility of your ETL process and gives your analysts greater control over data modeling.

That said, you may need to do some basic data cleansing during the ETL process. Certain commonly occurring scenarios should be handled prior to loading data into your warehouse:

• Restructuring data: Often, the way data is structured in a source data store makes it difficult or non-optimal to load it into another data store. This is the case, for example, when translating nested structures from a JSON API or MongoDB into a relational structure.
• Data typing: You need to type the data entering your system and maintain that type (currency, date, etc.) as it travels through your ETL process.
• Data schema evolution: This is a consideration for internal systems, but it applies even more when you’re dealing with external data sources like SaaS APIs. If a SaaS tool adds a new column to its data collection, your system needs to be prepared to accommodate the change, ideally without human intervention. As products and APIs evolve, new fields get added frequently.

Manage the ETL process

The final stage of designing an ETL architecture is figuring out the functionality you’ll need to manage the ETL process. Data being delivered to your organization needs to be reliably available and accurate; the processes you put in place here will ensure reliability and build organizational trust around your data accuracy.

1. Job scheduling: How often should the ETL process run? Should it be triggered or run automatically? Should this vary by data source? For example, your CEO will likely want daily revenue numbers, but your customer support team probably needs NPS data only every week or two. Getting these schedules right will reduce load on your internal systems. Even in environments where aspects of your ETL pipeline are built for streaming, there will likely be components that run in batch.
2. Monitoring: If any of your systems fail, or your testing rules reveal a data inaccuracy, you’ll want to know about it. Your monitoring system should escalate problems, alerting your team so they can take action immediately. You’ll likely want to integrate this with the other monitoring and alerting systems you use for other aspects of your technology infrastructure.
3. Recovery: It’s not a question of if, but when, your ETL process will fail. You need to be prepared for when it happens. Your data warehouse might go down, an extraction job might fail, a SaaS API might temporarily go down or start sending you nonconforming data. Your architecture needs to plan for failure and have recovery mechanisms in place for when it happens.
4. ETL testing: In addition to recovery options, you want checks and balances in place to test data accuracy. Bilal Mahmood wrote a great piece outlining the seven most common data inaccuracies and the rules to test for them.
5. Security Your security measures need to consider things like how to transfer data securely, as well as what data gets transferred. For example, unless you’re PayPal (or a company like it) there’s likely no benefit to storing customer payments information in your analytics warehouse.

To get in-depth knowledge, enroll for a live free demo on ETL Testing Training