I finally got this working! Security in Analysis Services (and SQL Server (and Windows)) is pants!

In the end I had opened up schema, database and server security to allow every user I could see to do absolutely anything (on my test server!) and it still wouldn't deploy, with errors about not having permissions on DimSalesReason (and sometimes on the fact table).

At the point of despair, I changed the Impersonation Information settings (second tab on Data Source popup).

It was on "Use the service account" by default, and I'd granted the service account dictator-level powers, but it failed to deploy. I tried "Inherit" and that also failed. Finally I tried "Use a specific Windows user name and password" and typed in the user name and password of the current user, and it deployed!!!

Surely setting things like this up shouldn't be so difficult? No wonder people end up with insecure systems after they've flipped every setting off in a blind attempt to get the blasted thing to work!

It works good after creating login and user in AdventureWorksDW2008R2 database for SQL Server Analysis Services service account before deploying the project. I assign db_owner, but suppose it will work with less privileges (for example db_datareader as recommended in instructions).

I used the SSAS sample database and instructions are crispy clear.

When I try to deploy the standard Adventure Works .sln, I get the following errors:

Error 1 Internal error: The operation terminated unsuccessfully. 0 0
Error 2 OLE DB error: OLE DB or ODBC error: Invalid object name 'DimCurrency'.; 42S02. 0 0
Error 3 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Currency', Name of 'Source Currency' was being processed. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while the 'Source Currency' attribute of the 'Source Currency' dimension from the 'Adventure Works DW 2008R2 SE' database was being processed. 0 0
Error 5 Server: The operation has been cancelled. 0 0

Any advice?

I cannot deploy, I am getting the following error:
OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\LOCAL SERVICE'.; 28000; Cannot open database "AdventureWorks2008R2" requested by the login. The login failed.; 42000.

Does anyone have advice?

I'm having problems with login in the connection manager of bids... I'm supplying server name and instance name and using windows authentication and can´t get a sucessful connection test.

I have error on deployment but once changed service account to local system on both sql server and analysis server, it works. thanks.

I changed Service Account for SQL Server and Analysis Services from Network Service to Local System and only then was I able to successfully Deploy from BIDS.

HI,
I am a total newbie to SQL, i downloaded sql express with advanced services and followed instructions on http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites%20for%20SQL%20Server%202008R2 and this page everything goes smoothly till i click on "deploy" on the Adventure Works DW 2008 then a "save" dialogue box appears and when i click "save" it prompts if i want to replace the file and when i give "yes" it says access denied......... i dont understand why, i am the administrator.............the connection test was successful and the deployment in the properties of the solution is the correct server name............Pls help i've been at it for the past 3 hrs.........

Thnx in advance

FYI I got the error Internal error: The operation terminated unsuccessfully.
I changed Service Account for SQL Server and Analysis Services from Network Service to Local System and it solved the problem (not sure why ?)

Terry Clancy (Microsoft)

What is the next step here? I have successfully deployed the Analysis Services database but I can it is not visible in Analysis Server. Also, how do you publish this through SSRS?

Hi

I opened solution in BIDS using correct path (version + edition). I changed datasource from localhost to my instance name, also changed impersonisations as I have different security settings on my laptop.

Click process and worked first time!
Regards
Emil

Deployment failed with message:
Internal error: Failed to generate a hash string.

FYI, I installed the databases and deployed the cube without any issues.

Cheers,
Kiran

Oh - stupid me. There are TWO databases. "Adventure Works" and "Adventure Works DW" I had only created "AdventureWorks2008R2" and attempted to deploy the Analysis project against that one - which results in the "Sequence Clustering ~MC...." errors. After installing "AdventureWorksDW2008R2" and making sure the project points to that DB, the deployment worked without any issues.

I must have missed that detail in the manual install instructions. user error!

Look in the folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Data Warehouse" and load the instawdwdb.sql into SQLMgtStudio, select "SQLCMD Mode" from the Query menu, make the :setvar edits as described, and execute that script.

