Excel drillthrough to OLAP error

I am using the MSDN workaround Drillthrough VBA code to enable a drillthough from an Excel pivot table to my OLAP cube. It works fine on 2 PCs in my area. However on a 3rd PC,I get the following error:

"The OLAP provider does not support custom grouping. Custom groups were previously defined for this PivotTable Report.
Do you wish to retain the custom group definitions for the future?....."

All 3 PCs are using Excel 2003. All have data access components v2.8 installed.
We are using Analysis Services on SQL Server 2000 Enterprise Edition.

I have been tearing my hair out trying to find out the differences between the PCs....

Any help would be appreciated.

Data access components probably has nothing to do with the situation. One idea is to check service pack version of the Office installed. Make sure it is the same version.
Also try and run Office setup through add/remove programs and repair Office installation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Excel drillthrough to OLAP error

I am using the MSDN workaround Drillthrough VBA code to enable a drillthough from an Excel pivot table to my OLAP cube. It works fine on 2 PCs in my area. However on a 3rd PC,I get the following error:

"The OLAP provider does not support custom grouping. Custom groups were previously defined for this PivotTable Report.
Do you wish to retain the custom group definitions for the future?....."

All 3 PCs are using Excel 2003. All have data access components v2.8 installed.
We are using Analysis Services on SQL Server 2000 Enterprise Edition.

I have been tearing my hair out trying to find out the differences between the PCs....

Any help would be appreciated.

Data access components probably has nothing to do with the situation. One idea is to check service pack version of the Office installed. Make sure it is the same version.
Also try and run Office setup through add/remove programs and repair Office installation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Excel Browser error

Hi

I'm trying to browse a cube using excel. I get through the pivot table wizrd ok but when I click finish I am getting the following error:

XML for Analysis parser: The localeidentifier property is not overwritable and cannot be assigned a new value

I get the message regardless of whether I am trying to connect to my own cubes or the Adventure Works sample. Any idea of cause & remedy please

Thanks

Wayne

A couple of things you may want to try.

This error seems to occur if local language is anything other than English-US

a) Refer to post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=961463&SiteID=17. This relates to CTP2, but there are a couple of tips that may be of use.

b) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

c) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

Excel Browser error

Hi

I'm trying to browse a cube using excel. I get through the pivot table wizrd ok but when I click finish I am getting the following error:

XML for Analysis parser: The localeidentifier property is not overwritable and cannot be assigned a new value

I get the message regardless of whether I am trying to connect to my own cubes or the Adventure Works sample. Any idea of cause & remedy please

Thanks

Wayne

A couple of things you may want to try.

This error seems to occur if local language is anything other than English-US

a) Refer to post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=961463&SiteID=17. This relates to CTP2, but there are a couple of tips that may be of use.

b) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

c) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

Excel as Frontend to SSAS 2005 Cube

Hi, there,

I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.

Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.

Thanks in advance.

Regards,
Yong Hwee

You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.

The code saples for writeback I have seen are in books, like this one.

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?

Thank you.

Regards,

Yong Hwee

|||

Regarding the Excel Add-In. No! I have only downloaded and tried it.

Have you heard that MS is working on a new server for budgeting and financial modelling?

ProClarity and Business Scorecard server will also be a part in this solution.

Regards

Thomas Ivarsson

|||

Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.

http://office.microsoft.com/en-au/FX101550371033.aspx

Thomas - we should catch up sometime.

PaulG

|||

Hi, Thomas and Paul,

Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.

BTW, thanks for your help and SQL Server 2005 is a great product.

Thank you.

Regards,

Yong Hwee

Excel as Frontend to SSAS 2005 Cube

Hi, there,

I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.

Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.

Thanks in advance.

Regards,
Yong Hwee

You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.

The code saples for writeback I have seen are in books, like this one.

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?

Thank you.

Regards,

Yong Hwee

|||

Regarding the Excel Add-In. No! I have only downloaded and tried it.

Have you heard that MS is working on a new server for budgeting and financial modelling?

