Getting started with the SQL Server 2012 Tabular Model - Part 2

In my last article, "Getting started with Tabular Model in SQL Server 2012 - Part 1" I talked in detail about differences between the multi-dimensional and tabular models, when to choose one model or another and I demonstrated step by step on how to create a basic tabular model project. But if you have prior experience working with PowerPivot, you might be thinking whatever we did, in the last tip, is very similar to what we can do in PowerPivot. Well, you are right to some extent, the Tabular model is actually a server side version of PowerPivot, but also has many more enterprise features, which are not available in PowerPivot for example partitioning, security, management, etc. In this article I am going to talk in detail about some of the compelling features of the tabular model and show you how tabular models differ from PowerPivot, for information click here.

Getting started with Tabular Model in SQL Server 2012 - Part 1

In my last article, Understanding BI Semantic Model (BISM) of SQL Server 2012, I talked in detail about the new BISM, its architecture, how it differs from the earlier UDM and how BISM lays down a foundation for the future. In this article I am going to talk in detail about differences between the multi-dimensional model and tabular model, when to choose one model over the other and go through a step by step guide on creating a tabular model project, for information click here.

Partitioning in SQL Server - Part 1

With every new release of SQL Server, partitioning has reached new heights of improvement. For example, though we could create partitioned views for better manageability and scalability since SQL Server 7.0, SQL Server 2005 started with native support for Table Partitioning. SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization (of modern multi-processors hardware). With SQL Server 2012, we are now allowed to even create up to a 15K partition on a single table.

To learn more about Why, What, When and How of Partitioning in SQL Server click here.

FileStream and FileTable in SQL Server 2012

Data explosion brought a need to store both structured and un-structured data together in the database so that the benefits of the database system (like transactional support, backup and restore support, integrated security support, Full-Text Search support, etc.) can be leveraged. SQL Server 2008 introduced the FileStream data type to store unstructured data, such as documents, presentations, videos, audios and images, on the file system with a pointer to the data in the database.

Storage of un-structured data in FileStream improves the performance by leveraging the NTFS APIs streaming along with additional benefits of the database system.

SQL Server 2012 enhanced this capability even further by introducing FileTable, which lets an application integrate its storage and data management components to allow non-transactional access, and provide integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata. I am going to talk about these two new features in detail, for more information click here.

Getting Started with the New Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request along with the newly introduced batch mode processing.

In my last article I talked in detail about the new Column Store Index, how it differs from regular indexes, and why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are.

In this article I am going to take this discussion to another level and show how you can create column store index, how you can use index query hint to include or exclude a column store index, how the performance differs when using column store index vs. row store index and more, click here for more information.

Understanding new Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. This article discusses the new Column Store Index in detail, how it differs from regular indexes, why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are, for more information click here.

Understanding BI Semantic Model (BISM) of SQL Server 2012

SQL Server 2012 introduced an unified BI Semantic Model (BISM) which is based on some of the existing as well as some new technologies. This model is intended to serve as one model for all end user experiences for reporting, analytics, scorecards, dashboards, etc. In this tip, I will talk in detail about the new BISM, how it differs from earlier the earlier Unified Dimensional Model (UDM) and how BISM lays down a foundation for future; for more information click here.

Importing Data From Excel Using SSIS - Part 2

In my last tip, "Importing Data From Excel Using SSIS - Part 1" we found out that the SSIS Excel Connection Manager determines the data type of each column of the worksheet on the basis of data for that particular column from the first 8 rows. This is the default behavior and connection manager uses a value in the registry key to consider the number of rows for data type determination. We also saw that the data import fails if the data in others rows (other than first 8 rows) is not compatible or has a longer length than the data in the first 8 rows. I talked about different ways to handle this to make the data import succeed. I also talked about the easiest solution which is to change the registry key value, but this has its own implications and hence the question; what other options are there without creating too much additional overhead? for information click here. 

Importing Data From Excel Using SSIS - Part 1

Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it?  Check out this tip to learn more. 

Let's Explore Excel add-in of Master Data Services of SQL Server 2012 - Part 2

SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS.  First, was the Master Data Manager which is a web based User Interface. Second, was loading data through the staging process. Third, was to use the MDS WCF services to programmatically manage master data. These were fine options, but there were many users who wanted to manage data in Microsoft Excel in bulk than managing each single record/member at a time in Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for SQL Server 2012 for MDS to manage master data. In my last tip I talked about how to get started with MS Excel MDS add-in to manage master data stored in Master Data Services database in Excel. In this tip, I am going to talk more about editing, creating, combining master data, creating entities and how it can be used in conjunction with Data Quality Services for data matching. For more information click here. 

Excel add-in for SQL Server 2012 Master Data Services - Part 1

SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS.  First is the Master Data Manager which is a web based user interface.  Second is loading data through a staging process.  Third  is using the MDS WCF services to programmatically manage the master data. These are fine, but there are many users who want to manage data in MS-Excel in bulk rather than managing each record one at a time in the Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for MDS server in SQL Server 2012 to manage master data. With this new option, now the questions are, how to get started with this new add-in, how to manage data in bulk with this add-in and how publish it to MDS server?  Check out this tip to learn more.

