Thursday, March 27, 2008

Analysis Services 2000 vs. 2005: SOLVE_ORDER

Analysis Services 2000

With Analysis Services 2000 the solve order of members defined within a query and the solve order of members in the cube are evaluated at the same time. This means that if you have a calculated member in an MDX query that has a lower solve order than a member defined in the cube then the query member would be evaluated first. For example, consider the following query.

WITH
MEMBER Time.Difference as '[Time].[1997].[Q2]-[Time].[1997].[Q1]', SOLVE_ORDER=10
SELECT
{Measures.[Store Sales Ratio]} on 0,
{Time.Difference, [Time].[1997].[Q2], [Time].[1997].[Q1]} on 1

FROM
Sales

Where [Store Sales Ratio] is defined as '[Measures].[Store Sales Net]/[Measures].[Store Sales]' in the cube and given a SOLVE_ORDER of 100.


Store Sales Ratio

Difference

0.6

Q2

0.6

Q1

0.6


Looking at these results, you may expect Difference to evaluate to 0, since the Q2 value minus the Q2 difference should be subtracting the two ratios. Since the [Store Sales Ratio] solve order is higher, however, it is evaluated after the [Difference] calculation, i.e.

(( [Unit Sales], Q2 ) – ( [Unit Sales], Q1 ) )/ (( [Store Sales], Q2 ) – ( [Store Sales], Q1 ))

This is probably not what is desired, since you would expect a difference calculation to typically be evaluated on the final value of the measure, not on the components.

To get the desired value in AS2K, the only real option is to change either the cube or the query solve order. Unfortunately, if you’re using a client which automatically generates MDX changing the query may not be feasible. And there may be other forces which dictate particular solve order of cube members. On more than one occasion this has left us with no good solution.

Analysis Services 2005

With SSAS 2005 the notion of “scope” was added to solve order. The solve order of the cube is in its own scope, and is evaluated before any of the calculation in the query. So running the same query above in SSAS 2005 produces the expected result:


Store Sales Ratio

Difference

0

Q2

0.6

Q1

0.6


For the occasions where you actually want to have evaluation interact with the cube solve order, you can specify “SCOPE_ISOLATION=CUBE” within the query members definition.

See http://msdn2.microsoft.com/en-us/library/ms144787.aspx for more details about scope isolation and when you would use it.

Aggregate() changes

A related change is related to the Aggregate() function. In AS2K if the Aggregate() function had a higher solve order than what it was evaluating it would throw an error. It did this because there is no generic way of determining how to aggregate over a calculated member. Aggregation is really something that applies to base measures.

The fact that Aggregate members were required to have lower solve order produced headaches in my environment, because some client tools will specify a higher solve order for a member which uses Aggregate(). To counteract this we would set cube members to have solve orders higher than what we would ever expect the client to choose, which was a hacky and unreliable alternative (and also produced some of the unexpected values mentioned earlier).

In SSAS2005, on the other hand, Aggregate() has been enhanced to always apply to the base components of a expression, regardless of solve order. This is exactly the sort of behavior we would expect, and greatly reduces the constraints around solve order assignment within the cube.

Monday, March 24, 2008

Collection of Bash Tips

I ran across a selection of bash tips at http://www.shell-fu.org/lister.php?top. Many of them are very specific but here are a few that seem to come up daily:

Using expansion to move a file aside without having to type the file name twice

> cp ReallyLongFileNameYouDontWantToTypeTwice{,.orig}


Running a second command with the same arguments as the previous command, use '!*' to repeat all arguments or '!:2' to use the second argument. '!$' uses the final argument.
> cd /home/user/foo
cd: /home/user/foo: No such file or directory
> mkdir !*
mkdir /home/user/foo


Don't search history by grepping
~/.bash_history, or repeatedly hitting the up arrow, instead use CTRL+r (or '/' in vi-mode) for search-as-you type. You can immediately run the command by pressing Enter.

Make a whole directory tree with one command
Use the -p option to mkdir and make all parent directories along with their children in a single command.
>mkdir -p tmp/a/b/c


Use history to SUDO last command
Forget that your running as underprivileged? No need to retype the command.
> rpm blah
Permission denied
> sudo !!


Bash fork bomb
Don't forget the bash fork bomb. DO NOT TRY THIS AT HOME... Posted here so that you don't see this in a forum or a mailing list and use it without knowing:
>:(){ :|:& };:


Explanation:
:()
defines a function called : (accepts no arguments)
{ :|:& };
This is the function: It calls the function itself and pipes the output to the same function ":" and puts the process in the background. (Recursive invocation) with ; it ends the function definition
:
Calls the function and creates havoc.

Use ALT+. to insert last parameter
In bash (or anything using libreadline, such as mysql) press ALT+. to insert the last used parameter from the previous line.
Ex:
> vim some/file.c
> svn commit


Selected Bash Keystrokes:
Ctrl-U - Cuts everything to the left
Ctrl-W - Cuts the word to the left
Ctrl-Y - Pastes what's in the buffer
Ctrl-A - Go to beginning of line
Ctrl-E - Go to end of line