Analysis Server Hangs

Guys,

I have been getting a very peculiar problem while i am processing a dimention with about approx 7 million (68 lakh) rows. The processing wizard shows the rows being read and once the rows are read from SQL server , the Analysis Manager Hangs. Can anyone help me out ? Is this a limitation of Analysis manager not being able to process such huge amount of data or are there some settings that i can tweak.Hi enigma,
Do you meant to say by seeing in'Task Manager/ Application'. showing 'Not Responding' ? You try watch using performance counter/AS:Proc:,AS:Agg.Proc objects, after some time, you will get some values related to that. In my case also, I was getting 'this hanging sort of things' but actually it is not.
HTH
====

Originally posted by Enigma
Guys,

I have been getting a very peculiar problem while i am processing a dimention with about approx 7 million (68 lakh) rows. The processing wizard shows the rows being read and once the rows are read from SQL server , the Analysis Manager Hangs. Can anyone help me out ? Is this a limitation of Analysis manager not being able to process such huge amount of data or are there some settings that i can tweak.|||Thanks Man,

I was doing exactly the thing you said ... well .. i processed the cube using a DTS yesterday and that worked perfectly fine .

Thanks for all your help

analysis Backup problem

Hi guys,

Im having problem in backing up my Analysis Database...Below is the error...

"The semaphore period timeout has expired"

Anybody who encountered this problem...Please let me know...

thanks,

Larry

Hi Larry,

I've encountered this same problem but this happened to me while starting up our Analysis Services. How did you resolve this problem?

Regards,

Joseph

|||

