Melick's Blog

I encountered a problem when using the slowly changing dimension (SCD) in large databases. SCD is easy and fast when it comes to less number of records. therefore I used following mechanism to  speed up the process using available SSIS components. First record set is send through Lookup command . in there we are checking that item is already available with the table. If yes we are redirecting to UPDARE Command and If not we are redirecting to Insert Command . But make sure following redirect option (Redirect rows to no match output) is set in the lookup component.

This is not a complete post. I came across a situation that I needed to show [Date] dimension values which is not empty as report parameter. But I need yeas that only have values in measure. there for I used following MDX to get that. with member yKey as [ Date ].[ Year ].currentmember.uniquename member yValue as [ Date ].[ Year ].currentmember.MEMBER_CAPTION select {yKey,yValue} on 0, { filter([ Date ].[ Year ].[ Year ],[Measures].[Total Amount]<> null ) } on 1 from [RetailigenceBI] this code result in getting

Normally  MDX queries are returning caption for reports when we are using data in SSRS. But  we need other information such as UniqueNumber and other when passing as parameters to other reports and datasets. Below shows example query used for get the member properties. (We can declare it using WITH MEMBER ). DayKey is the custom column we are creating with member DayKey as [Date].[Date]. currentmember . uniquename SELECT NON EMPTY { DayKey ,[Measures].[Sample], [Measures].[Sample2] } ON COLUMNS , NON EMPTY { [Date].[Year-Month-Date].[Date]. ALLMEMBERS } ON ROWS FROM [RetailigenceBI]

This error Occurred to me while I design the query in the Query Builder and generate the report in SSRS ( MDX). Months are showing alphabetical order (April , August,..) , but it should be January , February .. I did some background works and even follows the the thread http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/27765eda-371d-4428-ab13-2e96b2fbb6ca . But ultimately I  found there is a sorting adding by default to that column. I Deleted it .. Bingo .. It worked ..