Category Archives: Pragmatic Works

Free Training, PowerPivot, Pragmatic Works, SharePoint, SSAS

PowerPivot 101 Recording Now Available

1 Comment

Thanks to everyone who attended my PowerPivot 101 webinar last Tuesday morning! It was a blast and I had a great time presenting for the 300 strong crowd that attended!

In case you missed the webinar, you can easily view the recording here free of charge! Also, don’t forget to check out the dozens of other free webinar recordings. With this kind of free training available, there really is no excuse for you to not be learning something new!

If you’re looking for a great book to get you started with PowerPivot and DAX, I would suggest checking out the following books:

image

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

 

 

 

image

Practical PowerPivot & DAX Formulas for Excel 2010
Art Tennick

 

 

 

Also, check back for my PowerPivot Q&A responses in a later blog post! Thanks again to everyone who attended my session!

Pragmatic Works, TSQL

Comparing and Syncing Data With Data Inspector

image

Earlier this month Pragmatic Works released Workbench. Workbench combines the powerful tools many of you are already using with an entire host of new, powerful features part of DBA xPress.

DBA xPress features tools designed to make designing, maintaining, and administering your SQL Server databases easier than before. Now you can easily do things like visually navigate your databases, visualize schema dependencies, compare, synchronize, script, and navigate schemas, and more.

image

One of my personal favorite tools included with DBA xPress is the Data Inspector. Data Inspector allows you to create a comparison between the data stored in two different databases. Not only can you compare the data between databases, but you can also copy the data from one database to another.

With Data Inspector, there are three ways to sync data between databases. Data can either be synced directly between databases with the Data Inspector Synchronization Wizard,

a script can be generated to synchronize the data for you, or a hybrid of the first two options will directly sync the data and generate the script for you.

I’m very excited about this tool and am looking forward to using it to speed up my data warehouse development projects. Head over to PragmaticWorks.com and check out DBA xPress.

MDX, Pragmatic Works, SSAS

Implementing Security With SSAS

5 Comments

Pragmatic Works just published a video on their YouTube channel put together by yours truly on implementing security in your SQL Server Analysis Services cube.

The video covers implementing basic dimensional security, cell security, as well as an extended look at implementing dynamic data driven security. The video is about an hour long so grab a bag of popcorn, sit back, and hopefully learn how to make your cube as secure as if it were guarded by a squad of Segway riding special forces commandos.

So check out my video Implementing Security With SSAS and feel free to post any questions or comments!

Pragmatic Works, SSIS

Generating Junk/Test Data For SSIS

Everyone once in a while I’ll run across a requirement for me to generate a bunch of junk data for the sole purpose of performance testing or benchmarking. There are plenty of ways that I could generate my test data, including clever TSQL scripts or a Data Flow Script Component as a source. And those methods work well, but are somewhat limited if I need to generate unicode string data for testing. But that’s where Task Factory comes in.

imageIncluded in the latest release of Task Factory is the Placeholder Source. The beauty of the Place Holder Source is that I can quickly and easily generate test data for my package.

Here you can see the editor for the Placeholder Source. On the Component Properties tab, I can specify how many rows of junk data I’d like to generate. The default is 10,000 rows, but I can set it to whatever I like. I’ve tested the source component with 100 million records before with no problem.

image

On the Input and Output Properties tab is where you specify which columns and data types you’d like to include in your junk data.

image

By default, the Placeholder Source creates 5 output columns of various data type, but you can add more fields of whatever data type you like. Just click Add Column and then select the data type you want. Here I’ve added an additional column and selected Integer as the data type.

All that’s left to do is wire up my package and click execute. Instantly tons of junk data is created for us to test or play with. If you’re interested in expanding your SSIS arsenal, head over to PragmaticWorks.com and download Task Factory. The best part is that Task Factory is free to download. A license is only required to run the Task Factory components remotely. So why not check it out?

Pragmatic Works, SSIS

SSIS Advanced Execute Package Task with Task Factory

In the last few months, I’ve been giving a preview on some of the new advanced SSIS tasks and transforms being released in the coming version of Task Factory. Well Task Factory 3.0 has finally been released last month, and its packed with a ton of really cool tasks and transforms that are going to make SSIS development easier and will increase the performance of your packages.

image

One of the newest SSIS tasks released in Task Factory 3.0 is the Advanced Execute Package task. The really cool part about this task is the MUCH improved interface. It’s now incredibly simple to set up variable mappings from a parent package to a child package.

Here you can see the editor for the Advanced Execute Package Task. There are three areas of the editor I want to take a look at.

image

The first area, highlighted in green, is where you specify your connection to the child package. If the package is in the file system, you simply browse to the location of the package. If the package is located on a SQL Server, you need to select the SSIS connection manager to use. In either case, if the package has a password, you’ll need to enter that.

The second area of the Advanced Execute Package Task, highlighted in yellow, is where you create mappings between variables in the parent package and variables in the child package. In the Assignment Direction drop down list, you have a few different options.

image

We can read a variable from the child package, write a variable to the child package, or do both simultaneously. In my case, I’m passing a variable to the child package so I will select “Write variable to child package”. Then I just need to select the Child Package Variable and the Parent Package Variable.

image

Once I’ve done that, I can just click “Add Mapping”. Now we can see the mapping created between the two package variables in the last area (highlighted in pink in figure 1). Using this method, we can create multiple variable mappings between the parent package and the child package.

image

As you can see the Advanced Execute Package Tasks from Task Factory is very intuitive and simple. If you’ve used the native SSIS Execute Package Task, you’ll recognize that the new Advanced Execute Package Tasks is much simpler and easier to configure.

Pragmatic Works, SSIS

Sneak Peek: Advanced Conditional Split Transform in Task Factory 3.0

