Configuring BIDS 2008 / 2008 R2 and Visual Studio 2008 to work with Database & Business Intelligence Projects and Team Foundation Server (TFS)

Working with the SSIS, SSAS, and SSRS Business Intelligence projects along with Database projects in the same Visual Studio 2008 development environment can be challenging as there is no clear documentation how to enable both set of projects.  Database projects are supported by Visual Studio 2008 Developer, Professional and Team System Database Editions. The  SSIS, SSAS, and SSRS Business Intelligence projects are supported by the Visual Studio 2008 shell that comes with SQL Server 2008 / 2008 R2.

If you have BIDS 2008 / 2008 R2 installed by itlself and you try to open a database project you get an error saying that this type of project is not supported. You get the same error if you have Visual Studio 2008 installed by itself and try to open one of the Business Intelligence projects. The reason behind these errors is that BIDS 2008 / 2008 R2 does not come with the database project template and Visual Studio 2008 does not come with the SSIS, SSAS, and SSRS Business Intelligence project templates.

So how do you get both set of templates in a single development environment?

In order to get both set of templates in the same development environment, you need to install both Visual Studio 2008 and BIDS in the right order as described in the following steps:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media

At this point should have a working Visual Studio environment with both set of projects.  A quick way to verify that the installation was successful, is to take a look at the Visual Studio version in the Help|About page. It will show as Version 9.0.30729.4462 QFE as shown in the picture below. QFE stands for Quick Fix Engineering.

When you launch Visual Studio 2008 or BIDS 2008 / 2008 R2 you will be prompted to select the default environment settings. Since I work with the Business Intelligence projects most of the time, I select the Business Intelligence environment. This is an option presented only the first time you open Visual Studio. To change this setting, use the Import and Export Settings wizard, which is available on the Tools menu. For more information on choosing and changing the environment settings go to http://msdn.microsoft.com/en-us/library/6k364a7k(v=vs.90).aspx.

Connecting to TFS 2005 and 2008

To connect to TFS 2005 and 2008 you will need to download and install Team Explorer 2008 from http://www.microsoft.com/en-us/download/details.aspx?id=16338.

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2005 or TFS 20008 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5.  Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338.

Connecting to TFS 2010

