Thursday, July 28, 2011

PRO-ACTIVE CACHING IN SSAS

Proactive Caching is a feature in SSAS that allows one to specify when to process a measure group partition or dimension as the data in the relational data source changes.


SSAS supports three storage modes:
• MOLAP - stores detailed data and aggregations in a compressed, proprietary format; i.e. a complete copy of the data is made but query performance is excellent
• HOLAP - stores aggregations same as MOLAP, detailed data is accessed as required from the relational data source
• ROLAP - accesses detailed data and aggregations from the relational data source
Note that with MOLAP or HOLAP storage, the cube becomes out of date as soon as the relational data source changes. Proactive Caching is a feature in SSAS that allows you to specify when to process a measure group partition or dimension as the data in the relational data source changes. When Proactive Caching is implemented, SSAS will handle keeping the cube up to date on its own, per the parameters you specify. The alternative to Proactive Caching is to develop an SSIS package that processes the dimensions and measure group partitions; you would execute the SSIS package periodically.
Configuring proactive caching
The proactive caching feature ensures a cube or a measure group partition or dimension reflects current data on its own. Depending on your choice of proactive caching enabled storage mode, interception of data change notification, storage location and update frequency will vary.
1. In the Cube Browser, open your cube and select the Partitions tab.
2. Expand the measure group and select the partition for which you want to enable proactive caching.
3. Click the Storage Setting link to open the Partition Storage Settings dialog box. 4. Move the Standard Setting slider to storage mode as per your requirement.
STANDARD SETTINGS:
A. MOLAP
B. Scheduled MOLAP
C. Automatic MOLAP
D. Medium Latency MOLAP
E. Low Latency MOLAP
F. Real Time HOLAP
G. Real Time ROLAP