SQL Server Reporting Services vs. PerformancePoint Services - SSRS vs PPS

When we are done creating a SSAS cube we often think of whether to use SSRS (SQL Server Reporting Services) or PPS (PerformancePoint Services) for the reporting UI. The question is what are the pros and cons of each of these approaches and in what scenario should one be chosen over the other or can they be used together to compliment each other?

For more information click here.

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 SQL Server 2012 Data Quality Services - Part 1

Getting Started with SQL Server 2012 Data Quality Services - Part 2

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.

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.

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.

Creating a Map Report in SSRS - SQL Server 2008 R2

SQL Server 2008 R2 brought several new features into the SSRS (SQL Server Reporting Services) arena. In the data visualization category, we now have three additional ways to display and visualize/analyze data in the reports:

• Sparkline and data bars – Sparkline and data bars are normally used inside tables and matrices to analyze the trend and series and compare them with each other.
• Indicators – If you are aware of KPIs, the concept of indicators is not new to you. As the name implies, indicators have icons to represent trends (up, down or flat), progress state, conditions.
• Maps – It allows you to create maps or maps layers to let you visualize data against a geographic background.

In this article, I am going to demonstrate how you can create map reports to analyze your data against a geographical background and then in the next article I will be talking about creating a map report with drill down functionality, click here for more information.

SSAS - Best Practices and Performance Optimization - Part 3 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 for performance optimization for your cube design and development, for more click here.

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

In the first part of this series we looked at processing performance, query performance and hardware resources for your SSAS environment. In this tip, I am going to share best practices and performance optimization techniques for source system design and network settings for your Analysis Services environment, for more click here.

SQL Server Analysis Services - SSAS - Best Practices and Performance Optimization - Part 1 of 4

There are several aspects that can take a toll on performance for your Analysis Services cubes. Some problems could be related to the source systems, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series, I am going to talk about some of the best practices which you should consider during the design and development of your Analysis Services cube and some tips which you can follow to tune your existing environment if it is suffering from performance issues, for more information click here.

Managing and Connecting to AlwaysOn Availability Groups

There are several ways to effectively manage and monitor availability groups, replicas and databases in SQL Server 2012 but the most efficient is the inbuilt dashboard which displays information in very a intuitive way for high-level monitoring and management. In addition, you can also use the newly introduced catalog views and dynamic management views, extended events as well as system performance counters, for more information click here.

Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012

In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group. For more information, click here.

SQL Server 2012 AlwaysOn Availability Group Environmental Setup

Continuing from AlwaysOn Part 1 the series continues with a step-by-step guide to setting up the environment for an AlwaysOn availability group. In this article we will break down AlwaysOn environment preparation into three steps :
1. Installation of SQL Server 2012 on each node/replica/server.

2. Installation of the failover clustering feature on each node/replica/server.

3. Creation of a failover cluster and joining all nodes/replicas/servers to the cluster.
For more information click here.

Using The AlwaysOn Feature of SQL Server 2012

Prior to SQL Server 2012, SQL Server had several high availability and disaster recovery solutions for an enterprise’s mission critical databases such as failover clustering, database mirroring, log shipping or combinations of these. Each solution typically has a major limitation, in the case of failover clustering for example, its configuration is very tedious and complex and you arguably have single shared storage or single point of failure. Database mirroring is relatively easy to configure in comparison with failover clustering, but you can have only one database in a single mirroring setup and you cannot read from the mirrored database. Log shipping does not provide automatic failover (higher availability) though it be used for disaster recovery with some expected data loss.

SQL Server 2012 (Code name SQL Server Denali) introduces a new feature called AlwaysOn which combines the best of failover clustering and database mirroring and overcomes major of the limitations imposed in failover clustering or a database mirroring setup, for information click here.

New User-defined Server Roles in SQL Server 2012

As we all know, we have fixed server roles (total nine) in SQL Server and if we make a group of DBAs a member of sysadmin, so that they can query DMVs and run traces/profile, we are actually giving them more than the permissions needed. Being part of sysadmin, they will have complete control on the instance. Unfortunately there is no way to change these fixed server roles to limit/give more permissions than needed in earlier versions of SQL Server. This means there is no way to separate DBAs into groups according to their roles, other than those that are available as fixed server roles. Starting with SQL Server 2012, apart from using the fixed server roles, we can also create user defined server roles and assign only server level/scope permissions needed to this role, for more information click here.

New Built-In TSQL Functions in SQL Server 2012

SQL Server 2012 (Code named SQL Server Denali) introduces 14 new built in functions in four different categories with one existing function (LOG) being modified to have an optional second base parameter, for more information click here.

Contained Database Authentication feature in SQL Server 2012

SQL Server 2012 introduces a new feature called Contained Database Authentication. The Contained Database Authentication feature allows the database to partially contain the data that was previously stored outside the database. In other words, once you enable the containment feature at the SQL Server instance level, you can create a database that can store user information/credentials inside the database itself (rather than having a login at instance level, which gets stored in the master database) and hence you don't need to create logins on the target instance (and do mapping of SID) again during database movement. Quite a relief...isn't it? For more information click here.