If you see this problem persisting please report it at Connect (http://connect.microsoft.com/sql)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

analysis Backup problem

Hi guys,

Im having problem in backing up my Analysis Database...Below is the error...

"The semaphore period timeout has expired"

Anybody who encountered this problem...Please let me know...

thanks,

Larry

Hi Larry,

I've encountered this same problem but this happened to me while starting up our Analysis Services. How did you resolve this problem?

Regards,

Joseph

|||

If you see this problem persisting please report it at Connect (http://connect.microsoft.com/sql)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

An unexpected error occurred file 'mddiscretizer.cpp'

Hey guys,

I'm not sure if this one should be addresses to the data mining team or the analysis services team, but I first encountered this error while attempting to process a Naive Bayes mining model that has a column with only a couple of distinct values in it. I set the discretization method for the column to clusters and then tried to process the model. The processing completed with failures and complained that the column had too few distinct values to apply the selected discretization.

I have now also noticed that when I attempt to process an OLAP cube that has the same column as a measure group in it the cube processing fails with the following error:

[Analysis Services Execute DDL Task] Error: Internal error: An unexpected error occurred (file 'mddiscretizer.cpp', line 1532, function 'MDDiscretizer::GetBucketForValue').

Any thoughts on this one?
I have found a workaround for this issue:

http://zen-turkey.com/blog/default.aspx?id=32&t=An-unexpected-error-occurred-file-mddis

This seemed to resolve the issue for me, but I am not sure if this would be considered a bug or not? For me the work-around resolves the issue and I consider it closed... but it might bear further investigation...

Cheers :-)
|||This is still a bug that we will investigate and fix in a future release. Thanks for reporting it.

An interesting idea...

Having no more experience than reading books online, here is an interesting idea I would like to run by you guys and you can let me know if it is feasible or tell me I need to put the crack pipe down...

We are going to increase the number of disks in our SAN, and I was speaking with the SAN administrator and he mentioned the shuffling of logical drives to match the new space. He said he is going to have to go through quite a few combinations/permutations on figuring out the best configuration for what data goes on the old vs. new to get the optimal space.

Is this something that can be modeled out? I can write something that recursively figures it out, but why not explore fun ideas with tools that may be able to do it?

Thank you in advance,

John Hennesey

If you collect enough data you can easily build models to predict what best performance. You should include things like load, etc. You can likely start off by using the Analyze Key Influencers tool in the Data Mining Addins for Office 2007 to get a rough idea of what factors indicate the best configuration.

HTH

-Jamie

|||cool - thanks for the ideas!

An alternative for Dynamic SQL required

Hey guys I've something like this which is a reauirement. I dont want to use
a dynamic SQL
eg is
create proc proc1
@.ownername varchar(100),
@.IPaddress varchar(15)
as
select a,b,c from Table1 t1
join Table2 t2 on t1.col1=t2.col2
where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
+'%') OR
(t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
(t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
go
Hopefully you can see the situation here.
I have an app where I can select (either or fname or lname) and I can also
select (either or internal IP or external IP). The above proc will work for
either in both cases.
What I want to do in the stored proc is somehow check these conditions. I
dont want to use dynamic SQL. Can you suggest an alternative which will help
in performance when compard to Dynamic SQL? Thank you.One obvious performance issue is that you are performing a LIKE comparison
on a value that is left truncated. For example, if OwnerName is indexed,
then this is will result in an index scan:
where OwnerName LIKE 'John%'
However, the following would result in a non-indexed table scan:
where OwnerLastName LIKE '%Smith'
where OwnerLastName LIKE '%Smith%'
Also, read this document; specifically the paragraphs about "sargable"
comparison arguments.
http://www.microsoft.com/technet/pr...s/inside14.mspx
Actually, dynamic SQL may be the solution to your problem. If you could
construct your SQL on the application side or construct the SQL within the
procedure in a variable and execute using the T-SQL Exec function.
http://msdn.microsoft.com/library/d...br />
05ro.asp
You can use the Show Execution Plan feature of Query Analyzer to determine
if your query is properly utilizing an index.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:8FD1CC5B-A8F9-4080-A75C-65D85E331CF9@.microsoft.com...
> Hey guys I've something like this which is a reauirement. I dont want to
> use
> a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
> +'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
> go
>
> Hopefully you can see the situation here.
> I have an app where I can select (either or fname or lname) and I can also
> select (either or internal IP or external IP). The above proc will work
> for
> either in both cases.
> What I want to do in the stored proc is somehow check these conditions. I
> dont want to use dynamic SQL. Can you suggest an alternative which will
> help
> in performance when compard to Dynamic SQL? Thank you.|||On Fri, 17 Mar 2006 09:05:26 -0800, Tejas Parikh wrote:

>Hey guys I've something like this which is a reauirement. I dont want to us
e
>a dynamic SQL
>eg is
>create proc proc1
>@.ownername varchar(100),
>@.IPaddress varchar(15)
>as
>select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
>where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
>+'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
>go
>
>Hopefully you can see the situation here.
>I have an app where I can select (either or fname or lname) and I can also
>select (either or internal IP or external IP). The above proc will work for
>either in both cases.
>What I want to do in the stored proc is somehow check these conditions. I
>dont want to use dynamic SQL. Can you suggest an alternative which will hel
p
>in performance when compard to Dynamic SQL? Thank you.
Hi Tejas,
Lots of useful information for this type of problem can be found on
Erland's page: http://www.sommarskog.se/dyn-search.html.
Hugo Kornelis, SQL Server MVP|||Tejas Parikh (TejasParikh@.discussions.microsoft.com) writes:
> Hey guys I've something like this which is a reauirement. I dont want to
> use a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%' + ISNULL(@.ownername,
> A.OwnerFirstName) +'%') OR
> (t1.OwnerLastName LIKE '%' + ISNULL(@.ownername,
> A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.ExternalIP) + '%') OR
> (t1.InternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.InternalIP)+'%'))
> go
Well, as long as you have the % first in the LIKE operations, this is
going to table scan no matter what you do, so there would not be much
point with using dynamic SQL for better performance. Your current code
would work fine.
I would recommend that you leave it to the users to specify any initial %
if they need it. Then there is a at least a ghost of a chance for
indexes to be used. It's difficult to give detailed suggestions though,
as I don't know the tables, and I suspect that your real procedures have
more than these two parameters.
But my article, that Hugo also pointed you to, might give you some
ideas: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

An "EXISTS" Problem

