SQL Alternative to cursor: version

Showing posts with label version. Show all posts

Tuesday, March 27, 2012

analysis services

Which version of the sqlserver comes with analysis server? how do we setupAnalysis services comes with SQL 7 and SQL 2k..
Simply load the CD ( with autorun set) and you will get an option to setup
SQL Server or Analysis services...
"jbomma" <jbomma@.clayton.com> wrote in message
news:uWAQFXz6DHA.1556@.tk2msftngp13.phx.gbl...
> Which version of the sqlserver comes with analysis server? how do we setup
>sql

Analysis Services

I am trying to work through the tutorial when you load SQL 2005. I am running the 64 bit version. I have deployed my cube and am trying to display data in the browser. I move over the sales amount into the data area I get the below message. What should I do to fix this issue?

The query could not be processed:

Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed.

Sounds similar to the problem discussed in this thread:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/d2ca4c8981db915a
>>

" noWrap width="1%" bgColor=#b71c0c>Mosha Pasumansky [MS] Oct 17, 2:24 pm show options

Newsgroups: microsoft.public.sqlserver.olap From: "Mosha Pasumansky [MS]" <mos...@.online.microsoft.com> - Find messages by this author Date: Mon, 17 Oct 2005 11:24:25 -0700 Local: Mon, Oct 17 2005 2:24 pm Subject: Re: AS 2005: ....a string was found that was larger than the page size Reply | Reply to Author | Forward | Print | View Thread | Show original | Report Abuse

Please read this thread - there is plenty of feedback from the product team.
It is a bug in CTP16 that is fixed in RTM. If you are TAP customer - you can
get CTP16 hotfix for it.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
>>

Sunday, March 25, 2012

analysis services

quote:

> Which version of the sqlserver comes with analysis server? how do we setup
>

sql

Analysis Service stops soon after starting

Hi all,

I'm quite new to MS SQL server. I downloaded the Evaluation version of 2005 recently. I tried installing it. Partially I succed. I'm able to get all other services running but for the "Analysis service" when I try to start it from control pannel, It starts and stops immedieatly with a message "It started and stopped immedieatly.....". I'm not able to figure it out why.

I've Vista Business edition and I've installed the SP2 for the SQL 2005 as well. Any directions would be helpful

Best regards,

Markish

Hello. When you start Management Studio or BI_Dev Studio you can right click on them and choose "Run as administrator", provided that you are an administrator on you machine.

Vista also requires SP1 for Visual Studio and an additional update. From my memory I also think that you will need SQL Server SP2. Have you installed these updates?

HTH

Thomas Ivarsson

|||

Hi,

Thanks for the reply. I've installed SQL SP2. I doubt If I have the SP1 for the Visual Studio. (I'll install it today anyhow... Smile ) I'm running as an administrator only. I'll update you on the status.

Best regards,

Markish

|||

Hi Markish, Were you able to solve this problem? If so, pls let know how it happenned. I face the same issue, posted here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2113409&SiteID=1

regards

Analysis Service stops soon after starting

Hi all,

I've Vista Business edition and I've installed the SP2 for the SQL 2005 as well. Any directions would be helpful

Best regards,

Markish

Hello. When you start Management Studio or BI_Dev Studio you can right click on them and choose "Run as administrator", provided that you are an administrator on you machine.

Vista also requires SP1 for Visual Studio and an additional update. From my memory I also think that you will need SQL Server SP2. Have you installed these updates?

HTH

Thomas Ivarsson

|||

Hi,

Best regards,

Markish

|||

Hi Markish, Were you able to solve this problem? If so, pls let know how it happenned. I face the same issue, posted here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2113409&SiteID=1

regards

Analysis Server not working with MSDE SQL Server

I installed Visual Studio .NET v.1.0 and the included MSDE version of
SQL Server. I have an additional copy of MS SQL Server 2000 Enterprise
Ed. on CD. I installed Microsoft Analysis Services from this CD on my
machine with MSDE SQL Server; it appeared to install cleanly.
I ran Analysis Manager from the Start menu under Windows XP Pro. The
local SQL Server showed red block indicating the server was not
running. I tried to connect, and got the following error message:
Unable to connect to the registry on the server (myserver_name), or you
are not a member of the OLAP Administrators group on this server.
Does anyone know the problem here? I was wondering whether I had to
install MS SQL Server enterprise ed. to use Analysis Server with it,
rather than more limited MSDE version of SQL Server ?!
Thanks all.
Fred Z.
hi Fred,
silversw2000@.yahoo.com wrote:
> I installed Visual Studio .NET v.1.0 and the included MSDE version of
> SQL Server. I have an additional copy of MS SQL Server 2000 Enterprise
> Ed. on CD. I installed Microsoft Analysis Services from this CD on my
> machine with MSDE SQL Server; it appeared to install cleanly.
> I ran Analysis Manager from the Start menu under Windows XP Pro. The
> local SQL Server showed red block indicating the server was not
> running. I tried to connect, and got the following error message:
> Unable to connect to the registry on the server (myserver_name), or
> you are not a member of the OLAP Administrators group on this server.
> Does anyone know the problem here? I was wondering whether I had to
> install MS SQL Server enterprise ed. to use Analysis Server with it,
> rather than more limited MSDE version of SQL Server ?!
> Thanks all.
> Fred Z.
I do not think Analysis Service is supported on MSDE..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Thursday, March 22, 2012

