You may have noticed a flux of new blog post entires yesterday from a group of people talking about the Pragmatic Works Foundation. This is a great group of people that are new to IT and learning the basics of SQL Server. Per our website: The Pragmatic Works Foundation was created to find passionate people who are interested in joining the technology field but cannot make the financial investments needed for training and hardware. Working with our job placement partners our goal is to place all ...
Read More
Reporting Services has many expressions that can manipulate date fields. These expressions can be used just about anywhere in SSRS including object properties, displayed data, and parameters. Recently I’ve worked on some projects that required date parameters to do things that you would typically see in an Analysis Services cube. My client needed to see data brought back Week to Date, Month to date, Year to date, and on with a rolling week. My first suggestion of course was to create a cube to n...
The SSIS expression language is a powerful tool for a developer that gives you one method of making a package dynamic. When writing an expression there are a few things to be careful of that could potentially cause a stumbling block while writing code. For example, a common reason you may write expressions is to populate a changing file name. Say you want to have a file loaded but the name of the file should change each day the SSIS package is ran to have the current date appended to the end of ...
Several months ago I blogged about why Checkpoints are a great feature in SSIS to use. It seems with every feature there are potential problems you can run into and it is no different with Checkpoints. The major problem or what some people would call a feature is when Control Flow tasks are run in parallel checkpoints act a little erratically. The obvious solution for this is to not run tasks in parallel and instead have all your tasks kick off sequentially. Here’s an example of the problem: Fir...
If you weren't able to attend tuesday for SQL Lunch you can watch the recording now mosted on the website. This session was on Data Driven Subscription. We had a great group of people with lots of good questions. Thanks to Patrick LeBlanc , founder of SQL Lunch, for letting me present. Patrick recently wrote a blog post that answers a question that some people had about subscription names so make sure to read that. Make sure to watch the recorded presentation and add to your calandar some of the...
Recently while at a client I was discussing the differences between Report Builder 1.0 and Report Builder 2.0. The discussion was really around will the end user actually use Report Builder and if they will should they have access to Report Builder 1.0, 2.0 or both. I explained that Report Builder 2.0 had a lot more features than 1.0 and that if the end user is tech savy enough they should go that route. So it was decided to go with Report Builder 2.0 but I explained that the Report Manager auto...
For a little more than a year now I've been a part of the PASS BI SIG and have tried to contribute whenever possible. The SIG is split into several focus groups that meet on all BI topics. I help in leading the SSIS group. Today I actually had an opportunity to present to the Analysis Services focus group on the topics of Analysis Services Partitions and Aggregations which you can view here. If you think you would like to present on any BI topic contact me and we'll get you lined up as a speaker...
In part one of this post I showed how you can use a report to run SQL Server Agent Jobs by using a report. In that post I only showed you how to run a single job but hardcoding in the job name. Someone recently asked if it was possible to have a drop-down list of jobs to choose from instead of have the hardcoded job name so I thought it made for a great opportunity to write a part 2. Step One Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is lo...
Running SSIS on a 64-bit machine has several caveat that developers need to be aware of. Tasks like the ActiveX task and Execute DTS 2000 Package Task only work either on a 32-bit machine or by running in 32-bit mode on a 64-bit machine. This is also the case for many data providers like Excel, Access, and many ODBC drivers. If you are working on a 64-bit machine don't worry there is a work around to keep yourself productive while still using 32-bit tasks and drivers. Executing In BIDS Right-cli...
Often when designing a report you may find a need to concatenate values in an expression. For example, you want a column with full names but your data source has names stored separately for first name and last name. Easy enough you can write an expression like this: =Fields!FirstName.Value + " " + Fields!LastName.Value Problem solved right? Well let's complicate things a little. Let's say you not only want the full names but all addresses as well but you don't want it on the same line. You need ...
This is part four in the series of blog posts that will help in building a library of calculations you can use as a reference in any analysis services cube you build. You can find the previous posts to this blog series below: Part 1 – ParallelPeriod Part 2 – PrevMember Part 3 – PrevMember minus CurrentMember All of these blog posts are formatted to give you the business problem, a general solution and then the calculation needed to finish the job. Problem You need to show company sales year to d...
This is part three in a series of blog posts that will help you build an arsenal of MDX calculations that you can have ready at the drop of a dime. The first two posts of this blog series use the ParrallelPeriod and PrevMember functions to return data at different levels. All of these blog posts are formatted to give you the business problem, a general solution and then the calculation needed to finish the job. The problem this time will use part of the solution we used from the second blog post...
This post is a part of a series of blog posts I am writing to give you a Batman-like Utility Belt of MDX calculations. With this Utility Belt available you can have a template of commonly needed calculations you can use for just about any Analysis Services project. In part one of this series I showed how you could use the function ParallelPeriod to return sales from the previous year at all levels (Year, Quarter, Month). In this series I present the business problem, a general solution, and the ...
Developing MDX calculations is one of the most confusing and time consuming pieces of building an Analysis Services cube. That is why I have accumulated what I like to call a Utility Belt of calculation that I can pull out at any moment to solve a problem. I consider this a Utility Belt because on just about every Analysis Services project that I have worked on these calculations have come in handy and I hope they will be helpful for you. When building calculations you may find the templates tha...
A common question I've been asked a lot lately is how to replace the icon in the Report Manager to my company's logo. It is actually fairly simple to do by following these steps: 1. On the server reporting services is installed on open the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images 2. Rename the file 48folderopen.jpg to anything else (Ex. 48folderopenOLD.jpg) 3. Resize your logo to be 48 x 48 pixels jpg file 4. Rename it 48folderopen.j...
When developing Reporting Services reports that use Analysis Services as a data source you may find that it is difficult to dynamically set default parameter that are passed into the dataset being used. It turns out this is a lot simpler to do then many people think. In this post I’ll walk you through the steps of setting a default date value in a dataset’s parameter. Step One When creating a dataset that uses Analysis Services you will select Query Designer and then drag over the measures and d...
Here are some steps to fixing an issue I ran into with Window 7 and Reporting Services 2008. The problem was when I attempted to access the Report Server or Report Manager from my browser I received the error HTTP 404 Not Found. I didn’t find a direct answer on the web for this but I did see a lot of people being directed to change the port used to 8080 without any explanation why. I don’t think this is an issue exclusive to Windows 7 but it happened immediately after installing it (maybe someon...
Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel. Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner. With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did: Step One Create a Data Source that points t...
Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution. Before you even see the classic Report is being generated sign the report is slow to start. The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset. For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for. This would mean...
If you've ever had this error "OLE DB error: OLE DB or ODBC error: Operation canceled; HY008." appear while processing your cube you may be a little confused. Here were some of my frustrations with it: First, this error doesn't really give you a single place to start looking. Second, it may produce errors on several attributes that it has nothing to do with. Last, there are plenty of references online asking questions about this error but not a lot of answers. The only clue I had...
There are hundreds of post on this topic but I just ran into this problem recently so I thought I should add one more. I created a package that sends a flat file to an FTP. The package runs fine in BIDS but when you deploy and try and run from a SQL Agent job it fails. Here’s a segment of the error: Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error There’s actually a very simple solution to this problem. By default...
I decided to do a little performance testing on some common SSIS “Tricks” that increase speed and effectiveness of SSIS packages. I wanted to define to what extent just one simple change could make to a package. So in this test I decided to show the difference in using a staging table vs. using the OLE DB Command in a SSIS package that uses a change data capture technique. Many people have the impression that landing data in a staging table is bad for some reason, but in almost...
I had another chance last week to answer an email follow up question from the session Brian and I gave at PASS. This made for a great blog opportunity so I thought I would write about using Fast Parse in SSIS. Hopefully I can explain it even more basic then Microsoft does on MSDN . Using fast parse can drastically improve performance of your package when using a flat files source or data conversion transform by basically not validating the columns that you specify. Keep in mind that these column...
I have decided to make a short series of blogs about possible interview questions to help you prepare for an interview. Last time I wrote about basic DBA questions. Today I have given you some basic SSIS question to study. These basic SSIS questions will give you a starting point on how to prepare for an interview. If you have any other suggestions feel free to add to this list but keep in mind these questions are probably for a mid to junior level BI consultant. I would assume if you're a senio...
This is just a starting point for you to prepare for your interview so make sure not to limit yourself to what you find here. These answers could easily vary but generally someone who is interviewing is looking not only for a correct answer but confidence in your answer. If you have any questions about my answers or have something to add feel free to do so, but I did try to keep the answers short and sweet. SSIS Interview Questions 1. What does a control flow do? A control flow manages the workf...