In some cases, the solutions & projects are 2008 / 2008 R2 projects but the repository is TFS 2010 or TFS 2012. Even if you installed Team Explorer 2008 you will get an error when trying to connect to a TFS 2010 or TFS 2012 server. The reason for this is due to the fact that Team Explorer 2008 does not support full URL paths (i.e. https://myservername/mytfs/mycollection) in the TFS server name section.

To fix this issue you will need to download and install the Forward Compatibility Update Team Explorer 2008 SP1 for Team Foundation Server 2010 (http://www.microsoft.com/en-us/download/details.aspx?id=10834).

Notice that this update can only be applied to Team Explorer 2008 SP1 (Service Pack 1). The curve ball here is that there is no Team Explorer 2008 SP1 available  as a download. In order to turn Team Explorer 2008 into Team Explorer 2008 SP1 is to apply the Visual Studio 2008 SP1 to it. This means that if you followed steps 1 to 4 above, you will need to repeat Step 3 (Re-install Visual Studio 2008 SP1).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2010 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install the forward compatibility update for Team Explorer 2008 SP1 for Team Foundation Server 2010
   http://www.microsoft.com/en-us/download/details.aspx?id=10834

Connecting to TFS 2012

To connect to TFS 2012 the following update is required :

Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview (http://www.microsoft.com/en-us/download/details.aspx?id=29983).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2012 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview
   (http://www.microsoft.com/en-us/download/details.aspx?id=29983

Hopefully this post helps you get your environment all squared away.

SQL Server Upgrade error: It is not possible to change the SQL Server features to be upgraded in this release. Validation errors. There are no features selected for upgrade.

On a recent project I was asked to “upgrade” a licensed SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Enterprise Edition. I have done this multiple times with no issues. The “kicker” was that the upgrade required a 48-hour turnaround time but no installation media for SQL Server Enterprise was going to be available in that same timeframe due to a delay in the procurement process.

In order to meet project requirements and gain some time, a team member suggested we go ahead and do the “upgrade” using a downloaded copy of SQL Server 2008R2 Evaluation Edition since Evaluation Edition contains all the Enterprise features that were required. Once the installation media for SQL Server 2008R2 Enterprise arrived, we were to simply upgrade the Evaluation Edition to Enterprise Edition.

My immediate response was that going from a licensed edition to an evaluation edition was not possible. It is not a “natural” upgrade path and is actually considered a “downgrade” as you are going from a licensed edition to an unlicensed edition. As a professional I had to back this statement up with official Microsoft documentation. As a geek no matter what the documentation said I had to try it myself to see what happened. So I did both.

Documentation
A quick search of Books on Line on SQL Server 2008R2 led me to the following MSDN article titled “Version and Edition Upgrades” (http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx) . This article is great as it outlines all the supported upgrade scenarios for all versions and editions of SQL Server. A quick scroll towards the bottom shows that the only two upgrade paths supported for Standard Edition is Enterprise and Datacenter. As a matter of fact this article shows that there is no support going from any other edition to Evaluation Edition.

Additionally, there are a couple of footnotes at the bottom. An important footnote to take a look at is footnote No. 2. Although the statements in footnote No. 2 are within the context of failover clusters, some of them also apply to stand-alone installations. In this footnote it is clearly stated that SQL Server 2008R2 Standard to SQL Server 2008R2 Evaluation upgrade is not supported.

Test

I wanted to see for myself the error message generated if I tried to carry out the upgrade anyways. Of course I did not try to do this on the actual client’s environment. That is a big no no! I decided to spin up my own sandbox virtual environment. The steps I followed on my test were:

1. Install SQL Server 2008R2 Standard Edition (Database Services, Analysis Services, SSMS, BIDS)
2. Reboot
3. Upgrade SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Evaluation Edition using the Upgrade Setup Wizard from Installation Center.

As I went through the Upgrade Setup Wizard I got the following validation error as expected:

In summary, you cannot upgrade from a licensed SQL Server edition to SQL Server Evaluation Edition. It is perfectly possible though, to upgrade from SQL Server Evaluation Edition to other licensed SQL Server editions. For more details on the supported upgrade paths refer to MDSN article: http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx.

Master Data Services Error: Resolving from a long-running stucked Staging Batch with Status “Queued to Clear” or “Not Running”

In some ocassions,  you may run into a runaway, long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.

The issue has to do with Service Broker, either due to a timeout or notifications not being received or received incomplete.  You can confirm the issue when you query the queue [mdm].[microsoft/mdm/queue/stagingbatch]. You will see one or more messages in this queue. After searching through a couple of forum threads I found the solution to be fairly simple: rollback transactions an re-enable Service Broker. The steps are as follows:

1. Set the MDS database in single user mode and rollback uncommitted transactions.

   ALTER DATABASE [MDS] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Re-enable Service Broker on the MDS database.

   ALTER DATABASE [MDS] SET  ENABLE_BROKER WITH NO_WAIT;

3. Set the MDS database back in to “regular” multi-user mode.

   ALTER DATABASE [MDS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the MDS database is brought back online, all queued messages should be cleared out and MDS database will be able to communicate normally with Service Broker.

You can check Books on Line for more information on the difference between “NO WAIT” and “WITH ROLLBACK IMMEDIATE” options when issuing the ALTER DATABASE command here. Pinal Dave has a good post about the difference between these two options as well here.

Hope this post has helped you. Your feedback is greatly appreciated.

The CASE of the missing non-NULL T-SQL Error: None of the result expressions in a CASE specification can be NULL.

Recently, while doing some data scrubbing for a customer I got an interesting error in SSMS with one of my CASE statements. The error received was:

None of the result expressions in a CASE specification can be NULL.

It was a long T-SQL script (and a long night) and could not understand the error message. After a couple minutes BinGling (Google +Bing) around the web, I still could not find the root cause, so I decided to comment out every single CASE statement and run one by one until I pinpointed the offending syntax.

 

To my surprise, the issue was very simple, yet undocumented. The T-SQL syntax I was writing was somewhat as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

After executing this script the error mentioned above is raised. The same error is raised even if we rewrite the script as a simple CASE statement:

 

SELECT ProductID,

CASE SerialNumber

WHEN ‘0’ THEN NULL

WHEN  ‘-‘ THEN NULL

WHEN ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

So what if we add an ELSE statement as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE NULL

END AS SerialNumber_Clean

FROM tblProduct

 

We still get the same error. The issue as the raised error describes can be narrowed down in that you cannot explicitly return NULL for every resulting expression. There must be at least one non-explicit NUL in the resulting expression. For example, we can rewrite the script correctly as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE SerialNumber

END AS SerialNumber_Clean

FROM tblProduct

 

As you can observe, adding an ELSE statement that does not return another explicit NULL makes the script work and as a matter of fact, should be the correct syntax. Interestingly, ELSE is not necessary to make the script valid. The script will run even without an ELSE expression, but only if at least one of the resulting values is not an explicit NULL. The following script runs successfully (notice no ELSE):

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’  THEN NULL

WHEN SerialNumber = ‘No Serial’ THEN ‘Non-Serialized Product’

END AS SerialNumber_Clean

FROM tblProduct

 

Books on Line should include this caveat on their documentation. http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

The following sections should be modified as follows:

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression. If no else_result_expression is specified or if else_result_expression is set to return an explicit NULL, at least one result_expression has to be specified to return a non-explicit NULL.

 

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion and at least one of them should be specified to return a non-explicit NULL.

SSAS errors: DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

When attempting to browse an Analysis Services cube you may be presented with the following error message:

DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

The error message is self-explanatory. There is a Default Member being referenced and was not found. But, pinpointing where this “DefaultMember” is being referenced may me a bit challenging.

The Root Cause

The root cause of this issue is quite simple: a deleted or renamed measure is still being referenced by its original name. Some of the areas where this deleted or renamed default measure could still be referenced by its original name include:

1. Cube DefaultMeasure property
2. Perspective DefaultMeasure property
3. Calculations

The fix

The fix may involve adding the measure back or renaming the measure to its original name or digging through several areas of the cube where the deleted or renamed measure is still being referenced by its original name. Here are your options:

1. Add or rename the measure back to its original name.
This may not be the desired option, but it could be the easiest and quickest way to fix the issue.

2. Modify each Perspective’s DefaultMeausure property
Unfortunately, deleting or renaming a measure does not “trickle-down” where it is being referenced in a perspective. The original measure’s name will remain.

3. Change the cube DefaulMeasure property to the new measure name.
Similar to Perspectives, deleting or renaming a measure does not “trickle-down” its new name to the cube’s DefaultMeasure property. You will need to select a new measure in the DefaultMeasure property.

4. Modify the cube XML code
This option may be a little daunting to some, but in the end it is the quickest way to make sure the original measure’s name is replaced by the new measure’s name. To do this, in Solution Explorer right click on the Cube’s name and select View Code. The XMLA definition will open. At this point, press CTRL+H or click on Edit->Find and Replace->Quick Replace.The Quick Replace window will open. Type the original measure’s name in the “Find what” textbox and type the new measure’s name in the “Replace with:” textbox.

SSRS Error: Report Preview displays Access to the path ‘…\bin\Debug\Report.rdl’ is denied

While working on Reporting Services at a client site I came across this error when trying to preview a report in BIDS:

The report preview failed because the report could not be built. Read the errors, warnings and messages in the Error List window for specific build failures.

An error was raised in the Error List window below with the following message:

Access to the path ‘C:\Users\Jose\Documents\Visual Studio 2008\Projects\Report Project\bin\Debug\Report.rdl’ is denied

The root cause why this happens is unclear to me, but it seems that the Debug folder’s permissions get changed to Read Only for no apparent reason as can be seen on the screenshot below:

The workaround is to uncheck the Read-only attribute checkbox for the entire Debug folder or simply delete the Debug folder. BIDS will recreate this folder when you click on the Preview tab. If I come up with the root cause of this issue I will update this post.

The development environment is:
– VMWare virtual machine
– Windows Server 2008-R2 64-bit
– SQL Server Reporting Services 2008-R2 64-bit

SSAS errors: Errors in the metadata manager. The dimension with ID of ‘xxx’ referenced by the ‘yyy’ cube, does not exist.

When deploying or processing a cube or dimension you may encounter an error similar to this:

Errors in the metadata manager. The dimension with ID of ‘Student Financial Aid ~MC-Student AK’, Name of ‘Student Financial Aid ~MC-Student AK’ referenced by the ‘Student Financial Aid ~MC’ cube, does not exist.
Errors in the metadata manager. An error occurred when loading the Student Financial Aid ~MC cube, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\SSAS Labs.0.db\Student Financial Aid ~MC.1.cub.xml’.
(Microsoft.AnalysisServices)

If you try to process, deploy or delete this cube database you will get the same error over and over. You may even get this error when trying to deploy or process a totally separate cube database or dimension in another project or solution.

Basically, there has a been a corruption of the XML file that holds your cube definition possibly because of conflicting dimension definition between your cube structure inside your project and the deployed cube database or changes in the underlying source schema. The exact reason why this happens is a little unclear as I have not been able to replicate the circumstances why this happens. I have come across this issue only twice. The purpose of this post is to show you how to get rid of the cube database, allowing you to re-deploy it from your BIDS project.

First, stop the SQL Server Analysis Services service from the Windows Services console,  from SQL server Configuration Manager or from the command prompt using net stop <service> (or even from PowerShell) . Once the service  has stopped rename or delete the folder where the cub.xml file resides as specified on the error window.

In my case, I need to rename or delete the folder “SSAS Labs.0.db” as specified on the third sentence: “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\SSAS Labs.0.db\”. You can rename this folder whatever you want, such as “XXX SSAS Labs.0.db” or simply delete it.

Once you rename or delete the folder, restart the SQL Server Analysis Services service. Connect to your SQL Server Analysis Services instance via SQL Server Management Studio (SSMS) and delete the cube database. Now you should be able to re-deploy your cube database from your BIDS project.

Once I replicate the circumstances that cause this issue, I will do a follow-up post. Make sure to check back or subscribe to my blog.

*** Update 5/10/2011: Apparently this is a SQL Server 2008 R2 issue when re-deploying several times in a short period of time. I was advised this issue should be corrected in Service Pack 1. If you are having this issue in SQL Server 2008 R1 or still experiencing it after installing SQL Server 2008 R2 Service Pack 1 please comment.

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

.
3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

SSAS errors: Logon failure / Cube process failure

Often I encounter this error myself when trying to process an SSAS cube at a client’s site or get asked by customers and friends how to solve this error. Although it may seem really obvious,  not everyone may understand “what” is rejecting their credentials or “what” are they authenticating against.

Simple. The data source.

So, what credentials are being passed through to your data source?

At this point you may answer: “my Windows credentials” or “credentials of the current user.” This is not always the case.

Impersonation
Your Windows credentials are used to create the connection strings to your data source at design-time. During processing and run-time, Analysis Services needs an account to be able to reach the data source. Keep in mind that you will not always process Analysis Services cubes manually within BIDS or SSMS. Once the Analysis Services cube is initially deployed, in most environments you will have scheduled SSIS packages that will re-process the cubes every night. In order for Analysis Services to be able to read the data from your data source, it needs some form of credentials to pass through. This is also known as impersonation.

In Analysis Services 2008-R2, there are several impersonation options:

• Use a specific Windows username and password
• Use the service account
• Use the credentials of the current user
• Inherit (or default)

To learn more about each of these options you can read MSDN Books Online at: http://msdn.microsoft.com/en-us/library/ms187597.aspx

In a nutshell, the impersonation account specified needs to have read access to your data source. For example, if your data source resides in SQL Server, the account specified as your impersonation account needs to  be mapped to your database with db_datareader role membership. It is best practice to specify a dedicated account that has limited read-only access to the database. Avoid using an account with elevated privileges such as sysadmin or db_owner.

The recommended impersonation setting is to specify a Windows username and password. If your server is joined to a domain, you will have to specify DOMAIN\Username in the User name textbox and the associated password in the Password textbox as seen below:

If you specify “Use the service account”,  it will use the account used to start the Analysis Services instance to authenticate against your data source. In this case, the service account will need read access to the data source. Keep in mind that the system service accounts Network Service, Local System and Local Service will not have access to your data source if the data source is on a different server. Typically, you will use a Windows domain account that has Log on as Service privilege in Active Directory Group Policy as your service account to start Analysis Services.

Inherit (SQL Server 2008/2008-R2, Default in SQL Server 2005) uses the impersonation mode  and credentials set in the Data Source Impersonation Info database property. To view or modify this database property, open SSMS and connect to the Analysis Services instance, expand the databases folder and right click on the database you are interested and select properties. You will now see the database properties window and the impersonation mode selected. To change the impersonation mode, click on the elipsis inside of the Data Source Impersonation Info textbox highlighted below:

Per MSDN Books Online, by default the Data Source Impersonation Info database property is set to Use the service account. This means that if the Inherit impersonation option is selected in your BIDS solution, it will use whatever is specified in the Data Source Impersonation Info database property. If this database property was set to Default as shown on the image above, then it will use the Default impersonation mode in which it will use the impersonation method that is most appropriate for the context in which impersonation is used. For more details read http://technet.microsoft.com/en-us/library/ms126693.aspx.

In summary, if you are getting a logon failure when processing an SSAS cube, check the impersonation settings of your data source. As a best practice, choose “Use specific username and password” as Impersonation option and use a dedicated Windows domain account.

The dedicated domain account should be configured at a minimmum as follows:
– Regular domain user or part of a restricted domain user group
– No password expiration policy should be applied. If not, password may expire and processing will fail at some point.
– Account should be mapped to a SQL Server database login with db_datareader role membership.

If the dedicated domain account will be used as the service account, make sure it is part of the “Logon as a service” AD Group Policy.