AS_EMEA blog

SQL Server 2012 SP1 CU3 release fixes frequent SP1 issues

2013-03-26T10:16:00Z

We normally don't comment much on Cumulative Update releases since the best source for this type of information is the blog of the SQL server release services team : http://blogs.msdn.com/b/sqlreleaseservices/ .

However, adding to the SP1 issue that was described in previous blog by Guillaume Fourrat we observed two further SSAS related problems that affected several customers.

Issue 1:

Powerpivot workbooks with corrupted table relationship(s) will raise the following error message when opened with SQL server 2012 SP1 Powerpivot add-in:

Microsoft Excel: Measure group 'MeasureGroup' missing reference dimension for existing indirect relationship to dimension 'Dimension'

Reason being that SP1 code introduced a check of table relationships as part of the model upgrade. Unfortunately the meant that affected workbooks could not be opened with SP1 Powerpivot add-in.

With the newly released SQL server 2012 SP1 CU3 Powerpivot add-in the broken table relationship(s) will be fixed automatically when workbook is opened uand you can open the problem workbooks without issues.

CU3 KB article with hotfix link: http://support.microsoft.com/kb/2812412/en-us

KB for Powerpivot workbook issue: http://support.microsoft.com/kb/2801889

From SLQ Server 20121 SP1 CU3 you would need to install one of the following Powerpivot add-ins depending on excel bitness:

64 bit: 2012_SP1_PPExcel_CU3_2812412_11_0_3349_x64

32 bit: 2012_SP1_PPExcel_CU3_2812412_11_0_3349_x86

Issue 2:

After SQL Server 2012 SP1 upgrade some of your MDX queries that used to work fine may suddenly fail with error message:

The MDX function failed because the coordinate for the 'AttributeName' attribute contains a set.

This issue is caused by a regression that was already introduced with SQL Server 2012 RTM CU2 fix.

However, since many customers do not install cumulative updates without a specific need (which is our recommendation), the issue became more widespread with SQL Server 2012 SP1 release.

If you observe this issue,then please install the SQL Server 2012 SP1 CU3 fix. Thereafter your problem queries should run successfully again.

CU3 KB article with hotfix link: http://support.microsoft.com/kb/2812412/en-us

KB for MDX query issue: http://support.microsoft.com/kb/2823086

Please note:

SQL Server 2012 SP1 CU3 fix can introduce issues with SSIS packages (and thus also with maintenance plans).

see sql connect bug https://connect.microsoft.com/SQLServer/feedback/details/781921

An "on demand" Hotfix for this problem can be downloaded from the following KB Article link: http://support.microsoft.com/kb/2832017

SQL 2012 SP1 : Recommended udpate.

2013-02-21T10:47:13Z

Hi,

Sorry for bringing up something not strictly related to our 'cube world', but more coverage can't hurt here :

If you have installed SQL Server 2012 Service Pack 1, or if you plan to, it is highly recommended to apply a specific subsequent update as quickly as possible.

This is announced on the official relase team blog, and full technical details and fix details are in KB 2793634.

The technical issue won't take place in every case, but when it does its impact increases over time, hence the recommendation to apply the update as soon after SP1 is installed as possible : better safe than sorry :)

Regards,

Guillaume Fourrat

1 hour timeout for data refreshes in Powerpivot for Sharepoint

2013-01-11T15:07:00Z

In the previous blog post I explained that we have a 1 hour timeout in Powerpivot for Excel due to the inherited default SSAS instance properties. Now you might argue that this timeout should not pose a problem in the Powerpivot for Sharepoint environment. After all we are using a standalone SSAS instance in Sharepoint mode. We can thus modify the Instance properties to our liking via the Management Studio properties GUI or SSAS instance configuration file "msmdsrv.ini".

Unfortunately this won't help you. Even if you set your timeout properties to values much higher than the expected data refresh execution time.

The data refresh job will still fail after 1 hour execution time and will see the following error message appearing:

"The data refresh job failed because it has been inactive for more than 3600 seconds"

Whats the reason?

