How to allow normal user (not Administrator) deployment of SSAS Cube?

Hello,

how to allow normal user (not Administrator) deployment of SSAS Cube? It says always that normal user cannot create new objects.

Andrey.

In order to create a database you need to be an Analysis Server admin.

In order to create cubes, dimensions, partitions you need to be database administrator.

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

|||

If you want your user to work with a BI project (that he will edit in Visual Studio and then deploy), these are the things to do:

- you need to create the database yourself (empty database) as server admin

- you need to give user admin rights for the database (using SQL Management Studio, create a Role)

- then user can deploy the BI project. Important: his project needs to contain the Role definition specifying him as admin (otherwise when he deploys the project, he will cut his access). To ensure you have the Role definition, you could initially create the BI project out of the live empty database (from VS, chose New Project, Import Analysis Services 9.0 Database wizard; then make sure that the newly create BI project specifies in its properties your database name).

Adrian Dumitrascu.

|||

I created a Cube from my DataWarehouse.
Under Administrator account I can deploy the Cube and everything is fine.
Then I want to allow normal user (programmer) to develop and deploy it in Business Intelligence Development Studio. This user has no Administrator rights.
So, I receive something like "User _xxx_ has no rights to change _yyy_-objects or an object does not exist" (my translation from German).

Granting user all rights to the DataWarehouse, creating Role in SQL Management Studio for DataWarehouse does not help. What really helps: creating Administrator Role for user in SSAS project and deploying project to Analysis Service under Administrator! So, this role will be uploaded to the service. After this, service recognizes user as an Administrator!
If you try to deploy project for the first time under user, then the role will not be uploaded (no rights). If you try to create role in SQL Management Studio instead of in BI project, then user will update service, remove role from service (since there is no role in project) and after this will be non-recognizeable.
It is possible also to create role in SQL Management Studio and then identical role in BI project. And then user can deploy.

Thanks for help! It gave a clue :)

How to add parameters to a MDX query in RS 2005?

I'm currently struggling with parameters when doing reports based on SSAS
2005 in RS 2005.
How do I add a new parameter to an MDX query, after having built the query
using the wysiwig query builder? I could probably start all over again to
use the query builder, but I can't figure out how to create semi-dynamic
queries like I did with RS 2000 / AS 2000.
And is there a way to choose if a parameter should be multivalue when adding
it as a parameter?
Any good books focusing on Reporting Services and Analysis Services 2005?
Most of them focus on SQL based reports.
Kaisa M. Lindahl LervikHope you have tried puting "@." to the parameters this is basics and go to
report parameter from menu and can define multi value.
A book from "Brian Larson" of SSRS and SS BI is good book to start with and
your online help and samples are best resources for learning.
Amarnath
"Kaisa M. Lindahl Lervik" wrote:
> I'm currently struggling with parameters when doing reports based on SSAS
> 2005 in RS 2005.
> How do I add a new parameter to an MDX query, after having built the query
> using the wysiwig query builder? I could probably start all over again to
> use the query builder, but I can't figure out how to create semi-dynamic
> queries like I did with RS 2000 / AS 2000.
> And is there a way to choose if a parameter should be multivalue when adding
> it as a parameter?
> Any good books focusing on Reporting Services and Analysis Services 2005?
> Most of them focus on SQL based reports.
> Kaisa M. Lindahl Lervik
>
>|||I had problems with the whole strtomember syntax. Just making a parameter
didn't put this new parameter into the query, probably because I'd gone from
the editor to hand editing the query, and I couldn't figure out the syntax
of a new parameter.
Anyway, I found out how to make dynamic mdx queries the old way*. I'll check
out the book by Brian Larson, there's probably a more modern way of making
mdx based reports than the old school way I'm used to. ;)
*= Make a new data source with OLE DB instead of Analysis Services, and then
connect it to Analysis Services 9.0. Gives you back the good old text only
editor, which I prefer right now.
Kaisa M: Lindahl Lervik
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FFF7458A-F34C-4CC7-8CA0-55C59B8C0106@.microsoft.com...
> Hope you have tried puting "@." to the parameters this is basics and go to
> report parameter from menu and can define multi value.
> A book from "Brian Larson" of SSRS and SS BI is good book to start with
> and
> your online help and samples are best resources for learning.
> Amarnath
> "Kaisa M. Lindahl Lervik" wrote:
>> I'm currently struggling with parameters when doing reports based on SSAS
>> 2005 in RS 2005.
>> How do I add a new parameter to an MDX query, after having built the
>> query
>> using the wysiwig query builder? I could probably start all over again to
>> use the query builder, but I can't figure out how to create semi-dynamic
>> queries like I did with RS 2000 / AS 2000.
>> And is there a way to choose if a parameter should be multivalue when
>> adding
>> it as a parameter?
>> Any good books focusing on Reporting Services and Analysis Services 2005?
>> Most of them focus on SQL based reports.
>> Kaisa M. Lindahl Lervik
>>

