Because of some unorganized OLTP (transactional system), sometimes it may occur that duplicate records get entered in the OLTP database. It is the most common thing in case of legacy database and bad database. For a better output in analysis service or in reporting, the data must be cleaned, scrubbed and must be flawless. And it becomes worse when the records are alike even for values in each column.
Before taking any step to clean the redundant data, you must first see and check for data redundancy.
Read more…
SQL Server provides us with a powerful Data Integration service called SSIS. But to make it more robust and effective, we need to monitor some of the vital measures so as to increase its performance, troubleshooting errors and to keep track of the data flow. The best way provided by SSIS to accomplish this is SSIS Logging. SSIS will log entries in the log file or table on the basis of the events that occur during the execution of the package.
Here in this section, we will tell you about different features of SSIS logging and the implementation of the same by programming Integration Services.
Read more…
For proper analyzing of data, sometimes we need to group them under certain categories. These categories are defined under some constraints over the data. For this GROUP BY Clause is very useful.
GROUP BY Clause is used with SELECT Command, and specifies the groups into which output rows are to be placed by the values of one or more columns or expressions. And if aggregated functions are used in the SELECT statements, then GROUP BY clause computes a summary value for each group.
Read more…
Lookup Transformation is used to lookup data by joining data in input columns with columns of a reference table. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL statement. The lookup is used to access additional information in a related table that is based on values in common columns.
For Example:
Let us have one employee table and one transaction table. The employee table contains information about all the employees. And the transaction table keeps record of the type of transaction done by any employee.
Read more…
As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any uneven query by the user can eat all the space available to thetempdb resulting decrease in the performance of all other applications running under the same instance.
So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb.
Read more…
SSIS provides a unique feature to track the execution of the packages so that it can be utilized for debugging or analyzing performance later. Here the application provides an ultimate way to set the logging features of the packages batch wise i.e. set log features to all the packages at a time.
Read more…
Tempdb is one of the system databases in SQL server 2005, more or less similar to any other user database except that the data stored in the tempdb got lost after shutting down the SQL server service.
Each time the SQL server service starts, the tempdb is newly created by copying from model database (another system database) and inheriting some database configuration from it.
Read more…
There may be some circumstances in a sanctuary where it becomes necessary to keep a leash of foxes and a pack of wolves separate so that they must not fight with each other and make things a mess. Similarly, in the database jungle we do the same things by using some conditional statements in order to keep things distinct. SSIS provides a nice transformation to do the same, i.e. Conditional Split Transformation.
By using this conditional split transformation, the incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions.
Read more…
Minimized costs, increased effectiveness, higher flexibility and easy management have become keys to success in the IT industry today. This page aims at achieving the above by introducing a new approach to solve a set of problems faced in ETL process.
The IT industry is ever expanding, and elements of IT can be found in almost every sector, every company. Whether you are playing on a website like (www.casino.com/) or using advanced computerized utilities on a farm, knowledge of IT and programming is going to be very useful. Computer skills are necessary all over the world these days, from basic knowledge to complex procedures. Processing a set of operations with different source and target names, but same structure and same set of transformations is a common scenario in development areas. So approaching an automated methodology for the same using some programming languages like C# .Net or VB .Net with little or no modifications is better. This can reduce the total work load up to 80 – 90%.
This page deals with the approach of creating the ETL Package by using C# .Net and VB .Net language. As SQL Server 2005 is a product of Microsoft Corporation, we got the whole set of APIs in Microsoft Visual Studio. Using these APIs and some standard procedures and scripts in T-SQL, we can ease the ETL Process to increase the productivity.
Read more…
Recent Comments