Conditional MDX ?

I've got a solution where I need to apply some adjustment to a measure for a given scope:

Scope ([Organization].[Organization].&[13], [Product].[Division Temperature].&[Frozen], [Measures].[FOB]); This = [Measures].[FOB] - 1.25; End Scope;

This works fine, but given that this solution uses role based security to limit access to specific dimension data, there are some roles that don't have access to the [Organization].[Organization].&[13] and the MDX fails parsing.

Is there any way to make a conditional MDX that I can use to only apply the given MDX SCOPE when the role has access to the dimensions used in the statement ?

Trond

I guess it is safe to ignore the entire SCOPE for the users who have no access to Organization 13. One quick way to do it, is to change the ScriptErrorHandlingMode to IgnoreAll (although this will start ignoring all other errors). More details here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/05/02/13315.aspx

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Note that ScriptErrorHandling has some limitations (in RTM and SP1) whereby certain kinds of script errors are not ignored. This will be fixed in SP2.

|||

Thank you for the input - ScriptErrorHandling did indeed work but I ended up with a different solution in the end.

I guess I was so into using SCOPE that I didn't see any other tool/way to do it. Once I took a new look at it, I ended up with not using SCOPE at all, but rather a CASE:

Case When [Organization].[Organization].CurrentMember.Name = "Norway" And [Product].[Division Temperature].CurrentMember.Name = "Frozen" Then ([Measures].[Net Sales] / [Measures].[Volume]) - 1.25 Else ([Measures].[Net Sales] / [Measures].[Volume]) End

Memo to self: Read up on MDX before the next SQL 2005 project !

|||

Another approach that appears to work (at least in Adventure Works) is to first test the member using IsError(), like:

Create Set CurrentCube.[OrgSet] As iif(IsError(StrToMember("[Organization].[Organization].&[13]")),
{}, {[Organization].[Organization].&[13]}); IF [OrgSet].Count > 0
THEN ([Measures].[FOB], [Product].[Division Temperature].&[Frozen],
[OrgSet]) = [Measures].[FOB] - 1.25 END IF; |||

Please note, however, that this solution is far less efficient then the one using SCOPE. Likewise, Deepak's suggestion of using IF/END IF is also less efficient.

Probably you would get the best performance by combining Deepak's idea of building named set with SCOPEs, i.e.

CREATE SET orgs = ...;

SCOPE (orgs);

...;

END SCOPE;

|||But I couldn't figure out how to build the set, such that the assignment wouldn't update any cells when the targeted member is not accessible to the role/user. The empty set that I substituted for the secured dimension seemed not to narrow the scope, so all accessible members got updated...|||Deepak, the empty set in the SCOPE translates to the empty subcube, and therefore no cells are affected by the assignmets inside this SCOPE. Therefore, since for the user with dimension security the named set will end up empty - it will behave as that portion of MDX Script didn't exist.|||

Mosha, thanks for clarifying that - looks like my version didn't work because I also included the measure in the scope, like:

>>

Scope({[Measures].[FOB]}, [OrgSet]);

This = [Measures].[FOB] - 1.25; End Scope; >> Instead, if only the Org set is specified in the scope, then the empty set defines an empty subcube. So '({[Measures].[FOB]}, {})' isn't empty, but '({})' is:
>> Scope([OrgSet]); ([Measures].[FOB]) = [Measures].[FOB] - 1.25; End Scope; /* OR */

Scope([Measures].[Order Quantity]);

Scope([OrgSet]);

this = [Measures].[Order Quantity] * 2;

End Scope;

End Scope;

>>

Concurrency puzzle (ODBC API related)

I hope this is not outside the scope of this newsgroup.

have been useing BulkAdd with ms-access with good success.
Until now that I;m switching to MSDE which is on a shared server.

The problem is that MSDE gives an error:
Invalid attribute/option identifier, sql state=HY092
which corresponds to:
SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.

but my BulkAdd routine explicitly has a call to:
SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)

I cannot imagine how the concurrency is readonly.
Could it be that I'm looking at the wrong place or the wrong attribute?

Thanks in adv.Ernesto (tsh@.mathematicuslabs.com) writes:
> have been useing BulkAdd with ms-access with good success.
> Until now that I;m switching to MSDE which is on a shared server.
> The problem is that MSDE gives an error:
> Invalid attribute/option identifier, sql state=HY092
> which corresponds to:
> SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.
> but my BulkAdd routine explicitly has a call to:
> SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)
> I cannot imagine how the concurrency is readonly.
> Could it be that I'm looking at the wrong place or the wrong attribute?

Without seeing your code, it is not possible to give a definitive answer.
(And it doesn't help that I have little experience of ODBC API programming.)

But checking the docs, my conclusion is that the SELECT statement for your
cursor is such that the cursor is not updatable. This could be due to
several causes: lack of primary key, use of expressions etc. Would need to
see the SQL code together with some information on the table to say more.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland ,
You are good!
I changed the query which created the table to create a primary key and it
SEEMS to solve the problem.
(It's a good thing having a primary key is ok in this case, else I would not
know how to "solve" this.)

I'm not completely out of the woods yet, but I think now there is another
unrelated problem.
Hopefully you'll keep reading if I am fooled and the problem is still there.

Now if you can only tell me how or where in the world can I learn these
things about the odbc api.

Not having found any good resource which explains these things, the way I
operate is by prayer only and geenrous people like yourself.

E|||Ernesto (tsh@.mathematicuslabs.com) writes:
> Now if you can only tell me how or where in the world can I learn these
> things about the odbc api.

The place to read about the ODBC API in general is the MDAC Books Online,
which comes with the MDAC SDK. Then there is a section in the SQL Server
Books Online which covers specifics for the SQL Server ODBC Driver.

For this particular issue, though, it's more of a general understanding
of databases in general, and in this particular case about cursors. I
know that Kalen Deleany has a chapter on cursors in her "Inside SQL
Server 2000". I doubt that she says much about ODBC, but the same issues
apply when you use cursors in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for the sources. I will look them up.
One more dumb question in case you can respond:

How would I do this if I did not want to add a primary key to the table?|||On Wed, 7 Jul 2004 14:20:02 -0400, Ernesto wrote:

> Thank you for the sources. I will look them up.
> One more dumb question in case you can respond:
> How would I do this if I did not want to add a primary key to the table?

You could add it to a temporary table that did have a primary key, then
execute an INSERT INTO command to transfer it to the permanent table.

However, a primary key is almost always a good idea. Some will argue that
without a primary key it isn't a table at all.|||>> How would I do this if I did not want to add a primary key to the
table? <<

Without a key, this is not a table; it is a punch card file with a
little class. You would return to what we used to call "EDP"
(Electronic Data Processing) in the late 1950's and throw away all 30
years of progress.

Well, you asked and that is the answer.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!