IMG_8325

You may have seen my previous blog post a couple weeks back highlighting some of the neat things the dev team of Pragmatic Works is including in the latest build of Task Factory. Well I was doing some more sneaking around and I was able to snag these screen shots of the new and still experimental Advanced Conditional Split Transform!

I think one of the biggest and coolest improvements is the new functions made available for us in Advanced Conditional Split Transform. IMG_8328You’ll notice the editor for this transform is very similar to the Advanced Derived Column Transform we looked at last time. Now you have access to a ton more functions and expression that allow you to do some very cool things, like data cleansing with Regex, encrypting/decrypting fields, and access parts of file names with an expression. The SSIS expression language has been vastly improved and expanded in these transforms and is now more intuitive and easier to use than ever before.

IMG_8327One of the other things that is new with the Advanced Conditional Split transform included in Task Factory 3.0 is the ability to test and validate expressions within the editor for the Advanced Conditional Split transform. Not only can you validate your expressions in the editor before run time, you can actually test the performance of the expression you have written by specifying a number of iterations to test the expression with. This should give you a good idea of how the expression will perform during run time. This is a feature we haven’t had before. Now those of us who are SSIS developers will be able to test, fine tune, and retest our SSIS expressions!

This new generation of SSIS tasks and transforms are going to change the way I build my SSIS packages due to the new flexibility and power. Stay tuned for future posts on some of the incredible improvements and additions that are going to be available in the next version of Task Factory by Pragmatic Works.

Pragmatic Works, SSIS

Sneak Peek: Super Advanced Derived Column Transform In Next Release of Task Factory

2 Comments

Don’t ask how I was able to acquire these screenshots or what favors I may or may not have performed in order to snag these pics of the next release of Task Factory. I’m not proud of what I’ve become. But the fact of the matter is that Task Factory 3.0 is going to be insane!

Task Factory 3.0 has about 10 or so new tasks and transforms that radically expand the power of SSIS. One of the new Data Flow transforms included in Task Factory 3.0 is the Advanced Derived Column Transform. The Advanced Derived Column Transform includes a ridiculous amount of new expressions and functions. Sources tell me to expect around 180 or so total functions in the Advanced Derived Column Transform.

I snagged these screenshots which show some of the new functions. You can see what appears to be a host of Regular Expression functions available for use in the transform! Yes! It’s about time!

IMG_8082

Also, it appears there a bunch of pretty interesting functions having to do with parsing file paths and names, such as GetDirectoryName, GetFileName, and GetFullPath, among others.

IMG_8083

One of the coolest features of the Advanced Derived Column Transform that I’m really excited about is the Expression Validation pane. Not only can you validate your expression and preview the expression results, you can also test your expression for performance!

IMG_8084

This new version of Task Factory is going to be sick. Keep on eye on PragmaticWorks.com for news of the release, which I’m told should be sometime in the next month. I’ll keep you posted so keep checking back here for more information.

Pragmatic Works, SSRS

SSRS Performance Monitoring With BI xPress 3.5

1 Comment

Earlier this month the latest version of BI xPress was released. There are several new features in this latest release of BI xPress, but one of the coolest is the SSRS Performance Monitoring.

The BI xPress SSRS Performance Monitor has four performance monitoring panels.

  • Average Run Time

    The Average Run Time panel displays the average run times of your report. This is a great place to figure out which reports are taking the longest to execute. This is the place to start if you’re trying to figure out why a report is taking a long time to run. There are three different very helpful metrics displayed in the panel:

    • Avg. Data Retrieval Time: This number shows how long it took to retrieve the data from the source system. If the Avg. Data Retrieval time is the majority of the report run time, take a look at the source query and find ways to optimize it.
    • Avg. Processing Time: This is the time the SSRS server takes to calculate and process the retrieved source data. Any formulas or expressions in the report are calculated during this time.
    • Avg. Rendering Time: This is the amount of time it takes to paint the graphics, charts, text, etc.

  • Longest Running Reports on Average


    This panel provides you a chart that can be read quickly and easily to allow you to see if a report’s execution is taking too long, which could negatively effect the performance of the entire SSRS server.

  • Frequent Reports


    The Frequent Reports panel displays the reports that are most frequently run. If your reports are being executed more than expected, this could lead to scalability issues. Tracking the number of executions is a good way for you to begin to determine if its time to scale up or out.

  • Active Users

    The Active Users panel displays report executions by users.

This newest feature is one I’ve been waiting for a long time since I first saw it during the development phase. To read more about BI xPress and the numerous other features included, head over to PragmaticWorks.com and check it out.

Pragmatic Works, SQL Server, SSAS, SSIS, SSRS

BI Documenter 4.2 Released

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.

Pragmatic Works, SSIS

BI xPress Version 3.2.1 Released

Earlier this month on the 14th, version 3.2.1 of BI xPress was released. With this release comes a few nice improvements that increase the ease of use.

One of the major improvements with this release (and to be honest its about time) is the ability to view packages that have been stopped by some external source (i.e. the user, the operating system, etc.). The stopped packages will now appear highlighted in blue in the Package Execution viewer. Previously it was impossible to tell which packages had been stopped by the end user, but with this improvement it’s not possible to tell which package has been manually stopped by the end user.

Packages that have not started execution are white, packages currently executing appear in yellow, packages that have successfully executed appear in green, packages that have failed are red, and packages that have been stopped are now blue.

One of the other nice improvements is the ability to detect invalid file paths for configuration files. If the file path for a configuration file cannot be found, the configuration will appear underlined and highlighted in red in the Configuration Manager.

There’s a host of other improvements and bug fixes in this release. To read the full release notes, go here. To download the trial version of BI xPress, head over to PragmaticWorks.com.