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.