ProClarity and Business Scorecard server will also be a part in this solution.

Regards

Thomas Ivarsson

|||

Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.

http://office.microsoft.com/en-au/FX101550371033.aspx

Thomas - we should catch up sometime.

PaulG

|||

Hi, Thomas and Paul,

Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.

BTW, thanks for your help and SQL Server 2005 is a great product.

Thank you.

Regards,

Yong Hwee

Excel add-in: invisible measures

I've a cube in Analysis Services 2005 with two measuregroups. Each group contains about 4 measures. Those measures are only used for making some calculated measures. Only the calculated measures have to be visible to the user. Whenever I put the property 'visible' on false for al the normal measures in the two measuregroups, the retrieval of the measures (selecting measures in dimension-tab) in excel-add-in takes too lang (more then one minute). The retrieval of the measures works fine in other front-en tools like proclarity.

Does anyone knows about this problem and knows a solution or a workaround?

Thanks,

ADL

Try to hide the measures you dont like to see using perspectives feature of Analysis Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Does Excel 2003 with PTS9 support perspectives?|||

The excel 2003 add-in for analysis services supports perspectives, but that gives no alternative:

It seems that I can't disable all measuregroups in a perspective. When you uncheck all measuregroups, the excel add-in will still show those measuregroups. The unchecking of just one measuresgroups works fine ?

By further investigation and looking at the profiler I noticed that the retrieval of de measures in Proclarity works fine, but the soapcall is somewhat different :

PROCLARITY : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>Demo Emmaus</CATALOG_NAME><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>2057</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

EXCEL ADD-IN : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><SafetyOptions>2</SafetyOptions><LocaleIdentifier>2057</LocaleIdentifier></PropertyList>

Is this giving anyone a clue of what can cause the long response time of retrieval of measures metadata in excel-add-in whenever the measuresvisibility is set to false?

Excel add-in: invisible measures

I've a cube in Analysis Services 2005 with two measuregroups. Each group contains about 4 measures. Those measures are only used for making some calculated measures. Only the calculated measures have to be visible to the user. Whenever I put the property 'visible' on false for al the normal measures in the two measuregroups, the retrieval of the measures (selecting measures in dimension-tab) in excel-add-in takes too lang (more then one minute). The retrieval of the measures works fine in other front-en tools like proclarity.

Does anyone knows about this problem and knows a solution or a workaround?

Thanks,

ADL

Try to hide the measures you dont like to see using perspectives feature of Analysis Services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Does Excel 2003 with PTS9 support perspectives?|||

The excel 2003 add-in for analysis services supports perspectives, but that gives no alternative:

It seems that I can't disable all measuregroups in a perspective. When you uncheck all measuregroups, the excel add-in will still show those measuregroups. The unchecking of just one measuresgroups works fine ?

By further investigation and looking at the profiler I noticed that the retrieval of de measures in Proclarity works fine, but the soapcall is somewhat different :

PROCLARITY : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>Demo Emmaus</CATALOG_NAME><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>2057</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

EXCEL ADD-IN : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><SafetyOptions>2</SafetyOptions><LocaleIdentifier>2057</LocaleIdentifier></PropertyList>

Is this giving anyone a clue of what can cause the long response time of retrieval of measures metadata in excel-add-in whenever the measuresvisibility is set to false?

excel add-in

Does the add in for excel work against RS reports?
Does it work against local cube files?You cannot use RS reports as a data source within the add-in, however, you
can connect to a local cube file.
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"swetha" wrote:

> Does the add in for excel work against RS reports?
> Does it work against local cube files?
>|||We have an Excel Addin for Reporting Services
Please mail us at www.gmsbv.nl and i will mail you fro free
Regards, Marco
Steve Mann schreef:
[vbcol=seagreen]
> You cannot use RS reports as a data source within the add-in, however, you
> can connect to a local cube file.
>
> --
> RDA Corp
> Business Intelligence Evangelist Leader
> www.rdacorp.com
>
> "swetha" wrote:
>