How to add calculated Facts in Measure Group

hi,

I am new to SSAS. i am working up on cube. I want to add calculated fact in the cube.

like... Suppose we have fact table in which we have fields like (ID, Status)

and status can have 3 values (1,2 ,3) . My requirement is Count of rows having Status 1 + Count of rows having status 2.

How to accomplish this task.

Additionally if dimension is required suppose i have Status(statusID) dimension also.

Please provide me urgent solution!

Thanks in advance.

Mandip

Hi Mandip,

before I start I beg for pardon, because I am working on a German version of SSAS.

You can put two new named calculations in Your datasource view.

First.

CASE WHEN Status = 1 THEN 1 ELSE NULL END

(suggested columnname Stat1)

Second:

CASE WHEN Status = 2 THEN 1 ELSE NULL END

(suggested columnsname Stat2)

Now You can use these two new columns as new measures (count or sum).

cheers

B.

|||

Thanks very much!

I know if the status entries are going to be static this would work.

but in my case at anytime in future we can have new status suppose (4)...

then can there any other method by which i need not to add new columns in fact table... and still i can be able to take calculated fact? Means i don't want to change design structure of cube.

Mandip

|||In the situation you describe, status should be implemented as a dimension. As a rule of thumb any time you want to report amounts "by" something, it is a good indicator that you should implement it as a dimension.

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

http://msdn2.microsoft.com/en-us/library/ms145486.aspx

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.

How to Add a Calculated Column in Report Builder using a Model Based on a SSAS Cube

I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.

Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?

Thanks,

Some functions are not supported for calculated fields when running against an SSAS cube, in particular the aggregate functions like Sum, Count, Average, etc. The error message should provide some details on this.

Hope that helps!

|||

The support for calculated fields in an SSAS model is better than Bob lets on. Most functions work. The trick is that you need to install Excel 2003 on the Analysis Services server. (Not sure if other versions like Excel 2007 work, but I know Excel 2003 does the trick.)

Report Builder builds MDX that use Excel functions, and for those functions to succeed, Excel has to be installed on the server.

If you agree with me that this is a terrible architecture, vote for this issue:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124864

|||Are there any online documentation about this? Our IT policy is not to install office or office application on SQL Server boxes.|||

Common policy. Vote for that issue at connect.microsoft.com and maybe MS will change this in Katmai.

As for documentation:

http://msdn2.microsoft.com/en-us/library/ms145486.aspx

And documentation of the excel functions that are supported. (Don't know if there's a 2005 version of this page, but they're the same):

http://msdn2.microsoft.com/en-us/library/aa178231(SQL.80).aspx

I suppose one workaround is to code the Excel functions yourself and register them as specified here:

http://geekswithblogs.net/darrengosbell/archive/2006/12/13/100998.aspx

If you have interest in going that route, let me know because there are some gotchas.