The Best SQL Documentation Tool on the Market

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.

image

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), SSIS packages, SSAS cubes, and SSRS reports. Because all of these objects can be documented in a single snap shot, you are then able to easily perform impact analysis across all objects, which has a pretty slick UI and is very intuitive. You can also do snapshot comparison across your whole stack, so you can see how any object in a snapshot differs from a previous snapshot.

image

One other thing to mention about documenting SSIS packages is that BI Documenter is the only documentation tool that not only documents the SSIS packages textually, but also visually!

screen30

This means that after you document you SSIS packages, you can view the SSIS packages (Including the Data Flow Tasks!) just like you would in BIDS. Very cool!

BI Documenter is a pretty cool documentation tool that is very well rounded and since it’s the only tool on the market that can document the entire BI stack all together, its basically you’re only choice if you want to do impact analysis across the stack. BI Documenter also has a lot of awesome features coming up in the next release such as documenting server level objects, like SQL Agent Jobs, Linked Servers, Backup Locations, and others.

If you’re in the market for a rock solid documentation tool with the kind of support you can count on, I’d highly recommend that you check out BI Documenter and download the free trial. It’s at the very least worth a look.

image

MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions 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 Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=CurrencySelect [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

SSAS Workshop in Phoenix, AZ 6/21 – 6/22

Capture

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 information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

Add 26 High Powered Tasks to your SSIS Toolbox

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 functionality to your SSIS packages. One of the most powerful transforms that is included with Task Factory is the Dimension Merge SCD, formerly known as the Kimball SCD transform.

Dim Merge SCD

The Dimension Merge SCD transform is 100 X faster at handling Type 1 and Type 2 slowly changing dimensions than the native Microsoft SCD component which uses the slow-as-mess OLE DB Command transform. Being able to handle very large dimensions up to several million rows makes the Dimension Merge SCD included with Task Factory one of the most powerful transforms included with Task Factory. The Dimension Merge SCD offers the power and flexibility of the Kimball SCD with the support of the expert staff at Pragmatic Works.

One of my personal favorite new transformations just recently added to Task Factory is the Regex Replace transform.

Regex Replace

Previous to the development of the Regex Replace transform, I would have to use a Script Component and write some code to use regular expressions. But with the Regex Replace transform, I can easily and quickly use reg ex to either replace reg ex matched data or extract the reg ex matched data to a new column or the existing column.

The newest version of Task Factory includes 26 SSIS tasks and transforms that definitely are worth taking a look at. And if you’re even a little bit familiar with Pragmatic Works, you know of our dedication to the SQL community. With that said, Pragmatic Works offers a completely free Community Edition version of Task Factory, which includes five tasks and transforms such as the File Properties Task and the Data Validation Transform. Go here to download the latest version of Task Factory.

Execute Child Package in 32 bit Mode From 64 bit Master Package

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. Continue reading Execute Child Package in 32 bit Mode From 64 bit Master Package

Using BCP Utility in SSIS

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 cases where using the BCP in SSIS may be the better choice over a Data Flow Task if no transformations are needed.

To use the BCP from SSIS we’re going to need 2 Execute Process Tasks (EPT) in our SSIS package: One Execute Process Task to pull the data from our table into a file and another one to pull the data from the file into our second table. And for the purposes of this little post, I’ve put it in a Sequence Container.

Capture

Then in our first EPT, we need to set up a couple different properties. The first is the Executable property. This tells our EPT which process to execute. In this case, it’s the Bulk Copy Program executable, bcp.exe, duh Winking smile. If you’re not running SQL Server 2008, obviously the BCP isn’t going to be located under the 100 folder.

Capture2

Secondly, we’ll need to specify our arguments. Now there are many different switches you can use, such as –e to generate an error file, –T to use integrated security when connecting to SQL Server, or –b to specify the rows per batch, but you can check out this article on MSDN to learn about all the different switches available. I would highly recommend reading through the article to learn about the different switches available. For our example, we’re going to use a few different switches to specify things like an error file (-e), using the native data types of the data (-N), and the server (-S).

Our first argument is the fully qualified database.schema.table which we would like to pull data from. In this case, it’s the table PaidVins2 in my CashForClunkersDW database. And since we are extracting data from a table, we will use out for the second argument of the BCP command. The third argument of the command will be the location of the BCP file.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp”

So far our BCP command should look like what you see above. But we still need to add a few switches before this will work. We at least need to specify the format of the BCP file using –N, which will create the BCP file with the data types native to our table. We could use –x to specify an xml formatted file or –c to specify character data types for all fields.

We also need to use the –T switch to let BCP know it’s a trusted connection. In place of –T we could use –U and –P to specify a username and password.

Next we’ll use the –S to specify the server and –e to create an output file for any errors that could occur.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins2errors.txt”

Enter your BCP command in the Arguments property of the Execute Process Task. If you execute the task, you should see the .bcp file created in the directory you specified. If you used the character (-c) format and specified the file as .txt (and the file is not too big!), you should be able to open it in a text editor.

The last thing we need to do is add an additional Execute Process Task to copy the .bcp file we just created into our destination table and connect the two with a precedence constraint. In my case, the table is PaidVins3. But instead of using the out switch, we’ll us in.

CashForClunkersDW.dbo.PaidVins3 in “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins3errors.txt”

And we’re done!

Capture3

Now you can BCP large amount of data quickly from SSIS.

The Tool Any Serious SSIS Developer Shouldn’t Be Without

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 on where I have BI xPress as a development tool go much much faster simply because of the additional weapons in my SSIS development arsenal. I’ve blogged this before, but I just wanted to take a couple paragraphs to talk about my all time favorite BI xPress feature, as well as the newest BI xPress feature.

My number one favorite feature of BI xPress is the Package Builder Wizard. In my opinion, this is probably the most powerful development tool in BI xPress. The reason the BI xPress Package Builder Wizard is so powerful is simply because it allows you to speed up your package development.

Capture

Using the Package Builder Wizard, a developer/development team can develop and manage package templates. So imagine you have a couple senior SSIS developers on your team and you also have a couple junior SSIS developers on your team. The senior SSIS developer can develop package templates with the more complex pieces of the package already developed and then pass those on to the junior guys. I’ve also seen the senior team members develop package templates with the correct naming conventions, necessary variables and connections, and annotations. These templates are then used as a starting point for the main development team, which saves the developers a great deal of time.

One of the great things about the Package Builder Wizard (its also my personal favorite feature) is the ability to alter existing packages with a prebuilt template. I once did some work for a client that needed to add a connection manager to an auditing database, event handler logic, and package configurations to 100 or so packages. Without BI xPress, this would have taken a long time. I don’t even want to imagine how long it would have taken. But without BI xPress, this only took me less than an hour (not counting testing 😉 ). I developed a template which included all the necessary variables, connection manager, event handler tasks, and package configurations. I then applied that single template to all of the packages in my project. It was literally that easy. Needless to say the client was pretty excited that it only took a few minutes to get through the work, at which point they then instructed me not to tell anyone I had completed the work that quickly :).

The newest feature of BI xPress is the MDX Calculation Builder. In short, the MDX Calculation Builder allows you to quickly build MDX calculations without having to script them out by hand. Do you need to script in YTD, QTD, MTD calculations? The BI xPress Calculation Builder can do it in 3 steps. Do you need to calculate the percent of a parent, compare a measure to a parallel period, or calculate an average? The calculation builder can do all that, too, all without manual scripting of MDX. It can even build dynamic sets for you.

Capture2

There are tons of other feature in BI xPress that I haven’t even mentioned that make BI xPress really just an insane tool no serious SSIS developer should be without. And since it uses all native SSIS components, the only place BI xPress needs to be installed is on the machine where development takes place. If you haven’t checked out BI xPress, I highly suggest you download the free trial and just give it a shot.

Dustin Ryan is a Data Platform Solutions Architect with Microsoft interested in Azure, SQL Server, and Power BI.

%d bloggers like this: