error when using "Referenced" dimension usage against an oracle data source

Hi All,

I've built a cube using a data source and DSV that point to an Oracle 10g database. I am able to process this cube so long as the dimensions are all related to the main fact table using regular dimension usage. The problem I'm having arises when the dimension has to have "referenced" usage through an intermediate dimension. In this case, when the cube processes, the SQL that gets generated and run looks akin to the following:

--

SELECT [CstOrds].[CstOrdsDISCOUNT0_0] AS [CstOrdsDISCOUNT0_0],[CstOrds].[CstOrdsBUY_QTY_DUE0_1] AS [CstOrdsBUY_QTY_DUE0_1],[CstOrds].[CstOrdsDESIRED_QTY0_2] AS [CstOrdsDESIRED_QTY0_2],[CstOrds].[CstOrdsQTY_ASSIGNED0_3] AS [CstOrdsQTY_ASSIGNED0_3],[CstOrds].[CstOrdsQTY_ON_ORDER0_4] AS [CstOrdsQTY_ON_ORDER0_4],[CstOrds].[CstOrdsQTY_PICKED0_5] AS [CstOrdsQTY_PICKED0_5],[CstOrds].[CstOrdsQTY_RETURNED0_6] AS [CstOrdsQTY_RETURNED0_6],[CstOrds].[CstOrdsQTY_SHORT0_7] AS [CstOrdsQTY_SHORT0_7],[CstOrds].[CstOrdsQTY_TO_SHIP0_8] AS [CstOrdsQTY_TO_SHIP0_8],[CstOrds].[CstOrdsQTY_SHIPPED0_9] AS [CstOrdsQTY_SHIPPED0_9],[CstOrds].[CstOrdsQTY_SHIPDIFF0_10] AS [CstOrdsQTY_SHIPDIFF0_10],[CstOrds].[CstOrdsQTY_INVOICED0_11] AS [CstOrdsQTY_INVOICED0_11],[CstOrds].[CstOrdsCOST0_12] AS [CstOrdsCOST0_12],[CstOrds].[CstOrdsNET_AMOUNT0_13] AS [CstOrdsNET_AMOUNT0_13],[CstOrds].[CstOrdsGROSS_AMOUNT0_14] AS [CstOrdsGROSS_AMOUNT0_14],[CstOrds].[CstOrdsSPTA0_15] AS [CstOrdsSPTA0_15],[CstOrds].[CstOrdsTCA0_16] AS [CstOrdsTCA0_16],[CstOrds].[CstOrds0_17] AS [CstOrds0_17],[CstOrds].[CstOrdsCUSTOMER_NO0_18] AS [CstOrdsCUSTOMER_NO0_18],[CstOrds].[CstOrdsINVOICE_DATE0_19] AS [CstOrdsINVOICE_DATE0_19],[CstOrds].[CstOrdsREAL_SHIP_DATE0_20] AS [CstOrdsREAL_SHIP_DATE0_20],[CstOrds].[CstOrdsPDDNT0_21] AS [CstOrdsPDDNT0_21],[CstOrds].[CstOrdsDENT0_22] AS [CstOrdsDENT0_22],[CstOrds].[CstOrdsORDER_NO0_23] AS [CstOrdsORDER_NO0_23],[CstOrds].[CstOrdsLINE_NO0_24] AS [CstOrdsLINE_NO0_24],[CstOrds].[CstOrdsREL_NO0_25] AS [CstOrdsREL_NO0_25],[CstOrds].[CstOrdsCATALOG_NO0_26] AS [CstOrdsCATALOG_NO0_26],[sPart_8].[PART_NO] AS [sPartPART_NO2_0]

FROM

OPENROWSET

(

N'OraOLEDB.Oracle',

N'detest';N'bi';N'',

N'

SELECT "DISCOUNT" "CstOrdsDISCOUNT0_0","BUY_QTY_DUE" "CstOrdsBUY_QTY_DUE0_1","DESIRED_QTY" "CstOrdsDESIRED_QTY0_2","QTY_ASSIGNED" "CstOrdsQTY_ASSIGNED0_3","QTY_ON_ORDER" "CstOrdsQTY_ON_ORDER0_4","QTY_PICKED" "CstOrdsQTY_PICKED0_5","QTY_RETURNED" "CstOrdsQTY_RETURNED0_6","QTY_SHORT" "CstOrdsQTY_SHORT0_7","QTY_TO_SHIP" "CstOrdsQTY_TO_SHIP0_8","QTY_SHIPPED" "CstOrdsQTY_SHIPPED0_9","QTY_SHIPDIFF" "CstOrdsQTY_SHIPDIFF0_10","QTY_INVOICED" "CstOrdsQTY_INVOICED0_11","COST" "CstOrdsCOST0_12","NET_AMOUNT" "CstOrdsNET_AMOUNT0_13","GROSS_AMOUNT" "CstOrdsGROSS_AMOUNT0_14","SALES_PRICE_TOTAL_API" "CstOrdsSPTA0_15","TOTAL_COST_API" "CstOrdsTCA0_16",1 "CstOrds0_17","CUSTOMER_NO" "CstOrdsCUSTOMER_NO0_18","INVOICE_DATE" "CstOrdsINVOICE_DATE0_19","REAL_SHIP_DATE" "CstOrdsREAL_SHIP_DATE0_20","PROMISED_DELIVERY_DATE_NO_TIME" "CstOrdsPDDNT0_21","DATE_ENTERED_NO_TIME" "CstOrdsDENT0_22","ORDER_NO" "CstOrdsORDER_NO0_23","LINE_NO" "CstOrdsLINE_NO0_24","REL_NO" "CstOrdsREL_NO0_25","CATALOG_NO" "CstOrdsCATALOG_NO0_26"

FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"'

)

