Showing posts with label analysis. Show all posts
Showing posts with label analysis. Show all posts

Friday, March 30, 2012

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

OPENQUERY on AS 2005 produces different results than AS 2000

I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:

With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])

Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.

Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]

As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?

Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance

Any ideas at all? I am still having this issue and it needs to be resolved.

Thanks

Wednesday, March 28, 2012

openquery - sp_addlinkedserver problem

Hello, i'm trying to figure out how to solve my challenge.

my goal is to access the analysis service to execute an mdx-query.

the resultset is placed in a temp-table in my 'normal' database.

when i login to my db as integrated user (Administrator in this case) everything works tiptop.

but since we cannot use (for now) i have to use a sql-login.

when i login as that, i have trouble getting the resultset.

atm my code looks like this:

USE master

GO

/* Add new linked server */

EXEC sp_addlinkedserver

@.server='SASDEL003_SSAS', -- local SQL name given to the linked server

@.srvproduct='', -- not used

@.provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)

@.datasrc='SASDEL003', -- analysis server name (machine name)

@.catalog='TNT Grip AnalysisServices' -- default catalog/database

GO

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'SASDEL003_SSAS'

, @.useself = false

, @.locallogin = 'domain\foo'

--, @.rmtuser = 'foo'

--, @.rmtpassword = 'bar'

GO

SELECT *

FROM OPENQUERY(SASDEL003_SSAS,'SELECT some highly interesting query')

GO

/* Remove any previous references to the linked server */

EXEC sp_droplinkedsrvlogin @.rmtsrvname= 'SASDEL003_SSAS' , @.locallogin= 'SASDEL003\foo'

GO

EXEC sp_dropserver 'SASDEL003_SSAS'

GO

but i get the error:

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS".what i'm seeing in the profiler i'm logging in the ssas-database with "anonymous user"

i don't understand that, because i use linked server with sp_addlinkedsrvlogin

should i allow anonymous login then?

Wednesday, March 21, 2012

Open source tools for performance analysis

Hi all,
Does anyone know of an open source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,
kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something like
> OpenSQLTrace with a good interface) ?
> Thanks,
>
|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled without a key. In the interest of fairness, I think Idera has something similar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>

Open source tools for performance analysis

Hi all,
Does anyone know of an Open Source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an Open Source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something lik
e
> OpenSQLTrace with a good interface) ?
> Thanks,
>|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled
without a key. In the interest of fairness, I think Idera has something sim
ilar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It
's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. ([url]www.sqlpass.org[/url
])

> Hi all,
> Does anyone know of an Open Source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>sql

Open source tools for performance analysis

Hi all,
Does anyone know of an open source tool capable of analyzing SQL server
performance (something comparable to Spotlight maybe or even something like
OpenSQLTrace with a good interface) ?
Thanks,kokonut,
I don't know of any Open Source products to do this. SQL Profiler in
combination with Windows Performance monitor is pretty good for this
kind of work.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kokonut wrote:
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL server
> performance (something comparable to Spotlight maybe or even something like
> OpenSQLTrace with a good interface) ?
> Thanks,
>|||There is a freeware mode for Quest's Spotlight on SQL Server that's enabled without a key. In the interest of fairness, I think Idera has something similar.
There's also the SQL2H tool that does some tracking of SQL Server perfmon counters. It's one of the top downloads on http://www.microsoft.com/sql.
Hope this helps,
-kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Hi all,
> Does anyone know of an open source tool capable of analyzing SQL
> server performance (something comparable to Spotlight maybe or even
> something like OpenSQLTrace with a good interface) ?
> Thanks,
>

Saturday, February 25, 2012

Only for Gurus -- Analysis MDX Query help needed

I need a expression to get the name of the Member of a dimension.
For example .. if I have a Time Dimension ... and Child in hierarchy are Months.. I would like the result as
January
February
.....
December
Any Ideas ?http://www.winnetmag.com/Article/ArticleID/16311/16311.html

HTH