Wednesday, January 31, 2018

SSIS - Control Flow

In SQL Server Integration Services, a package is a graphical design tool which may consist of organized collection of connections, control flow elements, data flow elements, event handlers, variables, parameters, and configurations.
A control flow is the starting point of any package which defines a workflow of tasks to be executed, often a particular order. It is collection of three different types of control flow elements such as containers, tasks and precedence constraints.
Without these control flow elements, a control flow task is not cable to do nothing in itself to the data. It defines the data movement and transformation from source to destination.
If we try to define these control flow elements then we can say that –
Containers that provide structures in packages,
Tasks that provide functionality in the package and
Precedence constraints that connect the executable, containers, and tasks into an ordered control flow.
As we know that SSIS architecture supports the nesting of containers, and a control flow can include multiple levels of nested containers.

The following diagram shows a control flow that has two container and seven tasks. Five of the tasks are defined at the package level, and two tasks are defined at the containers level which are inside those containers. Each task and containers are linked with each other with the help of precedence constraints.

According to data process requirements,  a package could contain a container such as a Foreach Loop container, which in turn could contain another Foreach Loop container and so on. Most interesting thing is that Event handlers also have control flows and they are built using the same kinds of control flow elements.

Control Flow Implementation
A control flow control is responsible to provide the logic for when data flow components are run and how they are run. Also control flows can: perform looping, call stored procs, move files, manage error handling, check a condition and call different tasks depending on the result, process a cube, trigger another procedures, etc.
When the Control Flow tab is active, the Toolbox lists the tasks and containers that you can add to the control flow.

The following diagram shows the control flow of a simple package in the control flow designer. The tasks and container are connected by using precedence constraints.
Many tasks require a connection to a data source, and you have to add the connection managers that the Related Tasks task requires to the package. Depending on the enumerator type it uses, the Foreach Loop container may also require a connection manager.

SSIS Control Flow performs operations; such as executing a SQL Statement or Sending an email and provide a data flow from one task to another task or containers with the help of precedence constraints. 

Monday, January 22, 2018

Data modeling tools and database design tools

Enterprise data model comprises of all entities required by an enterprise. The visual representation of objects in a relational database (usually a normalised) is called as relational data modeling and data models are tools used in analysis to describe the data requirements and assumptions in the system from a top-down perspective.
As we know that data is business asset which is essential to the day-to-day operation of most businesses, but its value lies in providing insights to inform decision-making. To make sense of data, the first step is often to use a data modelling tool. A data model is a conceptual representation of business requirement (logical data model) or database objects (physical) required for a database and are very powerful in expressing and communicating the business requirements and database objects.
Now a day, there are n number common data modeling tools available in the market are given below –
Data Modeling Tool
One of the industry’s leading data modelling tool which includes fully integrated models, different modelling techniques that cater to both an IT-centric audience and non IT-centric.
It also supports a powerful metadata repository and various output formats.
ER/Studio supports single and multi-platform environments, with native integration for big data platforms such as – MongoDB and Hadoop Hive.
It can forward and reverse engineer models, includes a compare and merge function and is able to create reports in various formats (XML, PNG, JPEG).
ER/Studio is a great tool that that is easy to start working with due to its intuitive design and good user support.
Sparx Enterprise Architect
Enterprise Architect prides itself on being the cost-efficient option. It helps business users build robust and maintainable systems quickly and can easily scale to accommodate large teams collaborating on shared projects.
It also has the capability of running a dynamic model simulations to verify the correctness of models and provide better understanding of how specific business systems operate.
Oracle SQL Developer Data Modeler
Oracle data modeller is a free graphical tool to help business users with data modelling tasks. This tool is robust, offering features and utilities centred around productivity.
This includes easily accessible report tool, DDL preview capabilities, built in quality check tool and a sophisticated search engine.
CA Erwin
ERwin is one of the leading data modelling solutions that provides a simple, polished user interface for a complex data environment.
This solution provides business agility – models and metadata can be managed in a common repository to ensure consistency and security.
ERwin supports high customisation and automation allowing macro language, custom datatypes, APIs and much more. It also has an extensive user community enabling consumers to share knowledge and expertise.
Data Modeler Standard Edition is a data modeling tool for organizations to launch big data initiatives and decrease data management costs. Access data modeling tools and techniques to support data modeling projects, including articles, and expert advice on evaluating and using data modeling software.

Tuesday, January 16, 2018

SSRS - Adding Secondary Axis Chart

In the data visualisation, a chart can more effectively convey data information than can lengthy lists of data. When multiple series are present on a chart, we must have to determine the best way to compare the series.

As we know that a chart always has two axis types called them as primary and secondary. The secondary axis comes into the picture when comparing two value sets with two distinct data ranges that share a common category.
To understand this beautiful feature, we can take an example where we have a chart that calculates Yearly Total Sales vs Yearly Average Sales. In this case, time period is common to both value sets.
USE [Demo]

       Years=Year( [OrderDate])
   Group by
     Year( [OrderDate])
However, when both series are plotted on the same y-axis, we cannot make a useful comparison because the scale of the y-axis is optimized for the largest values in the dataset.

In the above chart, we can see the average yearly sales are too short in comparison of total yearly sales. So, we can understand that when a series in a chart is not measured on the same scale used for the other series in the chart, a secondary axis can make the chart easier to read.
How to implement Secondary axis for Yearly Average Sales
In the report designer, right-click the data series on the graph (in our case, that will be the gold column in our column chart) and select Series Properties as given below:

In the Series Properties window, Select Axes and Chart Area, then Secondary for either vertical or horizontal axes as needed (in our case, we will select Secondary under Vertical axis), and then click OK when done as given below-

After closing the Series properties window for Yearly Average Sales, we can preview of the report as given below:

If we show Yearly Total Sales on the primary axis and Yearly Average Sales on the secondary axis, we can display each series on its own y-axis with its own scale of values. The series still share a common x-axis.