Monday, July 23, 2012

Getting Started with Data Quality Services of SQL Server 2012 Using SSIS

In my earlier articles, I talked about the Data Quality Service (DQS) in SQL Server 2012, which helps ensure data integrity and quality by data profiling, matching, cleansing, correcting and monitoring overall status of the data cleansing process. We also talked about using the interactive Data Quality Client tool and in this article we will walk through how to do data cleansing in a automated mode using SSIS, for more information click here.

Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012

This is third article of the series. In the first article I talked about data quality challenges and its source, then I talked about the Data Quality Service in SQL Server 2012 and its processes. In the second article, I talked about different components of DQS and how to go about installing and configuring DQS and then I talked, how DQS works. In this article, I am going demonstrate step by step how you can create a knowledge base and do data cleansing, for more click here.

Getting Started with SQL Server 2012 Data Quality Services

The amount of data is increasing on a regular basis and so is the responsibility to ensure the quality of data remains intact. Data quality issues consist of inconsistency, incompleteness, invalidity, inaccuracy, duplicates, etc. These data quality issues can arise from different sources like erroneous data entry, corruption in transmission or storage, data consolidation issues from different sources with different standards or formats, etc. These types of issues can lead to incorrect data analysis, data mining and reporting, which ultimately impacts the business. I have heard that SQL Server 2012 has a brand new feature called Data Quality Service (DQS) which greatly helps to ensure the data integrity and quality by data profiling, matching, cleansing, correcting data and monitoring overall status of the processes. Let's explore this feature in this tip series:



Getting Started with Master Data Services (MDS) In SQL Server 2012

Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. Master Data Services is an extensible Master Data Management (MDM) platform, which is built on top of the SQL Server database engine and Windows Communication Foundation (WCF).
MDS allows you to create a centralized hub for creating and managing enterprise master data and provides many functionalities, to learn more click here.

Creating a Custom Report Template in SSRS

When we start creating reports at the enterprise level, we need to ensure the consistency in the look and feel of reports throughout the organization. For example, the header of all reports should have the company logo and address whereas the footer should have other company information or page layout information across all reports in a consistent manner. Now the challenge is, at the enterprise level there might be several departments/developers creating reports, so how do you ensure report layout consistency?
Fortunately, SQL Server Reporting Services (SSRS) allows us to create standard/custom report templates with the desired report layout and use the same custom template every time when creating a new report. With this, you can ensure consistent report layout across departments of the organization, for more information click here.

Snapshot Isolation Level in SQL Server - What, Why and How Part 2


Introduced in SQL Server 2005, Snapshot Isolation levels improve performance, however, there are some caveats to consider when using this feature. Some people use it frequently, as it minimizes blocking and improves performance/concurrency, without knowing its impact on maintaining versions in tempdb. Others stay away from it because of this extra overhead. Some people are confused by the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one variant where the other is needed or vice versa. In my last article (Snapshot Isolation Level in SQL Server - What, Why and How – Part 1) I talked about basic of isolation levels and snapshot based isolation levels and how they differ from each other in terms of performance and cost; in this article I am going to demonstrate how to use these isolation levels with examples; for more information click here.

Snapshot Isolation Level in SQL Server - What, Why and How - Part 1


Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are couple of things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.  In this article, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples, for more information click here.

Sunday, July 22, 2012

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 2


SQL Server Reporting Services allows you to embed indicators and Gauges in your report to analyze the data and its state. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent state value of Key Performance Indicator (KPI).
In my last article (Creating Indicator and Gauge in SSRS - SQL Server 2008 R2 - Part 1), we discussed the details of Indicator and Gauge, and created a step-by-step tablix report with two levels of grouping, which  I will be using further to add different types of indicators and gauges to it, for more click here.

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 1

SQL Server Reporting Services provides several ways to analyze the data; a few of them are creating reports with indicators and Gauges. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. There are several types of indicators, to learn more about these indicators and how to use them click here.

Saturday, July 21, 2012

Creating Chart Reports in SSRS SQL Server 2008 R2


SQL Server Reporting Services provides several ways to analyze data; one of them is creating Chart reports. With Chart reports you can analyze the aggregated information (either on small or large volumes of data) at a glance. But before you start creating Chart reports it’s essential to understand and prepare the data to be analyzed so that you can choose the appropriate chart type and create the report quickly and efficiently.
In this article, I am going to demonstrate how you can go about creating, modifying and beautifying the chart report easily and quickly, for more information click here.

Creating Map Drill Down Reports in SSRS - SQL Server 2008 R2


In my last article, I talked about creating map report based on ESRI shapefile. In this article I will be talking about creating a map report with drill down functionality, so basically I will be creating two reports as follows:
  • Main report based on SQL Server Spatial data for showing countries wide sales
  • Drill down map report based on ESRI shapfile for showing statewise sales for the selected country
The idea behind this article is to demonstrate how you can use a combination of sources of spatial data and create a map drill down report. Also I will explain how you can import ESRI shapefile to SQL Server as a spatial data type and use it in a map report; for more information click here.

SQL Server Analysis Services Best Practices for Performance Optimization - Part 4 of 4

In this tip series we have been discussing various techniques that can be used to optimize your SQL Server Analysis Services (SSAS) environment. In this segment we look at best practices and performance optimization techniques for Analysis Services Server Resources and Reporting Services; for more information click here.