This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business i...
Read More
Thanks to everyone who attended my session at SQL Saturday #130 in Jacksonville, FL a couple weeks back. I apologize for posting this so late, but better late than never. To download my session materials, just click this link . In it you'll find my PowerPoint slide deck and the MDX script I used in the class. If you have any questions about what we went over or any questions regarding the materials, feel free to leave me a comment or shoot me an email. Thanks again to all those who attended... e...
This weekend is SQL Saturday #130 in Jacksonville, FL. On Friday, April 27th, is a great pre-con session by SQL Server MVP, Kevin Kline on Performance Tuning SQL Server 2008 R2. Go here to get registered for this phenomenal opportunity to learn from an unquestioned expert. Also, I'll be presenting a session on MDX called MDX 101: An Introduction to MDX. I'm going to be giving an introduction into the multidimensional expression language used to query SSAS cubes. In this session, we will learn th...
With the release of SQL Server 2012 comes a whole host of improvements to Integration Services that makes development and administration of your SSIS packages much easier. And it also looks new and shiny, so you can't beat that. After you've upgraded to SQL Server 2012, you'll have to start converting your SSIS packages to 2012, which isn't all that difficult. There are, however, some fundamental changes to the way packages are built, configured, and managed that are important to know. In this a...
On a recent project I created a Date dimension script that I thought, I?d share (mostly so I would have access to it from anywhere). I based this script on one written by Mike Davis , but I included a few changes such as a first and last day of month flag, open flag, relative time period attributes, and a few others. Enjoy! --Make sure you set the Start and End Date below on row 58 and 59 --Create the tables BEGIN TRY DROP TABLE [DimDate] END TRY BEGIN CATCH --DO NOTHING END CATCH CREATE TABLE [...
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]...
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...
On May 10th and 11th, all SQL hell is breaking loose in Dallas, Texas at SQL Rally ! It's sure to be an incredible event, with tons of experts presenting on an amazingly wide range of SQL-y topics. There will be everything from PowerShell to development to Business Intelligence to SQL 2012 stuff. If you're not signed up for this great even, make sure you do that soon so you still get the early bird pricing , which ends March 15. As if attending this great even wasn't enough, I'll also be present...
The MDX function, Descendants, is a very versatile and commonly used function that returns the members at, below, or even above a specified level. The first argument of the descendants function is a member or set expression. The second argument can either be a specific level expression or a number indicating the number of levels to travel. The following queries return the same results: With Member [Measures].[ Level Name] AS [ Date ].[Calendar].CURRENTMEMBER. LEVEL .NAME Select {[Measures].[ Lev...
PASS SQLRally 2012 voting is now open! There's tons of great sessions out there and many of them have been submitted by some of my good friends here at Pragmatic Works. While voting look for great sessions presented by Pragmatic Works experts like Adam Jorgensen Chris Albrektson Gareth Swanepol Dan Clark Brad Schacht and me, too! Make sure you head over to SQLRally.com and cast your vote ! You can vote for as many sessions as you want but you can only vote once, so make your selections wisely! Y...
If you haven't heard about some of the great things my company, Pragmatic Works, is doing to give back to our veterans integrating back into civilian life, check out what Microsoft is doing to get involved with this. In light of this, Microsoft wants to hear more about #SQLFamily and other amazing instances where someone in the SQL community has lent a helping hand. Microsoft has pledged to donate $50 for the first 400 stories submitted illustrating #SQLFamily. I thought it'd be appropriate, the...
Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work. To create a named set, open your SSAS project and head to the Calculations tabs. Find the icon with the curly brackets and click it. Give your named set a name. For this example, I'm using the...
Last week it was announced that Pragmatic Works' tool, BI xPress , won the SQL Server Magazine Gold Editors Choice Award for Best Business Intelligence and Reporting Tool and the Silver Community Choice Award. BI xPress allows for real time monitoring of all your SSIS packages executing on your servers. The way that BI xPress can allow you to monitor your SSIS package in real time is by applying a standardize auditing framework across all of your SSIS package using the BI xPress Auditing Framewo...
I get lots of questions about how to implement various types of calculations in a cube so I figured I'd cover some of the more commonly used calculations I run into on a regular basis. All of these example calculations I'll be providing were written against the Adventure Works 2008 R2 example cube. 10. YTD, QTD, and MTD Calculations These kinds of calculations are pretty common and I see these in a lot of cubes. They're pretty easy to wire up since there are only a couple simple MDX functions ne...
If the organizations you've worked with are anything like the ones I've had the pleasure of working with, then they probably had or have thousands of SSRS reports spread out all over the place. And back around the time a majority of companies were gearing up to migrate to SQL 2008 from 2005, getting a grasp on the number and complexity of these reports was quite a challenge. This is just the scenario where BI Documenter really shines. Besides being able to document your SQL databases, SSIS packa...
Thanks to everyone who attended my session, Reporting on a Cube with SSRS 2008. I had a great time at the event and I hope those who attended enjoyed the great sessions and maybe even learned a couple things. If you're interested, here is the link to download my slide deck and examples I used during the session. In my example, I used the Adventure Works 2008 R2 cube, so head over to Codeplex to pick up the materials . Also, here is the link to Mosha Pasumansky]s MDX Studio Online tool that will ...
The native SSIS functionality currently prevent you from performing a batch update from a Data Flow Task without staging the data. If you wish to update records from within the Data Flow Task, you're going to have to use the OLE DB Command transform, which is very slow. If you're update more than just a few records, I'd highly recommend first staging the data and then using an Execute SQL Task to perform the batch update. Or you could just use the Task Factory Update Batch Transform from Pragmat...
If you've followed my blog at least somewhat, you've probably read a blog post or two where I cover one or several of the really cool features of BI xPress . That's because, simply put, BI xPress is a very versatile and useful tool that no SSIS developer should be without. It pains me to think about all the time I have to waste whenever I go work for a client that does not have BI xPress. With that said, one of the smaller but more useful features of BI xPress that I found myself making use of i...
One of the most powerful aspects of Analysis Services is the ability to easily view your facts over time. What's not always so easy is writing those calculations that make complex time analysis possible. MDX can have a steep learning curve, which is where the BI xPress MDX Calculation Builder by Pragmatic Works steps in. After installing BI xPress, you will very quickly and easily add calculations to your cube without having to write a lick of MDX. To access the MDX Calculation Builder, open BID...
Next month on November 5th is SQL Saturday #86 in Tampa, Florida . But this is no ordinary SQL Saturday. This is SQL Saturday: BI Edition! Which means this SQL Saturday is going to be even more amazing, hard core, and in your face than usual! If you've never been to a SQL Saturday event, let me just say that you are missing out. And if you have been to one before, then you know exactly how awesome these events are. Whether you show up for the incredible FREE training from qualified industry pro'...
The Visual Basic for Applications library functions are a great way to extend the functionality of your MDX statements, allowing you to do some very handy stuff. Irina Gorbach has created a nice little document outlining the VBA functions available , so definitely check that out. There's an important performance difference between the VBA functions and native MDX functions, which Irina points out. One of the VBA functions I've been asked about by a couple people is the IsNumeric function. The Is...
I recently encountered a requirement for a client to display a list of all the individual stores of a ragged hierarchy (the leaf members) on an SSRS report with a bunch of different measures. But they also wanted to be able to quickly see the the ancestor members of each level above each store quickly by using a tool tip to display the different ancestors. Turns out there are a couple nifty, little MDX functions that can allow us to do this very easily. The Ancestors and Generate functions make ...
If you're even slightly familiar with SSIS you know that one of the incredible strengths of SSIS is the ability to make packages dynamic in countless different ways. One of the ways SSIS accomplishes this is through the use of SSIS expressions and the expression language. BI xPress has a really useful feature called Expression Manager . The Expression Manager is a very cool feature that allows an SSIS developer to create, share, and manage custom SSIS expressions. Creating your own SSIS expressi...
One of the most powerful features of BI xPress , in my opinion, is the Package Builder Wizard . The Package Builder Wizard allows an SSIS developer to create a template from a package or a from a few components of a package. The developer can then use those templates to create new packages or alter existing packages. In this short post, I'm going to walk through creating a template that contains a package configuration and then show you how to apply that template to all the packages in my SSIS p...
I was working with a client the other day designing a couple cubes. These cubes used two pretty large dimensions, each containing many attributes. In order to increase the performance of these large dimensions, I worked with the client to identify attributes that the end users would not necessarily need to slice and dice with, but would still be useful to view the attributes. Once those attributes were identified, I displayed them as what was known as members properties. So lets walk through how...