Category Archives: Pragmatic Works

Pragmatic Works, SSIS

Comparing SSIS Packages with BI Compare

Comparing SSIS packages isn’t always easy, as I’m sure you’ve probably discovered if you’ve ever tried to compare one package to another. Of course SSIS packages are simply XML files underneath the shiny GUI, but scrolling through pages of XML in order to discover the slightest differences between two versions of a package is not how I prefer to spend my afternoons. Thankfully, there’s an easier solution.

Pragmatic Works has developed a tool called BI Compare that allows you to easily compare two SSIS packages and easily view the properties of each package side by side with easy to spot indicators making it very easy to spot differences in the packages. BI Compare is a tool included with Pragmatic Works fabulous BI development and administration tool BI xPress.

With BI Compare we can set up a new comparison scenario, identifying the packages we wish to display on the left and the right.

Once we have executed the comparison between the two package, the package comparison displays a image for component and properties in the two packages that match, a image next to components that do not match, and a image next to components that are missing in the other package.

So its very easy to skim through the comparison results and view the differences between the two packages by simply looking for the X icons or <- icons. You also have the ability to filter which types of objects you wish to compare. For instance, if you don’t need to compare the connection strings simply because you’re comparing a package in development against a package in production, you can exclude the Connection String property from the comparison.

As an SSIS developer and BI Consultant, I’ve found this tool very useful during SSIS package upgrades and migrations. To get more information on BI Compare, head over to Pragmatic Works and download the free trial version of BI xPress.

Pragmatic Works, SSIS

Unit Testing Your SSIS Packages

One of the challenges SSIS developers often face is being able to meaningfully test our SSIS packages or components within the packages. Pragmatic Works has released a new component to BI xPress that allows you to easily create a suite of tests to perform on an SSIS package.

Unit Tests can be performed on entire packages or individual components in a package. And those Unit Tests are grouped into Test Suites, so think of a Test Suite as a series of unit tests designed to test the functionality  of a set of components in a package or a series of packages.

Set up steps can be configured to make retesting very easy. If a file needs to be created or a T-SQL command executed before a test can be performed, those steps can be configured within the BI xPress unit testing interface. And these set up steps can be performed once for the Test Suite or once for each Unit Test within the Test Suite.

Once the packages have been added to the Test Suite, creating the Unit Test is as simple as selecting the package or task within a package to execute. Then specify the expected results.

Then its time to create the Unit Test Assertions, which are the steps to check to execution of the packages or package components. An Assertion could be anything from checking a table for a certain number of rows, checking for the existence of a file, or checking a property within the package, among many others.

The entire Test Suite can be executed or an individual Unit Test can be fire. The test results are displayed in an easy to read format.

Take a look at SSIS Unit Tests with BI xPress if you’re looking for a way to streamline testing of your SSIS packages. I’ve personally used this tool during client engagements and its a great way to standardize testing of your SSIS packages.

Pragmatic Works, SSIS

Are You Using SSIS Best Practices?

image

Earlier this month, Pragmatic Works released a new tool for their Pragmatic Works Workbench toolbox: The Best Practice Analyzer. The guys and gals over at Pragmatic Works have come up with a tool that analyzes your existing SSIS packages and compares them with a standardized set of best practice guidelines produced by the expert team of consultants and developers you’ve come to know and love over the past several years.

The Best Practice Analyzer combs through the SSIS packages you have selected and produces a report outlining all the best practice violations including violations of varying severities, such as “Warning”, “Error”, “Performance”, and “Informational”.

First, select the packages you’d like to investigate for adherence to best practices.

Next, select the best practices to be included in the report.

The end result is an easy-to-read report identifying any violations of best practices.

Violations could include everything between leaving a component description blank, using a fully block transform in a Data Flow Task, not enabling error logging, plus many more. Head over to PragmaticWorks.com to get more information on the SSIS Best Practice Analyzer.

Pragmatic Works, SSIS

Migrating Away From Informatica To SSIS? You’ll Need Workflow Profiler

image

If you’ve been paying attention to this blog or PragmaticWorks.com, you probably picked up on the fact that Pragmatic Works has put together a new service offering for those companies currently running Informatica as their ETL tool of choice and are sick and tired of paying hundreds of thousands of dollars a year in licensing (and in some cases millions of dollars! o_O!!). Pragmatic Works is now offering to quickly and efficiently migrate their clients from Informatica to SQL Server. One of the ways Pragmatic Works makes this process less painful is by using their tool, Workflow Profiler.

