Analytics in Action

Archive for September, 2010

A simple framework to solve technical problems

Sooner or later (and most likely sooner) you will run into a technical problem that you can't solve.  This applies to any technical issue- whether its a problem with SQL Analysis Services, or Excel, or a coding bug. In this...

How to update PowerPivots when the underlying source data changes.

In this post I demonstrate how to update data in Powerpivots. Updates are different from a data refresh as updates incorporate new columns of data- so the structure of the underlying excel table changes. A standard data refresh wouldn't pull...

Standardizing date formats in SSIS using SubString, Findstring & Right Expressions.

SQL Server Integration Services often has problems with recognizing & interpreting dates. Often it struggles to figure out what is the Day, Month and Year component. This can cause big problems, particularly if the date needs to be manipulated in...

Creating dynamic 95% Confidence Intervals in Powerpivot charts

Powerpivots are a great tool for exploring data and drilling down into trends and patterns. However as you drill down into subsets of the data, sample sizes decrease and you need to be cautious as to how you interpret...

An introduction to using Variables in SSIS packages

Integration Services provides system variables and supports user-defined variables. In this post is will demonstrate user defined variables. User-defined variables can be used in many ways in SSIS: in scripts; in the expressions used by precedence constraints, the For Loop...

Multicast transformation in SQL Server Integration Services (SSIS)

The Multicast transformation splits a a single data file into multiple files. Multicasts are handy if you need to send multiple copies of data to different destinations or perform different subsequent transformations. The transformation has one input and multiple outputs. There...

Unpivot transformation in SQL Server Integration Services

Not surprisingly the SSIS Unpivot transformation does the the opposite of a Pivot transformation. It will convert data  in columns into rows.  Data that as previously been pivoted is difficult to manipulate further- so this is where the unpivot transformation...

Pivot transformations in SSIS

Most people are familiar with Pivoting within Excel- where  row values are converted to columns.  For those familiar with Microsoft Access the same process is known as a Crosstab query.  The pivot transformation in SSIS is a bit different from the process...

Using Aggregate & Data Conversion Transformations in SSIS

In this post I demonstrate the Aggregate and Data Conversion Transformations in SQL Server Integration Services (SSIS). Aggregate functions summarize data - such as averaging or summing. The actual aggregate function available will depend on the data type of field...

Using Union all & Merge transformations to append datasets in SSIS.

Union All & Merge transformations are used to append datasets together. This post demonstrates how to configure both transformation  and discusses the relative benefits of each. Amazon.com Widgets

Using Merge Join Transformations in SQL Server Integration Services (SSIS).

Merge Join transformations are one of the most useful and commonly used  SQL Server Integration Services (SSIS) transformations . They are typically used to join data from two files together. When used in conjunction with an "Inner Join" between datasets  they...

Using a Decision Tree Algorithm via the Excel Data Mining Addin

The Excel Data Mining Addin can be used to build predictive models such as Decisions Trees within Excel. The Excel Data Mining Addin sends data to SQL Server Analysis Services (SSAS) where the models are built. The final model is...

Generating a Pareto analysis (80/20 principle) in Excel.

Pareto analysis is a  handy analysis to help clarify how to allocate resources. The basic idea to to identify a small proportion of items or results that are disproportionally responsible for the overall outcome. For example the 20% of customers that are...

PowerPivot in Excel 2010. How to refresh data.

Excel 2010 Powerpivots are great tool to report on and slice and dice data.  Often underlying data sources changes and the Powerpivots need updating.  An example might be customer segmentation changing. A data refresh is used when now new columns of...

How to build a Powerpivot in Microsoft Excel 2010

The Powerpivot addin in Excel 2010 makes it easy to build reports with  incredibly  large volumes of  underlying data data. OLAP cube technology from SQL server  is now included within Excel 2010 with the Powerpivot addin. This means it now...

How to build a SQL Server Integration Services package.

One of the biggest challenges for analysts is to cleanse, transform &  join multiple datasets so that the data can be used for  reporting, analysis or modelling.  This process is known as a data integration or  in more technical terms  as...