5. The Custom Setting allows you to explicitly enable proactive caching (if you don't want to use Standard Setting), set storage mode, and notification options.
6. When you use proactive caching, there are several settings, as shown below, which you can manually tune

• Silence Interval - SSAS waits for a set amount of time after receiving a change notification to see if there are more change notifications coming. With this setting, SSAS ensures as many as possible can be incorporated in a single refresh cycle.
• Silence Override Interval - Because of Silence Interval, if there are continuous changes happening, your cube will never be processed. To override this we have the Silence Override Interval setting.
• Latency - Latency is the maximum amount of time SSAS waits to create a new MOLAP cache. Once latency has been reached, SSAS drops the existing MOLAP caches and starts making another one; during this time frame (during processing) queries are routed to relational data source.
• Update the cache periodically - This is the additional setting, which if selected, SSAS will ensure the MOLAP cube is refreshed periodically irrespective of any data change notification.
• Bring online immediately - If you select this option, queries are routed to the underlying relational data source while the MOLAP cache is being rebuilt.
• Enable ROLAP aggregations - If you select this option, indexed views are created at the relational database for aggregations.
• Apply settings to dimensions - If you select this option, the same proactive caching setting will be applied on related dimensions as well.
Proactive Caching supports three notification methods which inform SSAS that the relational data source has changed:
• SQL Server (2000 and later)
• Client Initiated
• Scheduled Polling
SQL Server notification can only be used if the relational data source is a SQL Server 2000 or later database. SQL Server raises trace events when data changes. In order to catch these trace events, SSAS must connect to the SQL Server with administrator rights. With SQL Server notifications, measure group partitions are always processed using Full Process which discards the contents of the partition and rebuilds it; dimensions are processed using Process Update which picks up inserts, updates, and deletes in the relational data source.
The client initiated notification is performed by sending a NotifyTableChange XMLA command to the SSAS server. For example an SSIS package that updates the data warehouse could use the Analysis Services Execute DDL Task to send the NotifyTableChange XMLA command to the SSAS server every time the data warehouse update processing is completed.
Scheduled polling simply queries the relational data source periodically to determine if the data has changed.

Monday, July 25, 2011

Data Extraction from SharePoint List using SSRS

Step 1: Ensure that you have a SharePoint List.

Step 2: Create new Shared Data Source and Report Server Project

Create a new Report Server Project in the Business Intelligence Development Studio (BIDS). First step of configuring the SSRS report is creating the new shared data source. Select the SharePoint List as a data source type and specify the SharePoint Site URL hosting the lists for the connection string.

On the credentials tab, specify either windows authentication or no authentication if anonymous authentication is enabled on the SharePoint Site. Please do no select any other options. None of the other options would work for the SharePoint List Web Service.

Step 3: Create a new Report.
Next steps would be creating the report specific data source and data set to configure the data for the report. Make sure Report Data pane is available in the designer.

Step 4: Create a new Report Data Source.
Using the Report Data pane, create the new report data source. Specify the Shared Data Source as a report data source.

Step 5: Create a new Report Data Set.
Using the Report Data pane, create the new data set. Specify the Report Data Source as a data source. New Query designer support for the SharePoint Lists as a data source, allows the developers (from the BIDS tool) or end users (from the Report Builder tool), browse through the SharePoint lists, select the specific list, and define the filters and parameters without knowing detailed SOAP or CAML query language. As stated earlier, one of biggest limitations of this approach is SharePoint Lists as a data source doesn’t support selecting data from the multiple lists. One way you can avoid joining multiple lists is define the list relationships and bring the additional fields along with lookup column. For more complex joins, you can create the custom web service. Custom web service requires XML as a data source and specify the SOAP command to retrieve the data from the SharePoint Web Service in the query designer.

Step 6: Validate the fields returned by the Data Set
In the Query designer, you can verify if query is valid and returns the fields.
Optionally, you can click on the fields tab on the data set to see if query returns the data fields contained by the data set.

Step 7: Design the Report and Preview the Report in BIDS
Once you have properly configured the data source, data set, and fields, you can design the report by dragging and dropping the fields on the report designer. In this scenario, we will create a simple tabular report and preview the report to make sure reports renders fine in the BIDS before publishing to the SharePoint.

Step 8: Deploy the Report to the SharePoint
You can use the BIDS or upload the RDL files to the SharePoint Document Libraries directly.

Step 9: Verify the SSRS Report in the SharePoint
You can use Report Viewer or Report Explorer web parts if SSRS is installed in the native mode to consume the SSRS reports in the SharePoint. Alternatively, you can use the SharePoint SSRS integrated mode to create and consume SSRS reports more collaboratively by enabling the SSRS report content types in the document libraries. In this scenario, we have SharePoint Integrated environment and SharePoint document library is enabled to host the SSRS reports.

Reading / Writing Data From / To SharePoint Lists Using SSIS

Pre-Requisite:
Since the Sharepoint List related SSIS components are not available in the standard SSIS toolset one has to add these dataflow components to the toolbox by adding a reference (a DLL) available at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 (Please consider the Beta version as it is the latest one).

Note: we have two sets of SharePointListAdaptersSetup.msi, one for 2005 and the other for 2008 and above versions.

How to Configure?
Once after downloading the (.msi) file. Run the executable and provide the installation path where the rest of the (.dll’s) are placed (which is nothing but the directory you used for installing the BI Components while installing SQL Server) because the editor fails to identify the components if they are installed to some other directory.
If you are not aware of the directory details, prior running the .msi, open one of your package in your SSIS project, Open Dataflow task, Right click on the tool box and select Choose Items…

Path gives you the absolute path info where the rest of the SSIS components are installed. Use the same path as the destination for your .msi. After proper installation your SharePoint list components appears in the list.

How to Read the Data from Sharepoint List:

1. Create a new connection under connection managers. Provide proper credentials.

2. Open the SharePoint List Source and select the connection you have just created.

3. Under Component Properties provide the CamlQuery, SiteListName & SiteURL.

4. Map the columns using column Mappings tab.

How to Write the Data to Sharepoint List:

1. Steps 1 & 2 (creation of new connection and using it) are same as above.

2. Select Batch Type as Modification (for Insertion & Updation) & Deletion for Deletes. Provide the SiteListName & SiteURL.

3. Map the columns using column Mappings tab. Ignore ID attribute mapping if it is an insertion case. Use it only for Updation Scenario.

Thursday, July 21, 2011

Backup and Restore in SQL Server

Backing Up and Restoring Databases in SQL Server
A copy of data that can be used to restore and recover the data is called a backup.
Microsoft SQL Server enables you to back up and restore your databases. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures.
With good backups, you can recover from many failures, such as:
• Media failure.
• User errors, for example, dropping a table by mistake.
• Hardware failures, for example, a damaged disk drive or permanent loss of a server.
• Natural disasters.
Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up database mirroring, and archiving.
Types of Backups:
• Full backup: A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
• Differential backup: A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential backup contains only the data that has changed since the differential base. Differential backups can speed up the process of making frequent backups to decrease the risk of data loss. Usually, a differential base is used by several successive differential backups. At restore time, the full backup is restored first, followed by the most recent differential backup.
• Partial backup: A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.
• Differential Partial backup: A backup that contains only the data extents that were modified since the most recent partial backup of the same set of filegroups.
• File backup: A full backup of all the data in one or more files, or filegroups.
• Differential File backups: A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.
Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup. After the first data backup, under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and the log backup includes all log records that were not backed up in a previous log backup.
NOTE: Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups. You can create a file backup of a read/write filegroup, but before you can restore the read/write file backup, you must set the filegroup to read-only and take a differential read-only file backup.
Restrictions:
• Offline Data Cannot Be Backed Up: Any backup operation that implicitly or explicitly references data that is offline fails. Typically, a log backup succeeds even if one or more data files are unavailable. However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.
Syntax:
-- Backing Up a Whole Database
BACKUP DATABASE { database_name @database_name_var }
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Backing Up Specific Files or Filegroups
BACKUP DATABASE { database_name @database_name_var }
[ ,...n ]
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Creating a Partial Backup
BACKUP DATABASE { database_name @database_name_var }
READ_WRITE_FILEGROUPS [ , [ ,...n ] ]
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL [ ,...n ] } ]
[;]