The primary goal of Workflow Profiler is to fully document your Informatica PowerCenter MetaData environment very quickly, thus giving you a better idea of just how much effort could be required during the migration process. This information is displayed in a series of detailed reports viewable in Workflow Profiler.

Reports, such as the Widget Distribution Summary Report, give you a detailed analysis of your Widget distribution.

Reports like the Workflow Detail Report, Mapping Summary Report, Mapping Detail Report, and many others display all types of information regarding your Workflows, including widget usage, what tasks are used in the workflows, which workflows use parameter files and more.

There is also the Workflow Execution Summary Report. This report displays all kind of performance data and metrics including execution status, performance numbers for each workflow and even individual tasks, and success/failed row counts for sources and targets for each session task.

If you’re looking for more insight into your Informatica environment and would like to know more about what kind of performance you’re getting out of your Workflows, I recommend checking out Workflow Profiler. Download the free trial here.

Pragmatic Works, SSIS

Pragmatic Works Now Offering New Service Migrating From Informatica To SSIS

Today for the first time, Brian Knight, founder of Pragmatic Works, announced that Pragmatic Works will be offering a new service aimed at migrating their clients from Informatica to SSIS!

This is a very exciting offering for Pragmatic Works and especially those companies running Informatica and looking to break away from the expensive licensing model. Check out this video with Pragmatic Works experts Brian Knight and Devin Knight discuss some of the differences between Informatica and SSIS. Stay until the end of the clip to catch the announcement of the new service!

For more information on migrating from Informatica to SSIS, head to http://www.PragmaticWorks.com or call (904) 638-5743.

Pragmatic Works, SQL Server

Browsing SQL Server Databases With DBA xPress

image

With the latest release of the Pragmatic Workbench, we are exposed to the Object Browser. The Object Browser is a very cool feature that is party of the DBA xPress package of tools designed with the database administrator in mind. This is the first time I’m exploring this tool, so I wanted to walk through the feature with you and give my take on it (even though I’m not a DBA, but nonetheless…).

When you first launch Object Browser, you must first point to the server where your database exists. So far so good.

image

After connecting, we’re immediately treated to a nifty tree view of our database and the objects within that database. In this case, of course, I’ve connected to the Adventure Works DW 2008 R2 database.

image

The browser is extremely responsive and very quick. Drilling down and exploring the database is slick and fast. Once you’ve connected to the database, you can browse it and search for a single object using the search function. All I had to do is type in my search term and the tree view filtered faster than Google.

image

One of the really cool features is that you can see the SQL script to create the object you’ve selected. whether it’s a table, a view, a column in a table, a procedure, or a role. Very handy! We can also hover over the objects and view the meta data for that object.

image

Another neat feature is the ability to take a snapshot of the database. Click the Take Snapshot button at the top of the Object Browser. A nice, clean XML file is the output and can be saved to the location of your choosing.

image

To learn more about DBA xPress and download the free trial, head here.

Pragmatic Works, SSIS

Import Native SSIS Performance Data With BI xPress

imageOne of the most powerful features of BI xPress is the Auditing Framework, which allows you to apply a standardized and robust auditing framework to multiple packages in just a few clicks. I’ve blogged about the BI xPress Auditing Framework before because its an extremely impressive tool that has saved me and the teams I’ve worked with countless hours. But now that SQL Server 2012 has been released, we have a whole slew of execution and performance data available to us natively within the 2012 SSIS Catalog. Wouldn’t it be great if we could view the native execution and performance data within the BI xPress Monitoring Console even if the BI xPress Auditing Framework has not been applied?

Your prayers have been answered. If you’re running SSIS 2012, you can now easily import the native execution and performance data into the BI xPress database for even more in depth reporting on the execution of your SSIS packages. Of course, this feature only works if you’re running 2012 :).

To import the SSIS Catalog data into the BI xPress database, click the SSIS Catalog Import button located on the Pragmatic Workbench home screen.

image

Next specify the location of the BI xPress database that is your target for the import. Then add the SSIS Catalog as the source for the import.

image

imageAnd the really nice part is that you can turn on Auto Importing so that the native performance and execution data will be automatically imported into the BI xPress database!

Now that the data has been imported into the BI xPress database, any native SSIS 2012 package stored in the SSIS Catalog can now monitored and measured in the BI xPress Monitoring Console. Head over to PragmaticWorks.com for more information and a trial download.

Pragmatic Works, SSIS

Migrating From Informatica To SSIS?

image

If you’re even considering migrating your Informatica Workflows to SSIS packages, you’re going to need a clear understanding of the level of difficulty you’re going to be up against. That’s where Workflow Profiler comes in.