This time the limitation does not come from the SSAS instance timeout properties. Instead we need to look at the PowerPivot Data Refresh timer job architecture. Scheduled data refreshes are triggered by a PowerPivot Data Refresh timer job that scans schedule information in the PowerPivot service application database at one minute intervals. When data refresh is scheduled to begin, the timer job tries to add the request to a processing queue on an available PowerPivot server. If there is a previous data refresh run still active , the new triggered data refresh job won’t be able to run and it’ll simply return and wait for its next chance. In order to avoid complete blocking of data refresh activity by a single "hanging" data refresh run a data refresh timeout of 3600 sec was introduced. Now when a new data refresh request comes along and is blocked by a data refresh activity that has been running for > 3600 sec, then the old data refresh run will be aborted and the new data refresh request can proceed. For the aborted long runner data refresh job you will see the above mentioned error message: "The data refresh job failed because it has been inactive for more than 3600 seconds" . Which is not necessarily a very accurate statement because the data refresh job could have been quite busy during its life time. But in the application logic everything that has been running > 3600 sec is considered "hanging" and will be aborted.

The described data refresh timeout of 3600 sec cannot be modified. But it will only be applied whenever a new data refresh request enters the processing queue. Thus we do have a workaround in that we can increase the scan interval of the Powerpivot Data Refresh Timer Job from its default value of 1 min to a value that is larger than the execution time of your long running data refresh.

Be advised that this workaround has strong side effects because your data refreshes can now only run with lower frequency and you may have many scheduled data refreshes trying to run at the same time (when scan is done). For a discussion of this problem see the "Reschedule the PowerPivot Data Refresh Timer Job" section of the online documentation article Enable and Configure PowerPivot Data Refresh (Analysis Services).

1 hour timeout for data imports in Powerpivot for Excel

2013-01-10T16:00:00Z

This article describes an issue you may face when your data import in Powerpivot for Excel takes longer than 1 hour. Typically you should not run into this type of issue because Powerpivot for Excel was designed as an interactive tool with moderate amounts of data in mind. 1 hour should give you ample time to import all the required data from a given data source.

However, if you are using queries that take a long time for execution or queries that deliver very large result sets, possibly in combination with a relatively slow provider/driver, then you may run into this limit. Typically we have seen this type of problem when trying to import large amounts of data (> 10 million rows) from an instance of Analysis Services. The recommended solution would be to import leaf level data directly from the original data source (typically a relational database). Imports from relational databases will usually have a much better performance and you can avoid the 1 hour timeout.

The reason we have this timeout limit is easy to explain. Powerpivot for Excel makes use of an embedded SSAS instance . It comes with basically the same server configuration properties that you may already know from working with regular SSAS instances. One of these properties is called ExternalCommandTimeout and it has a default setting of 3600 sec = 1 hour.

From Books online documentation:

ExternalCommandTimeout

An integer property that defines the timeout, in seconds, for commands issued to external servers, including relational data sources and external Analysis Services servers.

The default value for this property is 3600 (seconds).

For regular SSAS Instances you can modify the SSAS instance properties (including timeout values) via the SSAS instance properties GUI in Management Studio or by editing the server configuration file "msmdsrv.ini" directly. In the Powerpivot for Excel scenario we do not expose the (embedded) SSAS instance properties for modification. And we do not support changing the default values. Thus Powerpivot for Excel has a fixed data import timeout of 1 hour.

Please be also aware that you may not even notice the failed data import when using an older version of the OLED provider for Analysis Services for data import. With the older provider versions the data import was also stopped after one hour, but it was reported as successful.

See KB 2761400 for details:

2761400 FIX: An incomplete data import is reported as successful after a time-out in SSAS 2008 R2 or in SSAS 2012
http://support.microsoft.com/kb/2761400/EN-US

SSAS project deployment fails with error "Internal error: Invalid enumeration value. Please call customer support."

2012-12-05T12:31:00Z

Recently I have seen this error popping up more frequently.

It can occur when you modify a SSAS project in Visual Studio 2008 (or BIDS 2008) and try to deploy it against an existing SSAS 2008 R2 database. The full error message will usually also mention a "storagemode" element and a metadata error for instantiating a dimension object:

Internal error: Invalid enumeration value. Please call customer support. is not a valid value for this element.

An error occurred while parsing the 'StorageMode' element at line 1 , column xxxx under Load/ObjectDefinition/Dimension/StorageMode

Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\<AS DB name>.0.db\<dimension name>.3.dim.xml'.

The issue can appear shortly after SSAS service restart, when meta data for SSAS database objects are not completely loaded yet. You typically won’t see it if the SSAS instance has been running for a while, objects were processed and/or object properties were queried.

The problem has already been fixed for SSAS 2012. Thus you should not see it with SSAS 2012 deployments.

