Changing DSV file name of and existing cube

One of my requirement was to create a new cube from existing cube (structure of the cube was important for me) and point it to a different Data source view (since i wanted to extend the existing DSV to accomodate additional changes while retaining the old cube as it is.).  Unfortunately the DataSourceViewID property for the cube/ dimensions are greyed out from BIDS.

So the trick is to open the Cube in Code view and search for DataSourceViewID element.  Once found change the value to new DSV file name.


<Source dwd:design-time-name="b225080e-6fcc-4a40-8a7a-7233084ee828">
    <DataSourceViewID>XXXXXX</DataSourceViewID>
  </Source >


Thats it now when you go on the UI and choose to Edit Data Source View for the cube or dimension it would take you to the new DSV file name you have associated it to.

Excel 2007 error : The cube might have been reorganized or changed on the server.

While trying to deploy a cube and opening in Excel 2007 (Pivot table). I encountered this error:

Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. 

Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube.

I checked the datasource it was all good.  I could further access the processed cube through Proclarity and SSMS.  But excel wouldnt accept it.


So I suspected that it might be one of the calculated member in my cube that might be causing this issue.  And viola there it was one of the named set created in my cube had an incorrect Hierarchy path.  I guess Proclarity and SSMS ignored this but excel was bit strict on that.  Fixed the Hierarchy path, reprocessed the cube and bang!! I am back on business.

Other reason for this as explained in MS KB articles may be due to invalid characters in attributes:

http://support.microsoft.com/kb/210806

or problem with translation setting

http://support.microsoft.com/kb/931388/en-us

In my case all these seems be ok.  The issue was the faulty named set.

Insert tid bits from SQL Pass 2011

Found this useful bit of tip from SQL Pass 2011 presentation.

create table dbo.numbers
(
Numbers int primary key
);

SELECT TOP(1000) RN = ROW_NUMBER() OVER
(ORDER BY c1.[object_id])
FROM sys.columns c1
cross join sys.columns AS c2
cross join sys.columns as c3
order by c1.[object_id]

Alternative Time Intelligence implementation reference link

Very interesting blog by Marco Russo about alternative time intelligence implementation (YTD etc).

http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx