Proactive Caching

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

Alternate for sp_help ( Alt + F1)

Gives only the needful Info :

SELECTO.name AS TableName, C.Name As ColumnName,T.name AS DateType,T.max_length AS [Length], T.precision AS Precesion, T.scale AS Scale,
CASEWHEN C.is_identity = 1 THEN 'Y' ELSE 'N' END AS [Identity],
CASEWHEN C.is_nullable = 1 THEN 'Y' ELSE 'N' END AS Nullable
FROMSYS.OBJECTS O
INNERJOIN SYS.columns C ON C.object_id = O.object_id
INNERJOIN SYS.types T ON T.system_type_id = C.system_type_id
WHERETYPE = 'U' AND T.name <> 'SYSNAME'
-- AND O.Name = 'Employee'ORDER
BY O.name, C.column_id

How To Know Which tables are related ?

To Get the List of Tables Which are Related in a Database:

SELECT ParentTable.Name AS PTable, PK.Name AS PrimaryKeyForParent, FK.name AS ForeignKeyOfChild, ChildTable.name AS CTable
FROMSYS.foreign_keys FK 
INNER
JOIN SYS.OBJECTS ChildTable ON FK.parent_object_id = ChildTable.object_id
INNER JOIN SYS.OBJECTS ParentTable ON FK.referenced_object_id = ParentTable.object_id
LEFT OUTER JOIN SYS.key_constraints PK ON ParentTable.object_id = PK.parent_object_id
ORDER BY PTable

Designing Effective Aggregations in SSAS 2008

SQL Video Tutorials

http://www.sqlshare.com/videolist.aspx

SSIS Video tutorials

http://www.learnitfirst.com/Course/153/Chapter/120/SSIS-2005-Introduction.aspx

Architectural Tools

http://www.embarcadero.com/products