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

How to check permission in SSAS by impersonating user

Question:

There is a button "Change User" in Cube Browser, we can impersonate other user in "Security Context" dialog. But in SSMS, how do I impersonate another user to test the MDX query?

Answer:

In SSMS, connect to SSAS server, type the Server name and then click button “Options”. Switch to “Additional Connection Parameters” tab, specify the user account for property “EffectiveUserName”:

EffectiveUserName="UserAccount"

You can also test the user account with below query:

WITH MEMBER MEASURES.X AS
USERNAME
SELECT MEASURES.X ON 0
FROM [Adventure Works]
 
But note: Only SSAS server administrators can use the EffectiveUserName property. Since you are still using current windows account to logon to the SSAS server, you will access to SSAS server with your own credential, and EffectiveUserName property only works with your MDX query.

INF: MDX: How to Query for the 10 Best Customers and the Top 5 Products for Each Customer

INF: MDX: How to Query for the 10 Best Customers and the Top 5 Products for Each Customer: "--The top 10 customers can be defined in a named set, using the TopCount
--function to limit the set and to return the names of the customers with
--the 10 highest Units Sales for the year 1997.

WITH SET Top10Cust AS
'TopCount( { [Customers].[Name].Members }, 10, ([Time].[1997], [Unit Sales]) )'

--Then use the Generate and CrossJoin functions in the body of the query
--to combine the Top10Cust Named Set with the top 5 products for those
--customers.

SELECT { [Time].[1997] } ON COLUMNS ,
Generate( {Top10Cust}, CrossJoin( {Customers.CurrentMember},
TopCount([Product].[Product Name].Members, 5, ([Time].[1997], [Unit Sales])))) ON ROWS
FROM [Sales]"

How find info regarding MOSS 2007 metadata

Sharepoint_config database stores metadata information related to sharepoint websites and content.  While I did my own digging through the tables i found the below SQL statement from Bert Van Landeghem's blog:

SELECT [Id]

,[ClassId]
,[ParentId]

,[Name]

,[Status]

,[Version]

,[Properties]

FROM [Sharepoint_Config].[dbo].[Objects]

WHERE properties LIKE '

BI Thoughts and Theories » Blog Archive » View Permissions for Reporting Services in SharePoint Integrated Mode

BI Thoughts and Theories » Blog Archive » View Permissions for Reporting Services in SharePoint Integrated Mode: "You simply need to ‘Publish a Major Version’ of the item in SharePoint with the context menu in the library. Once you do this the users no longer need the edit capabilities and you can follow the security here – http://msdn.microsoft.com/en-us/library/bb283148.aspx. If you don’t publish a major version then the only way the end-users will see the items is if they have the edit items permission which is something that you definitely do not want to provide them."

The Permission granted to user DOMAIN\UserName is insufficient for performing this operation.(rsAccessDenied)

Found this link and seems to be logical and interesting solution to the problem.  But I could not figure out the approval part for data sources.  Could not find the option.

Permission to reports in Sharepoint integrated mode.

Update:  The option to approve a data source appears if a subsite is created and data connection library exists under this subsite.  I am not sure why it should not show up under default website.

Sharepoint Enabling Usage Analysis and Importing data

Usage analysis in Sharepoint 2007 can be enabled by navigating to Central Admin -> Operations -> under Logging and Reporting -> Usage Analysis processing.

Check the Logging Setting's,  Enabling Logging box.
Set the time when want the log needs to be processed under Processing Settings.
Note :  The log generation job is daily job and only runs once in a day.

Once set you would need to wait for a day to get the generated log file.  The log file is generated under
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\.  The web application GUID can be extracted by connecting to Sharepoint_Config Database and running the following query.


select o.id,, o.* from objects o
inner join Classes c on c.Id = o.ClassId and c.FullName like '%SPWebApplication%'
where o.name like

If your sharepoint infrastructure on configured in a farm then each server on the farm generates its own log.  you would need to search for the web application guid folder and the folder for each day to view the log.

Use Javascript to open URL in SSRS report using action

="javascript:void(window.open('http://xxxxx/Dashboards/Supply%20Chain%20-%20Help%20Desk/Top%2010%20Analysis.aspx','_blank'))"

Access Denied error while trying to Preview SSRS Report

You might get a message "An error occurred during local report processing. Access Denied. (Exception from HRESULT: 0x80030005 (STG_E_ACCESSDENIED))" while you try to preview a report you developed under VS 2008 and which has been added to TFS or any source control.  

The reason for this is due to the .data file associated to the report which is read only state. You can resolve this issue by either checking out the .data file or unchecking the readonly attribute of the file under local folder.

Here is a nice article post by about the issue 

http://ddkonline.blogspot.com/2009/07/fix-sql-reporting-services-2008-error.html