Showing posts with label including. Show all posts
Showing posts with label including. Show all posts

Saturday, February 25, 2012

Announcing the Analysis Services Stored Procedure Project

A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!

Very Nice!

How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.

|||

We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.

To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.

HTH,

Chris

|||

Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:

Create Member [Measures].[Summation] AS

Aggregate({[DimMember1], [DimMember2]});

Scope ([DimMemberX]);

this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];

End Scope;

Whereas this statement will make it calculate it twice:

Scope ([DimMemberX]);

this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));

End Scope;

|||

Yes, that's what I understand.

Chris

Thursday, February 9, 2012

Analysis Services 2005 is not getting connected

All,

I am running Windows 2003 Server Standard Edition. Installed SQL Server 2000 and SP4 with default instance.

Installed SQL Server 2005 including analysis services with named instance (SQL2005).

Able to connect to SQL Server 2005. But when i am trying to connect to analysis services with

Windows authentication and server name as "UKPRESALES1\SQL2005".

Getting an Error : "A connection cannot be made to redirector. ensure that sqlbrowser service is running."

Pls suggest me. Finally I ended up in un-installing everything and trying out.

Raja.

Have you read the following paper on AS connectivity problems?

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

It could be a firewall problem...

HTH,

Chris

|||

Indeed, please make sure that both Analysis Services 2005 (msmdsrv.exe) and SQL Browser are allowed in the firewall to accept connections (related article here: http://msdn2.microsoft.com/en-us/library/ms175638.aspx).

|||

hello chris,

Windows Firewall have been disabled already. That too i am trying to connect in the local machine. Even the Sql browser service is running under local account.

As of now i am trying to install sql 2005 in default instance. Later I will install Sql 2000 with named instance.

Pls help me if any other solution is available.

Rajas.

|||So, to be clear - you can't connect even from the same server that you've installed AS on?

Analysis Services 2005 is not getting connected

All,

I am running Windows 2003 Server Standard Edition. Installed SQL Server 2000 and SP4 with default instance.

Installed SQL Server 2005 including analysis services with named instance (SQL2005).

Able to connect to SQL Server 2005. But when i am trying to connect to analysis services with

Windows authentication and server name as "UKPRESALES1\SQL2005".

Getting an Error : "A connection cannot be made to redirector. ensure that sqlbrowser service is running."

Pls suggest me. Finally I ended up in un-installing everything and trying out.

Raja.

Have you read the following paper on AS connectivity problems?

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

It could be a firewall problem...

HTH,

Chris

|||

Indeed, please make sure that both Analysis Services 2005 (msmdsrv.exe) and SQL Browser are allowed in the firewall to accept connections (related article here: http://msdn2.microsoft.com/en-us/library/ms175638.aspx).

|||

hello chris,

Windows Firewall have been disabled already. That too i am trying to connect in the local machine. Even the Sql browser service is running under local account.

As of now i am trying to install sql 2005 in default instance. Later I will install Sql 2000 with named instance.

Pls help me if any other solution is available.

Rajas.

|||So, to be clear - you can't connect even from the same server that you've installed AS on?