Comparing Data Warehouse Design Methodologies for Microsoft SQL Server

In my last couple of articles, I talked about the importance of a Business Intelligence solution, why it is becoming priority for executives, what a typical Business Intelligence system architecture looks like, etc. In this tip, I going to talk in detail about how a data warehouse is different from operational data store and the different design methodologies for a data warehouse. For more information click here.

Microsoft SQL Server Business Intelligence System Architecture

In my last tip in this series, I talked about how Business Intelligence (BI) systems have become a priority for top executives in order to optimize resource utilization as well as to remain competitive. I also talked about BI users and how BI relates to Data Warehousing and Business Analytics. In this tip, I am going to take this discussion even further in order to understand how Business Intelligence differs from an organization's OLTP systems, what a typical BI system architecture looks as well as the different components of the Business Intelligence system architecture. For more information click here.

Microsoft SQL Server Business Intelligence - What, Why and How

Because of the recent crisis or surge in competition, it has even become more important for organizations to optimize its resources and stay competitive. This has led Business Intelligence to become one of the top priorities for many organizations, especially for top management responsible for strategic decision making. But what is Business Intelligence? How it can help an organization in optimizing their utilization of resources?  How can it help the organization remain competitive?  Check out this tip to learn more.

Importing and Processing data from XML files into SQL Server tables

In my last article, I talked about how you can use an  FTP task in SSIS to download files from an FTP server. But what if the file you have downloaded is an XML file and you need to import this data from the XML file into a SQL Server table? How do you process/parse XML data into SQL Server tables? For information click here. 

Getting Started with Microsoft Power Map for Excel

Power Map is an Excel add-in under the Microsoft Power BI umbrella that extends the capability of Power View by enabling end users to visualize data in a 3D visualization format. It lets you plot geographical and temporal data visually, analyze it in 3D visualization format and create an interactive, guided cinematic tour to discover new insights by seeing your data in geographic space and seeing time-stamped data change over time (which you might not have seen in traditional 2D tables and charts) and to share it with others. For more information click here.

Getting Started with Microsoft Power Query for Excel

Power Query is an Excel add-in that can be used for data discovery, reshaping the data and combining data coming from different sources. Power Query is one of the Excel add-ins provided as part of Microsoft Power BI self-service solution.

You can think of an ETL tool built into your familiar Excel to search or discover data from a wide variety of data sources (both from your enterprise as well as from online public data sources). Power Query has an intuitive and interactive user interface which can be used to search, discover, acquire, combine, refine, transform and enrich the data. For more information click here.

Understanding Microsoft Power BI – Self Service Solutions

Microsoft Power BI is a familiar, intuitive and cloud based self-service BI (Business Intelligence) solution for all your data needs in your very own Excel, which users have been using for decades. It includes different tools for data discovery, analysis and visualization. This article discusses Microsoft Power BI, different tools under the Microsoft Power BI umbrella and when each of them can be used. For more information click here.

Getting Started with Columnstore Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Though it had several limitations, now SQL Server 2014 enhances the columnstore index and overcomes several of those earlier limitations. In this article series I am going to talk about how you can get started with using this enhanced columnstore index feature in SQL Server 2014 and do some performance tests to understand the benefits.

If you are new to columnstore index, you can refer my earlier articles on the topic to understand it better.

• Understanding New Columnstore Index of SQL Server 2012
• Getting Started with the New Columnstore Index of SQL Server 2012
• New Enhanced Columnstore Index in SQL Server 2014

For more information click here for part 1 and click here for part 2.

New Enhanced Column Store Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. 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. Apart from the benefits it provides, it has several limitations in SQL Server 2012. SQL Server 2014 enhances the columnstore index and overcomes several of the earlier limitations. In this article, I am going to talk about the new enhanced columnstore index feature in SQL Server 2014. For more information click here.

SQL Server Data Tools in SQL Server 2012

As developers, we have always wanted to have a single Integrated Development Environment (IDE) for not only writing application code but to also to write database code in a single place without switching the environments. SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 that provides this capability primarily intended for developers and lets developers write their application as well as database code in a single development environment of Visual Studio. For more information, click here for part 1 and click here for part 2.

 

Managing Slowly Changing Dimension with MERGE Statement in SQL Server

In my last article I talked about Slowly Changing Dimension (SCD) Transformation, which is available out of box in the SSIS toolkit and can be used easily and configured quickly for managing smaller slowly changing dimension. The Slowly Changing Dimension Transformation is good if you want to get started easily and quickly but it has several limitations (I talked about these limitations in my last article, Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS) and does not perform well when the number of rows or columns gets larger and larger.

In this article I am going to talk about some of the alternatives that you can take for managing larger slowly changing dimensions. For more information click here.

Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS

As a data warehouse expert or as an ETL developer you will often come across scenarios in which you need to maintain and manage slowly changing dimensions. There are multiple ways to implement that in SQL Server and the easiest of those is using Slowly Changing Dimension Transformation in the data flow task of SSIS packages.

In this article I am going to provide you the steps and guidance needed to manage Slowly Changing Dimension with Slowly Changing Dimension Transformation in data flow task with an example. For more information click here.

Partitioning in SQL Server: Managing Sliding Window Scenario

Earlier articles of this series discussed what partitioning in SQL Server is; the different kinds of partitioning options, why and when we should go for partitioning and the benefits a partitioned table/index provides. Then I talked about different partitioning concepts like partition function, partition scheme, guidelines on choosing a partitioning column and creating a partition on table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning, with an example.

This article provides you the steps and guidance needed to manage a sliding window scenario, with an example. For more information, click here.

Partitioning Using the Wizard in SQL Server

In my earlier articles of the series, I discussed what partitioning in SQL Server is; the different kinds of partitioning options that we have, why and when we should go for partitioning and what benefits partition table/index provides. Then I talked about different partitioning concepts, such as partition function, partition scheme, guideline on choosing partitioning column and creating partition on a table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In my last article of this series, I provided a step-by-step guide on creating a partition table/index using T-SQL commands. In this article I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index Views. For more details, click here.

Extending SSIS Capabilities by Creating Custom SSIS Components for SharePoint

SQL Server Integration Services (SSIS) - a component of SQL Server - is an extensible platform for building high performance data integration (ETL - Extraction, Transformation and Loading) and workflow solutions. The extensibility part of SSIS comes from the fact that, the developers have ability to create custom components if the in-built components of it do not suffice the specific need. The platform makes it very simple to embed your own code into control flow or data flow pipeline.

This article demonstrates how to create custom SSIS components (connection manager, source component and destination component) that integrate data to/from SharePoint lists and work like any other native components of SSIS utilizing the SSIS pipeline for better performance. For more information click here.

Partitioning in SQL Server - Part 3

In my earlier articles of the series, I talked about what partitioning is in SQL Server; the different kinds of partitioning options, why and when we should go for partitioning and the benefits partition table/index provides. Then I talked about the different partitioning concepts like partition function, partition scheme, choosing partitioning columns and creating a partition on a table or an index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In this article of the series, I am going to provide a step-by-step guide on creating a partition table/index. For more information click here.

Partitioning in SQL Server - Part 2

When a table grows large or huge in size it becomes really difficult to load new data, remove old data, maintain indexes and queries involving this table, and runs extremely slow. In this situation, we can partition the table, which essentially breaks the table and its indexes into logically smaller chunks called partitions. Further, with a partitioned table, maintenance works, lock escalation settings and compression settings can be applied individually at each partition rather than on a single huge table. Not only that, Query Optimizer references only partitions needed to serve the query requests and eliminates the partitions that don’t contain data needed by the query during query optimization and execution, and also it uses parallelism to process multiple partitions in parallel. 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 15K partitions on a single table.

In my last article of the series, I discussed what partitioning in SQL Server is, the different kinds of partitioning options available, why and when we should go for partitioning and all of the benefits partition table/index provide. In this article I am going to examine the different concepts of partitioning in SQL Server and provide a step-by-step guide on creating a partition table/index. For more information click here.

Extending SSIS Capabilities by Creating Custom SSIS Components for SharePoint

SQL Server Integration Services (SSIS) - a component of SQL Server - is an extensible platform for building high performance data integration (ETL - Extraction, Transformation and Loading) and workflow solutions. The extensibility part of SSIS comes from the fact that, the developers have ability to create custom components if the in-built components of it do not suffice the specific need. The platform makes it very simple to embed your own code into control flow or data flow pipeline.

This article demonstrates how to create custom SSIS components (connection manager, source component and destination component) that integrate data to/from SharePoint lists and work like any other native components of SSIS utilizing the SSIS pipeline for better performance. For more information click here.

SQL Server Integration Services SSIS FTP Task for Data Exchange

An FTP (File Transfer Protocol) server is often used for data exchanges in many data integration scenarios. SSIS includes an FTP task to download and upload data files to and from an FTP location, but how does this work and how can we configure it for data file downloads and uploads? For more information click here.

Data tapping during SSIS package execution in SQL Server 2012

During SSIS package development we can add a data viewer to the data path of the data flow task to analyze the data passing through the data path or the pipeline, but once the package is deployed (or when not using Business Intelligence Development Studio) there was no built-in support for data tapping to analyze data for troubleshooting. I have heard we can do this now with SQL Server 2012, can you explain how it works? For information click here.

Creating SSIS Package Templates for Reusability

We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages? To learn about creating SSIS package template click here.