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

Having JPG format in SSRS export dropdown

Append the below xml emlement to reportserver.config file under element.

<Extension Name="JPG" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering">
     <OverrideNames/>
         <Name Language="en-us">JPG</Name>
     </OverrideNames>
     <Configuration>
        <DeviceInfo>
             <OutputFormat>JPEG</OutputFormat>
        </DeviceInfo>
     </Configuration>
</Extension>

How to transfer data from Analysis services using SSIS

Transfering data from SSAS to a destination table is bit tricky.

Using SSIS you can create a source connection property using OLEBD for Analysis Services source connection. But the problem with this provider is that although you would be able to parse and preview the results of the MDX query on the SQL Command window, it does not execute during run time.

There are 2 workaround for this. Use OpenQuery with Linked server to execute the MDX under a SQL Server Native provider (which has overheads and an effective solution). Or use ADO.net provider to execute MDX query by writing it to Datareader.

The below link explains the steps to transfer data using ADO.net provider.
http://bhavikmerchant.wordpress.com/2009/09/06/using-mdx-queries-in-ssis-without-openquery/

I found that the above solution only works with SQL 2005 where it has a Datareader Source data flow task.

Another workaround I found is to use the Execute SQL task to get the MDX result set and loop through the result set to insert data into destination table. Below is the screen shot of sample Control flow task with annotation.

Finding a Last 52 Weeks based on current day in MDX

WITH
SET
How to find the last 52 weeks data based on current week as the end week in MDX?

There are two ways to achieve this:

First using Lag() function and such that you can define the range as 51 weeks prior to the current week. Below is the sample MDX query to achieve this.


Last52Weeks AS
{STRTOMEMBER("[Date].[Calendar Week of Year].&[25]", CONSTRAINED).Lag(51):STRTOMEMBER("[Date].[Calendar Week of Year].&[25]", CONSTRAINED)}
SELECT {
[Measures].[Internet Sales Amount]
} ON 0,
NON EMPTY {
Last52Weeks
} ON 1
FROM [Adventure Works]


Second way of achieving this using LastPeriods() function.

SELECT Tail([Time].[Fiscal YWD].[WEEK].MEMBERS,1) ON 1
, NULL ON 0 FROM PROFITBASE
WHERE
LASTPERIODS(365,STRTOMEMBER(@CurrentDay,CONSTRAINED))

Master Data Services (SQL 2008 R2 x64)

One of the key component as part of SQL Server 2008 R2 release is Master Data Services. Master Data Services is an MDM (Master Data Management) solution to store common metadata information for the entire Organization.

The key to MDS is to load Dimension, Hierarchies and Attributes such that any app subscribing this data would have one single truth to refer to.

The Dimension data can be loaded into 4 key tables in MDS created during its installation.

1. tblSTGMembers : This stores all Leaf level data and all Consolidate data in it.

2. tblSTGAttributeMember: This stores all the attributes of a dimension.

3. tblSTGRelationShip: Stores the hierarchy information between Lead member and parent members.

Once data is loaded into these tables the MDS UI interface will need to be used to validate and publish the data.