SQL Server 2008 R2 CTP August Released!

You can find the SQL Server 2008 R2 CTP here ...
http://www.microsoft.com/downloads/details.aspx?familyid=e19689bd-38dd-46c4-8645-f58ca4d61d1f&displaylang=en

Looking for the next release of the CTP with more features surrounding BI, 2010 and Gemini for now Happy Testing with R2 :)

Updating SSAS Cube Perspective

An SSAS cube Perspective is a close cousin of our View(From Database engine) except that it cannot be looked at from the security stand point(which a traditional DB view provides). It should be used to simplify the overwhelming amount of information provided to a user(not all users need to see everything from the Warehouse).A Perspective hides\shows information like attributes\measures\calculations\KPI's.. of a cube. Thus creating a scenario where a user might request more and more information from a Warehouse as time , which is hidden based upon in functional role in his company.

Changing a Perspective should not force you to Reprocess the Cube. As this is just a pair of glassed sitting on top of the actual cube limmiting what we see. A perspective can be changed in two ways.
(1) Changing Online
Open Visual Studio and then File-->Open-->Analysis Services Database. Enter the credentials to connect to the SSAS Server, this will open up the cube on the server. Go to the Perspectives tab in the Cube Designer, add\remove attributes\measures... and then click the SAVE button on the left hand top corner. That's it .... we are done. :)
The disadvantage of this is our solution file (probably the one checked into source safe) gets out of sync. Which i hope we don't want to happen.

(2)Updating the Visual Studio Solution
Open you solution and make sure you update you Perspective as described above and then SAVE the solution. Once done got o properties of the solution and make sure when you deploy you do not process the cube. This way only changes to metadata are passed on to the server. Then right click on the solution and then say DEPLOY and you are done!

CDC Retention Period

With reference to my earlier article on restoring CDC, here is one more catch!

By default retention period is 3 days (4320 minutes). So when we restore CDC backup and then enable the job, the job tries to clean up all data from the restore which is older than 3 days. Hence we have to make sure the retention period is passed as a parameter while we re-enable the job.

Here is the piece of code which needs to be updated
EXECUTE sys.sp_cdc_add_job
@job_type = N'cleanup',
@retention = 43200; --for 30 days
GO

You can also change an exisitng CDC job using the below code
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 21600; --for 15days
GO

This information is stored in MSDB.sys.cdc_jobs

Restore a CDC Enabled Database

You might not be able to restore a CDC Enabled DB from a backup using the GUI(Management Studio). Below is the script which explicitly mentions about KEEP_CDC. Once you restore the DB, enable the cleanup and capture job and the DB should be completely up with CDC enabled.

RESTORE DATABASE Sample
FROM DISK = N'E:\SQLBackup\Sample_07232009_1058.bak'
WITH FILE = 1,
MOVE N'Sample' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV\MSSQL\DATA\Sample.mdf',
MOVE N'Sample_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV\MSSQL\DATA\Sample_1.ldf',
NOUNLOAD,
STATS = 10,
KEEP_CDC
GO

and then

USE Sample
GO
EXEC sys.sp_cdc_add_job @job_type = N'cleanup'
GO
EXEC sys.sp_cdc_add_job @job_type = N'capture'
GO

SSIS - Excel 2007 .xlsx files as source

By default Excel 2007 (.xlsx) files cannot be read by SSIS. Office 2007 uses Microsoft Access 12.0 Access Database Engine OLEDB Provider (ACE) and not Microsoft Jet 4.0 OLEDB provider. You can download and install this drive from
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

If you happen to use OLEDB Source and try to access Excel 2003 and below files using Jet 4.0 driver, by default Jet 4.0 OLEDB Provider expects .mdb file.We have to explicitly instruct Jet 4.0 to read excel files (.xls). This can be done my keying in "Excel 8.0" in the "Extended Properties" property in the "ALL" tab of the Datalinks form.



Similarly for Excel 2007, we have to use ACE 12.0 OLEDB Provider and specify "Excel 12.0" in the Extended Property