Get Job Name of the SQL Job

DECLARE @SQL NVARCHAR(72),        @jobID UNIQUEIDENTIFIER,        @jobName SYSNAMESET     @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'EXEC    sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT

SELECT  @jobName = nameFROM    msdb..sysjobsWHERE   job_id = @jobID

Querying Cube (SSAS) Metadata Information

Internal SSAS DMV's can be used to gather MetaData information about SSAS Objects. Following are the list of DMV's which enable to query Cube, Dimension, Hierarchies, Performance information etc using SSMS.

$SYSTEM.MDSCHEMA_CUBES $SYSTEM.MDSCHEMA_DIMENSIONS $SYSTEM.MDSCHEMA_FUNCTIONS $SYSTEM.MDSCHEMA_HIERARCHIES $SYSTEM.MDSCHEMA_INPUT_DATASOURCES $SYSTEM.MDSCHEMA_KPIS $SYSTEM.MDSCHEMA_LEVELS $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS $SYSTEM.MDSCHEMA_MEASUREGROUPS $SYSTEM.MDSCHEMA_MEASURES $SYSTEM.MDSCHEMA_MEMBERS $SYSTEM.MDSCHEMA_PROPERTIES $SYSTEM.MDSCHEMA_SET

References : 

Checking Cube performance: 

http://blogs.microsoft.co.il/blogs/yanivmor/archive/2010/01/27/dmvs-for-analysis-services.aspx

Querying SSAS:

http://richardlees.blogspot.com/2010/07/querying-analysis-services-for-cube.html

http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

http://msdn.microsoft.com/en-us/library/ms126079.aspx

Debugging Filter Link in PPS Dashboard

Found this link by Nick Barclay which describes method to debug and test Filter Link parameters passed to PPS dashboard components http://nickbarclay.blogspot.com/2008/02/debugging-filter-links-with-web-page.html Further the Post Filter formula can also be evaluated by adding additional formula : For example : Descendants(<<uniquename>>,<<level>>) TOPCOUNT(<<uniquename>>,<<level>>), UNION also IIF conditions can be used.

Tool to create Insert data Scripts in SQL Server 2008

A Niffty tool SQLPubwiz.exe under %Program Files%\Microsoft SQL Server\90\Tools\Publishing\1.2 allows you to generate scripts for either a individual objects or the entire database. For table it generates insert scripts with data which is really valuable considering you need to backup master data information entered into tables.

Link a Scorecard to a Web aspx page

Assumption: The web pages bears the Sharepoint list reference through webpart and the list has been filtered using the filter condition of the webpart.

1) Add custom property to KPI in the dashboard such that the name of the custom property should be same as the QueryString name used to pass to the web page. The value can be either be hard coded or dynamic.

2) Once custom property has been added to KPI it should display in dashboard editor under KPI. Select the customer property name and drag and drop it on the web page.

3) Make sure that the display condition is set appropriately such as to show all the KPI's for which the sharepoint list values needs to be displayed.

Publish and browse the dashboard to view.

You can debug if the Query string value is being passed correctly using fidler utility.

PS: You can acheive the same using SSRS report too. If the SSRS Report points to a Sharepoint List.

Fixing corrupted SSAS Cube.

1. Stop the Analysis Services service
2. Delete everything in your C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\%YOUR_PROJECTNAME% folder
3. Restart the Analysis Services service
4. Re-deploy your project

How To : Create Parent/ Child List in Sharepoint Videos

http://www.youtube.com/watch?v=HPsk3RRVPtg&feature=related