Installed SQL 2008R2 Developer Edition on a standalone workgroup attached PC 64-Bit running Windows 7 Professional. The installation of the SQL 2008R2 went without a hitch. Installed using mixed authentication both Windows and SQL accounts for admin. I have the SQL services set with their own unique user/password. These accounts are local Window user accounts. When installating SQL Developer I set both my user account and SQLSERVERSSAS as admins in the Analysis Services installation step.
I have both my user account with is both admin on the PC and admin within SQL mapped to the AdventureWorksDW2008R2 (and all the rest of the databases) as a db_owner. I went ahead (in frustration) and set the SQLSERVERSSAS user mapped to the AdventureWorksDW2008R2 as db_owner as well.
Going into BIDS and running through the installation of the Adventure Works solution file. I set up the connections correctly and they tested OK. I set the Deployment properties with the correct "Server" (local computer name) and "database": AdventureWorksDW2008R2. I run deploy and get errors after the second command gets a ways into it. After it errored out, I have stopped and read one suggestion was to on the same menu as DEPLOY to run REBUILD, change the OPTIONS on the DEPLOYMENT menu tab under solution properties to FULL for processing option and DEPLOY ALL for deployment mode. Did that - still errors.
I will guess I still have a permissions issue somewhere. The error messages are not a great help to point to the "particular" issue(s) that is actually causing "those" errors. But only God knows ... or maybe you :) - hope someone can help out. I really want to get on studying for my cert rather than continue to wipe the blood off my forehead hitting it on this brick wall. Thanks
Hm, strange. Only idea I can think of at the moment is with a Window 7 box. You'll have to run BIDS as an administrator to deploy to localhost but I would have thought you would have been warned when you opened the project. From the Start menu, navigate to the BIDS icon, right-click on the shortcut, and select the Run As Administrator option from the popup menu. Then open the AdventureWorks solution and try to deploy.
Martin thanks for the reply and suggestion.
I did run BIDS as an administrator. From the Data Source Designer on the Impersonation Information, I did specify the specific user name and password instead of leaving the selection "Use service account". The specific user name and password if the same user name and password I am using for the SSAS service account in the SQL 2008R2 set up. Interesting that this time a pop up window requested me to enter the password for the user account I was using ... although I had submitted the correct password on the Impersonation Information. Don't know if that means anything but thought I would mention any different activity.
I again chose on the same menu as DEPLOY to run REBUILD, change the OPTIONS on the DEPLOYMENT menu tab under solution properties to FULL for processing option and DEPLOY ALL for deployment mode. Did that - still errors. :(
Generally, impersonation information should be left as "Use service account". Changing impersonation to a specific user requires the password for that user be stored somewhere for security reasons, SSAS deployment will not store. Therefore, that's the reason you're getting prompted for the password.
For the initial deployment of an SSAS database, the Default ProcessingOption and the Full ProcessingOption will perform the same operation. For very small cubes, like AW, I usually leave the ProcessingOption as FULL to avoid potential duplicate member issues that may get masked by a Default processing option.
Other possible suggestions. Make sure you have all the most recent patches. Make sure you have the correct AW download for the SQL Server version you're trying to deploy to.
BTW, what errors are you getting? I'll be around for awhile but then offline for most of the rest of the day.
Martin again thanks.
You wrote, "Generally, impersonation information should be left as "Use service account"." Just trying different things to see if I could get around this issue if it is permissions (although buried if it is). I read another blog where they were talking about the impersonation selection. So I changed it back as you suggested.
The Processing Option is set to FULL. Just did that after I read that in another blog, detailed in my first post on this topic. When I uninstalled everything yesterday, I made sure I had the latest download of the AW sample databases.
Here is the version I am running: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) . From searching it appears that Service Pack 1 is the latest for 2008R2.
Here is where BIDS is erroring out:
Processing Dimension Attribute 'Fiscal Year' failed.
Start time: 3/18/2012 2:16:27 PM; End time: 3/18/2012 2:16:30 PM; Duration: 0:00:02
Error Messages 1
OLE DB error: OLE DB or ODBC error: Invalid object name 'dbo.DimTime'.; 42S02.
Here is the list of errors (6):
Error 62 Internal error: The operation terminated unsuccessfully. 0 0
Error 63 OLE DB error: OLE DB or ODBC error: Invalid object name 'dbo.DimTime'.; 42S02. 0 0
Error 64 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Date' was being processed. 0 0
Error 65 Errors in the OLAP storage engine: An error occurred while the 'Fiscal Year' attribute of the 'Date' dimension from the 'AdventureWorksDW2008R2' database was being processed. 0 0
Error 66 Server: The operation has been cancelled. 0 0
Error 67 Errors in the OLAP storage engine: An error occurred while processing the indexes for the v Assoc S~2 ~MG partition of the v Assoc S~2 ~MG measure group of the Market Basket ~MC cube from the AdventureWorksDW2008R2 database. 0 0
To this issue of yours, here I would give you some advises:
1. Firstly please make sure you give the enough permission to Services Account on your database server, you can open your database engine using admin account, then click the Security folder, then give the serveradmin to NT AUTHORITY\NETWORK SERVICE
2. Have you modified some records in the DW database? If so I would recommend restore the DW database.
This is the similar thread hope it give you helpful reference: http://social.msdn.microsoft.com/Forums/eu/sqlanalysisservices/thread/5b820adf-165a-49d6-850a-ee3c855f8b2a
TechNet Community Support
Thanks for the suggestion:
"... you can open your database engine using admin account, then click the Security folder, then give the serveradmin to NT AUTHORITY\NETWORK SERVICE"
This is a standalone, local install, so I don't have "NT AUTHORITY\NETWORK SERVICE" but there is NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT.
I gave NT AUTHORITY\SYSTEM serveradmin rights. Out of frustration, I also gave the two NT SERVICE accounts serveradmin rights as well.
You asked, "Have you modified some records in the DW database?" Well if you mean "REBUILD" in BIDS for the target database AdventureWorksDW2008R2, then yes. Before I tried you suggested "permissions" changes, I did REBUILD again before I DEPLOYED.
However, it still ended in errors.