AS [CstOrds],

OPENROWSET

(

N'OraOLEDB.Oracle',

N'detest';N'bi';N'',

N'"IAL"."SALES_PART"'

)

AS [sPart_8]

WHERE

(

(

[CstOrds].[CstOrdsCATALOG_NO0_26] = [sPart_8].[CATALOG_NO]

)

)

The error that is returned when I run the query from SQL Server Management Studio is as follows:

Msg 7357, Level 16, State 2, Line 1

Cannot process the object ""IAL"."SALES_PART"". The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Note that it seems to attempt to query the table behind the referenced dimension (SALES_PART) by simply opening the table, rather than selecting something specific from it. Unfortunately this doesn't work when processing the cube, nor does it work when I paste the same query into SQL Server Management Studio. However if I modify it a little to use

N'select * from "IAL"."SALES_PART"'

instead of just

N'"IAL"."SALES_PART"'

then it runs no problem. Is there any way to control the SQL that Analysis services produces to process the cube, or is there some other way to resolve this problem so I can include dimensions with "referenced" usage? (Note that I've tried with both the Microsoft and the Oracle OLEDB providers, in both cases the behaviour & error were the same)

Thanks in advance.

Kind regards,

Miles

Miles, do you have multiple data sources involved here?

What appears to be happening is that the server detects a remote data source for the intermediate dimension and has to use remote queries to join the results together.

Some choices would be:

- Avoid the need for the remote query if possible (this would improve performance). If all your data is really coming from the same data source, then maybe something has just been improperly configured and is incorrectly causing a remote query.

- Create a linked server between the SQL Server and the Oracle server and then point to the SQL Server as the source of the tables in the DSV. This would mean credentials from the SQL Server to the Oracle server would be saved as part of the linked server.

- Change the referenced dimension to be unmaterialized (there is a check box in the dimension usage dialog for referenced dimensions). This would mean that query performance might be impacted because the join between the two dimensions would have to be done at query time instead of during processing.

- Perhaps you can change the table in the DSV to look like SELECT * FROM "IAL"."SALES_PART"

HTH,

Akshai

|||

Hi Akshai,

You're absolutely right that there were 2 data sources involved. The eventual solution was to make the primary data source for my DSV the oracle one, and then it all started behaving itself properly. As you rightly pointed out, it was doing an unnecessary remote query, tunnelling the requests to Oracle through SQL server leading to all kinds of problems. When the queries go directly to oracle, it all works fine. Thanks for the reply :)

Kind regards,

Miles

error when using "Referenced" dimension usage against an oracle data source

Hi All,

I've built a cube using a data source and DSV that point to an Oracle 10g database. I am able to process this cube so long as the dimensions are all related to the main fact table using regular dimension usage. The problem I'm having arises when the dimension has to have "referenced" usage through an intermediate dimension. In this case, when the cube processes, the SQL that gets generated and run looks akin to the following:

--

SELECT [CstOrds].[CstOrdsDISCOUNT0_0] AS [CstOrdsDISCOUNT0_0],[CstOrds].[CstOrdsBUY_QTY_DUE0_1] AS [CstOrdsBUY_QTY_DUE0_1],[CstOrds].[CstOrdsDESIRED_QTY0_2] AS [CstOrdsDESIRED_QTY0_2],[CstOrds].[CstOrdsQTY_ASSIGNED0_3] AS [CstOrdsQTY_ASSIGNED0_3],[CstOrds].[CstOrdsQTY_ON_ORDER0_4] AS [CstOrdsQTY_ON_ORDER0_4],[CstOrds].[CstOrdsQTY_PICKED0_5] AS [CstOrdsQTY_PICKED0_5],[CstOrds].[CstOrdsQTY_RETURNED0_6] AS [CstOrdsQTY_RETURNED0_6],[CstOrds].[CstOrdsQTY_SHORT0_7] AS [CstOrdsQTY_SHORT0_7],[CstOrds].[CstOrdsQTY_TO_SHIP0_8] AS [CstOrdsQTY_TO_SHIP0_8],[CstOrds].[CstOrdsQTY_SHIPPED0_9] AS [CstOrdsQTY_SHIPPED0_9],[CstOrds].[CstOrdsQTY_SHIPDIFF0_10] AS [CstOrdsQTY_SHIPDIFF0_10],[CstOrds].[CstOrdsQTY_INVOICED0_11] AS [CstOrdsQTY_INVOICED0_11],[CstOrds].[CstOrdsCOST0_12] AS [CstOrdsCOST0_12],[CstOrds].[CstOrdsNET_AMOUNT0_13] AS [CstOrdsNET_AMOUNT0_13],[CstOrds].[CstOrdsGROSS_AMOUNT0_14] AS [CstOrdsGROSS_AMOUNT0_14],[CstOrds].[CstOrdsSPTA0_15] AS [CstOrdsSPTA0_15],[CstOrds].[CstOrdsTCA0_16] AS [CstOrdsTCA0_16],[CstOrds].[CstOrds0_17] AS [CstOrds0_17],[CstOrds].[CstOrdsCUSTOMER_NO0_18] AS [CstOrdsCUSTOMER_NO0_18],[CstOrds].[CstOrdsINVOICE_DATE0_19] AS [CstOrdsINVOICE_DATE0_19],[CstOrds].[CstOrdsREAL_SHIP_DATE0_20] AS [CstOrdsREAL_SHIP_DATE0_20],[CstOrds].[CstOrdsPDDNT0_21] AS [CstOrdsPDDNT0_21],[CstOrds].[CstOrdsDENT0_22] AS [CstOrdsDENT0_22],[CstOrds].[CstOrdsORDER_NO0_23] AS [CstOrdsORDER_NO0_23],[CstOrds].[CstOrdsLINE_NO0_24] AS [CstOrdsLINE_NO0_24],[CstOrds].[CstOrdsREL_NO0_25] AS [CstOrdsREL_NO0_25],[CstOrds].[CstOrdsCATALOG_NO0_26] AS [CstOrdsCATALOG_NO0_26],[sPart_8].[PART_NO] AS [sPartPART_NO2_0]

FROM

OPENROWSET

(

N'OraOLEDB.Oracle',

N'detest';N'bi';N'',

N'

SELECT "DISCOUNT" "CstOrdsDISCOUNT0_0","BUY_QTY_DUE" "CstOrdsBUY_QTY_DUE0_1","DESIRED_QTY" "CstOrdsDESIRED_QTY0_2","QTY_ASSIGNED" "CstOrdsQTY_ASSIGNED0_3","QTY_ON_ORDER" "CstOrdsQTY_ON_ORDER0_4","QTY_PICKED" "CstOrdsQTY_PICKED0_5","QTY_RETURNED" "CstOrdsQTY_RETURNED0_6","QTY_SHORT" "CstOrdsQTY_SHORT0_7","QTY_TO_SHIP" "CstOrdsQTY_TO_SHIP0_8","QTY_SHIPPED" "CstOrdsQTY_SHIPPED0_9","QTY_SHIPDIFF" "CstOrdsQTY_SHIPDIFF0_10","QTY_INVOICED" "CstOrdsQTY_INVOICED0_11","COST" "CstOrdsCOST0_12","NET_AMOUNT" "CstOrdsNET_AMOUNT0_13","GROSS_AMOUNT" "CstOrdsGROSS_AMOUNT0_14","SALES_PRICE_TOTAL_API" "CstOrdsSPTA0_15","TOTAL_COST_API" "CstOrdsTCA0_16",1 "CstOrds0_17","CUSTOMER_NO" "CstOrdsCUSTOMER_NO0_18","INVOICE_DATE" "CstOrdsINVOICE_DATE0_19","REAL_SHIP_DATE" "CstOrdsREAL_SHIP_DATE0_20","PROMISED_DELIVERY_DATE_NO_TIME" "CstOrdsPDDNT0_21","DATE_ENTERED_NO_TIME" "CstOrdsDENT0_22","ORDER_NO" "CstOrdsORDER_NO0_23","LINE_NO" "CstOrdsLINE_NO0_24","REL_NO" "CstOrdsREL_NO0_25","CATALOG_NO" "CstOrdsCATALOG_NO0_26"

FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"'

)

AS [CstOrds],

OPENROWSET

(

N'OraOLEDB.Oracle',

N'detest';N'bi';N'',

N'"IAL"."SALES_PART"'

)

AS [sPart_8]

WHERE

(

(

[CstOrds].[CstOrdsCATALOG_NO0_26] = [sPart_8].[CATALOG_NO]

)

)

The error that is returned when I run the query from SQL Server Management Studio is as follows:

Msg 7357, Level 16, State 2, Line 1

Cannot process the object ""IAL"."SALES_PART"". The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Note that it seems to attempt to query the table behind the referenced dimension (SALES_PART) by simply opening the table, rather than selecting something specific from it. Unfortunately this doesn't work when processing the cube, nor does it work when I paste the same query into SQL Server Management Studio. However if I modify it a little to use

N'select * from "IAL"."SALES_PART"'

instead of just

N'"IAL"."SALES_PART"'

then it runs no problem. Is there any way to control the SQL that Analysis services produces to process the cube, or is there some other way to resolve this problem so I can include dimensions with "referenced" usage? (Note that I've tried with both the Microsoft and the Oracle OLEDB providers, in both cases the behaviour & error were the same)

Thanks in advance.

Kind regards,

Miles

Miles, do you have multiple data sources involved here?

What appears to be happening is that the server detects a remote data source for the intermediate dimension and has to use remote queries to join the results together.

Some choices would be:

- Avoid the need for the remote query if possible (this would improve performance). If all your data is really coming from the same data source, then maybe something has just been improperly configured and is incorrectly causing a remote query.

- Create a linked server between the SQL Server and the Oracle server and then point to the SQL Server as the source of the tables in the DSV. This would mean credentials from the SQL Server to the Oracle server would be saved as part of the linked server.

- Change the referenced dimension to be unmaterialized (there is a check box in the dimension usage dialog for referenced dimensions). This would mean that query performance might be impacted because the join between the two dimensions would have to be done at query time instead of during processing.

- Perhaps you can change the table in the DSV to look like SELECT * FROM "IAL"."SALES_PART"

HTH,

Akshai

|||

Hi Akshai,

You're absolutely right that there were 2 data sources involved. The eventual solution was to make the primary data source for my DSV the oracle one, and then it all started behaving itself properly. As you rightly pointed out, it was doing an unnecessary remote query, tunnelling the requests to Oracle through SQL server leading to all kinds of problems. When the queries go directly to oracle, it all works fine. Thanks for the reply :)

Kind regards,

Miles

Error when trying to process cube after SP2

Hi Guys

Seems a nasty error started to appear after I installed SP2 on the corporate server and my machine and then trying to process a cube. Process goes through fine, but when I try to browse it a following error pops up. If anyone can help me with this, that would be great.

Parser: The end of the input was reached. (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Discover(String requestType, String requestNamespace, IDictionary restrictions, InlineErrorHandlingType inlineErrorHandling)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, IDictionary adomdRestrictions, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, AdomdRestrictionCollection restrictions, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, AdomdRestrictionCollection restrictions)
at Microsoft.AnalysisServices.Browse.CubeBrowser.BrowsableObjectsManager.Refresh()
at Microsoft.AnalysisServices.Browse.CubeBrowser.BrowsableObjectsManager.UpdateComboBox()
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.ReconnectCanFail()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)

Thank you

Found a problem: the default measures were not set up in perspectives. Hmm... I thought the first one will be selected automatically.

Error when Deploying Cube

Hey guys,

Have a really strange error, I have a AS Cube designed and ready to go. Have been deploying it previosly and was erroring out due to attributes not being found in the tables. So ive worked on fixing all of them and now, i have an error message occuring and all it says is:

Server: The Operation has been cancelled.

I havnt cancelled anything, it doesnt show any more details for the error either.. Is there a log file somewhere that i can look further into?

Or has anyone had this message before? I am stumpped and now its annoying me as i was so close to having my first cube up and deployed...

Thanks in advance

Scotty

If you have made some changes in the source DB, you can try re creating the Data source and data source view and processing the dimensions and the cube after that.

|||

Are you receiving the error when you build and deploy the cube? Or does the error happenwhen you process the cube *after* deploying. If you have the Processing Options set to "default", then your cube will automatically process after each deployment.

If Processing Options are set to "default", then first set this to "do not process" (Right click on Project name to find this).

Then go into the Data Source View tab and right click anywhere in the diagram pane and select "Refresh...." This will perform some comparisons between your DSV and your relational database.

At this point, you can start processing your cube. What I would do at this point is select all the dimensions and process them first. If you see any errors, look at the error messages and backtrack until they are all corrected. Keep in mind that because your dimensions are being processed in parallel any error will cause any dimensions that are still being processed to stop and they will display a "canceled" message.

One you get the dimensions processed then start processing the partitions.

This is somewhere tedius, but if you haven't been keeping the relational database and your cube structure in synch, you'll have to take the time to do it now.

Hope this helps,

Pam

|||

Thanks Pam, I am receiving the error when i am deploying the cube. Building the cube works fine.

I will go through your instructions and see if it works. i am thinking you are right as i have been changing one particular table,. keeping all the columns names the same, but the problem started happening after i did this..

Ill let you know how i go in a couple of minutes..

Once again,,thanks.. this place in invaluable for advice!!

|||

Pam,

I went through and did what you suggested. Everything processed correctly to i go to the partition and tried to process that. This is where i got the message : Server: The Operation has Been Cancelled.

Any ideas on this? Should i try to create a new partition? if so, can i base if of the old one? I havnt done much with partitions yet as im pretty new to SSAS but im willing to read up on it if need be..

Thanks

Scotty

|||

hi guys,

Even i have error while deploying the cube

Error is as follows:

Error 1 The project could not be deployed to the "Server name" server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

but the server was working fine..

i didnt get what excatly was the error

could any help me out

lalitha

|||

Hi
I am getting the same error while running the MDX. The query uses subcube. Query runs ok when there is less data BUT it gives me error "Server: The operation has been cancelled." there is large amount of data.
Thanks in advance!
Rakesh

|||Looks like its a perfomance issue. long-running queries can lock the system as a whole. |||When I highlight a few partitions and start processing, the process occasionally stops with a message that operation has been canceled, like this:

Response 3
Server: the operation has been cancelled.
Response 4

Server: the operation has been cancelled.

Response 5
Server: the operation has been cancelled.

..etc...

(no further error message details are provided)
SQL profiler shows that batch was completed (but rowcount shown in process progress log is too small).
Analysis Services profiler shows no messages at that time at all. It just shows messages when it started, and when I restarted the processing.

This is an occasional error and sometime occurs within 15-20 minutes from starting to process. It could fail on 1st partition in the process list , or on some partition in the middle. Some partitions might run for a few hours and not error out, but sometimes it fails quickly.

Also looks like the profiler (engine and ssas) are not working correctly - they are skipping messages (not capturing or not showing). I do not have any filters on the Profiler.

Error when Deploying Cube

Hey guys,

Have a really strange error, I have a AS Cube designed and ready to go. Have been deploying it previosly and was erroring out due to attributes not being found in the tables. So ive worked on fixing all of them and now, i have an error message occuring and all it says is:

Server: The Operation has been cancelled.

I havnt cancelled anything, it doesnt show any more details for the error either.. Is there a log file somewhere that i can look further into?

Or has anyone had this message before? I am stumpped and now its annoying me as i was so close to having my first cube up and deployed...

Thanks in advance

Scotty

If you have made some changes in the source DB, you can try re creating the Data source and data source view and processing the dimensions and the cube after that.

|||

Are you receiving the error when you build and deploy the cube? Or does the error happenwhen you process the cube *after* deploying. If you have the Processing Options set to "default", then your cube will automatically process after each deployment.

If Processing Options are set to "default", then first set this to "do not process" (Right click on Project name to find this).

Then go into the Data Source View tab and right click anywhere in the diagram pane and select "Refresh...." This will perform some comparisons between your DSV and your relational database.

At this point, you can start processing your cube. What I would do at this point is select all the dimensions and process them first. If you see any errors, look at the error messages and backtrack until they are all corrected. Keep in mind that because your dimensions are being processed in parallel any error will cause any dimensions that are still being processed to stop and they will display a "canceled" message.

One you get the dimensions processed then start processing the partitions.

This is somewhere tedius, but if you haven't been keeping the relational database and your cube structure in synch, you'll have to take the time to do it now.

Hope this helps,

Pam

|||

Thanks Pam, I am receiving the error when i am deploying the cube. Building the cube works fine.

I will go through your instructions and see if it works. i am thinking you are right as i have been changing one particular table,. keeping all the columns names the same, but the problem started happening after i did this..

Ill let you know how i go in a couple of minutes..

Once again,,thanks.. this place in invaluable for advice!!

|||

Pam,

I went through and did what you suggested. Everything processed correctly to i go to the partition and tried to process that. This is where i got the message : Server: The Operation has Been Cancelled.

Any ideas on this? Should i try to create a new partition? if so, can i base if of the old one? I havnt done much with partitions yet as im pretty new to SSAS but im willing to read up on it if need be..

Thanks

Scotty

|||

hi guys,

Even i have error while deploying the cube

Error is as follows:

Error 1 The project could not be deployed to the "Server name" server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

but the server was working fine..

i didnt get what excatly was the error

could any help me out

lalitha

|||

Hi
I am getting the same error while running the MDX. The query uses subcube. Query runs ok when there is less data BUT it gives me error "Server: The operation has been cancelled." there is large amount of data.
Thanks in advance!
Rakesh

|||Looks like its a perfomance issue. long-running queries can lock the system as a whole. |||When I highlight a few partitions and start processing, the process occasionally stops with a message that operation has been canceled, like this:

Response 3
Server: the operation has been cancelled.
Response 4

Server: the operation has been cancelled.

Response 5
Server: the operation has been cancelled.

..etc...

(no further error message details are provided)
SQL profiler shows that batch was completed (but rowcount shown in process progress log is too small).
Analysis Services profiler shows no messages at that time at all. It just shows messages when it started, and when I restarted the processing.

This is an occasional error and sometime occurs within 15-20 minutes from starting to process. It could fail on 1st partition in the process list , or on some partition in the middle. Some partitions might run for a few hours and not error out, but sometimes it fails quickly.

Also looks like the profiler (engine and ssas) are not working correctly - they are skipping messages (not capturing or not showing). I do not have any filters on the Profiler.

Error when Deploying Cube

Hey guys,

Have a really strange error, I have a AS Cube designed and ready to go. Have been deploying it previosly and was erroring out due to attributes not being found in the tables. So ive worked on fixing all of them and now, i have an error message occuring and all it says is:

Server: The Operation has been cancelled.

I havnt cancelled anything, it doesnt show any more details for the error either.. Is there a log file somewhere that i can look further into?

Or has anyone had this message before? I am stumpped and now its annoying me as i was so close to having my first cube up and deployed...

Thanks in advance

Scotty

If you have made some changes in the source DB, you can try re creating the Data source and data source view and processing the dimensions and the cube after that.

|||

Are you receiving the error when you build and deploy the cube? Or does the error happenwhen you process the cube *after* deploying. If you have the Processing Options set to "default", then your cube will automatically process after each deployment.

If Processing Options are set to "default", then first set this to "do not process" (Right click on Project name to find this).

Then go into the Data Source View tab and right click anywhere in the diagram pane and select "Refresh...." This will perform some comparisons between your DSV and your relational database.

At this point, you can start processing your cube. What I would do at this point is select all the dimensions and process them first. If you see any errors, look at the error messages and backtrack until they are all corrected. Keep in mind that because your dimensions are being processed in parallel any error will cause any dimensions that are still being processed to stop and they will display a "canceled" message.

One you get the dimensions processed then start processing the partitions.

This is somewhere tedius, but if you haven't been keeping the relational database and your cube structure in synch, you'll have to take the time to do it now.

Hope this helps,

Pam

|||

Thanks Pam, I am receiving the error when i am deploying the cube. Building the cube works fine.

I will go through your instructions and see if it works. i am thinking you are right as i have been changing one particular table,. keeping all the columns names the same, but the problem started happening after i did this..

Ill let you know how i go in a couple of minutes..

Once again,,thanks.. this place in invaluable for advice!!

|||

Pam,

I went through and did what you suggested. Everything processed correctly to i go to the partition and tried to process that. This is where i got the message : Server: The Operation has Been Cancelled.

Any ideas on this? Should i try to create a new partition? if so, can i base if of the old one? I havnt done much with partitions yet as im pretty new to SSAS but im willing to read up on it if need be..

Thanks

Scotty

|||

hi guys,

Even i have error while deploying the cube

Error is as follows:

Error 1 The project could not be deployed to the "Server name" server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

but the server was working fine..

i didnt get what excatly was the error

could any help me out

lalitha

|||

Hi
I am getting the same error while running the MDX. The query uses subcube. Query runs ok when there is less data BUT it gives me error "Server: The operation has been cancelled." there is large amount of data.
Thanks in advance!
Rakesh

|||Looks like its a perfomance issue. long-running queries can lock the system as a whole. |||When I highlight a few partitions and start processing, the process occasionally stops with a message that operation has been canceled, like this:

Response 3
Server: the operation has been cancelled.
Response 4

Server: the operation has been cancelled.

Response 5
Server: the operation has been cancelled.

..etc...

(no further error message details are provided)
SQL profiler shows that batch was completed (but rowcount shown in process progress log is too small).
Analysis Services profiler shows no messages at that time at all. It just shows messages when it started, and when I restarted the processing.

This is an occasional error and sometime occurs within 15-20 minutes from starting to process. It could fail on 1st partition in the process list , or on some partition in the middle. Some partitions might run for a few hours and not error out, but sometimes it fails quickly.

Also looks like the profiler (engine and ssas) are not working correctly - they are skipping messages (not capturing or not showing). I do not have any filters on the Profiler.

Error when browsing the cube

I installed Analysis Services and then I installed
SP3 for SqlServer2k and
SP3 for Analysis Services
But I am still getting an error when browsing the cube
Errors like
"Provider not found. not properly installed
and then
"Unspecified error"
and some times
"Unable to connect to the registry on the server you are not a member of olap administartors group on this server"
Any help/suggestions?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
We create a single user/group, add the user/group to the olap admin
group via Control Pannel-->Admin Tools-->Comp Mgmt-->Local Users and
Groups-->Olap Administrators
SqlJunkies User wrote:
> *I installed Analysis Services and then I installed
> SP3 for SqlServer2k and
> SP3 for Analysis Services
> But I am still getting an error when browsing the cube
> Errors like
> "Provider not found. not properly installed
> and then
> "Unspecified error"
> and some times
> "Unable to connect to the registry on the server you are not a member
> of olap administartors group on this server"
> Any help/suggestions?
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
> engine supports Post Alerts, Ratings, and Searching. *
RaiderD
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message479867.html

Error when browsing the cube

I installed Analysis Services and then I installed
SP3 for SqlServer2k and
SP3 for Analysis Services
But I am still getting an error when browsing the cube
Errors like
"Provider not found. not properly installed
and then
"Unspecified error"
and some times
"Unable to connect to the registry on the server you are not a member of ola
p administartors group on this server"
Any help/suggestions?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.We create a single user/group, add the user/group to the olap admin group vi
a Control Pannel-->Admin Tools-->Comp Mgmt-->Local Users and Groups-->Olap A
dministrators

quote:

---

Originally posted by SqlJunkies User
I installed Analysis Services and then I installed
SP3 for SqlServer2k and
SP3 for Analysis Services
But I am still getting an error when browsing the cube
Errors like
"Provider not found. not properly installed
and then
"Unspecified error"
and some times
"Unable to connect to the registry on the server you are not a member of ola
p administartors group on this server"
Any help/suggestions?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post A
lerts, Ratings, and Searching.

---

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error when browsing processed Cube

Hi,

I get an error after making calculation changes to a cube.The AS solution builds and processes fine, but when I browse to the region of the cube where the MDX change has impacted I get the following error: "An Error 0xE0040200 occurred. No Further information was provided." The popup box is titled as: Microsoft Pivottable List. I've got this same error on my PC running the September CTP, and a development server running the RTM.

i.e. if I change the following working piece of MDX in a calc

Aggregate(

{ [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc BSC Time Dim Calculations].DefaultMember } * PeriodsToDate( [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember.Level, [BSC Time Dim].[YearID - HalfYearDesc - QuarterDesc - MonthDesc].CurrentMember )

)

to, for example:

555

I get this error.

Any idea of what this could be?

Thank you.

I also get this error when the browser shows a three level time dimension (Year / Period / Week) on the rows and a Cumulative dimension with three members (Total / YTD / PTD) on the columns. The common denominator with the original error reported is the use of the PeriodsToDate function which is used in the two calculated members YTD and PTD shown below:

CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[YTD]
AS Sum(PeriodstoDate([d Time].[Time].[Year],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[d Special].[Cumulative].[PTD]
AS Sum(PeriodstoDate([d Time].[Time].[Period],[d Time].[Time].CurrentMember)
,[d Special].[Cumulative].&[Total]),
VISIBLE = 1;

The problem goes away when I drag the Year level out of the browser just leaving Period and Week.

There appears to be nothing wrong with the cube since I can view it through Panorama Novaview with no problem. The view that causes the error in the Visual Studio browser can be produced without error.

|||

Hello, I am getting the same error. Did you find the workaround or fix for this.

Thanks.

|||

Hi,

I was also getting this error when using the Visual Studio SQL2005 Ananlysis Service project cube browser. But when I use MDX query in Microsoft SQL Server Management Studio and then browse the given cube and caluclated members and hand write the code in I don't get the error.

I think it may be a bug with the 2005 browse tool and calculated members.

hope this helps

Cheers

Tamzyn

|||I also get this error after installing the SP2 CTP. The error seems to come when using writeback tables and custom rollup formulas.|||

was receiving the same error message. 0xE0040200

was able to resolve the problem so that my calculations would appear.

removed the dimension that contained my named calculates from the cube under cube structure.

readded the dimension but did not select materialize (figured I could do this while in development)

reprocessed the cube.

worked

|||I tried removing the dimension from the cube and then readded it without materialize. Makes no difference at all, I still get the same error.

Error viewing cube from Excel

When attempting to connect to an OLAP cube from excel. The Multidimensional Connection returns the error: "Unable to open connection. Cannot connect to the server 'myServername'. The server is either not started or too busy." Can someone please help to resolve this?

Take a look at the connection troubleshooting guide here: http://www.sqljunkies.com/WebLog/edwardm/

Another suggestion, try and repair your office installation by going through Add/Remove programs in Control panel.

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

Error using neural network mining model viewer

I've successfully created and processed a very simple neural network mining model (defined against a cube). However, when I go to the model viewer in BI studio, it displays the following error:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format.."

Any ideas about what's going wrong? This is with SQL Server 2005 SP1.

Hello,

Some issues with the neural network viewer were fixed for Service Pack 2. You can download the latest technology preview (CTP2) for SP2 from here http://www.microsoft.com/downloads/details.aspx?FamilyID=7A9AD90F-7F95-4369-A206-E84053D63FD3&displaylang=en

If this is not an option, or it does not solve the problem with your model, could you provide a few details about the mining models? (columns, their content type , data type).

thanks

|||Going to the SP2 beta a bit problematic for us right now. Any idea when it's suppose to RTM?

I've been playing around with different variations of the mining structure - mostly super-simple structures (I'm just dipping my toes in the DM waters here). The results seem to vary. For example, a two attribute structure - a text key column and a double continuous Predict column results in the error

"Internal error: An unexpected exception occured.
Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Object reference not set to an instance of an object.."

I then added a nested table as an input (a text key column), and after that get a different error:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format.."

Processing the model and doing prediction queries seems to work OK, but the Mining Model Viewer is just completely broken.|||SP2 is scheduled for release in Q1 '07. If this becomes a blocking issue for you, please contact support (start at support.microsoft.com)

error using Analysis Sevices in Web

Hi, i have question when i'm publishig a web page with a cube inside, the component displays the next error:

the html element at line 1, column6 (namespache) cannot appear under

then i log on into my server to the analysys Services and i can browse my cube without any problems, but the page just cannot show it, any suggestions?

thnks!!!

right... i should've been more especific. the problem was the conexion..i'm usign a web service to achieve this.. somehow.. this component caused some errors in the server...so i added a new one and changed the mappings in the web page...this solved the problem correctly

thanks anyway.. hope this helps to some of you!!

Error trying to create cube partition

I've converted 4 AS2000 databases to AS2005. In just one of these databases, I am not able to add any partitions to any of the cubes. As soon as I click the "New Partition" link, get the following error:

The wizard will close because of the following error: An item with the same key has already been added (mscorlib).

I get this error if I try to create a parition from either Visual Studio 2005 or SQL Server Management Studio. AS2005 version is 9.00.2047.00. I can add paritions to cubes in other databases on the same server.

I've been all over looking for help on this, with no luck at all.

If anyone can help me, I'd sure appreciate it.

Most likely something went wrong with the database during migration.

It is possible your database had some problems even before migrated and then migration wizard created AS2005 database with the wrong structure.

It is hard to say what exactly you are having. First go through your AS2000 database structure and make sure it is all valid. Make sure you got the latest AS2000 service pack.

Then migrate your database again and try creating a new AS2005 project based on migrated database. You do that by going through new project wizard and selecting "Import Analysis Ser...."

Go through your new database, open every object in editor and make sure there are no errors and all the object relationships are set right. One area is to pay attention is Dimensions Usage tab in cube editor.

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

|||

I've checked the AS2000 datbase structures and it's all valid: cubes, dimenions, security roles, partitions, data. Everything there checks out from Analysis Manager as well as from our front-end application that uses OWC to connect to these cubes. The latest service pack is installed.

I have checked all of the objects in the AS2005 databases. There are no errors, and the cubes function properly.

I could migrate the database again, but it's got over 150 cubes, and over 400 seucirty roles. I really hate to do it unless I absolutely have to. Thanks for your suggestion...it may be my last resort.

Error that has nothing to do with my cube

I right click on my AS cube and click "Process". It tells me the server content is out of date would I like to build and deploy first. i say yes.

I get this error

Error 1 File system error: The following file is corrupted: Physical file: \\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Dim Product.0.dim\1.Start Date.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Product dimension, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Dim Product.1.dim.xml'. Errors in the metadata manager. An error occurred when loading the Adventure Works cube, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Adventure Works DW.2.cub.xml'.
This file has nothing to do with my database/project and is not even in the solution. Why is my deploy getting failed due to this file?
AllanDear Allan,

I have same problems, anybody out there with an solution?

Regards
Michael|||Michael

Hast du etwas fruher getan? Ich hatte das AS beispiel Projekt aber in eine verschiedene loesung eingesetzt. Vielleich gibt's etwas kaputt?

Have you done anything earlier? I had deployed the sample AS project but in a different solution. Perhaps there is something broken?
Allan

|||Allan,

I added befor a second fact table, but even when I deleted the second one I can no longer deploy the solution.

Michael|||Try to clean up your data directory and re-deploy your solution.
You data directory by default is under C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Data.

It would be also very helpful if you can post exact steps leading to the situation you describe.
Edward Melomed (MSFT)

---

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

Besides my data Directory for OLAP being in MSSQL.2 this works. i had already done this in fact. What I would like to hear though is why this happens.

Why does this happen?

Any way to stop it from happening again?

Thanks for anything you can offer.

Allan|||

One of the files in the data folder got corrupted and the error you are seeing indicates that Analysis Server was unable to load it.

Now, about how did that happen: the hope is that you would provide series of steps on how did you get in such situation :)

We are looking into every instance of such corruption, it usually indicates bug in the product, and in order to fix the problem we need to be able to reproduce it.

Edward Melomed (MSFT)

---

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

|||

I'm getting this same error, have cleaned up my data directory, and still it is not resolved. Any other ideas?

|||

Hi,

I am getting the same error. I will try to give as much info regarding this issue as possible. Hopefully it will help.

Since I am new to Analysis, I was practicing with the BI tool by setting up different dsvs, cubes, mining structures, etc. Most of them through the wizard. Every time I reached a level that I knew I was basically wrong and things were not working I would delete the previously generated output from the wizard. I started noticing this error, after I had deleted a two mining structures (outputs of wizard), followed by deleting I believe the dimensions first and then the cube (all related). I was working on another dsv and setting up the dimension and fact tables and everything seemed fine, I generated the cube fine as well. But processing the new cube results in this error. At the time I was working in the same soln. I started a new soln -> same error. Reregistered msolap90.dll same error. deleted the xml that seemed to be causing the problem and seemed to be corrupt (I did find it in the path specified) -> still the same error. Seems like I am stuck and can't do anything any more with analysis services. I was hoping that there was a soln for this problem online, but I guess short of reinstalling there is none.

If there is anything I would love to know, thanks,

Amadelle

|||

I had similar errors but they referred to the correct project but said they could not find a certail dim.xml file.

I refreshed the data source views

I right clicked the database and selected validate database. That seemed to clean up the problem.

the error has reoccurred when I try to process the cube, which has a legitmate error (missing filed).

However, the dimensions can no longer be processed.

"file system error. The following error occurred during a file operation. The system cannot find the file specified. \\?\C:Program files\Microsoft Sql Server\MSSQL.2\OLAP\Data\Touch.45.db\Project4447005.dim.xml"

|||

I had a similar problem.

1. I restored a database from an .abf file. The database was now visible in SQL Server Management Studio.

2. I was unable to browse or view properties for any cube, not just the cubes in the restored database. I was also unable to delete the restored database.

3. I stopped the Analysis Services service (not sure whether this was necessary)

4. I deleted the folder in my data directory (under mssql.2) corresponding to the restored .abf

5. I re-started the service

6. I was able to surf my other cubes

7. I was also able to delete the folder in Analysis Services for the corrupt database.

I'm piling on to an old post. Has this been fixed yet?

|||

I have a similar problem, and I could not fix it by deleting the folder in data directory and restarting the service.

I have AS database witch works on my computer. When I tried to restore it on another computer I received:

"Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\BudOlap07.45.db\Act List1.16.dim.xml"

I’ve deleted the folder in data directory and restart the service, but I could not still use it.

I didn’t see no cubes and dimensions in it.

I appreciate any help!

|||We had this same problem. As soon as we upgraded the target machine to SP2, the problem went away.