Analysis Server 2005 (SP2, Version 9.0.3042) returns wrong results (from cache)

In some cases the Analysis Server returns wrong results: If You at first make
a query containing several restrictions and after that make a similar query with less
restrictions, You sometimes get the result of the first query (which is wrong
for the second query).

Of course this error does not occur in every situation. In our environment it occurs
when CustomRollupFormulas of ParentChild-Dimensions are involved.

My questions:
- Has anyone seen this error before?
- Is there a way to report this as an error to microsoft?
- Are bug fixes or workaround available?
(At the moment we have SQL Server 2005 SP2, Version 9.0.3042)

Any help is welcome.

The following contains a description of a small sample where this error occurs.
(A Cubedefinition and the underlying Datawarehouse can be provided).
Detailled Description:
=====================

The OLAP-Database contains:
- A Year dimension with one member '2007'
(to make this example simple we have no quarters, months and so on)
- An Account dimension with Attributes "Key", "UserKey" and "Type" and 3 elements:
Key: UserKey: Type:
1 "1000" A
2 "2000" B
3 "3000" C
- A dimension for KPIs with Attributes "Key" and "Parent". "Parent" is a ParentChild
Dimension. This dimension has a CustomRollupColumn with MDX-Formulas.
The dimension has two elements:
Key: Designation: Parent: MDX-Forumula
-1 All NULL NULL
1 KPI 1 NULL Sum(
Filter(
CrossJoin(
{
([Account].[UserKey].&[1000]:
[Account].[UserKey].&[3000])
},
[KPI].[Parent].&[-1]
),
[Measures].CurrentMember > 0
)
)
- A fact-table with 3 lines:
AccountKey: Amount: Year: KPIKey
1 1000.00 2007 -1
2 2000.00 2007 -1
3 -4000.00 2007 -1