You can then grant the MSSQLServer account dbo as mentioned above or do security your own way (probably good enough for a sample). I tend to never give the NT Service account admin/dbo access on a production server just in case a virus/worm wanders by).

I had to delete the Data Source. It was originally named "Adventure Works DW 1" in my project which caused many errors during both compile and attempts to deploy. After creating a new one called "Adventure Works DW" the project could be compiled. However I'm still having Deployment problems ("OLE DB or ODBC error: Invalid object name 'dbo.DimDate'.; 42S02") which I'm debugging right now. I also had to create this DB by using the manual instructions because I too received the error about a bad SMO when running the MSI package.

Needless to day this demo package isn't simple to install. It is aptly named "Adventure."

Win7 Ultimate x64, SQL Server 2008 R2 x64 (upgraded from SQL Server 2008).

I deploy the Analysis Services database unsuccessfully after I had downloaded the sample database for SQL Server 2008 R2,error message as below,
Error 1 metadata Administrator errors. ' Sequence Clustering ~ MC ' Cube identifier of the reference to ' Sequence Clustering ~ MC-Order Number ', the name is ' Sequence Clustering ~ MC-Order Number ' dimension does not exist. There is an error in the metadata Manager. From the archives ' \\? \C: \Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2 SE.0. db\Sequence Clustering ~ MC.1.cub.xml ' loading Sequence Clustering ~ MC cube. 0 0

I deploy the Analysis Services database unsuccessfully after I had downloaded the sample database for SQL Server 2008 R2,error message as below,
Error 1 metadata Administrator errors. ' Sequence Clustering ~ MC ' Cube identifier of the reference to ' Sequence Clustering ~ MC-Order Number ', the name is ' Sequence Clustering ~ MC-Order Number ' dimension does not exist. There is an error in the metadata Manager. From the archives ' \\? \C: \Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2 SE.0. db\Sequence Clustering ~ MC.1.cub.xml ' loading Sequence Clustering ~ MC cube. 0 0

Thanks MinorMata
Deployment had error, but as suggested by MinorMata changes to Localsystem and the deployment was a charm with a green check mark Deployment Completed Successfully.
Vijay

Deployment failed. Adjusted Adventure Works.ds to use "Log on to the server" - "Use SQL Server Authentication". I then inserted approriate user name and password which was different from my Windows Authentication. Deployment worked like a charm. Thanks!

MinorMata : your solution worked, i used to have as as network service, but changing it to local did the trick

The deployment is succesfull but i have 61 warnings! something wrong?

The LocalSystem account is what worked for me too, thanks MinorMata!

Installed per instructions with no issues. SSAS installed and deployed successfully. Done
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
Great Job!

If yuu have and Deplymend error, please try to change how the Anslysis service start.
I tried changed to LocalSystem account and it works. The Deployment become succesfull.

SQL Server Analysis Services must be running, check its status from SQL Server Configuration Manager.

Following these instructions results in a failed deployment with 61 warnings and 1 error.

7. Note that for the Deploy Solution to succeed the SQL Server Service Account should be granted rights on the databases used by the solution. e.g.:
USE AdventureWorksDW2008R2
GO
EXEC sp_addrolemember 'db_owner',
'<your_sql_server_service_account>'
GO

4b. After opening the solution, in your Solution Explorer, Find the "Adventure Works.ds" data source. Double click on it. See the Connection String box, and click the [Edit...] button just below. Supply your SQL Server (database engine) server and instance name (if its a named instance). Click the Test Connection Button. If the test succeeds, click OK and OK to save the changes. Go to step 4c.

4c. After opening the solution, right click on the solution in Solution Explorer, choose Properties. On the Adventure Works DW 2008 SE Property Pages dialogue, choose the tree item for Deployment. Change the Target - Server property to your AS server name and instance name.

Step 4b. Change the server name in Adventure Works.ds if using a named instance. e.g. localhost => myserver\myinstance