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.
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.
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.
Once that is selected I need to provide the Total Cost.
(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.
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.