We use two Queries:
- Query 1:
SELECT {[Measures].[Amount]} ON 0,
NON EMPTY
AddCalculatedMembers(
Hierarchize(
Descendants(
{[Year].[Key].&[2007]},
[Year].[Key].[Key],
SELF_AND_BEFORE
)
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE ON 1
FROM [PRO110530]
WHERE(
{
[Account].[Type].&[Type A],
[Account].[Type].&[Type C]
},
[KPI].[Parent].&[1]
);

This query first restricts the accounts to "1000" (Type A) und "3000" (Type C)
and the formula of "KPI 1" filters out account "3000".

The correct result is "1000.00".

- Query 2:
SELECT {[Measures].[Amount]} ON 0,
NON EMPTY
AddCalculatedMembers(
Hierarchize(
Descendants(
{[Year].[Key].&[2007]},
[Year].[Key].[Key],
SELF_AND_BEFORE
)
)
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_TYPE ON 1
FROM [PRO110530]
WHERE(
[KPI].[Parent].&[1]
);
This query is nearly the same as query 1, but the accounts are not filtered out
by their type. Therefore the formula of "KPI 1" applies to all accounts and
only account "3000" is filtered out.

The correct result is "3000.00" (Sum of accounts "1000" and "2000")

Analysis Server returns this result if You run Query 2 as first query.

The error occurs in the following case:
- Process the cube (or restart) the Analysis Server.
- Connect to the database.
- Run Query1:
The server returns result: "1000.00". That's correct.
- Run Query2 (on the same connection):
The server returns result: "1000.00". That's wrong.

The error does not occur in the following cases:
- If you restart the server between Query1 and Query2.
(That is no pratical workaround).
- If You do not put the Year-dimension on Axis 1
(In real life that is no practical workaround, because we have a more complex
Time-dimension and want for instance to see two years on Axis 1)
- If You use different connections for the two Queries
(That is no workaround for us, because we use a third-party tool, which
calls the Analysis Server)
- If You add the VisualTotals-function to the MDX for Axis 1
(That is a bad workaround, because the runtime of complex KPIs rises up significantly)

It's hard to tell, but it almost feels like an issue with the formula cache.

Your options would be

1. Ring Product Support - If you need to get a fix in a timely manner, then this is your best bet. You would have to check when you call, but I think you would get your fee refunded if the call is confirmed as a bug in the product.

2. You could log the bug in the SQL Server section on the connect website connect.microsoft.com, but you are not guarenteed of a timely response (the product team does go through these issues, but there is just no telling when this will happen)

It could be something to do with the CustomRollup formulas, have you tried turning off the custom rollups and moving the calc into a scope statement in the calc script? If this work around does fix your issue I would still encourage you to log a bug on connect.

|||

Other than filing a bug, if you need an immediate workaround, try issuing the following ClearCache command through XML/A between the two queries.

<Body>

<DatabaseID>Your Database ID</DatabaseID>

</Object>

</ClearCache>

</Batch>

</Command>

</Execute>

</Body>

</Envelope>

|||

Good idea Jeffrey, I did think of this, but forgot to mention it. If it is a cache issue this would probably work too. The only issue being that you cannot "chain" this command onto the start or the end of a query, so the problem then becomes "how do we know when to run this command".

I also have a variation on this command wrapped in a stored proc on the AS Stored Procedure project on codeplex www.codeplex.com/asstoredprocedures - it is not part of the lastest release, you would have to download the source and compile it (we are planning to compile another release soon), there is docmentation on this command on the codeplex site.

|||

Darren and Jeffrey,

thank Your for the replies.

Unfortunately I can't clear the cache, because I only fill the OLAP-Database and the Database is called by a third party tool.

Therefore I will report this problem to Microsoft Product Support at next.

Hopefully they know a workaround or have a hotfix.

|||

We reported the error to Microsoft report. I'm citing from the answer:

"...the problem is really because of the CustomRollupFormula, where we are seeing performance issues as well as problems like yours. Our developer team recommended that these people should start using “Cache Policy = 9” with SP2 in order to obtain better performance (connection string setting). Now, Cache Policy is not doable in SSMS or BIDS, so what you can do is to change this setting server wide by changing inside the msmdsrv.ini: <CalculationCoverPolicy>9</CalculationCoverPolicy> Rebooting the server will use this new setting. Something to remember is that this will change the behavior of the query engine. So, some of your queries will go faster and some slower. So, remember that and monitor the server."

We changed this setting and the error does not occur anymore. Moreover the performance of some testcases which use custom rollup formulas was significantly better. I hope the overall performance of the system remains OK with this setting.

My question at this point is: Custom Rollup Formulas are a very powerful part of Analysis Services -- You can create very interesting solutions with them. But it seems to me that the use of Custom Rollup Formulas is not in the main focus of the Analysis Services development team. Otherwise I could not explain the error and performance problems in SP2 (the performance in SP1 was better!). Perhaps it would help, if Microsoft has real-life examples of the usage of this feature. Is it possible to establish a deeper contact between the development team and users of custom rollup formulas to exchange testcases and experiences for this topic?

|||have you written any custom aggregations - either through xlma or the aggregation design wizard?

Analysis Server 2005 (SP2, Version 9.0.3042) returns wrong results (from cache)

Of course this error does not occur in every situation. In our environment it occurs
when CustomRollupFormulas of ParentChild-Dimensions are involved.

Any help is welcome.

The following contains a description of a small sample where this error occurs.
(A Cubedefinition and the underlying Datawarehouse can be provided).
Detailled Description:
=====================

This query first restricts the accounts to "1000" (Type A) und "3000" (Type C)
and the formula of "KPI 1" filters out account "3000".

The correct result is "1000.00".

The correct result is "3000.00" (Sum of accounts "1000" and "2000")

Analysis Server returns this result if You run Query 2 as first query.

It's hard to tell, but it almost feels like an issue with the formula cache.

Your options would be

|||

Other than filing a bug, if you need an immediate workaround, try issuing the following ClearCache command through XML/A between the two queries.

<Body>

<DatabaseID>Your Database ID</DatabaseID>

</Object>

</ClearCache>

</Batch>

</Command>

</Execute>

</Body>

</Envelope>

|||

Darren and Jeffrey,

thank Your for the replies.

Unfortunately I can't clear the cache, because I only fill the OLAP-Database and the Database is called by a third party tool.

Therefore I will report this problem to Microsoft Product Support at next.

Hopefully they know a workaround or have a hotfix.

|||

We reported the error to Microsoft report. I'm citing from the answer:

|||have you written any custom aggregations - either through xlma or the aggregation design wizard?

Tuesday, March 27, 2012

analysis services

Analysis Services

Sunday, March 25, 2012

analysis services

Analysis Service stops soon after starting

Analysis Service stops soon after starting

Analysis Server not working with MSDE SQL Server

Thursday, March 22, 2012

Analysis Server 2005 (SP2, Version 9.0.3042) returns wrong results (from cache)

Analysis Server 2005 (SP2, Version 9.0.3042) returns wrong results (from cache)

SQL Alternative to cursor

Blog Archive

About Me