For SSAS 2008 R2 the fix is rather complex, and thus we currently have workarounds only:

workaround a) deploy to new SSAS target database (no incremental deployment)

workaround b) prior to incremental deployment execute „DISCOVER_XML_METADATA“ XMLA Command with „ExpandFull“ argument against target database .

This can be done from a XMLA query window in Management Studio. It will load all meta data for the specified SSAS database completely.

The full command syntax is:

<RequestType>DISCOVER_XML_METADATA</RequestType>

<DatabaseID>database ID</DatabaseID>

<ObjectExpansion>ExpandFull</ObjectExpansion>

</RestrictionList>

</Restrictions>

</Discover>

where "database ID" needs to be set to the ID of the database you are trying to deploy to. Note that database name and ID are not necessarily identical. You can determine name and ID by looking at SSAS database properties in Management Studio.

DAX over MD preview available

2012-11-30T08:59:32Z

Many customers have been waiting for this important feature that let's you generate power view reports from multi-dimensional Analysis Services instances.

Here you can get details and download the bits for testing: http://blogs.msdn.com/b/analysisservices/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

Where did my process progress events go?

2012-10-16T16:43:00Z

Today's post is about an elusive issue that was keeping us busy for a while.

When you process SSAS objects (database, cube, partition, dimension etc.) manually in SQL Server Management Studio (SSMS) or Business Intelligence Development Studio (BIDS), then you get a nice progress report window that shows you all processing stages:

However, sometimes you do not get any processing events, just the success message "process succeeded",

There's a variation of this problem in that it claims to be waiting on trace data (that never arrive): "Process succeeded. Trace information is still being transferred. If you do not want to wait for all the information to arrive press Stop."

We have never observed these issues on SSAS 2005, but confirmed occurences on SSAS 2008, SSAS 2008 R2 and even SSAS 2012. The issue is somewhat elusive because it only happens on certain machines, and even there usually on a random basis. The "Trace information is still being transferred" part of the problem can often (again not always) be removed by enabling "flight recorder". We had a few connect bugs filed for the problem area, but were never able to fix these issues completely. With more customers stumbling upon the issue and growing pain in customer support a thorough investigation of the problem was launched. As a result we found that all our issues were related to the asynchronous way that the SSMS/BIDS client connects to the session trace of the processing session.

This led to a number of potential timing problems ...

I'm pleased to announce that we know have a fix available that should address most (if not all) of the "missing process progress events" issues. At least it fixed the problem in all reproduction environments I had access to. Via changes in the AMO library we made sure that SSMS/BIDS clients now connect in a synchronous way, eliminating the timing problems.

This week we released the Fix as part of SQL Server 2008 R2 SP1 CU9:

Cumulative update package 9 for SQL Server 2008 R2 Service Pack 1 is available

which is accompanied by a proper KB article:

FIX: Empty or incomplete tree view is shown in the Process Progress window when you process an SSAS object in SSMS or BIDS in SQL Server 2008 R2

If everything goes according to schedule you can also expect it to be part of SQL Server 2008 R2 SP2 CU4 and SQL Server 2012 SP1.

So hopefully we won't hear about about missing process progress events anymore ...

Issues with using msolap.x provider name in linked server definition

2012-09-27T09:37:00Z

During a recent investigation I realised that a number of people are using MSOLAP.x as the provider name for a linked server e.g. MSOLAP.4 when creating a linked server... e.g.

EXEC master.dbo.sp_addlinkedserver @server = N'lsname', @srvproduct=N'msolap', @provider=N'MSOLAP.4', @datasrc=N'servera\inst', @catalog=N'SSASDBName'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsname',@useself=N'False',@locallogin=NULL,@rmtuser=N'dom\user',@rmtpassword='########'

etc

If you do this and you then right click the Linked Server that is shown in the SSMS linked servers list and select properties then you will note that SSMS displays the linked server as if it is a SQL provider linked server. If you just script it then it will appear to be using the options that you chose originally.

If you were to use processmonitor to check what it happening then you will also note that as well as checks on msolap from sqlserver.exe there will be activity done by dllhost for msolap.4. This is because the provider has defaulted to out of process because there is no provider called MSOLAP.4 in the SQL Server list of providers. From a sql server point of view the provider is called MSOLAP.

Change the linked server to use MSOLAP. So long as your MSOLAP driver installation is correct on the SQL Server machine then it will pick up the latest MSOLAP driver. For SQL Server Analysis Server 2008 R2 this will be MSOLAP.4.

