Changing MOLAP dimensions to ROLAP

I have a working SSAS 2005 cube running against a small SS2005 database and successfully deployed so that it can be seen across the internet, and Excel 2002 or 2003 with the correct OLEDB provider installed can view the cube in a pivot table.

I want to be able to demonstrate that the cube dynamically updates when the underlying data changes.

I have therefore changed the measure partition to ROLAP, and now realise that I need to change the dimensions to ROLAP too. I have changed all except one of them successfully. Trying to change the last one causes SSAS2005 to crash during deployment, apparently with no explanation.

On some occasions, deploying the cube reports error:

Internal error: An unexpected error occurred (file 'mdhierarchy.cpp', line 3142, function 'MDUnmaterializedHierIter::Init'). 0 0

rather than killing SSAS2005.

If I try to specify Proactive Caching for this dimension, it gets very confused, because it reports it as a MOLAP dimension, when it isn't.

The dimension it is getting upset about accesses the database through a view.

Has anyone else run into this? If so, what can be done about it? It looks like I have found a bug in SSAS2005.

Have you tried installing SQL Server 2005 service pack 1 to see if you still get the same problem there? http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en

If this is not working, please try and contact Customer Support to report the problem.

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

|||

Dear Edward,

Thanks for the suggestions.

The problem has shown up after upgrading to SP1, so I will have to take up your suggestion of contacting Customer Support.

changing existind dimension datasource

Anybody knows how to change a the datasource for an existing Dimension?

I've tryed deleting them but since i've imported the cube from a diferent server, if I try to delete the dimension it is also deleted from the cubes.

thanks for the help in advance.

Hugo

Not sure if your question is for AS2000 or AS2005.

If it is AS2000, you can change the datasource connetion string. That means it will affect other objects that pointing to this datasource.

If it is AS2005, you should add a table from different datasource and create cube and dimension as usual. Steps are:

1) Create a primary datasource (where you cube is) and dsv

2) Create a secondary datasource (where your dimension is)

3) In dsv designer, select Add\Remove tables. You see the dialog which has darasource drop down box. Select the secondary datasource and add the dimension table to dsv.

4) You may want to create relationship between fact table and dimension table before you create cube and dimension. After that, just now cube wizard as usual.

Hope this helps

Ken

|||

ok. thanks Ken for the info.

My SQL is a 2k.- Thanks for your quick response. I've tried it and it works.

Changing Data Source of Existing Cube

Hi Guys,

We had existing cubes in our Analysis Server, we were required to move them on another Reporting Server which would be using Data Replicated every night to that server. Problem is now source data is divided into 2 Reporting Database Servers. Table Names/View Names are the same in all the Databases. I just want to change the data source pointing to existing Database to the new Reporting Server. Can you tell me how this can be achieved?

Regards,

Kaushal

I have the same question while i also can't find better ways

in fact I have to create cube for another time to change datasource

|||

Looks like RS-related question.

Moving to reporting services forum.

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

|||By Reporting Server, do you mean SSRS? It looks like you just need to change the connection string inside the SSAS database.

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.