I did a webinar on Monitoring SQL Server with SSIS on pragmaticworks.com. Check it out on the webinars page there. Here are the code files for that webinar. The code is done in 2008 R2.
Read More
Our Product BI xPress won two awards from SQL Server Magazine. Check it out! http://www.sqlmag.com/content1/topic/2011-sql-server-magazine-editors-community-choice-awards-140830/catpath/awards/showprivate/1/page/3
With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used. For example: Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount]) This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time. Here is how I did this: Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or [Date].[Calendar].level is [Date].[...
I just returned from Seattle, WA and my first time speaking at SQL PASS Summit. I did the SQL Smackdown T-SQL vs SSIS Loading a Data Warehouse with Adam Jorgensen. The session was a hit, it was standing room only and the room was full from beginning to end. Adam did a great job showing the T-SQL method and I showed the SSIS method. In the End The referee Devin Knight and Adam jumped me a stomped me into the stage. I survived and will live to put on another SQL Smackdown. You can download the fil...
Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here . Well things have changed since I wrote that blog, in fact I believe that old code only works in SSIS 2005. So I thought I would be good to update to SSIS 2008 and show you new and better way to loop through all of the columns in a script tasks. You could do the same work with a derived column. The problem comes when you have hundreds of columns and you need to do the same work on eac...
SQL Saturday 85 in Orlando , FL on September 24, 2011 was a great event. The people putting it together did a great job of putting on the event. The logistics on these events must be a daunting tasks and they handled it perfectly. I would like to personally thank Bradley Ball ( Blog | Twitter ), Karla Landrum( Blog | Twitter ), and Shawn McGehee( Blog | Twitter ) for the work they did to put this SQL Saturday. I apologize to anyone I left out. In my first session I covered SSIS Script tasks. You...
Check out this cool Infomercial Pragmatic Works made about BI xPress. http://pragmaticworks.com/landing/bixpressinfomercial.aspx
My SSIS White Paper has been published! Download the White Paper and the Sample Files here. "SSIS is a fantastic Microsoft tool for performing Extract Transform and Loading (ETL) procedures. A challenge in SSIS is identifying a problem once you deploy your packages. In this webinar you will learn how to both identify and solve your SSIS issues." REGISTER NOW! What you'll learn: ? How to set up auditing on SSIS packages ? How to set up logging on SSIS packages ? How to use both native t...
The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results into a variable or into a file. Either way, you can then parse the XML to find the node values you need. This can be done with the XML task in SSIS. Click here to see my blog on the XML task . The Web Service I am using is a free demo service. It allows you to enter a zip code and returns the city ...
I?m very proud to announce that Pragmatic Works will be presenting a Business Intelligence Workshop pre-con for SQLSaturday #85 in Orlando on Friday September 23rd! In this session you?ll have not one, not two, not three but FOUR Pragmatic Works consultants walking you through how to develop a full business intelligence solution from the ground up! Your instructors for this awesome pre-con are: Jorge Segarra ( Twitter | Blog ) I?m a BI Consultant for Pragmatic Works and a SQL Server MVP. In addi...
Are you ready for a knock down drag out fight! Come See SSIS vs T-SQL at SQL Saturday 85 in Orlando on September 24, 2011. Watch Mike Davis (@MikeDavisSQL) take on Luis E Figueroa (@LuisEFigueroa) in a head to head battle. Showing the best practices of each method. Also, check out the pre con with Mike Davis and some of the other powerhouses from Pragmatic Works. http://www.sqlsaturday.com/85/eventhome.aspx
Here are three scripts that create and Date and Time Dimension and can add the fiscal columns too. First run the Dim Date script first to create the DimDate table. Make sure you change the start date and end date on the script to your preference. Then run the add Fiscal Dates scripts to add the fiscal columns. Make sure you alter the Fiscal script to set the date offset amount. The comments in the script will help you with this. This zip file contains three SQL scripts. Create Dim Date Create Di...
I am very excited to announce that the Schedule is now live for SQL Saturday 85 in Orlando, remember to save the date for Saturday September 24th! There will be a lot of information to come. There is a Full Day Pre-Con Training also. I will be seaking on SQL Server Denali and also on Scripting with .Net in SSIS. Hope to see you at my sessions. Please go to the link, http://www.sqlsaturday.com/85/schedule.aspx , to see the sessions and the tracks. Look forward to seeing you in Orlando!
PASS Summit is coming up fast, and my Session with Adam Jorgensen "SSIS vs T-SQL: Loading a Data Warehouse", is one of the comunity choices. Please take a moment and vote for my session. Be sure to vote for my Community Session for PASS Summit http://www.sqlpass.org/summit/2011/SummitContent/CommunityChoice.aspx SSIS vs T-SQL: Loading a Data Warehouse
Getting the YTD from a cube is a simple MDX formula either using the PeriodToDate function or using the YTD function. One request you may get it to see the previous year to date. So if you are looking at March 21, 2011 year to date, you want to see the year to date from March 21, 2010. This can be done by using the ParallelPeriod function. Here is the Previous YTD calculation using adventure works. (PARALLELPERIOD( [Due Date].[Date].[Calendar Year], 1, [Due Date].[Date] ), [Measures].[Sales Amou...
Previously I wrote a blog on Top N and Bottom N reporting. There were a couple of gotcha I did not cover in that blog so I thought I would cover those here to answer some of the questions I have received. The other blog can be found here . On a report you are trying to group the top N number of rows together and all others should be in a separate group. Let’s say you want the top 5 sales people based on amount. In my other blog I showed how to use the Top N and Bottom N filters on groups. The ma...
Checking a File in Use and File Compression in SSIS with Task Factory Getting compressed files from customer is a common occurrence in today’s database world. Decompressing and loading those files can be a lot of work, even in SSIS. Task Factory makes this very easy. Task Factory comes with almost 30 tasks. Two of those tasks are the File Properties Task and the Compression Task. The File Properties Task lets you check all of the properties of a file, including checking if a file is in use. Look...
Ever had to load a Data warehouse? Isn’t it so much fun creating all of those transforms to load a type 2 dimension? Of course it’s not; it is a lot of work. Well now you can load those dimensions very easily using Task factory. Task Factory is a group of almost 30 tasks that you can add on to SSIS. These tasks make your job much easier. Instead of creating dozens of task, you can use just one Task Factory task to accomplish the same work. Usually you would need a data flow like the following im...
How to Document SQL Server So you created this amazing BI solution. You have SSIS Packages loading your data warehouse, you have SSAS Cubes, and you have reports in SSRS. But how do you document this entire solution? It used to be a long tedious process. Now it’s easy! Bi Documenter is a must have tool for anyone using SQL Server. It will document SSIS, SSAS, SSRS, and databases. BI Documenter will create either HTML files, or one CHM file containing all of the information of your environment. E...
BI Xpress SSAS MDX Builder and SSIS Deployment If you build SSIS Packages, or work on SSAS Cubes, BI Xpress is a must have tool for you. It has made my life so much easier in all of my BI work. I want to share with you just a couple of the features it contains. This is by no means a complete list features. You can find a complete list at pragmaticworks.com. Deploying SSIS Packages is one of the biggest pains in SSIS development. Not anymore! I can deploy my packages with a two clicks now. You ca...
SSAS – Using the Euro or Pound currency Symbol in a measure Format String If you have a set of measures and some of them are US dollars, some are Euros, and some are UK Pounds, you want to format them correctly in SSAS. First you will need to know the ASCII command to type a pound and euro symbol. You can find this in the character map in windows. You must use the 10 key number pad to type these in while holding down ALT. Euro = Alt + 0128 € Pound = Alt + 0163 £ Next you will need to put a back ...
When you want to transform data in a data flow task in SSIS, the derived column transform performs this very fast due to the batch method. But if you have hundreds of columns that need the same transform performed on them, it can be very time consuming to create each of these derived column expressions. You can use a script task to loop through the columns in a data flow and perform the same task on every input column you select in the script task. Keep in mind, this will degrade performance, bu...
In some scenarios you will need to create a many to many relationship in your cube in SSAS. I have an article that shows you how to do this here . One of the problems that arise from many to many bridge tables are the size of these tables. In this example we have a fact table that contains the history of a person and measures for that person at a bi weekly basis. Each person can be in a Program. Each person can be in more than one program at a time also. We have thousands of people so the fact t...
When you are working with dimensional modeling there are some situations where several dimensional attributes don’t make since to be in any other dimension. When this occurs you can combine them into one dimension call a Junk Dimension. In this example you will see a person type dimension that only made since to be in a dimension together. This client has 4 different types of people in their system. The junk dimension will contain each possible combination of these person types. To avoid having ...
Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or...