I just passed the Microsoft BI certification test 70-448 on SSIS, SSAS, and SSRS. It was a tough test and covers all aspects of the Microsoft BI stack. I used a lot of different study materials. Here is my assessment of the study materials I used and which ones helped the most. MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008-Business Intelligence Development and Maintenance: MCTS Exam 70-448 (Self-Paced Training Kits) (Paperback) : This book was great, if you do all of the ...
Read More
The Merge component in SSIS will take two sorted sources and union them while maintaining the original sort order. The question arises, what about duplicate records. These duplicates do not get eliminated. Here are two tables with the ID 5 and the name Brian repeated on each. Here is the layout of the data flow. Both sources are using the same query with the different table names. Select ID,Name From Merge1 Order by ID Select ID,Name From Merge2 Order by ID Just having the sources sorted does no...
If you want to create a column chart and have a line across the top showing the numbers in two different ways, it is easy to do in SSRS 2008. Just drag over the data into the data field twice. Right click on the data field on change the chart type to line and the other to column. Here is a table I used to show this. I created a Column chart and dragged over the date and numbers fields as shown. Notice I placed the Number data in the data field twice. Then I right clicked on the data field and se...
When using a stored procedure to call data in SSRS (reporting services) you will need to pass the parameter from the report to the stored procedure. This is easy when you are passing in a single value. When you want to use a multi value parameter you will need to use a split function to divide the multi values into a table. Here is the Table-Valued function I use: /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 01/26/2010 10:22:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER...
In some instances you will want to check to see if another package was successful or failed before running a second package. If you have it set up as a master package calling a child this is easy. What if the package is in a separate job, not associated with the first package at all? You could have the first package write the results to a database or file and then read that in with an execute SQL task in the second package. There is an easier way to accomplish this. Simply set up the first packa...
In a recent Data Warehouse Quick Start I was helping a company build a cube. They wanted to change the Name of the Data Source View. I knew this would cause a lot of other items to break in the project. One of the employees gave me a tip. Hit Ctrl+F and search the XML through the entire project and change the name any where you find it. This worked great and saved a lot of time. This will work with any project in visual studio. Be careful though, if you have other items with a similar name it wi...
Sometimes you will pull a number that represents a date similar, 20090321, which is equal to March 21, 2009. If you need this to be the date data type you will need to conver this to a string and insert the dashes. This will allow you to use the dbdate convertion function to convert it to a date data type. The following code will be in a derived column transform: (DT_DBDATE)(SUBSTRING((DT_WSTR,8)intShipEndDate,1,4) + "-" + SUBSTRING((DT_WSTR,8)intShipEndDate,5,2) + "-" + SUBSTRING((DT_WSTR,8)int...
The Derived column transform can make a lot of changes in a data flow in SSIS. In about 95% of the time the derived column can take care of any data cleansing issues with text changes. There is one thing that is does not have and that is proper case. The proper case takes a text field and capitilizes the first letter and makes lower case the rest of the letters. For example: MY NaMe is mike = My Name is Mike. This is called proper case. In VB you can run a script to perform this. Simply drag in ...
When loading a Dimension in a data warehouse it is sometimes needed to have unknown rows to allow the fact table to load data even if the dimension is missing data. The easiest way to do this is to load the unknown into a specific surrogate key location like zero. To do this you place an Execute SQL task at the begining of the package with the following SQL code. SET IDENTITY_INSERT dimRegion ON IF NOT EXISTS (SELECT * FROM dimRegion WHERE RegionSK = 0) INSERT INTO dimRegion (RegionSK,[RegionCod...