Archive for April, 2012

h1

BIxPress MDX Calculation Builder

April 30, 2012

This weekend at SQL Saturday 130 I gave presented a session on Introduction to SSAS, and after my session one of the attendees came up to me and asked how to create what amounted to a rolling 30 60 and 90 days calculation inside of SSAS.  Since this was an 100 level session, the attendee hadn’t ever seen an MDX expression so walking her through the calculation would have been a bit over her head. This is a common problem for most developers just getting into cube building with SSAS.  MDX is a difficult language to pick up.  Thankfully Pragmatic Works has some software that can help with that. 

When I’ve written about BIxPress in the past it has mostly been about the SSIS features it contains, but SSIS isn’t all it does. BIxPress has an MDX calculation builder that plugs in to the Calculation Tab inside of BIDS in you SSAS project, and its not that difficult to use, meaning you don’t have to write any MDX at all. Yes I am serious and don’t call me Shirley (thank you Leslie Nielsen)

As I said before, navigate to the calculations tab in you SSAS Project inside of BIDS. Once there, and if you have BIxPress installed of course, you will see a little green calculator button has been added to the tool bar.

image

When you click on the icon, a list of available calculations and sets are presented in a new window. These include calculations like Month to Date and Percent of Parent, as well as sets returning Top 10 Count or Top 10 Percent.

image

In this example I’m going to do the Profit Margin calculation, so I select it from the list and hit next.  The first thing I’m asked is to provide the measure that represents the total for Sales.

image

Once that is selected I need to provide the Total Cost.

image

(Notice I haven’t written any MDX yet) The next screen allows me to set any conditional formatting, like text color or Red for Profit Margin of <%15 or something to that effect, but I don’t need that for this example.

On the last screen we give the Calculation an Name and associate it with the correct measure group(Internet Sales Profit Margin, and Internet Sale respectively) and then click finish.  Once that is done, build and deploy the cube, and just like that you have a new calculation in your SSAS cube, and you didn’t have to write a single MDX expression.

image

image

Take a look at this and many other feature of BIxPress by Downloading a trial version here.  Also if you have any suggestions for additional calculations, or other features you’d like to see in BIxPress or any of the Pragmatic Works software products, post them here.

h1

BIxPress MDX Calculation Builder

April 30, 2012

This weekend at SQL Saturday 130 I gave presented a session on Introduction to SSAS, and after my session one of the attendees came up to me and asked how to create what amounted to a rolling 30 60 and 90 days calculation inside of SSAS.  Since this was an 100 level session, the attendee hadn’t ever seen an MDX expression so walking her through the calculation would have been a bit over her head. This is a common problem for most developers just getting into cube building with SSAS.  MDX is a difficult language to pick up.  Thankfully Pragmatic Works has some software that can help with that. 

When I’ve written about BIxPress in the past it has mostly been about the SSIS features it contains, but SSIS isn’t all it does. BIxPress has an MDX calculation builder that plugs in to the Calculation Tab inside of BIDS in you SSAS project, and its not that difficult to use, meaning you don’t have to write any MDX at all. Yes I am serious and don’t call me Shirley (thank you Leslie Nielsen)

As I said before, navigate to the calculations tab in you SSAS Project inside of BIDS. Once there, and if you have BIxPress installed of course, you will see a little green calculator button has been added to the tool bar.

image

When you click on the icon, a list of available calculations and sets are presented in a new window. These include calculations like Month to Date and Percent of Parent, as well as sets returning Top 10 Count or Top 10 Percent.

image

In this example I’m going to do the Profit Margin calculation, so I select it from the list and hit next.  The first thing I’m asked is to provide the measure that represents the total for Sales.

image

Once that is selected I need to provide the Total Cost.

image

(Notice I haven’t written any MDX yet) The next screen allows me to set any conditional formatting, like text color or Red for Profit Margin of <%15 or something to that effect, but I don’t need that for this example.

On the last screen we give the Calculation an Name and associate it with the correct measure group(Internet Sales Profit Margin, and Internet Sale respectively) and then click finish.  Once that is done, build and deploy the cube, and just like that you have a new calculation in your SSAS cube, and you didn’t have to write a single MDX expression.

image

image

Take a look at this and many other feature of BIxPress by Downloading a trial version here.  Also if you have any suggestions for additional calculations, or other features you’d like to see in BIxPress or any of the Pragmatic Works software products, post them here.

h1

Task Factory Advanced Execute Package Task

April 30, 2012

If you’ve ever developed any kind of SSIS auditing framework, chances are you’ve wanted to pass variables from the child package to the parent package.  As you know it can be accomplished, but it takes some hoop jumping including writing some code in a script task.  As I’ve said before, scripting is not my strong suit, so if there is a way to work around it, I’ll give it a try.

Thankfully Pragmatic Works has made the work around unnecessary with the new component in the ever growing list of SSIS components.  The Advanced Execute Package Task component allows you to easily configure the passing of variables between packages in both directions.

image

The component is easy to configure.  Once you’ve selected the location of the package you want to execute, either SQL Server of File System, the next step is to assign the direction of the variable mapping.  There are three options, read from child package, write to child package, read and write variable from child package.

image

Once that is selected (in this case I’m going to read the variable from the child package) the next step is to select the variable from the child package you want to read.  The nice thing that the Advance Execute Package Task does, is that it only allows you to map variables of the same type. This means that if you choose a string variable in the child package you can only choose string variables in the parent package.

image

Once the variable mapping is set the way you want it, click the add Mapping button.  This adds the mapping to the package and you can now configure another variable.

image

With this component it is very easy to pass variables back and forth between packages.  Don’t take my word for it, download Task Factory for yourself and try it out.