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...
Read More
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...