Excel 2007, MDX Compatibility and connection string (for ragged dimensions)

I'm trying to open a simple SSAS 2005 cube with a ragged hierarchy (it has a level that contains member that should be hidden if the member name is the same as the parent).

I read about the necessity to use MDX Compatibility=2 into the connection string to see the HideMemberIf property in action. The dimension browser in BIDS does not display a member into a level only if it has no visible childs. May be this is a browser issue, as I read somewhere...

With Excel 2007, it should be possible to change the connection string. Unfortunately, even if I change it, Excel 2007 force the "MDX Compatibility=1" into my connection string. Moreover, it adds a "Safety Options=2" and "MDX Missing Member Mode=Error" and even if I try to change them, Excel still recreate the same connection string.

Is there someone that:

- has been successful using ragged hierarchies with Analysis Services 2005?

- has been able to change the connection string into Excel 2007?

Thank you

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Not tried them with Excel 2007, I'm afraid - it's always seemed pretty dumb to me that the default was that ragged hierarchies weren't displayed properly anyway. And what's worse is that in AS2005 you can't force the MDX compatibility property on the server either:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653115&SiteID=1

Can we get this fixed please?

Chris

|||

Hi Chris,

I just posted in Excel 2007 forum another annoying issue.

By default, Excel 2007 display calculated measures but not calculated members on other dimensions.
You have to change this pivottable option settings each time. For each Pivot Table you create.

I asked it there is a way (eventually with a registry hack) to change this default...

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

|||

Hi there,

Has anyone seen any updated information on the ragged hierarchy issue? I am having problems with setting MDX Compatibility = 2. Excel resets it back to 1.

Cheers

Excel 2007, MDX Compatibility and connection string (for ragged dimensions)

I'm trying to open a simple SSAS 2005 cube with a ragged hierarchy (it has a level that contains member that should be hidden if the member name is the same as the parent).

I read about the necessity to use MDX Compatibility=2 into the connection string to see the HideMemberIf property in action. The dimension browser in BIDS does not display a member into a level only if it has no visible childs. May be this is a browser issue, as I read somewhere...

With Excel 2007, it should be possible to change the connection string. Unfortunately, even if I change it, Excel 2007 force the "MDX Compatibility=1" into my connection string. Moreover, it adds a "Safety Options=2" and "MDX Missing Member Mode=Error" and even if I try to change them, Excel still recreate the same connection string.

Is there someone that:

- has been successful using ragged hierarchies with Analysis Services 2005?

- has been able to change the connection string into Excel 2007?

Thank you

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Not tried them with Excel 2007, I'm afraid - it's always seemed pretty dumb to me that the default was that ragged hierarchies weren't displayed properly anyway. And what's worse is that in AS2005 you can't force the MDX compatibility property on the server either:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653115&SiteID=1

Can we get this fixed please?

Chris

|||

Hi Chris,

I just posted in Excel 2007 forum another annoying issue.

By default, Excel 2007 display calculated measures but not calculated members on other dimensions.
You have to change this pivottable option settings each time. For each Pivot Table you create.

I asked it there is a way (eventually with a registry hack) to change this default...

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Excel 2007 Issue (KB 929766) for Cube Reports; Doesn't Make Any Sense

In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.

A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.

Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):

This issue occurs if the following conditions are true:

?The workbook contains a PivotTable that uses key performance indicators (KPIs).?The KPIs are created in the Analysis Services Business Intelligence Development Studio.?One or more of the KPIs have an expression in the Current Time Member property.

Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?

Any thoughts?Still stumped here and we're migrating more users to Office 2007.

Excel 2007 Issue (KB 929766) for Cube Reports; Doesn't Make Any Sense

In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.

A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.

Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):

This issue occurs if the following conditions are true:

?The workbook contains a PivotTable that uses key performance indicators (KPIs).?The KPIs are created in the Analysis Services Business Intelligence Development Studio.?One or more of the KPIs have an expression in the Current Time Member property.

Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?

Any thoughts?
Still stumped here and we're migrating more users to Office 2007.

Excel 2007 can't read data from AS 2005 cube

I have created an AS 2005 cube for my tester to access thru Excel 2007. He can connect to the cube fine, but can't read any measurement data elements from Excel. What Excel return is #N/A in the cell. I've checked many times the role that was setup to grant him permission to the cube. He should have all the rights to read data. In fact, the role setup was fine before I installed service pack 2. I wonder what's been changed in sp2. Any help would be great!

Mitch

It might be worth revisiting the "Cell Data" tab for the role, to ensure that none of the 3 "Enable .." options is checked.|||I wonder what the 3 "Enable..." options do in the "Cell Data" tab. I did unchecked the options and it works now.

Excel 2003 with MSFT Query cannot refresh data on Analysis Services 2005

Using MSFT Query and Excel 2003, I can connect to my OLAP cube in An. Serv. 2005. However, when I save the worksheet and then reopen it and try to refresh the data (right click the worksheet and select, Refresh Data), I get the first error message: "An error was encountered in the transport layer". And the second error message: "Errors in the OLE DB provider. An error occured while loading the connection dialog box component for prompting".

I can't find any related articles to fix this problem and get this running.

Thanks for any help.

-lance

Hi Lance. First thing I think you should determine is where the problem lies. Is is a problem with MSFT Query, security access, or with the OLAP Cube (or database) in AS 2005. A quick way to do this is download and install the Cube Analysis add in for Excel as a quick test. See if you can access the database/cube with the Cube Analysis add-in. The results will probably point you where to focus your attention. You can download the Cube Analysis Add-in from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

PGoldy

|||

Thanks for your help Paul.

Clearly, I'm no expert with connecting Excel to Analysis Services.

I downloaded the add-in you suggested. Now when I try to create a new connection using this add in (Cube Analysis > Manage Connection > New Connection), and add the same Analysis Server info I added when connecting via the method (Data > Pivot Table > External Data Source > Get Data > OLAP Cube > New Data Source), I get the error message:

"OLAP Server error on conecction 216.128.xxxxx"

"OLAP Server error: The peer prematurely closed the connection. An error was encountered in the transport layer."

Still not sure at what level this error is occuring, and how to get it resolved.

Thanks,
-lance

|||

Hi Lance:

Unfortunately this got uglier instead of better. Sorry about that. First - I've not seen the errors you're seeing so this is a bit of a fishing expedition. It seems like you have client side problems - what I'm not sure. Last easy thing to see if it's client or server side problem is using SQL Manager. Try SQL Manager on a couple of different machines and issue the simplest of MDX queries against the cube. Results should tell us if it's a cube problem, or client-side problem.

Good luck.

PGoldy

|||

Hi Paul,

I think I've narrowed down the possibilites and believe it's a client-side config problem - I can connect and refresh repeatedly to both cubes with no problems (I just verified this a minute ago). My client machine is relatively clean (it's only a month or two old) so I haven't had much time to clutter it up with other software that may cause hard-to-diagnose conflicts.

Thanks for any further insights.

-lance

|||

Hi Lance:

Here's a post which reported the same error message, and provides a solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236201&SiteID=1

However, it's a programmatic solution which controls the connection string to AS which directly may not be much help, but could point you in the right direction. You can control the connection string in Excel with the Cube Analysis add-in (I don't know about MSFT query). Perhaps you could look at your connection string and compare it with the details in the above link and determine what's different/not working for you.

You access the connection string used by the Cube Analysis add-in when you manage the connection. In Excel select Cube Analysis | Manage Connection. Highlight your existing connection and select Edit. Click Next to get past the server specification and you'll see the list of cubes exposed in your AS database. The cube you originally designated should be highlighted. In the lower left portion of the dialog is the "Advanced Provider String..." button. Click the "Advanced Provider String..." button and it exposes the connection string as used to access AS. You can hand edit the string and see if your changes make a difference in your connection.

Good luck - PGoldy

|||

Hi,