Hey, guys,
Below are my DDL,
CREATE TABLE [dbo].[test1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
CREATE TABLE [dbo].[test2] (
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
test1 table
id A B
--
1 c 1
2 c 2
3 c 3
4 c 4
6 b
9 d
10 e
test2 table
A B
--
c 1
b 1
b 2
d 1
result table
id A B
--
2 c 2
3 c 3
4 c 4
6 b
9 d
Here is my sql to get the result table
SELECT P.id,P.A,P.B
FROM test1 P left outer join test2 R on P.A=R.A
WHERE (NOT EXISTS
(SELECT *
FROM test2 Q
WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
and R.A is not null
group by P.id,P.A,P.B
Can this SQL command be neater?
thanks a lot.
AllenHere are a couple of other methods, although 'cleaner' is a bit subjective'.
Personally, I prefer the NOT EXISTS technique over LEFT JOIN.
INSERT INTO test1 VALUES(1,'c',1)
INSERT INTO test1 VALUES(2,'c',2)
INSERT INTO test1 VALUES(3,'c',3)
INSERT INTO test1 VALUES(4,'c',4)
INSERT INTO test1 VALUES(6,'b', NULL)
INSERT INTO test1 VALUES(9,'d', NULL)
INSERT INTO test1 VALUES(10,'e', NULL)
GO
INSERT INTO test2 VALUES('c', 1)
INSERT INTO test2 VALUES('b', 1)
INSERT INTO test2 VALUES('b', 2)
INSERT INTO test2 VALUES('d', 1)
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
WHERE NOT EXISTS
(
SELECT *
FROM test2 Q
WHERE
P.A = Q.A AND P.B = Q.B
)
GROUP BY P.id, P.A, P.B
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
LEFT JOIN test2 Q ON P.A = Q.A AND P.B = Q.B
WHERE Q.A IS NULL
GROUP BY P.id, P.A, P.B
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allen" <cpchen@.cht.com.tw> wrote in message
news:u19bQ77tDHA.2408@.tk2msftngp13.phx.gbl...
> Hey, guys,
> Below are my DDL,
> CREATE TABLE [dbo].[test1] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> CREATE TABLE [dbo].[test2] (
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> test1 table
> id A B
> --
> 1 c 1
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> 10 e
> test2 table
> A B
> --
> c 1
> b 1
> b 2
> d 1
>
> result table
> id A B
> --
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> Here is my sql to get the result table
> SELECT P.id,P.A,P.B
> FROM test1 P left outer join test2 R on P.A=R.A
> WHERE (NOT EXISTS
> (SELECT *
> FROM test2 Q
> WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
> and R.A is not null
> group by P.id,P.A,P.B
>
> Can this SQL command be neater?
>
> thanks a lot.
> Allen
>

AMO Browser Sample

Hey guys,

Since I'm new to SQL Server 2005, I was testing some Samples that came with it. Now, I'm having problems with the AMO Browser....
everytime I run it, it gives me the connection and the AMO Browser pop-ups but when I try to connect (Analysis Services Server: Data Source=localhost)

It gives me an error:

A connection cannot be made. Ensure that the server is running

Additional Information:

No connection could be made because the target machine actively refused it (System)
Show Technical details
All Messages:
===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
at Microsoft.Samples.SqlServer.AmoBrowser.connectButton_Click(Object sender, EventArgs e)

===================================

No connection could be made because the target machine actively refused it (System)

Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClientByAddress(String hostName, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

* My server is running for all Services and Databases!

Could anyone please help me out on this one, I'm not sure if anyone have tried this already. Thanks in advance!

hi,i have the same question!|||

First check whether the SQL SERVER ANALYSIS SERVICES 2005 WAS INSTALLED

IF installed and check whether it is running or not IN Background

AMO Browser Sample

Hey guys,

Since I'm new to SQL Server 2005, I was testing some Samples that came with it. Now, I'm having problems with the AMO Browser....
everytime I run it, it gives me the connection and the AMO Browser pop-ups but when I try to connect (Analysis Services Server: Data Source=localhost)

It gives me an error:

A connection cannot be made. Ensure that the server is running

Additional Information:

No connection could be made because the target machine actively refused it (System)
Show Technical details
All Messages:
===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
at Microsoft.Samples.SqlServer.AmoBrowser.connectButton_Click(Object sender, EventArgs e)

===================================

No connection could be made because the target machine actively refused it (System)

Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClientByAddress(String hostName, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

* My server is running for all Services and Databases!

Could anyone please help me out on this one, I'm not sure if anyone have tried this already. Thanks in advance!

hi,i have the same question!|||

First check whether the SQL SERVER ANALYSIS SERVICES 2005 WAS INSTALLED

IF installed and check whether it is running or not IN Background

AMO Browser Sample

Hey guys,

Since I'm new to SQL Server 2005, I was testing some Samples that came with it. Now, I'm having problems with the AMO Browser....
everytime I run it, it gives me the connection and the AMO Browser pop-ups but when I try to connect (Analysis Services Server: Data Source=localhost)

It gives me an error:

A connection cannot be made. Ensure that the server is running

Additional Information:

No connection could be made because the target machine actively refused it (System)
Show Technical details
All Messages:
===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
at Microsoft.Samples.SqlServer.AmoBrowser.connectButton_Click(Object sender, EventArgs e)

===================================

No connection could be made because the target machine actively refused it (System)

Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClientByAddress(String hostName, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

* My server is running for all Services and Databases!

Could anyone please help me out on this one, I'm not sure if anyone have tried this already. Thanks in advance!

hi,i have the same question!|||

First check whether the SQL SERVER ANALYSIS SERVICES 2005 WAS INSTALLED

IF installed and check whether it is running or not IN Background