The Workflow Profiler allows you to gather a complete list of of repository objects at the most granular level with the goal of determining their reusability. The Workflow Profilers also identifies connections utilized within the Workflows. Once the workflows have been documented, detailed reports are generated detailing the objects contained in the Informatica workflows.

First, identify the repository connection.

Then select the Workflows you want to document.

Next, you can select the type of report you’d like to generate. You can also specify if you would like to filter out certain types of objects.

And you’re report is ready for viewing and exporting to Excel, Word, or PDF formats.

Head over to PragmaticWorks.com and download the free trial of Workflow Profiler. If you’re considering migrating your ETL to SSIS, this is a tool you don’t want to miss out on.

Pragmatic Works, SSRS

BI xPress Report Deployment Tool

imageIf you’ve followed my blog for a while, you’ve probably seen me blog about BI xPress because its such an awesome tool. I use BI xPress on every gig I go on, even if the client has not purchased a license. There’s been several great enhancements since the earlier versions of BI xPress, but one of the more useful is the new Report Deployment Tool.

The practical need the Report Deployment Tool meets for the SSRS developer or administrator is the need to easily and quickly deploy SSRS reports from one environment to the next. With its drag and drop interface, I can move SSRS reports from the QA environment up to Production (or any other environment within my domain) simply by dragging and dropping the report.

After connecting to both SSRS servers, I can easily compare which reports exist in one environment versus the next. I can also bi-directionally move reports that are ready for deployment into Production.

I would highly recommend this tool for anyone who finds themselves facing challenging SSRS report deployment scenarios or for anyone who just wants to very easily move reports across servers in a split second. To learn more about BI xPress and the Report Deployment Tool, head over here.

Excel, Free Training, PowerPivot, Pragmatic Works

PowerPivot 101 Training Webinar with Q&A

2 Comments

To watch the free recording of my webinar called PowerPivot 101, just head over to PragmaticWorks.com, create a free login and you’re all set! Please let me know if you have any questions.

PowerPivot 101 free webinar training

Once again, thanks to everyone that attended my session on PowerPivot. Here are my responses to some of the questions I received during my session but did not have time to answer until now.

1. Does the current version of PP support drill through?

The current version of PowerPivot does support drill through. All you have to do is right-click a measure in your spreadsheet and select Show More Details or alternatively you can double-click the measure.

image

2. Is PowerPivot add-in required for SharePoint?

PowerPivot for SharePoint is required if you wish you share Excel workbooks that contain PowerPivot data via SharePoint. For more information on installing and setting up PowerPivot for SharePoint, check this out.

3. Where will a replay of the PowerPivot 101 webinar be available?

Head over to the Pragmatic Works Learning Center to view the recording of this webinar as well as the recordings of all the previous webinars!

4. Book/website recommendations?

Definitely check out PowerPivotPro.com run by Rob Collie. It’s a great website with tons of cool PowerPivot-y stuff, plus Rob is a cool dude.

Check out these books!

image4

PowerPivot for Excel 2010: Give Your Data Meaning
Marco Russo & Alberto Ferrari

image10

Practical PowerPivot & DAX Formulas for Excel 2010
Art Tennick

5. What is the name of the component/function in SharePoint that would make the “live” data connections possible in published PowerPivot?

I assume the question is directed at utilizing a live connection to a data source, such as SQL Server. To my knowledge, this is not natively supported with PowerPivot. The data in your PowerPivot workbook must be refreshed.

There are two ways to refresh data in a PowerPivot workbook. You can refresh it manually anytime you want or you can schedule a refresh through SharePoint like I showed during the webinar. For more information on refreshing data in a PowerPivot workbook, check out MSDN.

6. I thought DAX was only supported using the Tabular mode of SSAS. My understanding is that PowerPivot is built on the Multidimensional model. I wouldn’t think DAX would be supported. Can you comment?

DAX (Data Analysis Expression language) is the formula language for PowerPivot and Tabular modeling and is used to specify calculations and to create new columns. It’s basically an extension of the Excel formula language. For more information related to DAX in regards to PowerPivot and Tabular modeling, read this and this.

7. Can you blog about having a calculated field that I can put on a slicer to filter by number of records (i.e. products with more than 100 records).

To filter records where a measure is greater than or less than a certain amount is pretty easy. In your workbook, just click the drop down arrow above the attribute you wish to filter, go to Value Filters, and select Greater Than, Less Than, or whichever option you desire.

image

Then you can select which measure you want to use and what quantity to use.

image

Thanks for all the great questions everyone. Don’t forget to check the recording of the webinar in case you missed anything!