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.