SSIS: ETL for tables with Columnstore Index

Update :

Since SQL Server 2014, Tables with Columnstore Index are no longer read only and are updatable. This post is only applicable to SQL Server 2012.

Original Post

Columnstore Index on fact tables provide significant improvements in response time especially for aggregated queries. One of the first things I did after migrating my databases to SQL Server 2012 was to create Columnstore Index on fact tables. Now I can run aggregated queries that fetch results in reasonable response a time which is great for development, unit testing and helps testers to run their test scripts faster.

One caveat to be aware of is that the table on which a Columnstore Index is defined cannot be inserted, updated or deleted (at the time of writing this post). Continue Reading

SSAS: MDX Calculated Measures that Require Date Comparison

Often there is requirement to calculate measures based on two different date dimensions. For e.g. how many orders that were delivered this month was actually ordered last month? How many orders were delivered in the same month as they were ordered? How many orders were carried forward to next month?

Using MDX set functions EXISTS(),FILTER() and Range operator these calculations can be easily achieved provided the Date Dimension conform to same key columns.

Continue Reading

SSIS: Design Tip to Cascade Logging

An easy way to completely enable/disable SSIS Logging would be to set the LoggingMode property of the package using a Package Variable or Parent Package variable. LoggingMode takes the following enumerated values:

  • Enabled=1
  • Disabled=2
  • UseParentSetting=0

Continue Reading

SSIS: Making the Most of sysssislog

The default SSIS log provider for SQL Server captures package trace in sysssislog table. It does capture some useful but limited trace information about the package execution. This trace is of little use without user defined variable values like record count, status flags, control metadata, selection from and to dates etc.

Of course SSIS provides a framework to write custom log providers to handle specific situations like these. At the time of writing this post, the custom log providers are unable to access the package variables. The good news is significant improvements are anticipated in SSIS logging capabilities in next version of SQL Server.

For those of you on SQL Server 2008 R2 and below, you could still customize SSIS log by creating your own execution log table and using it in tandem with sysssislog. Your execution log table could be structured something as shown below. Note that the execution log table captures record count, status flags, control metadata, selection from and to dates etc.
Continue Reading

SSIS: Optimizing the ETL for Late Arriving Dimensions

 

Late Arriving Dimension/Early Arriving Fact as the name implies is a dimension record which is not available when the ETL loads the related fact record but becomes available at a later time.

This scenario could happen for a variety of reasons;

  • It might be a perfectly valid business process. For e.g. in an emergency ward, the patient interventions could be recorded first as priority while the patient details that is collected could be recorded into the system at a later time.
  • Sometimes due to operational reasons, the data might arrive at different times from their respective source systems. For e.g. The dimensions could be extracted from Master Data repository while the facts are extracted directly from the transaction system at different periods.
  • It might be due a failed ETL run.

Continue Reading

SSAS: One Database, many Cubes Vs. One Database per Cube

So you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes. Continue Reading

SSAS: Process Incremental – What it is & isn’t

SSAS provides Process Incremental as one of the processing options for cubes, measure groups and partitions. It is important to understand how Process Incremental works because it differs significantly from the seemingly equivalent Process Update for dimensions. Continue Reading