I get exact the same error. The excel pivot table has worked for weeks, and over night without change of anything, I got the same error. I tried the report some times an had always this error. At midday, we have a lower network load because of lunch, all works fine with the excel pivot table. So, in my opinion its a problem of a timeout. Does anyone know, where and what I could change to correct this problem?

Thanks
Hans

|||

Hi Hans:

You can manage the timeout with the connection string in the Excel add-in for Cube Analysis. In Excel select Cube Analysis | Manage Connection and you see the "Manage Connection" dialog. Highlight your connection and click Edit on the bottom of the dialog. Click Next to get by the "Define Connection - Choose Server " dialog and you see the "Define Connection - Choose Data" dialog. Click the Advanced Provider String... button on the bottom of the dialog and you can see the connection string. The default timeout value for the connection string is 30s. (Connect Timeout=30) You can adjust the timeout value to fit your environment.

Good luck.

PGoldy

|||

Hi Paul,

Is the connection string of CubeAnalysis and of the "normal" Pivotable of Excel the same connection string? Is this connection string saved in the Excel Sheet for one time? I changed the connection string in Cube Analysis from Database to HTTP (http://myOlapServer/olap/msmdpump.dll:80) and all worked fine in Cube Analysis, but in the Pivot Table all errors are still the same. So it seems to me, that this are 2 independent connection strings. Am I wrong?

Thanks
Hans

|||

Hi Hans:

I'm not 100% positive, but somewhat certain that the pivot table connection string and the cube analysis connection strings are stored separately and not shared. The cube analysis connection information is stored in an XML file on your local machine. It is not stored as part of the workbook. The XML file has extension .ODC.

Good luck.

PaulG

|||

Thanks for all your help. I fixed the problem, by going through every option in the Add Cube process. What finally solved it was, clicking the Options tab in step 3 and checking the box for Refresh on Connection and Save username and password.

thanks again

Excel 2003 with MSFT Query cannot refresh data on Analysis Services 2005

Using MSFT Query and Excel 2003, I can connect to my OLAP cube in An. Serv. 2005. However, when I save the worksheet and then reopen it and try to refresh the data (right click the worksheet and select, Refresh Data), I get the first error message: "An error was encountered in the transport layer". And the second error message: "Errors in the OLE DB provider. An error occured while loading the connection dialog box component for prompting".

I can't find any related articles to fix this problem and get this running.

Thanks for any help.

-lance

Hi Lance. First thing I think you should determine is where the problem lies. Is is a problem with MSFT Query, security access, or with the OLAP Cube (or database) in AS 2005. A quick way to do this is download and install the Cube Analysis add in for Excel as a quick test. See if you can access the database/cube with the Cube Analysis add-in. The results will probably point you where to focus your attention. You can download the Cube Analysis Add-in from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

PGoldy

|||

Thanks for your help Paul.

Clearly, I'm no expert with connecting Excel to Analysis Services.

I downloaded the add-in you suggested. Now when I try to create a new connection using this add in (Cube Analysis > Manage Connection > New Connection), and add the same Analysis Server info I added when connecting via the method (Data > Pivot Table > External Data Source > Get Data > OLAP Cube > New Data Source), I get the error message:

"OLAP Server error on conecction 216.128.xxxxx"

"OLAP Server error: The peer prematurely closed the connection. An error was encountered in the transport layer."

Still not sure at what level this error is occuring, and how to get it resolved.

Thanks,
-lance

|||

Hi Lance:

Unfortunately this got uglier instead of better. Sorry about that. First - I've not seen the errors you're seeing so this is a bit of a fishing expedition. It seems like you have client side problems - what I'm not sure. Last easy thing to see if it's client or server side problem is using SQL Manager. Try SQL Manager on a couple of different machines and issue the simplest of MDX queries against the cube. Results should tell us if it's a cube problem, or client-side problem.

Good luck.

PGoldy

|||

Hi Paul,

I think I've narrowed down the possibilites and believe it's a client-side config problem - I can connect and refresh repeatedly to both cubes with no problems (I just verified this a minute ago). My client machine is relatively clean (it's only a month or two old) so I haven't had much time to clutter it up with other software that may cause hard-to-diagnose conflicts.

Thanks for any further insights.

-lance

|||

Hi Lance:

Here's a post which reported the same error message, and provides a solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=236201&SiteID=1

However, it's a programmatic solution which controls the connection string to AS which directly may not be much help, but could point you in the right direction. You can control the connection string in Excel with the Cube Analysis add-in (I don't know about MSFT query). Perhaps you could look at your connection string and compare it with the details in the above link and determine what's different/not working for you.

You access the connection string used by the Cube Analysis add-in when you manage the connection. In Excel select Cube Analysis | Manage Connection. Highlight your existing connection and select Edit. Click Next to get past the server specification and you'll see the list of cubes exposed in your AS database. The cube you originally designated should be highlighted. In the lower left portion of the dialog is the "Advanced Provider String..." button. Click the "Advanced Provider String..." button and it exposes the connection string as used to access AS. You can hand edit the string and see if your changes make a difference in your connection.

Good luck - PGoldy

|||

Hi,

I get exact the same error. The excel pivot table has worked for weeks, and over night without change of anything, I got the same error. I tried the report some times an had always this error. At midday, we have a lower network load because of lunch, all works fine with the excel pivot table. So, in my opinion its a problem of a timeout. Does anyone know, where and what I could change to correct this problem?

Thanks
Hans

|||

Hi Hans:

You can manage the timeout with the connection string in the Excel add-in for Cube Analysis. In Excel select Cube Analysis | Manage Connection and you see the "Manage Connection" dialog. Highlight your connection and click Edit on the bottom of the dialog. Click Next to get by the "Define Connection - Choose Server " dialog and you see the "Define Connection - Choose Data" dialog. Click the Advanced Provider String... button on the bottom of the dialog and you can see the connection string. The default timeout value for the connection string is 30s. (Connect Timeout=30) You can adjust the timeout value to fit your environment.

Good luck.

PGoldy

|||

Hi Paul,

Is the connection string of CubeAnalysis and of the "normal" Pivotable of Excel the same connection string? Is this connection string saved in the Excel Sheet for one time? I changed the connection string in Cube Analysis from Database to HTTP (http://myOlapServer/olap/msmdpump.dll:80) and all worked fine in Cube Analysis, but in the Pivot Table all errors are still the same. So it seems to me, that this are 2 independent connection strings. Am I wrong?

Thanks
Hans

|||

Hi Hans:

I'm not 100% positive, but somewhat certain that the pivot table connection string and the cube analysis connection strings are stored separately and not shared. The cube analysis connection information is stored in an XML file on your local machine. It is not stored as part of the workbook. The XML file has extension .ODC.

Good luck.

PaulG

|||

Thanks for all your help. I fixed the problem, by going through every option in the Add Cube process. What finally solved it was, clicking the Options tab in step 3 and checking the box for Refresh on Connection and Save username and password.

thanks again

Excel 2003 does not refresh data properly ?

Hi all,

I'm currently having an issue with Excel 2003 pivot table accessing an AS2005 cube.

If we do an update on a dimension attribute (product description) for instance the modification does not show in our pivot table when the user hit the refresh button. To show the modified value of the attribute, the user need to drag the dimension out of the pivot table and drag it back in...

Here are the step to reproduce this issue on adventure works:

- Create a pivot table showing internet sales amount for every product (product description) on rows and every calendar year on columns.

- Change the description of one of the product

- Fully process dimensions and cubes

- hit the refresh button in your Excel pivot table

To show the modified description you need to drag the product description out of the pivot table and drag it back in.

Any help would be appreciated

TIA,

Eric

Any property I need to change in my connection string in order to refresh my dimension data when the user hit the refresh button in Excel 2003 Pivot table?|||We are dealing with the same problem. I would love to know if anyone has found a solution that doesn't involve dragging the field out of the table and then back in.

Kyle
|||

Hi Kyle,

I currently have an open case with Microsoft about this issue, I'll let you know as soon as they find a solution. The problem is they can't reproduce this issue on their side but I face this problem in every installation we do.

Currently, we are using a macro when the worksheet opens to move the fields out of the pivot table and the bring them back in.

Regards,

|||Thanks for the information Eric. After doing more research, it looks like the issue for us may be that we are using integer surrogate keys that can change every time the star schema tables are reloaded. So when the Excel PivotTable is initially built, the key for one dimension member may be 100, and when the data is reloaded the same member may end up with a key value of 105. Then, when the PivotTable is refreshed, it confuses the data between the dimension members with keys 100 and 105.

I'll be interested to hear what the solution is for you, and maybe you could say whether or not surrogate keys or changing dimension member keys would be possible in your environment.

Excel 2003 does not refresh data properly ?

Hi all,

I'm currently having an issue with Excel 2003 pivot table accessing an AS2005 cube.

If we do an update on a dimension attribute (product description) for instance the modification does not show in our pivot table when the user hit the refresh button. To show the modified value of the attribute, the user need to drag the dimension out of the pivot table and drag it back in...

Here are the step to reproduce this issue on adventure works:

- Create a pivot table showing internet sales amount for every product (product description) on rows and every calendar year on columns.

- Change the description of one of the product

- Fully process dimensions and cubes

- hit the refresh button in your Excel pivot table

To show the modified description you need to drag the product description out of the pivot table and drag it back in.

Any help would be appreciated

TIA,

Eric

Any property I need to change in my connection string in order to refresh my dimension data when the user hit the refresh button in Excel 2003 Pivot table?|||We are dealing with the same problem. I would love to know if anyone has found a solution that doesn't involve dragging the field out of the table and then back in.

Kyle
|||

Hi Kyle,

I currently have an open case with Microsoft about this issue, I'll let you know as soon as they find a solution. The problem is they can't reproduce this issue on their side but I face this problem in every installation we do.

Currently, we are using a macro when the worksheet opens to move the fields out of the pivot table and the bring them back in.

Regards,

|||Thanks for the information Eric. After doing more research, it looks like the issue for us may be that we are using integer surrogate keys that can change every time the star schema tables are reloaded. So when the Excel PivotTable is initially built, the key for one dimension member may be 100, and when the data is reloaded the same member may end up with a key value of 105. Then, when the PivotTable is refreshed, it confuses the data between the dimension members with keys 100 and 105.

I'll be interested to hear what the solution is for you, and maybe you could say whether or not surrogate keys or changing dimension member keys would be possible in your environment.

Excel 2003 does not refresh data properly ?

Hi all,

I'm currently having an issue with Excel 2003 pivot table accessing an AS2005 cube.

If we do an update on a dimension attribute (product description) for instance the modification does not show in our pivot table when the user hit the refresh button. To show the modified value of the attribute, the user need to drag the dimension out of the pivot table and drag it back in...

Here are the step to reproduce this issue on adventure works:

- Create a pivot table showing internet sales amount for every product (product description) on rows and every calendar year on columns.

- Change the description of one of the product

- Fully process dimensions and cubes

- hit the refresh button in your Excel pivot table

To show the modified description you need to drag the product description out of the pivot table and drag it back in.

Any help would be appreciated

TIA,

Eric

Any property I need to change in my connection string in order to refresh my dimension data when the user hit the refresh button in Excel 2003 Pivot table?|||We are dealing with the same problem. I would love to know if anyone has found a solution that doesn't involve dragging the field out of the table and then back in.

Kyle
|||

Hi Kyle,

I currently have an open case with Microsoft about this issue, I'll let you know as soon as they find a solution. The problem is they can't reproduce this issue on their side but I face this problem in every installation we do.

Currently, we are using a macro when the worksheet opens to move the fields out of the pivot table and the bring them back in.

Regards,

|||Thanks for the information Eric. After doing more research, it looks like the issue for us may be that we are using integer surrogate keys that can change every time the star schema tables are reloaded. So when the Excel PivotTable is initially built, the key for one dimension member may be 100, and when the data is reloaded the same member may end up with a key value of 105. Then, when the PivotTable is refreshed, it confuses the data between the dimension members with keys 100 and 105.

I'll be interested to hear what the solution is for you, and maybe you could say whether or not surrogate keys or changing dimension member keys would be possible in your environment.

Excel 2003 and nonemptycrossjoin

I am trying to utilize an Excel 2003 pivot table as a client tool to front end a project I am developing. I have created the cube in Analysis Services 2005 and it works fine when I run MDX against it directly. The problem that I am having is that Excel is not using nonemptycrossjoin in the pivot table. I have Teo Lachev's book "Applied Microsoft Analysis Services 2005' and I was looking for tips and found a note on p. 633 referring to a workaround (http://blogs.msdn.com/bi_systems/articles/162852.aspx).

To simplify testing, I reduced the query down to the most essential dimensions on rows and a single non-calculated measure on columns. The below query takes 1 second from SQL Server Management Studio.

select
nonemptycrossjoin([Product Family].[Product Families].[Product Brand].&[Family1],
[Shift Calendar].[Shift Fiscal].[Fiscal Year].&[2.006E3].&[2006-3].&[2006-08].&[2006-33],
[Failure Root Cause].[FA Failure Root Causes].[FA Pass Fail Indicator].&[Fail].&[Failure1],
[Comp1 Supplier].[Comp1 Suppliers].[Comp1 Supplier Description],
[Comp2 Supplier].[Comp2 Suppliers].[Comp2 Supplier Description]) on rows
{[Measures].[Sum Failed Qty]} on columns
from
[MyCube]

If I remove the nonempycrossjoin, keeping the () on rows to get the implicit crossjoin, the query runs but takes 114s. Now, if I start creating a similar query in Excel, the first few dimensions are pretty fast, but adding the last dimension on rows takes so long that my machine consumes all of its memory (2 GB) and CPU. I have to kill Excel to get my machine back.

So, Excel 2003 does not provide a facility to alter the Data Source, so after a bit of looking around I found the data source in "C:\Documents and Settings\username\Application Data\Microsoft\Queries". Opening my file test.oqy:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.3;Cache Authentication=False;User ID="";Initial Catalog=Analysis Services Test Project;Data Source=localhost;Impersonation Level=Impersonate;Location=localhost;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096
CommandText=AS Test Cube

According to the workaround above, this query should use the "new" method to eliminate empty rows since: 1) Non Empty Threshold is set lower than the number of tuples returned. and 2) there are no calculated measures in the query. Meeting both these criteria should clear the way for the "new" method to be used. Does anyone have any other ideas that I could employ to make this pivot table any faster? Any other tools that would do this with nonemptycrossjoin? I need slice/dice capabilities which is preventing my from using Reporting Services.

Thanks,

Keehan

Hi Keehan,

A question I would have, though, is whether the "Non Empty Threshold" connection property works in the same way in AS 2005 (or is just included for backward compatibility). Unfortunately, I can't locate this info in BOL; but Appendix B of the "MDX Solutions" 2nd Edition covers connection parameters. Hopefully, someone from [MS] will be able to authoritatively answer this?

In any case, I assume that the number of rows returned in your scenario easily exceeds the default threshold. If so, is there any possibility of restructuring the dimensions to exploit the AS 2005 Auto Exists feature - such as folding some separate dimensions into hierarchies of a single dimension?

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

>>

Appendix B: Connection Parameters That Affect MDX.

>>

|||

Deepak,

I kind of see where you are going with auto-exists but I'm not sure how it would be implemented. So, basically, for auto-exists to kick in, the attributes would have to be in the same dimension. So, how would I have say product and calendar in the same dimension. Is this the kind of work around you are thinking about?

Keehan

|||

I think I may know what you are talking about now. Let me dig into this a little more and I'll let you know how it goes.

Cheers