|
Home > Archive > MS SQL Server OLAP > January 2006 > Execute MDX from T-sql -Procs and Cons?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Execute MDX from T-sql -Procs and Cons?
|
|
|
| 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/darrengos...1/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@z14g2000cwz.googlegroups.com>,
alexdeiden@yahoo.com 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
>
>
| |
| Alex Deiden 2006-01-22, 3:23 am |
| 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/darrengos...1/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@z14g2000cwz.googlegroups.com>,
> alexdeiden@yahoo.com says...
>
>
| |
|
| 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/article...ri
ty.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@microso
ft.com>, "=?Utf-
8?B?QWxleCBEZWlkZW4=?=" <Alex Deiden@discussions.microsoft.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
>
>
>
>
| |
| Alex Deiden 2006-01-25, 3:27 am |
| 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:
> 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/article...ri
ty.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@microso
ft.com>, "=?Utf-
> 8?B?QWxleCBEZWlkZW4=?=" <Alex Deiden@discussions.microsoft.com> says...
>
| |
|
| 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@microso
ft.com>,
AlexDeiden@discussio
ns.microsoft.com says...[color=darkred]
> 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:
>
| |
| Alex Deiden 2006-01-26, 4:58 pm |
| great idea. thnx!
alexdeiden@yahoo.com
"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@microso
ft.com>,
> AlexDeiden@discussio
ns.microsoft.com says...
>
|
|
|
|
|