-- Backing Up the Transaction Log (full and bulk-logged recovery models)
BACKUP LOG { database_name @database_name_var }
TO [ ,...n ]
[ ] [ next-mirror-to ]
[ WITH { } [ ,...n ] ]
[;]
EXAMPLES:
Backing up a complete database
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
WITH FORMAT;
GO
Backing up the database and log
-- To permit log backups, before the full database backup, modify the database
-- to use the full recovery model.
USE master;
GO
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO
-- Create AdvWorksData and AdvWorksLog logical backup devices.
USE master
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
'Z:\SQLServerBackups\AdvWorksData.bak';
GO
EXEC sp_addumpdevice 'disk', 'AdvWorksLog',
'X:\SQLServerBackups\AdvWorksLog.bak';
GO

-- Back up the full AdventureWorks2008R2 database.
BACKUP DATABASE AdventureWorks2008R2 TO AdvWorksData;
GO
-- Back up the AdventureWorks2008R2 log.
BACKUP LOG AdventureWorks2008R2
TO AdvWorksLog;
GO
Creating a full file backup of the secondary filegroups
--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
GO
Creating a differential file backup of the secondary filegroups
--Back up the files in SalesGroup1:
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
WITH
DIFFERENTIAL
GO
RESTORE:
Restores backups taken using the BACKUP command. This command enables you to perform the following restore scenarios:
• Restore an entire database from a full database backup (a complete restore).
• Restore part of a database (a partial restore).
• Restore specific files or filegroups to a database (a file restore).
• Restore specific pages to a database (a page restore).
• Restore a transaction log onto a database (a transaction log restores).
• Revert a database to the point in time captured by a database snapshot.
Syntax:
--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name @database_name_var }
[ FROM [ ,...n ] ]
[ WITH
{
[ RECOVERY NORECOVERY STANDBY =
{standby_file_name @standby_file_name_var }
]
, [ ,...n ]
,
,
,
,
} [ ,...n ]
]
[;]

--To perform the first step of the initial restore sequence
-- of a piecemeal restore:
RESTORE DATABASE { database_name @database_name_var }
[ ,...n ]
[ FROM [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , [ ,...n ]
,
] [ ,...n ]
[;]

--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name @database_name_var }
[ ,...n ]
[ FROM [ ,...n ] ]
WITH
{
[ RECOVERY NORECOVERY ]
[ , [ ,...n ] ]
} [ ,...n ]
[;]

--To Restore Specific Pages:
RESTORE DATABASE { database_name @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , ] [ ,...n ]
[ FROM [ ,...n ] ]
WITH
NORECOVERY
[ , [ ,...n ] ]
[;]

--To Restore a Transaction Log:
RESTORE LOG { database_name @database_name_var }
[ [ ,...n ] ]
[ FROM [ ,...n ] ]
[ WITH
{
[ RECOVERY NORECOVERY STANDBY =
{standby_file_name @standby_file_name_var }
]
, [ ,...n ]
,
,
} [ ,...n ]
]
[;]

--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name
Restoring a full database
RESTORE DATABASE AdventureWorks2008R2 FROM AdventureWorks2008R2Backups

Restoring full and differential database backups

RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 9
RECOVERY;

Restoring a database using RESTART syntax

-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups WITH RESTART

Restoring a database and move files

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH NORECOVERY,
MOVE 'AdventureWorks2008R2_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.mdf',
MOVE 'AdventureWorks2008R2_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.ldf'
RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups WITH RECOVERY
Copying a database using BACKUP and RESTORE

BACKUP DATABASE AdventureWorks2008R2
TO AdventureWorks2008R2Backups ;

RESTORE FILELISTONLY
FROM AdventureWorks2008R2Backups ;

RESTORE DATABASE TestDB
FROM AdventureWorks2008R2Backups
WITH MOVE 'AdventureWorks2008R2_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks2008R2_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

Restoring to a point-in-time using STOPAT

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY;

Restoring the transaction log to a mark

USE AdventureWorks2008R2;
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product list prices';
GO

UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database and log backups are taken.
-- An error occurs in the database.
USE master
GO

RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE = 3, NORECOVERY;
GO

RESTORE LOG AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'ListPriceUpdate';

Restoring using FILE and FILEGROUP syntax

RESTORE DATABASE MyDatabase
FILE = 'MyDatabase_data_1',
FILE = 'MyDatabase_data_2',
FILEGROUP = 'new_customers'
FROM MyDatabaseBackups
WITH
FILE = 9,
NORECOVERY;
GO
-- Restore the log backups.
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 10,
NORECOVERY;
GO
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 11,
NORECOVERY;
GO
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 12,
NORECOVERY;
GO
--Recover the database:
RESTORE DATABASE MyDatabase WITH RECOVERY;
GO

Reverting from a database snapshot

USE master
RESTORE DATABASE AdventureWorks2008R2 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss1800';
GO


Backup: http://technet.microsoft.com/en-us/library/ms186865.aspx
Restore: http://technet.microsoft.com/en-us/library/ms186858.aspx