Great resource for SSIS logging

 There are a lot of ways you can create logging in SSIS and some of the most elaborate requires creating essentially a framework consisting of using package configurations, store procedures, and varies queries. Much of this can be done for you automatically in SQL 2012 with its many new logging capabilities. However, for a fast implementation of logging and a reporting solution I would recommend the strategy at one of my fellow bloggers.

One of the better sites around on SSIS logging is at http://troywitthoeft.com.

Logging SSIS processing

Common T-SQL Tasks when building Data Warehouse

I’m often scrambling to find code that I use during the beginning of data warehouse design to accomplish tasks that are easily done in T-SQL. Almost all deal with data conversion/transformation.  Typically I’m creating Date or Unknown Surrogate Keys, Replace values in columns, and having to document this at the same time. 
 

Four SQL Tasks I do all the time (but commonly forget the syntax)...

1.      Converting dates to the YYYYMMDD format. I do this task constantly and recommend this naming convention for date dimension surrogate keys.. This example assumes DateSK will be the name of the Date Surrogate Key.

 SELECT CONVERT(VARCHAR(8), GETDATE(), 112) as DateSK

2.      Remove or replacing a string with a blank (or something else). In this example, I will replace the instances of XYZ with nothing (which is the same as removing).

                SELECT REPLACE (ColumnName,’XYZ’,’’)

3.      Pull all the column names as well as Database Name, Table Name, and data type to prepare for data dictionaries when building data warehouses.

SELECT

       TABLE_CATALOG as [Database],

       Table_name  as [Table Name] ,

       column_name  as [Column Name],

       DATA_TYPE as [Data Type]

                        FROM  information_schema.columns
                        WHERE  table_name = 'Resource_Table$ '
                        ORDER BY ordinal_position

 

4.      Sometimes you simply need to insert data into a dimension with a specific values on a column that specified as INDENTITY. This is common when you have dimension but now you need to add UNKNOWN entries where the Surrogate Key as -1 or some value that represent unknowns.
 
            SET IDENTITY_INSERT Products ON
            INSERT into Products (ProductsSK, ProductName)
            VALUES(-1, 'Hammer')
            SET IDENTITY_INSERT Products OFF

Those are the four tasks I commonly use as but often forget the syntax.

Sorting Dimensions in SSAS

One of the most common tasks that is relatively easy to do in all Microsoft data applications is to sort data. Unfortunately, this is not true when developing cubes in SSAS. You’ll think there would be a simple “sort” option or something where you right-click and sort by XYZ. Nope! Instead, you have to configure sorting using several steps.  

Date dimension data will likely be the most commonly needed type of data to sort. Sorting won’t be an issue for numeric data, but it will be for the alphanumeric data like month names. For example, when I browse my Data dimensions for that represent month names, I get the following months names in this specific order: April, August, December, February….You get the idea, it would be in alphabetical instead of chronological order.. This is of course is useless in the real word.

To fix this we need to specify that the Month Name be sorted by the Month number.  When you design your data source view, it is important that when you bring in a column that needs to be ordered outside of the natural alphabetic or numeric order, then that column needs an associated column just for ordering. In the case we have Month name (that cannot be ordered alphabetically) and Month number (that we’ll use to sort Month name). Here is the process to sort by Month Name by Month number.

1.      Select the Month (or whatever represents the column used for sorting) attribute in the Dimension Structure and Select Properties.

      2.      Change the following properties for the Month Name. 

a.      AttributeHierarchyEnabled property to False  

                    b.      AttributeHierarchyOptimizedState property to NotOptimized
                    c.      AttributeHierarchyOrdered property to False

 

 

3.      We then need to establish Attribute Relationships. In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

       4.      In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set   the Related Attribute to Month.

5.      Since we expect this type of relationship to not change, in the Relationship type list, set the relationship type to Rigid. Select OK to apply changes.

 

6.      Go back to the Dimension Structure tab. Select the Month Name in Attributes. Change the Orderby property to Attribute Key and change the property OrderByAttribute to Month.

You can now reprocess the Date dimension and apply the changes. If now go back into the Dimension browser we can see that the Month names appear in the correct order.

 

We can use this method to sort anything that has an associated sort column. Any item dimension that must be order in some way other than alphabetically, we have to use this type of method.

SSAS cube dimension processing failure.

I was just developing a SSAS cube with relatively no problems. Once I began to process the dimensions , the Cube processing failed and returned the following errors...

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'dimTask', database 'Project', schema 'dbo'.; 42000.



Impersonation Information Tab

The first thing I checked was the Impersonation Information tab on the Data Source.

Secondly, I then looked in SSMS and needed to verify that this account had the appropriate SQL Security role to process data from the Project DB (or whatever database). To resolve this problem, I opened up SQL Server Management Studio (SSMS), Under Security, Select the specified account (SSASProc in this scenario), Select the Server Roles in the Login Properties page. I then gave this account sysadmin server role.

 

SQL Server Management Studio

 This solved my SSAS Cube processing problem. I hope this works for you.

Simple Performance Monitoring for SSIS

I come from an operating system support and monitoring background so I’ve always been aware of the manageability tools available in Windows operating systems and the tools available on the market. I also have learned that the native tools in windows have improved so much over the last decade that anyone can build a relatively simple yet power monitoring process.

 

Performance Monitor, System Monitor, or Reliability and Performance Monitor (or whatever we call it next)

 

Performance Monitoring (or whatever we want to call it today) has been introducing since Windows 3.51 and has gotten better over the releases on subsequent Windows operating systems.  It’s still relatively complex for users that don’t understand operating systems components, but even with some basic computer OS knowledge (i.e. I know what I CPU, RAM, and Disk is used for) you can gather and understand some of the basic information performance statistics. In our discussion, I’ll speak about SSIS related performance stats.

 

Data Collector Sets via Permon.exe

Within Performance Monitor application, there is an additional toolset called Data Collector Sets. Data Collector Sets are used to establish what will be collected, how frequently, and in which format.

What to Collect?

Adding SSIS Performance Counters

This will depend heavily on what your role is and what needs to be monitored from a performance standpoint. From a purely SSIS standpoint (specifically SSIS 2008), I recommend the following performance counters to be captured…

•    SQLServer:SSIS Pipeline 10.0 - Buffers in use 
•    SQLServer:SSIS Pipeline 10.0 -Rows read
•    SQLServer:SSIS Pipeline 10.0 -Buffers spooled
•    SQLServer:SSIS Pipeline 10.0 - Rows written
•    SQLServer:SSIS Service 10.0 – SSIS Package Instances

These five counters will give you a good view of the overall performance of your SSIS package processing. Buffers represent the amount of memory available to do SSIS work. So if you allocate 1MB for activity the Buffers is use should present up to 1MB. This is useful because it tells you how much memory is being used and if it has met the upper limit that has been allocated. This gets a bit more complicated as you add more packages and run them in parallel because if you allocate different amounts a memory between packages it would be difficult to see if one package has maxed out is assigned amount of memory. Its gets even more complicated if you commonly specify that DefaultMaxBufferRows exceeds the available to the DefaultBufferRows in the package setting. Because of this, we need to look at Buffers Spooled to see if we are sending SSIS work to disk (very bad). 

How Frequently?

The frequency of how you should monitor SSIS activity depends on when it executes, how many packages are being monitored, and how much disk space you are willing to assign to this monitoring process. Since the premise around this post is using Windows performance counters to address large SSIS installations, this assumes that your environment SSIS processing is an issue. Therefore, a collection interval of about 15 seconds would be suitable. If your packages typically take a few minutes to process, we don’t need to monitor this activity as that would generally be a suitable processing expectation. However, SSIS processing that takes hours is where we will have the most use of collecting this performance data.

 

What Format?

As a SQL guy, my preferred method would be to use a SQL format which allows you to insert the data into an existing SQL Database. To have this work, you need to have to establish a DSN so that the Data Collector Set properties can connect and insert to the targeted database. If you don’t have access to a database DSN (which I would not be surprised as most BI developers will not often have created these) then you can simply retain the performance monitor format (*.mlb) or write the results to a CSV file.

 

Overall

 

Get in the habit of using the native OS tools to help access the performance issues within your BI environment. Although I spoke specifically on SSIS, Data Collector sets can be created for other BI applications SSAS and SSRS processing/usage as well.

SSIS Lookups with Integers vs. non-integers

One of the most common SSIS Data Flow objects that a SSIS developer will use is the Lookup Data Flow task. When I’m developing for your typical data warehouse project where we need to populate the fact tables by joining the primary surrogate keys to foreign surrogate keys, or primary keys to foreign keys, we typically (or should always) use integers as surrogate or primarily key. Now this may be obviously since primary and surrogate keys often are identity type columns and the required data type is integer. However, there is nothing stopping you from using columns that are not integer data types as lookup relationships. In the image below, you can see the typical mapping relationship for SKU (In this example SKU was a Varchar data type)

Naturally, you would then create another data flow task that will sent matches columns to a slowing changing dimensions (SCD) data flow task, and if you’re using the SCD data flow task provided by SQL 2012, then your likely going to use the same column as your business key for the SCD wizard. This would subsequently create the OLE DB Command that uses the selected business key as the joining column. This is where the problem starts because you are now joining non-integer types in SSIS. This will become a major problem when you start to use lots of data as the updates will be extremely slow. The image below points to the Where statement that should only use integers

Now I’m not saying using non-integer data types won’t work, I’m, saying that it will work SLOWLY compared to integers. For example, just updating 65K rows had taking about 6 minutes compared to less than 10 seconds in my labs. Yes, it’s really a night and day difference!

Basically, avoid using alphanumeric/strings (or essentially any non-integer data type) for lookups relationships when possible. This is particularly true when using OLE DB Command (for your SCD operations) to update tables that match when doing the lookup.  You'll find DBAs that will say it makes no difference. However, when you'll joining fact tables that could contain millions of rows, using integers makes the big difference.