The database installer for SQL Server 2008R2 (November CTP) does not automatically deploy the Analysis Services database. To deploy the analysis services database perform the following steps:
  1. Start Business Intelligence Development Studio by clicking Start / All Programs / Microsoft SQL Server / SQL Server Business Intelligence Development Studio.
  2. Click File / Open / Project/Solution
  3. Navigate to the appropriate folder containing the Analysis Services database solution. If the sample databases were installed to the default location:
    1. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008R2 Analysis Services Project\standard\Adventure Works.sln will contain the solution for SQL Server Standard.
    2. C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\enterprise\Adventure Works.sln will contain the solution for SQL Server Enterprise and SQL Server Developer.
    3. Analysis Services databases are not supported on other editions of SQL Server.
  4. In the solution explorer click right on the solution (Solution 'Adventure Works') and click Deploy Solution

Last edited Oct 31, 2009 at 6:13 AM by bonniefe, version 2

Comments

julialloyds45 May 8 at 8:25 AM 
To restore a SQL Server Analysis Services Database you need to perform successful restore an analysis service database on a SQL Server 2008 Analysis Service Instance. It can easily be done using the SQL MDF file Repair Tool. It capable of repairing and restoring almost every items from the corrupt database including tables, views, stored procedures, triggers, indexes, keys, deleted records and so on.

http://sqlmdf.filerepairtool.net

rb126 Dec 19, 2012 at 12:38 PM 
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!

abdulakh Feb 29, 2012 at 8:09 PM 
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).

thakwani Jan 9, 2012 at 5:36 PM 
I used the SSAS sample database and instructions are crispy clear.

anand_raja Nov 29, 2011 at 7:24 PM 
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?

anand_raja Nov 28, 2011 at 10:55 PM 
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?

pimentones Oct 23, 2011 at 1:28 PM 
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.

eseeweb Apr 27, 2011 at 12:50 AM 
I have error on deployment but once changed service account to local system on both sql server and analysis server, it works. thanks.

RamkumarKrishnan Apr 9, 2011 at 4:14 AM 
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.

coolprasanth Apr 6, 2011 at 3:12 PM 
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

terryclancy Apr 6, 2011 at 8:05 AM 
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)

rwf Mar 18, 2011 at 1:35 AM 
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?

itcouple Feb 2, 2011 at 10:45 AM 
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

sheky Dec 28, 2010 at 1:42 PM 
Deployment failed with message:
Internal error: Failed to generate a hash string.

kiranbhogadi Dec 2, 2010 at 11:48 PM 
FYI, I installed the databases and deployed the cube without any issues.

Cheers,
Kiran

mjperplex Dec 2, 2010 at 7:11 PM 
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).

mjperplex Dec 2, 2010 at 6:28 PM 
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).

Shuchi Nov 2, 2010 at 3:39 PM 
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

5849 Oct 9, 2010 at 7:57 AM 
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

vijaykumar13 Oct 5, 2010 at 9:14 PM 
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

aceboku Aug 31, 2010 at 4:34 PM 
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!

Hyousef Jul 22, 2010 at 12:18 AM 
MinorMata : your solution worked, i used to have as as network service, but changing it to local did the trick

lmsc Jul 14, 2010 at 10:32 PM 
The deployment is succesfull but i have 61 warnings! something wrong?

cbaker0220 Jul 7, 2010 at 11:47 PM 
The LocalSystem account is what worked for me too, thanks MinorMata!

jagksg Jul 7, 2010 at 6:29 PM 
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!

MinorMata Jun 28, 2010 at 4:47 AM 
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.

Saeedalhs Jun 22, 2010 at 8:16 AM 
SQL Server Analysis Services must be running, check its status from SQL Server Configuration Manager.

InternetUser999 May 26, 2010 at 11:15 PM 
Following these instructions results in a failed deployment with 61 warnings and 1 error.

ErikBo Mar 21, 2010 at 5:48 PM 
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

JasonWHowell Jan 12, 2010 at 11:29 PM 
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.

JasonWHowell Jan 12, 2010 at 11:23 PM 
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.

smstrong Dec 15, 2009 at 5:44 AM 
Step 4b. Change the server name in Adventure Works.ds if using a named instance. e.g. localhost => myserver\myinstance