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...
Read More
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...
If you’ve ever built an SSIS package that utilizes the Microsoft Jet Driver for an Access or Excel data source or a package that executes a SQL 2000 DTS package, you know that you must set the Run64bitRunTime property in the SSIS project properties to False. This stinks if you’re executing these kinds of child packages from a master package that also executes child packages are not required to execute in 32 bit mode. Dry your eyes, my friend, because there is a work around for this. You can down...
As a Business Intelligence Consultant with Pragmatic Works, I have the pleasure of being able to take for a spin any of the sports cars in the garage of Pragmatic Works software. And if you’ve frequented my blog before, you’ve heard about how much I love using BI xPress to speed up my SSIS package development. One of the coolest pieces of software out of Pragmatic Works is Task Factory. Task Factory is a collection of high performance SSIS tasks that allows you to add and extend layers of functi...
The Bulk Copy Program Utility (BCP) is a very nifty little command line tool that allows us to bulk copy SQL Server data from or to a data file in a user specified format. The BCP is very useful when you need to copy large amounts of data into a data file for use in other programs or if you need to back up your data in a specified format for a third party. But the BCP is also very useful when you need to copy large amounts of data from one SQL Server table to another table. There are certain cas...
If you’re not familiar with the BIDS plugin by Pragmatic Works called BI xPress, you really should check out this tool. For the small amount of money you’ll spend on BI xPress, you’ll add a ton of powerful weapons in your SSIS/SSAS development arsenal that no serious developer should be without. Since I’ve been working for Pragmatic Works for the past couple years I’ve had many opportunities to work with BI xPress. I can definitively say without a doubt that the gigs I’ve been out where I have B...
This week's puzzle was pretty straight forward, but it required you to use the OpeningPeriod function . To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”. WITH MEMBER [Measures].[First Day Customer Count] AS (OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count]) In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we w...
If you’ve followed my blog, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out . Anyways, on to the puzzle! As always, these MDX queries were written against the Adventure Works DW. Here are the requirements: Rows: Calendar Year Columns: Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist) First Date in Year Hints: Take a gander at this . And here’s a sneak peak at my results: If you t...
As you've probably noticed, Pragmatic Works is once again running another promotion. This promotion is a little different though in that it's probably the best deal we've ever had on our software/training bundle. If your company purchases at least a single, lonely license to any of our incredible software, inlcluding Task Factory , BI Documenter , BI xPress , and/or DTS xChange , and you get a free seat in either our SSIS or SSRS class. The really great thing about these classes is that you can ...
Yesterday I was working on a requirement for a client that asked me to show a list of Organizational Units and the every single parent Organization above every Organization. The source table gave each Org Unit’s ID with their parent Org Unit’s ID. In Oracle’s PSQL, this requirement was met using the Start With and Connect By statements. In TSQL, however, there is no such thing. To meet this requirement, I could have written multiple select statements Union’ed together with differing amounts of j...
Devin Knight and I stumbled onto another weird SSAS bug today. We had a measure group that had two measures. The first measure was semi additive (Last Non Empty) and the second measure was a normal additive type (Sum). The additive measure would not display along side the semi additive measure, whether I browsed the cube in Excel, BIDS, SSMS, or wrote an MDX query. Well as it turns out this is a known issue that was identified some time ago. SQL Server 2008 SP1 solves this issue: http://support....
While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked perfect, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error: After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below), my calculations appeared ...
A couple blog posts ago , I discussed using a Common Table Expression (CTE) and the ROW_NUMBER() function to create a field in a data set the retrieves a field in the previous row. But since CTE's and the ROW_NUMBER() function aren't available in SQL Server 2000, how would you accomplish the same thing with SQL Server 2000? Well the good news is that its not complicated to do. The bad news is that it can be not very efficient performance-wise. In order to create our row numbers, we'll first decl...
If you're just setting up a brand new computer like I am, you may have experienced this issue before. When you try to restore a backup file or attach a database in SQL Server Management Studio, you may be unable to browse to the location of the .bak or .mdf file if it is located in one of your personal folder or another similarly secured folder, as seen below. Keep in mind I am using Windows 7 and SQL Server 2008 R2. While the folder that contains my backup file is located within the folder shar...
If you have ever worked with SAP as a SQL Server developer, you understand that it can be challenging. Recently I was working on a data warehousing project that featured SAP as the data source for our data warehouse. We needed to be able to create a record of history using mulitple tables from SAP. Many SAP tables feature effective dates (BEGDA and ENDDA). So one of the challenges we faced was merging the many tables together to create our record of history. But after some brain storming, we cam...
Update 2/10/2011 After some thinking, I decided to add the BI xPress Auditing Framework and Monitoring to my list of favorite BI xPress features. Being able to view the execution of the package while deployed on the production server is so priceless. Being able to monitor packages executed outside of BIDS and actually see the package executing as if you were in BIDS is simply amazing and really helps in troubleshooting those strange issues that always seem to creep up when pushing to production....
So imagine that you need to create a solution to moving the aggregated data from a cube into another location for a third party, such as a spread sheet, SQL Server table, or a flat file. We can accomplish this task using SSIS. First, create a new OLE DB Connection using the Microsoft OLE DB Provider for Analysis Services 10.0 (or 9.0 if its an SSAS 2005 cube you need to get to). After creating our Connection Manager, we can now create a Data Flow Task with an OLE DB Source using our new Connecti...