In the continuation from my previous post on For Loop Containers I?ll give you a little more practical example of using the container. A ?Watcher? package is used quite often for checking for the existence of a file in a folder or for a row to be inserted into a table. Lets set one up. Here?s what its going to do: Check for new records in a source table. If there are new records in the table, then rows will be inserted. If there are not any new records, the package will enter into a wait loop, a...
Read More
SQL Saturday Jacksonville is just under a month away (April 28) and I’m happy to announce the we recently released the schedule for the event. We have a great line up of speakers with a variety of topics for both DBAs and Business Intelligence developers. You can register to attend the event here . While the speakers may be all set for the event we’re still looking for sponsors. If you’re interested in sponsoring our event please visit our sponsorship page . Don’t forget ...
In a previous blog I talked about the Sequence Container, and some of its uses. To continue with that theme I now bring you the For Loop Container. The For Loop is one of two Loop containers available in SSIS. In my opinion it is easier to set up and use than the For Each Loop, but it is just as useful. The basic Function of the for loop is to loop over whatever tasks you put inside the container a predetermined number of times, or until a condition is met. The For Loop C...
Learning to write MDX is difficult enough, but learning to write efficient MDX and performance tune an MDX query can be even more of a challenge. With that thought, I wanted to put together a few tips that can help you improve the performance of your MDX calculations. 1. Subdivide your calculations For example, imagine you have an MDX query that looks like this one found in the AW cube: Create Member CurrentCube.[Scenario].[Scenario].[Budget Variance] As Case When IsEmpty ( ( [Measures].[Amount]...
It's been a day filled with status bars and brief fits of joy. I'm now sitting at my computer, waiting for the updates to finish installing on my virtual machine that I almost literally spent ALL DAY installing and configuring, so that I can continue to learn what the heck it is I'm supposed to be doing. I know that this will click at some point, and I have made a lot of progress in SSIS and T-SQL, but it seems like every day there's a new acronym, a new process, a new "Oh, you didn't realize yo...
Today I had the opportunity to revisit a problem I encountered previously; but, today was different. Today I found the solution. Adding Time Intelligence to your SSAS cube using the Business Intelligence Wizard is an easy way to add a whole bunch of useful information to your solution. It is one of my favorite topics when teaching boot camps or quick starts because it adds so much functionality to your Analysis Services solution without a lot of work. One day, I decided to create an SSRS report ...
A custom logo design is most essential for your organization in terms of its brand identity, recognition and reliability. A custom logo design is not something which is not done in a matter of hours, instead, even the simplest form or type of logo design takes days or sometimes evens months to get to the perfect last stage. Why is this so you might? You might think the logo design online is easy and looks simple enough to create, but you need to keep in mind that a custom logo design cannot be s...
Lincoln on Leadership by Donald T. Phillips Hello BIDN, Before everyone starts to ask why I’m posting a blog about a book that has nothing to do with SQL or Business Intelligence, let me briefly explain. You’re correct. This book in theory has nothing to do with SQL, BI or IT in terms of technicality. However let me ask one question, why do a lot of people in the SQL community visit great sites such as BIDN, SQLServerCentral and so on? The reason people visit these sites is to improv...
I?ve been asked to provide links to some useful resources for learning about SQL Server Reporting Services. Below are a list of my favorite blogs, books, and other sites to learn from. A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That?s because I?m lucky enough to work with some of the best people in the field. Also, in the case of the books I?ve linked to the Kindle version where possible, mostly because I?m a ...
Hello BIDN family, After a long time gap, I just want to share a link to download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’. This could be a great eBook for you to learn and know about what’s inside the ‘ SQL Server 2012 ’ released by Microsoft. I have already downloaded and have started to read and learn the things .Hope you will too. This link could be helpful for all of us in the field of BI-SQL server to know more about SQ...
In this third blog post we will cover the new DateTime functions available in SQL 2012. If you have read my previous 2 postings on this subject you probably have noticed only 2 -3 function per category. Well in this posting this changes where we will be covering 7 new DateTime functions. The first function we will be looking at today is the DateFromParts function. Syntax, DATEFROMPARTS ( year, month, day ) Examples: SELECT DateFromParts ( 2012, 07, 04 ) AS result; The second function is the Date...
Let me start by saying that I am not a DBA, I am a BI Developer. I work with some really bright DBA?s and they can tell you much more about this topic then I can, and probably in great depth, with many terms like PERFMON, DMV and querying a bunch of those Sys tables. To this point in my career I?ve haven?t had the exposure or need to think about many of the DBA tasks. I mostly worry about building packages, cubes and repots. This just isn?t going to cut it. As I?ve ...
In T-SQL there are many functions there to help do a variety of different things. SSIS is no different. In fact, a good number of the commonly used functions from T-SQL are in SSIS as well. Here we will explore the different Trim functions found in SSIS. We will be using the test phrase " Test " to illustrate the three functions: LTRIM, RTRIM and TRIM. LTRIM (Left Trim): Just as in T-SQL, this removed empty spaces from the beginning (or left) of a string. Notice the highlighted result that the t...
In an SSIS package you have the option to choose from a wide range of source from Flat Files to writing queries using OLEDB sources such as SQL Server. Often times, such as writing queries against SQL Server, the metadata will come from the table and you won't need to worry about truncation in the source. Flat files are scanned and metadata is chosen based on the file so those are usually good to go as well. Excel however tends to just use the default length on all columns of 255 characters. Thi...
With this post I begin a series of blog posts covering one of the most talked about features of SQL Server 2012: Tabular Modeling. Being new to this like most of us are, I hope to learn much about Tabular Modeling as I walk through the basics of building your first Tabular Model. In this installment, we'll talk about what a Tabular Model, when a Tabular Model is the right choice, and of course how to create a Tabular Modeling. What is a Tabular Model? With the release of SQL 2012, we are (re)int...
Over the past week I?ve detailed several different ways to handle Slowly Changing Dimension Type 1 Changes using various native and 3rd party components in SSIS. In this entry I?m going to show you the Update Batch Transform in Pragmatic Works? suite of SSIS components called Task Factory. While on the surface you might be wondering why I would replace one native SSIS component with a 3rd party component if they do the same thing. While they accomplish the same thing, updating dimens...
In a previous post I detailed how to create a package that handles SCD Type 1 changes using SSIS. The component that handles writing the updates to the destination table inside the data flow is the OLEDB Command. It is the only native component in the dataflow that can write update statements to the destination table. Unfortunately these update statements are done on a row by row basis. The question is then, how do we accomplish batch updates in the SSIS package? The answer is ...
Tomorrow at 11:30 CST Pam Shaw will be talking SQL Reporting Services (SSRS). If you have time, join us for some SQL Lunch . Here are the details: #64-Taking the Scary out of Monster Reports Speaker: Pam Shaw Add To Outlook: Add To Calendar Date and Time: 3/22/2012 11:30:00 AM CST BIO: Pam Shaw has been in IT for almost 30 years. Since 2001, Pam has been working with SQL Server, first 2000, then 2005 and now 2008. Pam is currently an independent contractor. Pam is also the Chapter Leader of the ...
Multidimensional Expressions which are usually abbreviated to MDX are also sometimes abbreviated by frustrated developers as @#%*^$!. For those of us who have been using T-SQL for a long time MDX can be an unpleasant new language to pick up. For those of us who do not work with MDX all the time, it’s a constant cycle of readjusting, relearning some old tricks and then setting it aside again. I have found that many web postings are either too simplistic to help the new MDX user, too complic...
Not every package requires the use of a Sequence container, and not everyone likes to use them. For me personally they can be a huge help when developing and debugging SSIS packages. Some of the most common uses for Sequence Containers are: Group and control the execution order of a package. For example loading a data warehouse, place all of the dimension load tasks inside the sequence container and allow them to run in parallel, then connect the sequence container to the fact table ...
I just wrapped up my 24 Hours of PASS session on consolidation. A big THANK YOU again to everyone in attendance, who kindly put up with my horrendous rapping "skills"! As promised, below is the link to the slide deck. In the presenter's notes you'll find some good links and resources for consolidation. Download link: STOP_Consolidate_and_Listen_24HOP (ZIP file) If you have any additional questions around consolidation, virtualization or my general sanity feel free to leave them in the comments s...
If you’ve followed this blog series from the beginning then you may have started thinking about which tools would be best for your environment. If so that’s great and I’m glad I got you thinking, but I encourage you to keep an open mind as we go through the last couple tools because both PerformancePoint and Power View provide some of the most impressive visualizations that the Microsoft BI reporting tools have to offer. If you’re new to reading this blog series I encoura...
Just when I thought I had my T-SQL querying down, I’m introduced to MDX, and terms like “tuple.” It’s almost like turning the things I learned about T-SQL sideways. So I’m poring through my book and doing some examples and thankfully I have time to practice this. Below is an example of a very simple MDX query; it was successfully executed and returned the Sales Amount of $29,358,677: SELECT [Measures].[Sales Amount] ON Columns FROM [Adventure Works Cube] I will defi...
Today is day two of my new life in the IT world as Pragmatic Works’ newest QA Engineer, and I’m wondering what I got myself into. However, I want to start out on a positive note: Nothing but green boxes. It’s a beautiful thing. This is an example of one of the packages that I worked on last night through this morning. I finally got it to execute properly though, so I eventually came out victorious. There is quite a bit to learn in SSIS, and I know I’ve got a lot ahead of ...
In loading “.csv” files to a SQL Server 2008 database, I have encountered several data type issues, especially regarding “Datetime” for a “Modified Date” column on my tables. Here is my ordeal: After adding my Flat File transform and linking it to a new connection manager source, I proceeded to customize the data type of my source to accommodate my SQL Table’s. However, I kept getting data type errors for my “Modified Date” column. I have che...