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.

Deploying WCF service to Azure CTP Platform

To deploy a WCF service (ASPnet Web Role) on Azure platform is fairly straightforward.

Once testing the solution on Development fabric, replace the &<;endpoint address="" with staging or production uri in the ServiceReferences.ClientConfig file. Publish the webrole project. This should generate application package file and configuration file to be deployed on Azure platform. Leave the port number to be default since Azure would run the webservice under port 80.

Compile error 'Cannot implicitly convert type 'System.Collections.ObjectModel.ObservableCollection'.

While trying to call a WCF service and assigning a resultant array to a local variable, you may encounter an compile error 'Cannot implicitly convert type 'System.Collections.ObjectModel.ObservableCollection'.

How to resolve this error.
below might be code you might be trying to compile

void proxy_GetUOSListCompleted(object sender, SilverScreens.SilverServiceProxy.GetUOSListCompletedEventArgs e)
{
//Here result returns a List object
SilverServiceProxy.UOS[] ResultUOS = e.Result;
UOSList.ItemsSource = ResultUOS;
}

Following are the steps to resolve it.

Right click on Service Reference in your Webapplication. In the context menu select "Configure Service Reference...", choose in the combobox of collection type: System.Array.

The other work around is to change the code to use ObservableCollection collection, since ObservableCollection also implements suitable property changed notification mechanism such as the INotifyPropertyChanged interface

void proxy_GetUOSListCompleted(object sender, SilverScreens.SilverServiceProxy.GetUOSListCompletedEventArgs e)
{
//Here result returns a List object
List&; = (List&;).Result.ToList();
UOSList.ItemsSource = ResultUOS;
}

One other observation was that while compiling the above code resulted in the compile time error that ToList() operator could not be associated. To resolve this issue, add the system.linq namespace statement.