How to determine which Analysis Services edition is installed

Hi,

How can I determine what edition (standard or enterprise) of Analysis Services is installed on a server?

thanks

Lyn

I guess that the version of AS will be the same as the version of SQL Server, which you can determine by executing the following SQL-query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Source: http://support.microsoft.com/kb/321185

|||

No, since only AS was installed on the server.

|||

Then you have to connect to the AS server using SQL Management Studio, highlight the server and then click Report in the Summary tab.

You'll see a reporting services report with the info:

Server: COMPUTERNAME(\INSTANCE) 4/19/2007 15:19 Edition: Developer Version: 9.00.2047.00

|||

The Analysis server is version 2000 though.

Thanks

|||

Look at this registry key value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\Edition

|||

On a named instance this value is at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE NAME>\Setup

Else, here is a feature comparison: http://msdn2.microsoft.com/en-us/library/aa175266(SQL.80).aspx

Enterprise Edition has support for linked cubes, user-defined partitions etc.

How to determine which Analysis Services edition is installed

Hi,

How can I determine what edition (standard or enterprise) of Analysis Services is installed on a server?

thanks

Lyn

I guess that the version of AS will be the same as the version of SQL Server, which you can determine by executing the following SQL-query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Source: http://support.microsoft.com/kb/321185

|||

No, since only AS was installed on the server.

|||

Then you have to connect to the AS server using SQL Management Studio, highlight the server and then click Report in the Summary tab.

You'll see a reporting services report with the info:

Server: COMPUTERNAME(\INSTANCE) 4/19/2007 15:19 Edition: Developer Version: 9.00.2047.00

|||

The Analysis server is version 2000 though.

Thanks

|||

Look at this registry key value:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\Setup\Edition

|||

On a named instance this value is at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE NAME>\Setup

Else, here is a feature comparison: http://msdn2.microsoft.com/en-us/library/aa175266(SQL.80).aspx

Enterprise Edition has support for linked cubes, user-defined partitions etc.

How to determine programatically what datafiles are associated with a database

I'm writing a data collection script to gather database and data file
information. This information will be used for reporting and analysis. I
want to be able to tell what data files are associated with each of the
databases. sysfiles doesn't include the database name and sysaltfiles
doesn't include all of the datafiles.
Query I'm using:
use master
select f.file_name, f.name, ...
from master.dbo.sysaltfiles a ,
dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid
and db.name = 'master'
I can probably infer the database name from the name column but I'd rather
not go there!
Please don't tell me to go somewhere and point and click. I'm monitoring
almost 70 databases and this data needs to be collected on a daily basis.
Message posted via http://www.webservertalk.comevelyn,
use sp_msforeachdb:
exec sp_msforeachdb '
select db.name, f.name
from master.dbo.sysaltfiles a ,
?.dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid '
hth
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eebc4237a7204ec3a8b8ca5fab4890a5@.SQ
webservertalk.com...
> I'm writing a data collection script to gather database and data file
> information. This information will be used for reporting and analysis. I
> want to be able to tell what data files are associated with each of the
> databases. sysfiles doesn't include the database name and sysaltfiles
> doesn't include all of the datafiles.
> Query I'm using:
> use master
> select f.file_name, f.name, ...
> from master.dbo.sysaltfiles a ,
> dbo.sysfiles f ,
> master.dbo.sysdatabases db
> where a.name=f.name
> and a.fileid = f.fileid
> and a.dbid = db.dbid
> and db.name = 'master'
> I can probably infer the database name from the name column but I'd rather
> not go there!
> Please don't tell me to go somewhere and point and click. I'm monitoring
> almost 70 databases and this data needs to be collected on a daily basis.
> --
> Message posted via http://www.webservertalk.com|||Try this:
exec sp_MSforeachdb
'
use [?]
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
'|||For this query add brackets around the question mark. i.e. [?]|||What does sp_msforeachdb do? It is not in my online book. I hesitate to
run something in 70 production databases without knowing the possible
ramifications.
Message posted via http://www.webservertalk.com|||I figured out what the procedure does.
Perhaps I'm not clear I want the database name and all associated data file
names. The following query gives me table names.
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
Message posted via http://www.webservertalk.com|||it is undocumented and unsupported, meaning that ms might choose not to
include it in future versions of sql server. however, it is widely used. you
can check the definition (in master db) with:
exec sp_helptext 'sp_msforeachdb'
nothing fancy (builds a cursor internallly), but very handy.
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f3affb3bea874a3fbf0edb3bcc8b8868@.SQ
webservertalk.com...
> What does sp_msforeachdb do? It is not in my online book. I hesitate to
> run something in 70 production databases without knowing the possible
> ramifications.
> --
> Message posted via http://www.webservertalk.com|||Hi
The database may have two or more log files. You also need to see them
Look at this script helps you.
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:960a4cbd69a241458a0b54a9fd0b14ea@.SQ
webservertalk.com...
> I figured out what the procedure does.
> Perhaps I'm not clear I want the database name and all associated data
file
> names. The following query gives me table names.
> select so.name as Table_Name,
> sfg.groupname as GroupName,
> sf.name as LogicalName,
> sf.filename as PhysicalFileName
> from dbo.sysobjects so
> inner join sysindexes si
> on so.id = si.id
> inner join sysfilegroups sfg
> on si.groupid = sfg.groupid
> inner join sysfiles sf
> on sf.groupid = sfg.groupid
> group by so.name,
> sfg.groupname,
> sf.name,
> sf.filename
> --
> Message posted via http://www.webservertalk.com

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete an Analysis Service DB?

Hi,

when i try do deploy an AS-Projekt, i get follwing error Massage:

Error 1 Errors in the metadata manager. The dimension with ID of 'v Kundeneigenschaft', Name of 'v Kundeneigenschaft' referenced by the 'v Kundeneigenschaft ~MC' cube, does not exist. Errors in the metadata manager. An error occurred when loading the v Kundeneigenschaft ~MC cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Project3.0.db\v Kundeneigenschaft ~MC.3.cub.xml'. 0 0

By the way, the related Cube is not Part of the AS-Projekt i wish to deploy. This Error above occurs on everything i do: Try to delete this Analysis Service Database, ReDeploy another AS-Projekt, Build and Deploy a new projekt and so on...

Cleanup (delete) of C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data doesn't help, some more error messages appear.

Anybody an idea?

Greetings, Torsten

Make sure you restart your Analysis Server after deleting files from the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data folder.

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

|||

Yes, thanks. Thought i've tried that - but it seem that not...

Now it works!