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...
Archive for September, 2010
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...
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...
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...
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...
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...
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...
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...
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...
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
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...
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...
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...
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...
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...
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...