Addition to BIDS: BIDS Helper

The Business Intelligence Development Studio (or BIDS) is typically used for developing Microsoft BI software. Now there is room for improvement everywhere and the BIDS Helper offers this improvements.

BIDS Helper is a Visual Studio add-in which will add a number of additional features and options to Visual Studio. Most additions are in the Analysis Services and Integration Services projects and range from comfortable to extremely useful. A lot of these additions are really things that make you wonder why Microsoft didn't include it themselves...

A few examples:
- Integration Services packages can sort by name within a project
- Variables in packages for Scope another move / copy
- Within Analysis Services option to create your attribute relations graphically
- A Smart Diff option packages or differences between two cubes to detect

And there are many more.

The add-in comes from an open source project on Codeplex (from Microsoft) and is free to use. It is available for Visual Studio 2005 as well as 2008, so be sure to choose the right one.

Download BIDS Helper, install and try it yourself!

ShellRunas – Launch Programs with Different Account Credentials

ShellRunas is free utility that integrates into the Windows context menu to launch programs as a different user. It provides similar functionality to that of the command line based RunAs utility. The command-line Runas utility is handy for launching programs under different accounts, but it’s not convenient if you’re a heavy Explorer user. ShellRunas provides functionality similar to that of Runas to launch programs as a different user via a convenient shell context-menu entry.

Screenshot

At BI Architects we are using ShellRunas a lot for logging into SSMS (SQL Server Management Studio) with different credentials. If you want more information about ShellRunas or if you want to download the utility, click here

SSIS Derived Column IF(IIF)

I use this post as a short reminder for myself, because the Derived Column Transformation Editor in SSIS simply does not spell out how to accomplish this.

The following syntax is used in the Derived Column control to place an IF(IIF) statement :
(Boolean Expression ? True Part : False Part)

So, if I were checking against a Trade Type code column (trade_type_cd) to determine whether this column IS NULL or IS NOT NULL, I could simply write up something like this:
ISNULL(trade_type_cd) ? "<Unknown>" : trade_type_cd

Addition to BIDS: Table Difference component

One question arising very often in the Data Warehouse programming is "What are the differences between two tables?" You receive several million customers and should decide what changes need to be done. The standard SSIS toolkit provides SCD component for handling this process, but it is of no practical use when you have to handle more than a couple of thousand records. The Table Difference component doesn't have this limitation.

The component has two inputs: one for "old" and one for "new data". The old and new rows are compared and the result is sent to different outputs:

• Unchanged rows (same in both old and new)
• Deleted rows (appear in old but not in new)
• New rows (appear in new but not in old)
• Updated rows (appear in both old and new, but there is a change)

The diagram below illustrates the component functionality better than thousand words:

Occasionally you may have a requirement to perform different updates, for different columns. The component includes option to tag each input column with identifier. Then for each unique identifier, a separate update output is created and maintained. In this way you can tag with "10" the historical attributes and with "20" the other columns and decide what kind of operation to carry on with the different updates.

The component inputs MUST be sorted and have a collection of fields (keys) that let the component decide when two rows from the inputs represent the same row. This is easily accomplished in SQL with a simple “order by” and a convenient index. Usually slowly changing dimensions maintain an index by the business key, so the sorting requirement is easily accomplished and do not represent a problem.

Click here for more information about the Table Difference component.

Business Intelligence extension for Clarizen - Presentation

Two weeks ago i posted about the Clarizen's online project management software. In the presentation below our Business Intelligence extension for Clarizen is described.

For more information about the extension for Clarizen please contact our sales manager Fred Booms (f.booms@bi-architects.nl)


Hermann-Otto Israël
BI Architects project manager