Is there any advantage to execute mdx queries from T-SQL for MSAS 2005?
Examples of code if any on internet to look at?

thank you,
Alex

Pros
====

* Returns a "flattened" rowset so the results look like they came from a
SQL query - most reporting engines can handle this type of data better
then a true multi-dimensional cellset. (Although you can get flattened
row sets without going through T-SQL, this is the way Reporting Services
works)

* Allows you to set up stored procs with standard parameters, in AS2k
you could not set up MDX stored procs.

* If you need to join relational based information onto the results of
an MDX query this is an easy way. (This was more relevant in AS2k where
the structure of the cubes was more restrictive)

Cons
====

* You are introducing another layer in the call. Client talks to SQL
Server, SQL Server talks to AS, AS passes results back to SQL Server,
SQL Server passes results back to Client.

* If you have any dimensional security in your cubes it will not work as
the AS server will only see the user account which the SQL Server is
running under.

* T-SQL stored procs with parameters need to generate dynamic SQL in
order to build the queries which produces a whole mess of doubled up
quotes and string concatenations which can be a nightmare to maintain
and debug.

* There is a bug in the RTM version of SQL 2005 that prevents OPENQUERY
calls from working with MDX queries that contain a WITH MEMBER or WITH
SET clause (should be fixed in SP1 - but no ETA yet on SP1)

See this post on my blog for an example of the bug (if you remove the
WITH clause from the query you will get an example of a working query)
http://geekswithblogs.net/darrengosb.../14/65848.aspx


Alternatives
============

* Reporting services in SQL 2005 is better at querying cubes than is was
in SQL 2000 (although there is still room for improvement)

* You can now write stored procs (in .Net assemblies) for AS 2005, so
these may replace a lot of what was previously done calling MDX queries
from T-SQL.


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1137525846.602705.150240 (AT) z14g2000cwz (DOT) googlegroups.com>,
alexdeiden (AT) yahoo (DOT) com says...

Thank you Darren!
It was of great help! but that brings another question:

is there any 'best practice' white paper for setting up security access to
MSAS 2k5 :
- cell security based on WIN2k3 domain user accounts/groups (mapping the
user accounts in the dimensions to allow access to)
or
- build Application security layer to execute MDXes in SQL server db or
ADSI or XML file;
for example:map user groups, reports,mdx(es) in the SQL server table(s)?
That last approach I have used for MSAS 2k as cell security to be set up in
MSAS 2k schema had some issues...

thnx,
Alex




"Darren Gosbell" wrote:

I have not seen too much material specifically on security. AS2k5 uses a
model similar to the one used in AS2k. Mosha (development lead for AS)
has the following page on his website:

http://www.mosha.com/msolap/articles...onsecurity.htm

As you are probably aware, if you are using an application tier (like
Reporting Services) you will find that you will either need to set up
Kerberos authentication or manage the security in that tier.

I like the idea of setting up the security in the database, but that is
not always practical. If you setup security in the application tier, you
need to make sure that the application is the only way people can get
access to the cubes. If anyone can bypass the application tier and
connect directly using a client like excel, they would be bypassing the
security. But as long as you are aware of this and manage it
appropriately there is no reason it will not work.

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <BC1DCF66-21A6-450D-A451-33CB7B84984D (AT) microsoft (DOT) com>, "=?Utf-
8?B?QWxleCBEZWlkZW4=?=" <Alex Deiden (AT) discussions (DOT) microsoft.com> says...

security really 'interesting subject'for MSAS 2k and msas2k5 - not finished
product ...at all...but...
Application security ...looks like the only right choice...
Would it be secure if I'll implement the following:
set up in asp.net site (IIS server) config file with 'windows' security
(token) and set up the path in application.config file to the registry where
user/pwd for the dummy winnt account is set up... Run MSAS under this dummy
account...therefore, the application has integrated security (with token
generated by this dummy account )...still this dummy account has 'full'
access ...to MSAS but at least nobody knows the username/pwd...and won't be
able to access MSAS from Excel...the drawback that this account will give
smbd. possibility to hack into the MSAS or even to network...(external
user)...for internal user is looks pretty safe...
Your opinion is highly appreciated.

Alex

"Darren Gosbell" wrote:

For IIS5 I think storing the credentials in the registry is probably a
reasonable approach.

For IIS6 you could setup an application pool and control the identity
from there without having to setup anything special in the web.config.

If your IIS server is in the DMZ I would setup a local account on it and
use that account.

On the MSAS server I would set up the same username/password, but I
would not run MSAS under that account. I would setup that account with
the absolute minimum privileges and then put that user in the
appropriate role(s) in MSAS. This user would not need the ability to see
even log on to the MSAS server and would not even need write privileges
to the disk drives.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <3FE2A0A5-2568-4B2E-9ABD-55E8122B8F2C (AT) microsoft (DOT) com>,
AlexDeiden (AT) discussions (DOT) microsoft.com says...

great idea. thnx!

alexdeiden (AT) yahoo (DOT) com

"Darren Gosbell" wrote: