Re: Execute MDX from T-sql -Procs and Cons?
- From: "Alex Deiden" <Alex Deiden@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 21 Jan 2006 21:11:02 -0800
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:
> 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/darrengosbell/archive/2006/01/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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
> alexdeiden@xxxxxxxxx says...
> > 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
> >
> >
>
>
.
- Follow-Ups:
- Re: Execute MDX from T-sql -Procs and Cons?
- From: Darren Gosbell
- Re: Execute MDX from T-sql -Procs and Cons?
- References:
- Execute MDX from T-sql -Procs and Cons?
- From: alexd
- Re: Execute MDX from T-sql -Procs and Cons?
- From: Darren Gosbell
- Execute MDX from T-sql -Procs and Cons?
- Prev by Date: Re: COUNT() and FILTER()
- Next by Date: Problem with MDX FILTER()
- Previous by thread: Re: Execute MDX from T-sql -Procs and Cons?
- Next by thread: Re: Execute MDX from T-sql -Procs and Cons?
- Index(es):
Relevant Pages
|