Usage and Benefits of Using WITH RESULT SETS In SQL Server 2012

SQL Server 2012 (Code name SQL Denali) introduces the WITH RESULT SETS clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.
The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately, for more information click here.

Data Driven Report Subscription in SSRS 2008 R2

In my last article I talked about standard subscription in which we need to specify whatever parameters the report expects at the time of subscription creation and which cannot be changed at runtime; in this article we are going to discuss and use data-driven subscription.
In data-driven subscription the data or parameter values required during execution of the report can come from a query from a database including recipient list, delivery method and parameter values needed for each recipient's report execution, which makes it a dynamic subscription that gets required data during run time from the external data source, click here fore more information.

Standard Report Subscription in SSRS 2008 R2

You can set the subscription for the report in which case report server processes the subscription, generates the reports and delivers it to its intended audience via email delivery or file share delivery on the defined schedule, to learn more about it click here.

Understanding SSRS Report Definition Language (RDL)

SSRS reports are defined in Report Definition Language (RDL), which is nothing but Extensible Markup Language (XML) adhering to a defined report definition language schema. When we use Report Designer or Report Builder to create reports, it defines the report in Report Definition Language (RDL), which is an XML representation adhering to a defined report definition language schema (defines where an RDL element should exist), for more information click here.

Report Snapshots in SSRS 2008 R2

You can set SSRS to create report snapshots, which show data at a point of time or you can subscribe to your published reports.

An SSRS report snapshot represents a report that contains data retrieved at a specific point of time along with layout information in the form of intermediate rendering format. SSRS allows you to create report snapshots on defined schedule or on demand whenever you need to; a report snapshot is stored in the ReportServer database, for more information click here.

Report Caching in SQL Server Reporting Services 2008 R2

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free form reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can specify to create report snapshots, which show data at a point of time or you can even subscribe to your published reports.

To improve the performance of report processing, SSRS lets you enable caching for the report so that if the same report request comes again, the stored copy can be rendered in the desired format and served instead of processing it from scratch, for more information click here.

Creating and using Report Parts in Report Builder 3.0

Report Builder 3.0, which comes with SQL Server 2008 R2, is a new enhanced report development and report authoring tool intended to be used by business users for ad-hoc reporting. Report Builder 3.0 has numerous new features and one of them is Report Part Gallery, which allows you to create and publish report parts and reuse them in other reports. In other words, you can publish different report parts on the report server and reuse them in different reports when required, without recreating them from scratch, for more information click here.

Getting Started with SQL Server Report Builder 3.0

SQL Server Reporting Services (SSRS) has a report development/authoring tool called Report Designer (Business Intelligence Development Studio) for creating managed reports. Though Report Designer has an intuitive interface for creating simple to complex reports, it can be a little complex for business users who want to create ad-hoc reports. SQL Server Reporting Services has a separate tool called Report Builder (separate downloadable) created especially for business users to do ad-hoc reporting.

Report Builder 1.0 first came with SQL Server 2005 and has grown up with each subsequent release. We are going to deep dive with Report Builder 3.0, which comes with SQL Server 2008 R2. For more information click here.

Getting Started with Utility Control Point in SQL Server 2008 R2

In my last article I talked about SQL Server Utility, Utility Control Point, what it is, how it helps SQL Server DBAs in multi-server administration, and finally I demonstrated creating Utility Control Point step-by-step.

To learn more about enrolling multiple SQL Server instances in an already created UCP for health data collection and monitoring purposes and how to administer SQL Server Utility and Utility Control Point, click here.

Understanding Utility Control Point (UCP) in SQL Server 2008 R2

As your business grows, the number of applications grows as well, as do the SQL Server instances to support these applications. As a SQL DBA, you need to have a multi-server management dashboard that proactively tells you about the resource utilization on each SQL Server instance.

SQL Server 2008 R2 introduced the SQL Server Utility and Utility Control Point, which lets you have a consolidated dashboard-type view of resource utilization on all the servers in your multi-server environments. It helps SQL DBAs to proactively monitor their SQL Server instances' resource utilization, for more information click here.

Managing and Monitoring SQL Server Service Broker Environments

Service Broker applications run in the background. You send a message and your command returns immediately. In the background, Service Broker keeps on trying to send the message to the destination service (Queue) until it puts the message there, times out or you end the conversation. These things all happen transparently to you and your sending application. So how would you troubleshoot your Service Broker applications if they're not working as expected, and how would you identify if something goes wrong? For for information click here.

Understanding Sequence Object in SQL Server Denali

Until SQL Server 2008 R2, the Identity column was used to generate sequential numbers to identify records in a table, mostly used as a primary/foreign key. The scope of the identity column is the table on which it has been created, and the next sequential number is created when the DML statement is executed. But what if you want to have sequential generation of numbers across tables (instead of tying the numbers with just one table), and you want to have the next sequence number even before execution of the DML statement? SQL Server Denali has a new feature called Sequence object for these purposes, while retaining Identity column functionality too, for information click here.