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...
Read More
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...
Among the many cool features of BI xPress is the extensive and robust Auditing Framework available right out of the box. With the BI xPress Auditing Framework, a user can track errors, warnings, row counts, variable, connections, source queries, and many other package properties. Whether you're applying this incredible Auditing Framework to a single package, a dozen packages, or 100 packages, stepping through the wizard only takes a few moments. In this post, I'm going to walk you through applyi...
If you've ever tried to use an IIF statement expression to fix an error received by dividing by zero, you probably still received the divide by zero error. Very frustrating. An expression like this returns an error when Sum(Fields!Beta.value) = 0: =sum(Fields!Alpha.Value)/sum(Fields!beta.Value) So you, being the critical thinker that you are, try the following: =iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/sum(Fields!Beta.Value)) Alas, this will not work. Even though SSRS may evaluate ...
Here’s an extremely helpful little tip that my friend, Ken Hendrix, showed me the other day. Currently, to execute a single SSIS package in BIDS, you have to open the Solution Explorer, right-click the package, and click Execute. If you’re debugging a lot or do not have your Solution Explorer open, this can sometimes be a little inconvenient. So Ken came up with a Macro that you can add to your tool bar that will allow you to execute your open package with a single click. 1. The first step is to...
With the release of R2 came a couple of pretty nifty new expressions in SSRS. The LookUp function allows us to perform a look up against a separate data set in our report using a field in our source data set where there is a 1 to 1 relationship. Likewise, the LookUpSet function allows you to do a look up values in a separate data set where there is a 1 to many relationship. The LookUp and LookUpSet functions are useful when you cannot write a query to join our two data sets together. So maybe we...
Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something. If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars fr...
Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works and it last about an hour. In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increas...
One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress , SSIS developers can easily and automatically encrypt any configured pr...
If you read my blog you know from time to time I will blog about an exceptional third party SQL tool. Previously I’ve blogged about Task Factory and BI xPress , two amazing SSIS development tools. Well this week I figured I would talk about a killer SQL documentation tool called BI Documenter . BI Documenter allows you to quickly and easily document your entire BI environment. So unlike the other popular tools out there, BI Documenter will document SQL Server databases (2000, 2005, 2008 & R2...
If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error: This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn't make sense and isn't something you'd do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example: WITH MEMBER [Measures].[YTD Average Price] AS Aggre...
Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great inform...