It will then correctly use the provider in InProcess mode so long as AllowInProcess is set for the MSOLAP provider.

e.g.

EXEC master.dbo.sp_addlinkedserver @server = N'lsname', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'servera\inst', @catalog=N'SSASDBName'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsname',@useself=N'False',@locallogin=NULL,@rmtuser=N'dom\user',@rmtpassword='########'

To test the interface use something like:

select * from openquery (lsname,'select [Measures].[testm] on 0 from [TEST]')

Here [TEST] is a cube name in the database [SSASDBName].

Generally the MSOLAP.4 options will work for any user in Local Administrators group on the SQL Server but users that are not in this group will probably get an error for example:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "MSOLAP.4" for linked server "lsname".

An error may also be reported in the SQL Error Log and sometimes the Application Event log on the server

The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Error: 17054, Severity: 16. State: 1.

4 GB Limit for Powerpivot text file imports via ACE text data source driver

2012-05-03T08:34:00Z

We have been investigating reports that large text files are not completely imported into Powerpivot. And indeed a thorough investigation showed two unexpected things happening.

The text import is cut off at 4 GB
Import is shown as successful.

This applies to the SQL Server 2008 R2 and the SQL Server 2012 Powerpivot add-in for excel in both, 32 and 64 bit version.

The Powerpivot "from text" import icon invokes the Access Connectivity Engine (ACE) text data source driver.

This driver was designed to work with Access databases that have a 2 GB size limit.

Thus it is kind of understandable that not much effort went into extending the size limit for this driver. We could have done a much better job documenting the size limit though.

The second point is clearly a software problem. The text driver should have reported an error for the incomplete text import to Powerpivot.

With the latest Office cumulative update from April 2012 this issue was addressed. With the new ACE text driver version import of text files > 4 GB will fail and you receive error message:

"The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result."

The error message references the 2 GB size limit of Access databases and is thus a bit misleading for the Powerpivot text import scenario. Remember that the real size limit is 4 GB.

But this is the best we could get as a quick fix and more importantly it stops the incomplete import of text files.

2598145 Description of the Access Connectivity Engine 2010 hotfix package (Ace-x-none.msp): April 24, 2012

This leaves of course the question "How do I import text files > 4 GB".

As of now the only solutions we have available are:

import text file into a SQL Server table via "import data task"/SSIS package and subsequently load the SQL Server table into Powerpivot (2 step process)
Use third party text driver that does not have a 4 GB size limit.

non-admin users not able to use Powerpivot for Excel 2012 on Windows XP

2012-04-27T11:45:00Z

Recently we investigated issues where non-admin users could not open Powerpivot 2012 window in Excel 2010 on Windows XP machines.

When trying to open the Powerpivot window they get the following error message:

“Embedded Analysis Services Engine: Could not open embedded Powerpivot data”

During the investigation it turned out that the "SeIncreaseBasePriorityPrivilege" windows privilege is required for opening the Powerpivot window.

Users that have this privilege won't have issues in opening Powerpivot window anymore.

Unfortunately only administrators have this privilege by default on Windows XP.

But administrators can assign the privilege to users or groups that need to work with Powerpivot via the "User rights assignment" folder in the Local Security Policy (secpol.msc) tool.

Alternatively roll it out to all domain machines via group policy.

A KB article explaing the issue is on the way and we hope to have a Hotfix available soon.

July 2012 update:

We noted that the issue is not limited to Windows XP, but can affect Windows Vista and Windows 7 clients as well.

The difference being that on these newer operating systems you should be able to use Powerpivot with non-admin users unless you remove privileges that were granted by default.

On Windows Vista and Windows 7 the privilege required for usage of the Powerpivot window is "SeIncreaseWorkingSetPrivilege".

While office programs will be (and should be) primarily used on client operating systems, Powerpivot usage on the corresponding server operating systems (Windows Server 2003, Windows Server 2008, Windows Server 2008 R2) has similar issues.

You can avoid the described privilege issues by downloading the Powerpivot add-in from cumulative update 2 for SQL Server 2012: http://support.microsoft.com/kb/2703275

The 64 bit add-in is available as "2012_RTM_PPExcel_CU2_2703275_11_0_2325_x64", the 32 bit add-in as "2012_RTM_PPExcel_CU2_2703275_11_0_2325_x86".